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!
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
