How to create variables from unique rows in R

March 25, 2022

This is how my dataset looks:

| hhid     | food_code    | consumed     |.....|
|----------|--------------|--------------|.....|
| 479      | 01.1.1.1.0.3 | 0.66666667   |.....|
| 479      | 01.1.1.2.1.3 | 0.00000000   |.....|
| 480      | 01.1.1.1.0.3 | 0.33333333   |.....|
| 480      | 01.1.1.2.1.3 | 0.26548932   |.....|
...
...

So, I have lots of hhids and 74 unique food_codes. For each hhid, I want to make a dataframe as follows:

| hhid     | 01.1.1.1.0.3 | 01.1.1.2.1.3 |.....|
|----------|--------------|--------------|.....|
| 479      | 0.66666667   | 0.00000000   |.....|
| 480      | 0.33333333   | 0.26548932   |.....|

I tried to transpose with t() function but it messed up everything.

I tried filter as well

raw2<-raw%>%
  select(hhid, food_code_str, fd_cspn_ph)%>%
  filter(hhid==479)

>Solution :

You can use pivot_wider from tidyr (which is in tidyverse).

library(tidyverse)

df %>% 
  pivot_wider(names_from = "food_code", values_from = "consumed")

Output

   hhid `01.1.1.1.0.3` `01.1.1.2.1.3`
  <int>          <dbl>          <dbl>
1   479          0.667          0    
2   480          0.333          0.265

Data

df <- structure(list(hhid = c(479L, 479L, 480L, 480L, 481L), food_code = c("01.1.1.1.0.3", 
"01.1.1.2.1.3", "01.1.1.1.0.3", "01.1.1.2.1.3", "01.1.1.2.1.4"
), consumed = c(0.66666667, 0, 0.33333333, 0.26548932, 0.26548932
)), class = "data.frame", row.names = c(NA, -5L))