tidyr::spread resulting in multiple rows

Question

I have a similar problem than the following, but the solution presented in the following link does not work for me: tidyr spread does not aggregate data

I have a df in the following structure:

    UndesiredIndex  DesiredIndex    DesiredRows Result
1   x1A x1  A   50,32
2   x1B x2  B   7,34
3   x2A x1  A   50,33
4   x2B x2  B   7,35

Using the code below:

  dftest <- bd_teste %>%
  select(-UndesiredIndex) %>%
  spread(DesiredIndex, Result)

I expected the following result:

DesiredIndex    A   B
A   50,32   50,33
B   7,34    7,35

Although, I keep getting the following result:

    DesiredIndex    x1  x2
1   A   50.32   NA
2   B   7.34    NA
3   A   NA  50.33
4   B   NA  7.35

PS: Sometimes I force the column UndesiredIndex out with select(-UndesiredIndex), but I keep getting the following message:

Adding missing grouping variables: UndesiredIndex

Might be something easy to stack those rows, but I'm new to R and have been trying so hard to solve this but without success. Thanks in advance!

try `aggregate(Result~DesiredRows,dat,paste)`. First ensure that your columns are not factors but rather characters — Onyambu, Aug 06 '18 at 18:51
Try `df1 %>% select(-UndesiredIndex) %>% group_by(DesiredIndex) %>% mutate(new =LETTERS[row_number()]) %>% ungroup %>% select(-DesiredRows) %>% spread(DesiredIndex, Result)` — akrun, Aug 06 '18 at 18:53
@Onyambu Thanks in advance for your help, but the proposed solution returns that the dat argument was not found. Should I fill it with my df's name? — Kim Sena, Aug 06 '18 at 19:29
@akrun In advance, Thanks for your help, but unfortunately, it ended up with the same result. — Kim Sena, Aug 06 '18 at 19:30
Based on the input you showed, it is giving me the expected output — akrun, Aug 06 '18 at 19:36
dat is your data. I don't know you called you called your data — Onyambu, Aug 06 '18 at 19:40
@akrun That's weird. I copied-pasted your solution, and it kept giving me NA and not stacking the columns. I can't get what could be wrong. Thanks again for the time to answer. — Kim Sena, Aug 06 '18 at 19:43

score 1 · Accepted Answer · edited Jun 07 '21 at 16:17

We group by DesiredIndex, create a sequence column and then do the spread:

library(tidyverse)

df1 %>% 
    select(-UndesiredIndex) %>% 
    group_by(DesiredIndex) %>% 
    mutate(new = LETTERS[row_number()]) %>% 
    ungroup %>%
    select(-DesiredIndex) %>% 
    spread(new, Result)

# A tibble: 2 x 3
#  DesiredRows A     B    
#  <chr>       <chr> <chr>
#1 A           50,32 50,33
#2 B           7,34  7,35

Data

df1 <- structure(
    list(
        UndesiredIndex = c("x1A", "x1B", "x2A", "x2B"), 
        DesiredIndex = c("x1", "x2", "x1", "x2"), 
        DesiredRows = c("A", "B", "A", "B"), 
        Result = c("50,32", "7,34", "50,33", "7,35")
    ), 
    class = "data.frame", 
    row.names = c("1", "2", "3", "4")
)

Thanks a lot @akrun. It didn't work in my original df, but it did work when inputted here. — Kim Sena, Aug 06 '18 at 20:44

score 1 · Answer 2 · edited Jun 07 '21 at 21:46

Shorter, but more theoretically round-about.

Data

(Thanks to @akrun!)

df1 <- structure(
    list(
        UndesiredIndex = c("x1A", "x1B", "x2A", "x2B"), 
        DesiredIndex = c("x1", "x2", "x1", "x2"), 
        DesiredRows = c("A", "B", "A", "B"), 
        Result = c("50,32", "7,34", "50,33", "7,35")
    ), 
    class = "data.frame", 
    row.names = c("1", "2", "3", "4")
)

This is a great technique for concatenating rows.

df1 %>% 
  group_by(DesiredRows) %>% 
  summarise(Result = paste(Result, collapse = "|")) %>% #<Concatenate rows
  separate(Result, into = c("A", "B"), sep = "\\|")     #<Separate by '|'

#> # A tibble: 2 x 3
#>   DesiredRows A     B    
#>   <chr>       <chr> <chr>
#> 1 A           50,32 50,33
#> 2 B           7,34  7,35

Created on 2018-08-06 by the reprex package (v0.2.0).

tidyr::spread resulting in multiple rows

2 Answers2

Data

Data