Generate a data frame from many xml files

cderv · June 28, 2018, 6:33am

I came up with one way to use tidyverse. I changed different things. If you just want to replace lapply, use purrr::map. map_dfr does the bind_rows inside. Take care: Base R is more permissive and tidyverse less. For example, it seems you have some identical columns names in your parent and kids table. not good!

library(xml2)
library(tidyverse)

# Make a temporary file (tf) and a temporary folder (tdir)
tf <- tempfile(tmpdir = tdir <- tempdir())

## Download the zip file 
download.file("https://data.val.se/val/val2014/valnatt/valnatt.zip", tf)

## Unzip it in the temp folder
xml_files <- unzip(tf, exdir = tdir)

## Parse the 4th file in the folder (first file with municipal data reg. municipal election)
t <- read_xml(xml_files[4])

df <- xml_find_all(t, "//VALDISTRIKT") %>% 
  map_dfr(~ {
    # extract the attributes from the parent tag as a data.frame
    parent <- xml_attrs(.x) %>% enframe() %>% spread(name, value)
    # make a data.frame out of the attributes of the kids
    kids <- xml_children(.x) %>% map_dfr(~ as.list(xml_attrs(.x)))
    # combine them (bind_cols does not repeat parent rows)
    cbind.data.frame(parent, kids) %>% set_tidy_names() %>% as_tibble() 
  })
#> New names:
#> RÃ–STER -> RÃ–STER..4
#> RÃ–STER_FGVAL -> RÃ–STER_FGVAL..5
#> RÃ–STER -> RÃ–STER..8
#> RÃ–STER_FGVAL -> RÃ–STER_FGVAL..9
#> New names:
#> RÃ–STER -> RÃ–STER..4
#> RÃ–STER_FGVAL -> RÃ–STER_FGVAL..5
#> RÃ–STER -> RÃ–STER..8
#> RÃ–STER_FGVAL -> RÃ–STER_FGVAL..9
#> New names:
#> RÃ–STER -> RÃ–STER..4
#> RÃ–STER_FGVAL -> RÃ–STER_FGVAL..5
#> RÃ–STER -> RÃ–STER..8
#> RÃ–STER_FGVAL -> RÃ–STER_FGVAL..9
#> New names:
#> RÃ–STER -> RÃ–STER..4
#> RÃ–STER_FGVAL -> RÃ–STER_FGVAL..5
#> RÃ–STER -> RÃ–STER..8
#> RÃ–STER_FGVAL -> RÃ–STER_FGVAL..9
#> New names:
#> RÃ–STER -> RÃ–STER..4
#> RÃ–STER_FGVAL -> RÃ–STER_FGVAL..5
#> RÃ–STER -> RÃ–STER..8
#> RÃ–STER_FGVAL -> RÃ–STER_FGVAL..9
#> New names:
#> RÃ–STER -> RÃ–STER..4
#> RÃ–STER_FGVAL -> RÃ–STER_FGVAL..5
#> RÃ–STER -> RÃ–STER..8
#> RÃ–STER_FGVAL -> RÃ–STER_FGVAL..9
#> New names:
#> RÃ–STER -> RÃ–STER..4
#> RÃ–STER_FGVAL -> RÃ–STER_FGVAL..5
#> RÃ–STER -> RÃ–STER..8
#> RÃ–STER_FGVAL -> RÃ–STER_FGVAL..9
#> New names:
#> RÃ–STER -> RÃ–STER..4
#> RÃ–STER_FGVAL -> RÃ–STER_FGVAL..5
#> RÃ–STER -> RÃ–STER..8
#> RÃ–STER_FGVAL -> RÃ–STER_FGVAL..9
#> New names:
#> RÃ–STER -> RÃ–STER..4
#> RÃ–STER_FGVAL -> RÃ–STER_FGVAL..5
#> RÃ–STER -> RÃ–STER..8
#> RÃ–STER_FGVAL -> RÃ–STER_FGVAL..9
#> New names:
#> RÃ–STER -> RÃ–STER..4
#> RÃ–STER_FGVAL -> RÃ–STER_FGVAL..5
#> RÃ–STER -> RÃ–STER..8
#> RÃ–STER_FGVAL -> RÃ–STER_FGVAL..9
#> New names:
#> RÃ–STER -> RÃ–STER..4
#> RÃ–STER_FGVAL -> RÃ–STER_FGVAL..5
#> RÃ–STER -> RÃ–STER..8
#> RÃ–STER_FGVAL -> RÃ–STER_FGVAL..9
#> New names:
#> RÃ–STER -> RÃ–STER..4
#> RÃ–STER_FGVAL -> RÃ–STER_FGVAL..5
#> RÃ–STER -> RÃ–STER..8
#> RÃ–STER_FGVAL -> RÃ–STER_FGVAL..9
#> New names:
#> RÃ–STER -> RÃ–STER..4
#> RÃ–STER_FGVAL -> RÃ–STER_FGVAL..5
#> RÃ–STER -> RÃ–STER..8
#> RÃ–STER_FGVAL -> RÃ–STER_FGVAL..9
#> New names:
#> RÃ–STER -> RÃ–STER..4
#> RÃ–STER_FGVAL -> RÃ–STER_FGVAL..5
#> RÃ–STER -> RÃ–STER..8
#> RÃ–STER_FGVAL -> RÃ–STER_FGVAL..9
#> New names:
#> RÃ–STER -> RÃ–STER..4
#> RÃ–STER_FGVAL -> RÃ–STER_FGVAL..5
#> RÃ–STER -> RÃ–STER..8
#> RÃ–STER_FGVAL -> RÃ–STER_FGVAL..9
#> New names:
#> RÃ–STER -> RÃ–STER..4
#> RÃ–STER_FGVAL -> RÃ–STER_FGVAL..5
#> RÃ–STER -> RÃ–STER..8
#> RÃ–STER_FGVAL -> RÃ–STER_FGVAL..9
#> New names:
#> RÃ–STER -> RÃ–STER..4
#> RÃ–STER_FGVAL -> RÃ–STER_FGVAL..5
#> RÃ–STER -> RÃ–STER..8
#> RÃ–STER_FGVAL -> RÃ–STER_FGVAL..9
#> New names:
#> RÃ–STER -> RÃ–STER..4
#> RÃ–STER_FGVAL -> RÃ–STER_FGVAL..5
#> RÃ–STER -> RÃ–STER..8
#> RÃ–STER_FGVAL -> RÃ–STER_FGVAL..9
#> New names:
#> RÃ–STER -> RÃ–STER..4
#> RÃ–STER_FGVAL -> RÃ–STER_FGVAL..5
#> RÃ–STER -> RÃ–STER..8
#> RÃ–STER_FGVAL -> RÃ–STER_FGVAL..9
#> New names:
#> RÃ–STER -> RÃ–STER..4
#> RÃ–STER_FGVAL -> RÃ–STER_FGVAL..5
#> RÃ–STER -> RÃ–STER..8
#> RÃ–STER_FGVAL -> RÃ–STER_FGVAL..9
#> New names:
#> RÃ–STER -> RÃ–STER..4
#> RÃ–STER_FGVAL -> RÃ–STER_FGVAL..5
#> RÃ–STER -> RÃ–STER..8
#> RÃ–STER_FGVAL -> RÃ–STER_FGVAL..9
#> New names:
#> RÃ–STER -> RÃ–STER..4
#> RÃ–STER_FGVAL -> RÃ–STER_FGVAL..5
#> RÃ–STER -> RÃ–STER..8
#> RÃ–STER_FGVAL -> RÃ–STER_FGVAL..9
df
#> # A tibble: 330 x 17
#>    KOD    MODNR   NAMN     `RÃ–STER..4` `RÃ–STER_FGVAL.~ TID_RAPPORT PARTI
#>    <chr>  <chr>   <chr>    <chr>        <chr>            <chr>       <chr>
#>  1 01140~ 117118~ Apoteks~ 926          845              2014091422~ M    
#>  2 01140~ 117118~ Apoteks~ 926          845              2014091422~ C    
#>  3 01140~ 117118~ Apoteks~ 926          845              2014091422~ FP   
#>  4 01140~ 117118~ Apoteks~ 926          845              2014091422~ KD   
#>  5 01140~ 117118~ Apoteks~ 926          845              2014091422~ S    
#>  6 01140~ 117118~ Apoteks~ 926          845              2014091422~ V    
#>  7 01140~ 117118~ Apoteks~ 926          845              2014091422~ MP   
#>  8 01140~ 117118~ Apoteks~ 926          845              2014091422~ SD   
#>  9 01140~ 117118~ Apoteks~ 926          845              2014091422~ FI   
#> 10 01140~ 117118~ Apoteks~ 926          845              2014091422~ PP   
#> # ... with 320 more rows, and 10 more variables: `RÃ–STER..8` <chr>,
#> #   `RÃ–STER_FGVAL..9` <chr>, PROCENT <chr>, PROCENT_FGVAL <chr>,
#> #   `PROCENT_Ã„NDRING` <chr>, TEXT <chr>, `RÃ–STBERÃ„TTIGADE` <chr>,
#> #   `RÃ–STBERÃ„TTIGADE_KLARA_VALDISTRIKT_FGVAL` <chr>,
#> #   `SUMMA_RÃ–STER` <chr>, `SUMMA_RÃ–STER_FGVAL` <chr>

Created on 2018-06-28 by the reprex package (v0.2.0).

About step 2

You can use fs to manipulate files and folder and stringr to detect your string pattern. Get the list of the files in your directory and keep only those name with valnatt_ then 4 digits then the letter K, using a regex:"valnatt_\d{4}K.xml$`

library(xml2)
library(tidyverse)
#> Warning: le package 'tibble' a été compilé avec la version R 3.4.4
#> Warning: le package 'tidyr' a été compilé avec la version R 3.4.4
#> Warning: le package 'purrr' a été compilé avec la version R 3.4.4
#> Warning: le package 'dplyr' a été compilé avec la version R 3.4.4
#> Warning: le package 'stringr' a été compilé avec la version R 3.4.4

# Make a temporary file (tf) and a temporary folder (tdir)
tf <- tempfile(tmpdir = tdir <- tempdir())

## Download the zip file 
download.file("https://data.val.se/val/val2014/valnatt/valnatt.zip", tf)

## Unzip it in the temp folder
xml_files <- unzip(tf, exdir = tdir)

files_to_import <- fs::dir_ls(tdir) %>%
  str_subset(pattern = "valnatt_\\d{4}K.xml$")
head(files_to_import)
#> [1] "C:/Users/chris/AppData/Local/Temp/Rtmpo5NbU2/valnatt_0114K.xml"
#> [2] "C:/Users/chris/AppData/Local/Temp/Rtmpo5NbU2/valnatt_0115K.xml"
#> [3] "C:/Users/chris/AppData/Local/Temp/Rtmpo5NbU2/valnatt_0117K.xml"
#> [4] "C:/Users/chris/AppData/Local/Temp/Rtmpo5NbU2/valnatt_0120K.xml"
#> [5] "C:/Users/chris/AppData/Local/Temp/Rtmpo5NbU2/valnatt_0123K.xml"
#> [6] "C:/Users/chris/AppData/Local/Temp/Rtmpo5NbU2/valnatt_0125K.xml"
length(files_to_import)
#> [1] 290

Created on 2018-06-28 by the reprex package (v0.2.0).

You can then use purrr::map and friends on this vector of use fs::dir_map() but you'll less control on the result.

Hopes it helps.