NA's by using pivot_wider

Question

I am trying to transform a data frame by using pivot_wider. The output should have 3 columns (treatment C, treatment E and release.year). That is my data frame:

        Fv.Fm treatment release.year
1   0.3131340         E         1995
2   0.2829015         E         1995
3   0.4881591         E         1995
4   0.4319261         E         1995
5   0.3045167         E         1995
6   0.4241259         E         1995
7   0.4086531         E         1995
8   0.5774378         E         1995
9   0.5450547         E         1995
10  0.5587373         E         1986
11  0.4988792         E         1986
...
13378   0.5938378   C   1956
13379   0.4781431   C   1956
13380   0.3494923   C   2004
13381   0.3617828   C   2004
13382   0.3898748   C   2004
13383   0.4318397   C   2004
13384   0.4780491   C   2004
13385   0.4950537   C   2004
13386   0.3579608   C   2004
13387   0.5040526   C   2004
13388   0.4333380   C   2004

I used that code:

A%>%
  group_by(treatment) %>%
  mutate(row =row_number()) %>%
  tidyr::pivot_wider(names_from = treatment,values_from = Fv.Fm)%>%
  select(-row)

That is the output:

release.year E           C
1   1995    0.3131340   NA
2   1995    0.2829015   NA
3   1995    0.4881591   NA
4   1995    0.4319261   NA
5   1995    0.3045167   NA
6   1995    0.4241259   NA
7   1995    0.4086531   NA
8   1995    0.5774378   NA
9   1995    0.5450547   NA
10  1986    0.5587373   NA
11  1986    0.4988792   NA
...
13089   1956    NA  0.4781431
13090   2004    NA  0.3494923
13091   2004    NA  0.3617828
13092   2004    NA  0.3898748
13093   2004    NA  0.4318397
13094   2004    NA  0.4780491
13095   2004    NA  0.4950537
13096   2004    NA  0.3579608
13097   2004    NA  0.5040526
13098   2004    NA  0.4333380

But I want to have the values for treatment C and E in one row, so without the NAs. How can I do that?

It should work if you don't add the `row_number` column. You don't need the `group_by` either. Just `A %>% pivot_wider(...)` — Andrew Gustar, Dec 06 '19 at 09:29
then I get an error: ```Warning message: Values in `Fv.Fm` are not uniquely identified; output will contain list-cols. * Use `values_fn = list(Fv.Fm = list)` to suppress this warning. * Use `values_fn = list(Fv.Fm = length)` to identify where the duplicates arise * Use `values_fn = list(Fv.Fm = summary_fun)` to summarise duplicates``` — David, Dec 06 '19 at 10:02

score 2 · Accepted Answer · answered Dec 06 '19 at 10:09

The output which you get is correct because there is no common release.year between E and C treatment groups. If you want to them to be next to each other then you need to remove the release.year column and spread

library(dplyr)

A %>%
  group_by(treatment) %>%
  mutate(row =row_number()) %>%
  select(-release.year) %>%
  tidyr::pivot_wider(names_from = treatment,values_from = Fv.Fm) %>%
  select(-row)

#      E     C
#   <dbl> <dbl>
# 1 0.313 0.594
# 2 0.283 0.478
# 3 0.488 0.349
# 4 0.432 0.362
# 5 0.305 0.390
# 6 0.424 0.432
# 7 0.409 0.478
# 8 0.577 0.495
# 9 0.545 0.358
#10 0.559 0.504
#11 0.499 0.433
#....
#....

NA's by using pivot_wider

1 Answers1