On Jan 22, 2013, at 3:11 PM, Adam Gabbert wrote:
> Hello,
>
> I'm attempting to read information from an XML into a data frame in R
using
> the "XML" package. I am unable to get the data into a data frame
as I would
> like. I have some sample code below.
>
> *XML Code:*
>
> Header...
>
> Data I want in a data frame:
>
> <data>
> <row BRAND="GMC" NUM="1" YEAR="1999"
VALUE="10000" />
> <row BRAND="FORD" NUM="1" YEAR="2000"
VALUE="12000" />
> <row BRAND="GMC" NUM="1" YEAR="2001"
VALUE="12500" />
> <row BRAND="FORD" NUM="1" YEAR="2002"
VALUE="13000" />
> <row BRAND="GMC" NUM="1" YEAR="2003"
VALUE="14000" />
> <row BRAND="FORD" NUM="1" YEAR="2004"
VALUE="17000" />
> <row BRAND="GMC" NUM="1" YEAR="2005"
VALUE="15000" />
> <row BRAND="GMC" NUM="1" YEAR="1967"
VALUE="PRICLESS" />
> <row BRAND="FORD" NUM="1" YEAR="2007"
VALUE="17500" />
> <row BRAND="GMC" NUM="1" YEAR="2008"
VALUE="22000" />
> </data>
>
> *R Code:*
>
> doc< -xmlInternalTreeParse ("Sample2.xml")
> top <- xmlRoot (doc)
> xmlName (top)
> names (top)
> art <- top [["row"]]
> art
> **
> *Output:*
>
>> art<row BRAND="GMC" NUM="1"
YEAR="1999" VALUE="10000"/>
>
>
>
>
> This is where I am having difficulties. I am unable to "access"
additional
> rows; ( i.e. <row BRAND="GMC" NUM="1"
YEAR="1967" VALUE="PRICLESS" /> )
>
> and I am unable to access the individual entries to actually create the
> data frame. The data frame I would like is as follows:
>
> BRAND NUM YEAR VALUE
> GMC 1 1999 10000
> FORD 2 2000 12000
> GMC 1 2001 12500
> etc........
>
> Any help or suggestions would be appreciated. Conversly, my eventual goal
> would be to take a data frame and write it into an XML in the previously
> shown format.
>
Hi,
You are so close!
You have a number of nodes with the name 'row'. The "[["
function selects just one item from a list, and when there's a number that
have that name it returns just the first. So you really want to use the
"[" function instead and then select by order index using
"[["
library(XML)
> s <- c(" <data>", " <row
BRAND=\"GMC\" NUM=\"1\" YEAR=\"1999\"
VALUE=\"10000\" />",
" <row BRAND=\"FORD\" NUM=\"1\"
YEAR=\"2000\" VALUE=\"12000\" />",
" <row BRAND=\"GMC\" NUM=\"1\"
YEAR=\"2001\" VALUE=\"12500\" />",
" <row BRAND=\"FORD\" NUM=\"1\"
YEAR=\"2002\" VALUE=\"13000\" />",
" <row BRAND=\"GMC\" NUM=\"1\"
YEAR=\"2003\" VALUE=\"14000\" />",
" <row BRAND=\"FORD\" NUM=\"1\"
YEAR=\"2004\" VALUE=\"17000\" />",
" <row BRAND=\"GMC\" NUM=\"1\"
YEAR=\"2005\" VALUE=\"15000\" />",
" <row BRAND=\"GMC\" NUM=\"1\"
YEAR=\"1967\" VALUE=\"PRICLESS\" />",
" <row BRAND=\"FORD\" NUM=\"1\"
YEAR=\"2007\" VALUE=\"17500\" />",
" <row BRAND=\"GMC\" NUM=\"1\"
YEAR=\"2008\" VALUE=\"22000\" />",
" </data>")
> x <- xmlRoot(xmlTreeParse(s, asText = TRUE, useInternalNodes = TRUE))
> x["row"][[1]]
<row BRAND="GMC" NUM="1" YEAR="1999"
VALUE="10000"/>
> x["row"][[2]]
<row BRAND="FORD" NUM="1" YEAR="2000"
VALUE="12000"/>
Your rows are set up so the attributes have the values you want - use xmlAttrs
to retrieve them.
> xmlAttrs(x["row"][[2]])
BRAND NUM YEAR VALUE
"FORD" "1" "2000" "12000"
You can use lapply to iterate through each row and apply the xmlAttrs function.
You'll end up with a list if character vectors.
> y <- lapply(x["row"], xmlAttrs)
> str(y)
List of 10
$ row: Named chr [1:4] "GMC" "1" "1999"
"10000"
..- attr(*, "names")= chr [1:4] "BRAND" "NUM"
"YEAR" "VALUE"
$ row: Named chr [1:4] "FORD" "1" "2000"
"12000"
..- attr(*, "names")= chr [1:4] "BRAND" "NUM"
"YEAR" "VALUE"
$ row: Named chr [1:4] "GMC" "1" "2001"
"12500"
..- attr(*, "names")= chr [1:4] "BRAND" "NUM"
"YEAR" "VALUE"
.
.
.
Next make a character matrix using do.call and rbind ...
> m <- do.call(rbind, y)
> str(m)
chr [1:10, 1:4] "GMC" "FORD" "GMC"
"FORD" "GMC" "FORD" "GMC"
"GMC" "FORD" ...
- attr(*, "dimnames")=List of 2
..$ : chr [1:10] "row" "row" "row"
"row" ...
..$ : chr [1:4] "BRAND" "NUM" "YEAR"
"VALUE"
And then on to a data.frame...
> d <- as.data.frame(m)
> str(d)
'data.frame': 10 obs. of 4 variables:
$ BRAND: chr "GMC" "FORD" "GMC" "FORD"
...
$ NUM : chr "1" "1" "1" "1" ...
$ YEAR : chr "1999" "2000" "2001"
"2002" ...
$ VALUE: chr "10000" "12000" "12500"
"13000" ...
Cheers,
Ben
> Thank you
>
> AG
>
> [[alternative HTML version deleted]]
>
> ______________________________________________
> R-help at r-project.org mailing list
> https://stat.ethz.ch/mailman/listinfo/r-help
> PLEASE do read the posting guide
http://www.R-project.org/posting-guide.html
> and provide commented, minimal, self-contained, reproducible code.
Ben Tupper
Bigelow Laboratory for Ocean Sciences
180 McKown Point Rd. P.O. Box 475
West Boothbay Harbor, Maine 04575-0475
http://www.bigelow.org