R function to spread rows into columns

Question

Spread row values into columns

Data looks like

> head(df2)
    ID fungi     Conc   Abs date_no
1    1    R3 2.500000 0.209       0
22   1    R3 1.250000 0.153       0
43   1    R3 0.625000 0.159       0
64   1    R3 0.312500 0.164       0
85   1    R3 0.156250 0.157       0
106  1    R3 0.078125 0.170       0

And I used this function, which spread the date column into three columns but didn't populate them correctly.

separate_DF <- spread(df2, "date_no", "Abs")

What I get is this...

> head(df3)
  ID fungi       Conc date_no_0 date_no_1 date_no_3
1  1    R3 0.01953125     0.162        NA        NA
2  1    R3 0.03906253     0.169        NA        NA
3  1    R3 0.07812500     0.170        NA        NA
4  1    R3 0.15625000     0.157        NA        NA
5  1    R3 0.31250000     0.164        NA        NA
6  1    R3 0.62500000     0.159        NA        NA

So that the three date columns are populated by the Abs values. And each fungi at each concentration is its own row.

What's wrong with what you get from your spread call? Looks fine to me. You could add `sep="_"` to the argument list to get the `date` into your col names — iod, Apr 08 '19 at 17:07
It does some weird calculations with concentration, so the column is no longer correct. When it separates Abs values across the three new date columns, only one column is populated at a time. So date1 has values 1:24, date2 25:96, and date3 97:168. When one column has values, the other two have NAs. Adding sep="_" did not resolve the issue. — Lauren Maynard, Apr 08 '19 at 18:07
Can you give some sample data where that problem happens? Because it doesn't happen with what you provided. Note that spread will create a row for every unique fungi/conc combination, and show all the dates for that unique combination. — iod, Apr 08 '19 at 18:25
Updated original post with more information. Is that helpful? — Lauren Maynard, Apr 08 '19 at 20:31
When I run the code in your question or in TheRimalaya's answer on the new data in your question, I get a result that looks perfectly reasonable. Perhaps you could do the same, put the result in your question, and not exactly what seems wrong to you. — Gregor Thomas, Apr 08 '19 at 20:57
Try `spread(date_df[,c(1:2,4:5)],date_no,Abs,sep="Abs") %>% left_join(., spread(date_df[,c(1:3,5)],date_no,Conc,sep="Conc"),by="fungi")` — iod, Apr 09 '19 at 12:50

score 2 · Answer 1 · answered Apr 08 '19 at 17:08

Try this one,

library(tidyr)

txt <- "fungi date  Abs Conc
1      1     x   2.5
1      2     x   2.5
1      3     x   2.5
2      1     x   2.5
2      2     x   2.5
2      3     x   2.5
"
date_df <- read.table(textConnection(txt), header = TRUE)
print(spread(date_df, date, Abs, sep=""))

Result:

  fungi Conc date1 date2 date3
1     1  2.5     x     x     x
2     2  2.5     x     x     x

R function to spread rows into columns

1 Answers1