Nrthugu

I want to create a new column that has combined info from two columns, but one column is on a different row. Below is an example dataframe I want to start with:

df <- data_frame(person = c(rep("Joe",4),rep("Bob",3)),

               meal = c(seq(1:4),seq(1:3)),

               food = c("Chicken", "Beef", "Soup and meal 2", "Lamb",

                        "Lamb","Salad and meal 1","Beef"),

               dependencies = c(NA,NA,2,3,NA,1,NA),

               solo_meal = c(1,1,0,1,1,0,1))

I want to create a new column that looks like:

data_frame(combined_meal = c("Chicken", "Beef", "Soup and Beef", "Lamb",

                              "Lamb","Salad and Lamb","Beef"))

If the dependency is used, I want to combine that "food" with the "meal".

I have a large dataset with several dependencies that I need to combine into one field. I feel like there should be a simple way to do this, but I can't seem to come up with one.

Thanks!

edit:
I want to thank those who have commented so far. The tidyverse option worked best for my needs. I have one edit that I meant to add - when searching through the meals - I may need to add more than one meal together.

df <- data_frame(person = c(rep("Joe",4),rep("Bob",3)),

               meal = c(seq(1:4),seq(1:3)),

               food = c("Chicken", "Beef", "Soup and meal 2", "Lamb and meal 3",

                        "Lamb","Salad and meal 1","Beef"),

               dependencies = c(NA,NA,2,3,NA,1,NA),

               solo_meal = c(1,1,0,1,1,0,1))

which gives:

# A tibble: 7 x 5





  person  meal food             dependencies solo_meal

  <chr>  <int> <chr>                   <dbl>     <dbl>

1 Joe        1 Chicken                    NA         1

2 Joe        2 Beef                       NA         1

3 Joe        3 Soup and meal 2             2         0

4 Joe        4 Lamb and meal 3             3         1

5 Bob        1 Lamb                       NA         1

6 Bob        2 Salad and meal 1            1         0

7 Bob        3 Beef                       NA         1

I want to have a column of combined meals:

# A tibble: 7 x 1

  combined_meal         

  <chr>                 

1 Chicken               

2 Beef                  

3 Soup and Beef         

4 Lamb and Soup and Beef

5 Lamb                  

6 Salad and Lamb        

7 Beef  

How do I recursively add the meals? Preferably using the tidyverse.

Thanks again!

A solution using the tidyverse. The idea is to self join the df table based on person, dependencies and mean, and then with some further operations.

library(tidyverse)



df2 <- df %>%

  left_join(df %>% select(-dependencies, -solo_meal), 

            by = c("person", "dependencies" = "meal")) %>%

  mutate(food.z = str_replace(food.x, "meal [0-9]", "")) %>%

  mutate(combined_meal = ifelse(is.na(food.y), food.z, str_c(food.z, food.y, sep = ""))) %>%

  rename(food = food.x) %>%

  select(names(df), combined_meal)

df2

# # A tibble: 7 x 6

#   person  meal food             dependencies solo_meal combined_meal 

#   <chr>  <int> <chr>                   <dbl>     <dbl> <chr>         

# 1 Joe        1 Chicken                    NA         1 Chicken       

# 2 Joe        2 Beef                       NA         1 Beef          

# 3 Joe        3 Soup and meal 2             2         0 Soup and Beef 

# 4 Joe        4 Lamb                       NA         1 Lamb          

# 5 Bob        1 Lamb                       NA         1 Lamb          

# 6 Bob        2 Salad and meal 1            1         0 Salad and Lamb

# 7 Bob        3 Beef                       NA         1 Beef  

This is a base solution. (I find base solutions easier to understand.) You make an index vector of rows to modify and then build a new value from the items to be modified and the ones immediately preceding them ( which from your example appears to be the assigned task.

 idx <- which(grepl("meal", df$food))

 df[ idx, "combined_meal"] <- 

             paste( sub("meal.*$", "", df$food[idx] ), df$food [idx-1] )



 # The fill in NA's with the original `food` values

 df$combined_meal[ is.na(df$combined_meal)] <-

          df$food[ is.na(df$combined_meal)]







> df

# A tibble: 7 x 6

  person  meal food             dependencies solo_meal combined_meal  

  <chr>  <int> <chr>                   <dbl>     <dbl> <chr>          

1 Joe        1 Chicken                    NA         1 Chicken        

2 Joe        2 Beef                       NA         1 Beef           

3 Joe        3 Soup and meal 2             2         0 Soup and  Beef 

4 Joe        4 Lamb                       NA         1 Lamb           

5 Bob        1 Lamb                       NA         1 Lamb           

6 Bob        2 Salad and meal 1            1         0 Salad and  Lamb

7 Bob        3 Beef                       NA         1 Beef           

> 

Single line solution (using dplyr):

df %>% group_by(person) %>% 

mutate(combined_meal=ifelse(!is.na(dependencies), paste0(gsub("(.* and ).*","\1",food), food[dependencies]),food))

For each person, we create a column combined_meal where if there are no dependencies, it repeats whatever's in food, and if there is one, it pastes together everything that comes before the word "and" with whatever's in the food column with the row number of the dependency.

(Note this assumes that the number in "dependency" is identical to the row number of the data frame if we only get the data frame for that person. That also implies the data frame is sorted by meal. If that assumption is incorrect, you can include the line arrange(meal) after the group_by.)

Result:

# A tibble: 7 x 6

# Groups:   person [2]

  person  meal food             dependencies solo_meal combined_meal 

  <chr>  <int> <chr>                   <dbl>     <dbl> <chr>         

1 Joe        1 Chicken                   NA         1. Chicken       

2 Joe        2 Beef                      NA         1. Beef          

3 Joe        3 Soup and meal 2            2.        0. Soup and Beef 

4 Joe        4 Lamb                      NA         1. Lamb          

5 Bob        1 Lamb                      NA         1. Lamb          

6 Bob        2 Salad and meal 1           1.        0. Salad and Lamb

7 Bob        3 Beef                      NA         1. Beef