How to copy grouped rows into column by dplyr/tidyverse in R?

Question

I am trying to copy sets of rows into columns using dplyr. Following is my data frame.

df <- data.frame(
    hid=c(1,1,1,1,2,2,2,2,2,3,3,3,3),
    mid=c(1,2,3,4,1,2,3,4,5,1,2,3,4),
    tmid=c("010","01010","010","01020",
           "010","0120","010","010","020",
           "010","01010","010","01020"),
    thid=c("010","02020","010","02020",
           "000","0120","010","010","010",
           "010","02020","010","02020"),
    )

It is printed in the following format:

> df
   hid mid  tmid  thid
1    1   1   010   010
2    1   2 01010 02020
3    1   3   010   010
4    1   4 01020 02020
5    2   1   010   000
6    2   2  0120  0120
7    2   3   010   010
8    2   4   010   010
9    2   5   020   010
10   3   1   010   010
11   3   2 01010 02020
12   3   3   010   010
13   3   4 01020 02020

My desired output is show below:

     hid   mid  tmid   thid  tmid1  tmid2  tmid3  tmid4  tmid5  thid1  thid2  thid3  thid4  thid5
 * <dbl> <dbl> <fctr> <fctr> <fctr> <fctr> <fctr> <fctr> <fctr> <fctr> <fctr> <fctr> <fctr> <fctr> 
 1     1     1   010    010    010  01010    010  01020      0    010  02020    010  02020      0
 2     1     2 01010  02020    010  01010    010  01020      0    010  02020    010  02020      0
 3     1     3   010    010    010  01010    010  01020      0    010  02020    010  02020      0
 4     1     4 01020  02020    010  01010    010  01020      0    010  02020    010  02020      0
 5     2     1   010    000    010  0120     010    010    020    000   0120    010    010    010
 6     2     2  0120   0120    010  0120     010    010    020    000   0120    010    010    010
 7     2     3   010    010    010  0120     010    010    020    000   0120    010    010    010
 8     2     4   010    010    010  0120     010    010    020    000   0120    010    010    010
 9     2     5   020    010    010  0120     010    010    020    000   0120    010    010    010
10     3     1   010    010    010  01010    010  01020      0    010  02020    010   02020     0
11     3     2 01010  02020    010  01010    010  01020      0    010  02020    010   02020     0
12     3     3   010    010    010  01010    010  01020      0    010  02020    010   02020     0
13     3     4 01020  02020    010  01010    010  01020      0    010  02020    010   02020     0

Converting thid and tmid into column
Suffix in thid_x and tmid_xis defined by mid; however, maximum number of mid is not scalable (it spreads from 1 to perhaps 8 in actual large data set)
Same values of thid_x and tmid_xare set by groups of hid
If value does not exist, it should be padded by 0

Idea of this manipulation is shown in the following figure.

I am currently trying to use spread but it returns specific pairs of mid and thid or tmid. I need to fill remaining <NA>s by a value which remains in the output grouped by hid.

> df %>% mutate(id1=str_c("tmid",mid)) %>% group_by(hid) %>% spread(key=id1,value=tmid)
# A tibble: 13 x 8
# Groups:   hid [3]
     hid   mid   thid  tmid1  tmid2  tmid3  tmid4  tmid5
 * <dbl> <dbl> <fctr> <fctr> <fctr> <fctr> <fctr> <fctr>
 1     1     1    010    010   <NA>   <NA>   <NA>   <NA>
 2     1     2  02020   <NA>  01010   <NA>   <NA>   <NA>
 3     1     3    010   <NA>   <NA>    010   <NA>   <NA>
 4     1     4  02020   <NA>   <NA>   <NA>  01020   <NA>
 5     2     1    000    010   <NA>   <NA>   <NA>   <NA>
 6     2     2   0120   <NA>   0120   <NA>   <NA>   <NA>
 7     2     3    010   <NA>   <NA>    010   <NA>   <NA>
 8     2     4    010   <NA>   <NA>   <NA>    010   <NA>
 9     2     5    010   <NA>   <NA>   <NA>   <NA>    020
10     3     1    010    010   <NA>   <NA>   <NA>   <NA>
11     3     2  02020   <NA>  01010   <NA>   <NA>   <NA>
12     3     3    010   <NA>   <NA>    010   <NA>   <NA>
13     3     4  02020   <NA>   <NA>   <NA>  01020   <NA>

Any suggestions?

Please note that the df that you created and the printed one are not the same — DJV, May 27 '18 at 05:19

akrun · Accepted Answer · 2018-05-27T06:14:17.673

We could gather and then do a spread

library(tidyverse)
df1 %>% 
  select(-tdid, -tiid) %>% 
  gather(key, val, tmid:thid) %>% 
  unite(keyn, key, mid, sep="")  %>%
  spread(keyn, val, fill = '0') %>% 
  right_join(df1) %>%
  select(names(df1), everything(), -tdid, -tiid)
# A tibble: 13 x 14
#     hid   mid tmid  thid  thid1 thid2 thid3 thid4 thid5 tmid1 tmid2 tmid3
#   <dbl> <dbl> <chr> <chr> <chr> <chr> <chr> <chr> <chr> <chr> <chr> <chr>
# 1     1     1 010   010   010   02020 010   02020 0     010   01010 010  
# 2     1     2 01010 02020 010   02020 010   02020 0     010   01010 010  
# 3     1     3 010   010   010   02020 010   02020 0     010   01010 010  
# 4     1     4 01020 02020 010   02020 010   02020 0     010   01010 010  
# 5     2     1 010   000   000   0120  010   010   010   010   0120  010  
# 6     2     2 0120  0120  000   0120  010   010   010   010   0120  010  
# 7     2     3 010   010   000   0120  010   010   010   010   0120  010  
# 8     2     4 010   010   000   0120  010   010   010   010   0120  010  
# 9     2     5 020   010   000   0120  010   010   010   010   0120  010  
#10     3     1 010   010   010   02020 010   02020 0     010   01010 010  
#11     3     2 01010 02020 010   02020 010   02020 0     010   01010 010  
#12     3     3 010   010   010   02020 010   02020 0     010   01010 010  
#13     3     4 01020 02020 010   02020 010   02020 0     010   01010 010  
# ... with 2 more variables: tmid4 <chr>, tmid5 <chr>

data

df1 <- structure(list(hid = c(1, 1, 1, 1, 2, 2, 2, 2, 2, 3, 3, 3, 3), 
    mid = c(1, 2, 3, 4, 1, 2, 3, 4, 5, 1, 2, 3, 4), tmid = c("010", 
    "01010", "010", "01020", "010", "0120", "010", "010", "020", 
    "010", "01010", "010", "01020"), thid = c("010", "02020", 
    "010", "02020", "000", "0120", "010", "010", "010", "010", 
    "02020", "010", "02020"), tdid = c("000", "01010", "010", 
    "02020", "000", "0100", "010", "010", "010", "000", "01010", 
    "010", "02020"), tiid = c("010", "02020", "010", "01020", 
    "020", "0220", "020", "020", "020", "010", "02020", "010", 
    "01020")), .Names = c("hid", "mid", "tmid", "thid", "tdid", 
"tiid"), row.names = c(NA, -13L), class = c("tbl_df", "tbl", 
"data.frame"))

Thank you for your suggestion, it worked out perfectly. for sample data frame, however I faced an error saying that "error: cannot allocate vector of size 50.7Gb". Perhaps it is too large. — HSJ, May 27 '18 at 07:29
Yes, actual data base contains around 60 thousand records with around 40 fields which are not used in `gather/spread `operation. I will search for a way to solve this issue and may ask here again if necessary. — HSJ, May 27 '18 at 07:38
@Hideo As it is a memory management issue, you would probably be directed to one of the links https://stackoverflow.com/questions/5171593/r-memory-management-cannot-allocate-vector-of-size-n-mb — akrun, May 27 '18 at 07:39
It might be an xy problem, you want to repeat a lot of data here. With the huge amount of data you have there's probably a simpler solution, I suggest you ask a new question explaining the broader scope of what you're trying to achieve. — moodymudskipper, May 27 '18 at 11:26

How to copy grouped rows into column by dplyr/tidyverse in R?

1 Answers1

data

Linked