0

I have excel file with data of fields and values comma separated like:

 |   A       | B
1| field 1   |  1,3,4,5,7
2| field 2   |  2,4,5,7,8

I want to convert the data to table in R like:

field 1  |   field 2
  1      |     2
  3      |     4
  4      |     5
  5      |     7
  7      |     8

I try to use read.xlsx but R use the first line of excel to name of the attributes like:

    field 1   |  1,3,4,5,7
    field 2   |  2,4,5,7,8

someone know how can I do that?

Data

# df <- readxl::read_excel('data.xlsx')
df <- structure(list(X__1 = c(1, 2), A = c("field 1", "field 2"),
                     B = c("1,3,4,5,7", "2,4,5,7,8")),
                .Names = c("X__1", "A", "B"),
                row.names = c(NA, -2L),
                class = c("data.frame"))
rawr
  • 20,481
  • 4
  • 44
  • 78
eliad
  • 1
  • 1
  • read.xlsx has an argument call header. Setting header=FALSE will prevent read.xlsx from using the first row as column names – digEmAll Apr 20 '18 at 13:06
  • @rawr that is not a complete dupe of this Q. I guess they need to reshape to wide after spliting the column – Sotos Apr 20 '18 at 13:06
  • @rawr the link you provided will give `data.frame(A = c('field1', 'field1', ...), B = c(1, 3, ...))` which is not the output they want. – Sotos Apr 20 '18 at 13:11
  • Of course If the split to wide then a simple transpose (and tidy up colnames etc.) will do it...something like `t(data.frame(A = df$A, do.call(rbind, strsplit(as.character(df$B),',',fixed=TRUE))))`... – Sotos Apr 20 '18 at 13:15
  • 2
    `data.frame(setNames(strsplit(df$B, ','), df$A))` – rawr Apr 20 '18 at 13:23
  • yeah...or that ^ – Sotos Apr 20 '18 at 13:25
  • @rawr: Should be correct answer! – Jan Apr 20 '18 at 14:16

0 Answers0