R: create new dataframe using id and record frequency

Question

For example, I have a table like below:

ID         A     B     C     D
1234       cat   0     dog   0
1234       0     new   dog   0
5678       0     new   dog   hi
0101       cat   0     0     hi

How do I get a table like this:

ID        word
1234      cat
1234      dog
1234      new
1234      dog
5678      new
5678      dog
5678      hi
0101      cat
0101      hi

Many thanks for your help!

Take a look at https://stackoverflow.com/questions/2185252/reshaping-data-frame-from-wide-to-long-format — Ronak Shah, Apr 14 '21 at 02:39

score 1 · Answer 1 · answered Apr 14 '21 at 01:04

This could simply be written as:

pivot_longer(`is.na<-`(df, df==0), -ID, values_to = "word",values_drop_na = TRUE)
# A tibble: 9 x 3
  ID    name  word 
  <chr> <chr> <chr>
1 1234  A     cat  
2 1234  C     dog  
3 1234  B     new  
4 1234  C     dog  
5 5678  B     new  
6 5678  C     dog  
7 5678  D     hi   
8 0101  A     cat  
9 0101  D     hi

if you want to use pipes:

df %>%
  `is.na<-`(df==0) %>%
  pivot_longer(-ID, values_to = "word", values_drop_na = TRUE)

For base R, if you dont care about the ordering of the results(of course you could order them) Then you could use:

na.omit(cbind(df[1], stack(`is.na<-`(df, df==0), -1)))
     ID values ind
1  1234    cat   A
4  0101    cat   A
6  1234    new   B
7  5678    new   B
9  1234    dog   C
10 1234    dog   C
11 5678    dog   C
15 5678     hi   D
16 0101     hi   D

1

Instead of writing "`is.na – crestor Apr 14 '21 at 11:28

R: create new dataframe using id and record frequency

1 Answers1