how can i use pivot wider to summarize by group

user_15467583 0
Viewed 84
df <- data.frame( plant1= c('1','0','1','0','0','1','0','0','1'),
                  plant2= c('0','1','0','1','1','0','1','1','0'),
                  Public.1= c('1','0','1','0','0','0','0','0','1'),
                  Private.1= c('0','0','0','0','0','1','0','0','0'),
                  Public.2= c('0','0','0','1','1','0','0','1','0'),
                  Private.2= c('0','1','0','0','0','0','1','0','0'))
df

how can i use pivot wider to summarize Public Private according Plant1 and 2? logical behind: every Plant element can only enrol either Public or Private plant 1 and Public.1 Private.1 correlated

Expected output:

 plant1 plant2 Public Private 
1      1      0      1       0
2      0      1      0       1      
3      1      0      1       0
4      0      1      1       0
5      0      1      1       0
6      1      0      0       1
7      0      1      0       1
8      0      1      1       0 
9      1      0      1       0
r
3 Answers

You can use pivot_longer :

tidyr::pivot_longer(df, 
                    cols = -starts_with('plant'), 
                    names_to = '.value', 
                    names_pattern = '(.*)\\.')

#  plant1 plant2 Public Private
#   <chr>  <chr>  <chr>  <chr>  
# 1 1      0      1      0      
# 2 1      0      0      0      
# 3 0      1      0      0      
# 4 0      1      0      1      
# 5 1      0      1      0      
# 6 1      0      0      0      
# 7 0      1      0      0      
# 8 0      1      1      0      
# 9 0      1      0      0   
#...
#...
user_39629140

We can use names_sep

library(tidyr)
library(dplyr)
pivot_longer(df, 
           cols = matches('Public|Private'), 
      names_to = c(".value", 'grp'), names_sep ="\\.") %>%
   select(-grp)

-output

# A tibble: 18 x 4
   plant1 plant2 Public Private
   <chr>  <chr>  <chr>  <chr>  
 1 1      0      1      0      
 2 1      0      0      0      
 3 0      1      0      0      
 4 0      1      0      1      
 5 1      0      1      0      
 6 1      0      0      0      
 7 0      1      0      0      
 8 0      1      1      0      
 9 0      1      0      0      
10 0      1      1      0      
11 1      0      0      1      
12 1      0      0      0      
13 0      1      0      0      
14 0      1      0      1      
15 0      1      0      0      
16 0      1      1      0      
17 1      0      1      0      
18 1      0      0      0
user_37322710

This gives your expected output:

First type.convert to get integer Then sum rowwise.

library(dplyr)
df %>% 
  type.convert(as.is=TRUE) %>% 
  rowwise() %>% 
  mutate(Public = sum(c(Public.1, Public.2)),
         Private= sum(c(Private.1, Private.2))) %>% 
  select(plant1, plant2, Public, Private)

Output:

  plant1 plant2 Public Private
   <int>  <int>  <int>   <int>
1      1      0      1       0
2      0      1      0       1
3      1      0      1       0
4      0      1      1       0
5      0      1      1       0
6      1      0      0       1
7      0      1      0       1
8      0      1      1       0
9      1      0      1       0
user_133216470
Related