Rows to Columns in R

Question

I have a list that I would like to convert to a data table Data looks as below:

"Customer"
"Steve"
"AddressLine"
"2041"
"Total"
"10"
"MailItemInfo"
"None"
"Customer"
"Mike"
"AddressLine"
"2043"
"Total"
"20"
"MailItemInfo"
"Yes"
"Customer"
"Jenn"
"AddressLine"
"1132"
"Total"
"24"
"MailItemInfo"
"Yes"

This pattern ends up repeating itself for 5 different addresses. I would like to put every other row into a new column, such that :

"Customer"  "AddressLine"  "BatchTotal"  "MailItemInfo" 
"Steve"  "2041"  "10"  "None"
"Mike"  "2043"  "20"  "Yes"
"Jenn"  "1132" "24" "Yes"

I would like it so that whenever these new "columns" continue to appear in the data list - they would auto populate into the new columns. Anyone that can provide a solution would truly be a help!

please have a look at this post - http://stackoverflow.com/questions/5963269/how-to-make-a-great-r-reproducible-example — Bulat, May 03 '16 at 23:24
Is it a `list`? Probably you have a vector... Sharing data with `dput()` leaves no ambiguity and is copy/pasteable! — Gregor Thomas, May 03 '16 at 23:41

HubertL · Answer 1 · 2016-05-04T00:23:16.043

l <- list("Customer",
          "Steve",
          "AddressLine",
          "2041",
          "Total",
          "10",
          "MailItemInfo",
          "None",
          "Customer",
          "Mike",
          "AddressLine",
          "2043",
          "Total",
          "20",
          "MailItemInfo",
          "Yes",
          "Customer",
          "Jenn",
          "AddressLine",
          "1132",
          "Total",
          "24",
          "MailItemInfo",
          "Yes")

If you have no change in the structure, this would work:

data.frame(matrix(unlist(l), ncol=8, byrow=TRUE))[c(2,4,6,8)]

     X2   X4 X6   X8
1 Steve 2041 10 None
2  Mike 2043 20  Yes
3  Jenn 1132 24  Yes

But if you want to add columns as they appear, you might want to go for a melted data.frame. Actually iy=t is easyier to perform this with data.table.

First, you'll have to decide what is the beginning of a new row, for example "Customer":

l <- list("Customer",
      "Steve",
      "AddressLine",
      "2041",
      "Total",
      "10",
      "MailItemInfo",
      "None",
      "Customer",
      "Mike",
      "AddressLine",
      "2043",
      "Total",
      "20",
      "MailItemInfo",
      "Yes",
      "Customer",
      "Jenn",
      "AddressLine",
      "1132",
      "Total",
      "24",
      "MailItemInfo",
      "Yes",
      "NewColumn",
      "xxx")

library(data.table)
dt <- data.table(matrix(unlist(l), ncol=2, byrow=TRUE)) # melted data.table
dt[V1=='Customer', id:=.I] # add id attribute to each "Customer"
dt[, id := id[1], by = cumsum(!is.na(id))] # set the id to following attributes
dcast(dt,id~V1, value.var="V2", fill=NA) # reverse the melted to large data.table

   id AddressLine Customer MailItemInfo NewColumn Total
1:  1        2041    Steve         None        NA    10
2:  2        2043     Mike          Yes        NA    20
3:  3        1132     Jenn          Yes       xxx    24

score 0 · Answer 2 · answered May 03 '16 at 23:29

0

Assuming the list above is named: "mylist". Try this:

titleseq<-seq(1, 8, by =2)
titles<-droplevels(mylist[titleseq,1])

nameseq<-seq(2, 40, by=8)
names<-droplevels(mylist[nameseq,1])
addres<-droplevels(mylist[(nameseq+2),1])
tot<-droplevels(mylist[(nameseq+4),1])
mailitem<-droplevels(mylist[(nameseq+6),1])

df<-data.frame(names, addres, tot, mail item)
names(df)<-titles

This is very brute forced but is should do the trick.

answered May 03 '16 at 23:29

Dave2e

22,192
18
42
50

1

The "list" (vector?) in OPs question is pretty clearly 1-dimensional, so why do you subset it in two dimensions? Also, rather than all the `droplevels` you could just convert to `character`. – Gregor Thomas May 03 '16 at 23:40
Greg. I have it listed here as a single column data frame. You do bring up a good point about converting to character first. – Dave2e May 04 '16 at 00:55

Rows to Columns in R

2 Answers2