Want to add a new column to list using another list rows

Firstly I am new to R, still in the exploring phase.
I have two lists (A & B) with unequal sizes. I want to add new column in A with rows from B using 2 conditions.
Can anyone suggest me the best way to figure it out.

A looks like this:

Country Booking Changes
AU 2
UK 10
US 3

B looks like this:
Country Type Booking_ Changes
AU CITY 1
AU REGIONAL 1
UK CITY 6
UK REGIONAL 4
US CITY 3
US REGIONAL 0
CA CITY 12
CA REGIONAL 7

Expected output is:

Country Booking Changes City Regional
AU 2 1 1

Help is highly appreciated.

Thanks.

Hi,

Welcome to the RStudio community!

I assume the objects are data frames and not lists. judging by the format. Since A is actually a summary of B, we don't need to join them but can just transform B like so:

library(dplyr)
library(tidyr)

A = data.frame(Country = c("AU", "UK", "US"), Total_Booking_Changes = c(2,10,3))
B = data.frame(
  Country = c("AU", "AU", "UK", "UK", "US", "US", "CA", "CA"), 
  Type = c("CITY", "REGIONAL"),
  Booking_Changes = c(1,1,6,4,3,0,12,7))

C = B %>% pivot_wider(names_from = Type, values_from = Booking_Changes) %>% 
  rowwise() %>% mutate(Booking_Changes = sum(CITY, REGIONAL))

C
#> # A tibble: 4 x 4
#> # Rowwise: 
#>   Country  CITY REGIONAL Booking_Changes
#>   <chr>   <dbl>    <dbl>           <dbl>
#> 1 AU          1        1               2
#> 2 UK          6        4              10
#> 3 US          3        0               3
#> 4 CA         12        7              19

Created on 2020-08-31 by the reprex package (v0.3.0)

I used the Tidyverse implementation here, with the pivot_wider as the most important function to get what you wanted. It might be a bit confusing learning these functions in the beginning, but I really recommend getting to know them as it will greatly help you.

Hope this helps,
PJ

This topic was automatically closed 21 days after the last reply. New replies are no longer allowed.

If you have a query related to it or one of the replies, start a new topic and refer back with a link.