pivot_wider/spread only a subset of levels from a column

user_13972333 0
Viewed 238

I want to reshape just a subset of levels/values within a single column to wide, but leave selected levels in the original column.

In this example data, the 'rice' and 'beans' values in the food column don't have a "type" characteristic. I want to keep the original column "food" and its levels "rice" and "beans" while pivoting the other values to wide.

Data

set.seed(1)
df<-tibble(index=sample(1:5, 10, replace=TRUE),
            food=c(rep('fruit', 4),rep('meat', 4), 'rice', 'beans'),
            type=c('apple', 'apple', 'banana', 'banana', 'steak', 'steak', 't-bone', 't-bone', NA, NA))

# A tibble: 10 x 3
   index food  type  
   <int> <chr> <chr> 
 1     1 fruit apple 
 2     4 fruit apple 
 3     1 fruit banana
 4     2 fruit banana
 5     5 meat  steak 
 6     3 meat  steak 
 7     2 meat  t-bone
 8     3 meat  t-bone
 9     3 rice  NA    
10     1 beans NA

The desired output would be like this:

output<-structure(list(index = c(1L, 1L, 4L, 2L, 5L, 3L, 3L), fruit = c("apple", 
"banana", "apple", "banana", NA, NA, NA), meat = c(NA, NA, NA, 
"t-bone", "steak", "steak", "t-bone"), food = c("beans", NA, 
NA, NA, NA, "rice", NA)), row.names = c(NA, -7L), class = c("tbl_df", 
"tbl", "data.frame"))

output
# A tibble: 7 x 4
  index fruit  meat   food 
  <int> <chr>  <chr>  <chr>
1     1 apple  NA     beans
2     1 banana NA     NA   
3     4 apple  NA     NA   
4     2 banana t-bone NA   
5     5 NA     steak  NA   
6     3 NA     steak  rice 
7     3 NA     t-bone NA

I could do it manually by moving the 'rice' and 'beans' values to the 'type' column and creating a corresponding 'food' level in the 'food' column. Apart from the laborious and non-systematic transformation, i got an unnexpected output, with duplicated 'beans' and 'rice' values:

df1%>%mutate(type=coalesce(type, food),
             food=replace(food, type %in% c('rice', 'beans'), 'food'))%>%
        pivot_wider(id_cols = index, names_from = c(food), values_from = c(type))%>%
        unnest
# A tibble: 7 x 4
  index fruit  meat   food 
  <int> <chr>  <chr>  <chr>
1     1 apple  NA     beans
2     1 banana NA     beans ###<-
3     4 apple  NA     NA   
4     2 banana t-bone NA   
5     5 NA     steak  NA   
6     3 NA     steak  rice 
7     3 NA     t-bone rice ###<-

I wonder if there is simpler and safer way to do it on-the-fly with pivot_wider

dplyr reshape dataframe r
2 Answers

We may do this with coalesceand replace

library(dplyr)
library(tidyr)
library(data.table)
df %>% 
    mutate(type = coalesce(type, food), 
           food =  replace(food, food == type, 'food'),
            rn = rowid(index, food)) %>%
    pivot_wider(names_from = food, values_from = type) %>% 
    select(-rn)
# A tibble: 7 x 4
  index fruit  meat   food 
  <int> <chr>  <chr>  <chr>
1     1 apple  <NA>   beans
2     4 apple  <NA>   <NA> 
3     1 banana <NA>   <NA> 
4     2 banana t-bone <NA> 
5     5 <NA>   steak  <NA> 
6     3 <NA>   steak  rice 
7     3 <NA>   t-bone <NA>
user_37322710

You can use -

library(dplyr)
library(tidyr)

df %>%
  mutate(type = if_else(food %in% c('rice', 'beans'), food, type), 
         food = replace(food, food %in% c('rice', 'beans'), 'food')) %>%
  group_by(index, food) %>%
  mutate(row  = row_number()) %>%
  ungroup %>%
  pivot_wider(names_from = food, values_from = type) %>%
  select(-row)
  
#  index fruit  meat   food 
#  <int> <chr>  <chr>  <chr>
#1     1 apple  NA     beans
#2     4 apple  NA     NA   
#3     1 banana NA     NA   
#4     2 banana t-bone NA   
#5     5 NA     steak  NA   
#6     3 NA     steak  rice 
#7     3 NA     t-bone NA
user_39629140
Related