How do you pivot longer columns in groups?

Question

I have a dataframe that effectively looks like this:

df_1 <- tribble(
    ~Name,   ~activity1, ~number_activity_1, ~attendees1,  ~activity2, ~number_activity_2, ~attendees2
    "John",   "Birthday",        1,               14,      "Sleep Over",         4,            10,
    "Chris",  "Sleep Over",      2,               18,      "Painting",           5,            8,
    "Alex",   "Track Race",      4,               100,     "Birthday",           1,            5
)

I need to pivot_longer() while handling the groupings within my columns:

There are multiple activity values (in this case 1 and 2) There are 2 numbers (number_of_activity and attendees) for each activity value. In my actual dataset, there are 10 activities per person.

Essentially, what I'd like to do is to apply pivot_longer() to the all the variables that make up activity 1 and all the variables that make up activity 2

What I'd like to end up with is this:

df_2 <- tribble(
    ~Name,   ~activity, ~number_activity, ~attendees,
    "John",   "Birthday",        1,            14,          
    "John",   "Sleep Over",      4,            10,
    "Chris",  "Sleep Over",      2,            18,          
    "Chris",  "Painting",        5,            8,
    "Alex",   "Track Race",      4,            100, 
    "Alex",   "Birthday",        1,            5)

I have tried a few ways but I can't get the numbers to pivot with the matching activity.

Anyone know how to handle this?

Thank you!

score 1 · Answer 1 · answered Oct 01 '20 at 16:12

Try this:

library(tidyverse)
#Code
dfnew <- df_1 %>% 
  mutate(across(everything(),~as.character(.))) %>%
  pivot_longer(-c(Name)) %>%
  mutate(name=gsub('number_activity_','number_activity',name),
         name=substr(name,1,nchar(name)-1)) %>%
  group_by(name) %>% mutate(id=1:n()) %>%
  pivot_wider(names_from = name,values_from=value) %>% ungroup() %>% select(-id)

Output:

# A tibble: 6 x 4
  Name  activity   number_activity attendees
  <chr> <chr>      <chr>           <chr>    
1 John  Birthday   1               14       
2 John  Sleep Over 4               10       
3 Chris Sleep Over 2               18       
4 Chris Painting   5               8        
5 Alex  Track Race 4               100      
6 Alex  Birthday   1               5

Baraliuh · Answer 2 · 2020-10-01T16:20:50.857

Something like this maybe?

library(tidyr)
#Load the data
df_1 <- tribble(
  ~Name,   ~activity1, ~number_activity_1, ~attendees1,  ~activity2, ~number_activity_2, ~attendees2,
  "John",   "Birthday",        1,               14,      "Sleep Over",         4,            10,
  "Chris",  "Sleep Over",      2,               18,      "Painting",           5,            8,
  "Alex",   "Track Race",      4,               100,     "Birthday",           1,            5
)

df_1 %>% 
#Stack all columns except the grouping varaible
  gather('k', 'v', -1) %>% 
#Make the ending of the names consistent
  mutate(
    k = str_replace(k, '([a-z])([0-9])', '\\1_\\2')
  ) %>% 
#Split the key column into key and replicate
  extract(k, c('tmp1', 'tmp2'), '^(.*)_(.*)$') %>% 
#Make the data wide
  spread(tmp1, v) %>% 
#Remove unneaded replicate columns
  select(-tmp2)

#Output
# A tibble: 6 x 4
  Name  activity   attendees number_activity
  <chr> <chr>      <chr>     <chr>          
1 Alex  Track Race 100       4              
2 Alex  Birthday   5         1              
3 Chris Sleep Over 18        2              
4 Chris Painting   8         5              
5 John  Birthday   14        1              
6 John  Sleep Over 10        4

score 0 · Accepted Answer · answered Oct 01 '20 at 16:18

You could full_join the relevant columns to each other:

library(dplyr)

newcols <- c("Name", "activity", "number_activity", "attendees")

full_join(setNames(select(df_1, 1:4), newcols),
          setNames(select(df_1, c(1, 5:7)), newcols)) %>% 
  arrange(rev(Name))
#> Joining, by = c("Name", "activity", "number_activity", "attendees")
#> # A tibble: 6 x 4
#>   Name  activity   number_activity attendees
#>   <chr> <chr>                <dbl>     <dbl>
#> 1 John  Birthday                 1        14
#> 2 John  Sleep Over               4        10
#> 3 Chris Sleep Over               2        18
#> 4 Chris Painting                 5         8
#> 5 Alex  Track Race               4       100
#> 6 Alex  Birthday                 1         5

Or even just rbind them:

newcols <- c("Name", "activity", "number_activity", "attendees")

arrange(rbind(setNames(select(df_1, 1:4), newcols),
              setNames(select(df_1, c(1, 5:7)), newcols)), rev(Name))

How do you pivot longer columns in groups?

3 Answers3