Sum columns by condition

[jynusmac]

I have a df like this

structure(list(month = c("2022-07", "2022-07", "2022-07", "2022-07", 
"2022-07", "2022-07"),  country_orig = c("Germany", "Total Europe”, "Total UE", 
"France”, “Belgium”, "Italy"), city_dest = c(“Bruxelles”, 
“Berlin”, “Barcelona”, “Barcelona”, “Madrid”, “Madrid”), tourist = c(103, 
101, 99, 30, 55, 165)), row.names = c(NA, -6L), class = c("tbl_df", 
"tbl", "data.frame"))

I want to obtain the number of tourist from each country (country_orig) that arrives at each city_dest. Any idea?

TIA

[FJCC]

If I understand you correctly, you want to get the sum of tourist for each combination of country_origin and city_dest. You can do that with group_by() and summarize() from the dplyr package. With you example data set, it doesn't seem very useful but I assume you have a larger data set to work with.

library(dplyr)

DF <- structure(list(month = c("2022-07", "2022-07", "2022-07", "2022-07", 
                               "2022-07", "2022-07"),  
                     country_orig = c("Germany", "Total Europe", "Total UE", 
"France", "Belgium", "Italy"), 
city_dest = c("Bruxelles",  "Berlin", "Barcelona", "Barcelona", "Madrid", "Madrid"), 
tourist = c(103, 101, 99, 30, 55, 165)), row.names = c(NA, -6L), 
class = c("tbl_df", "tbl", "data.frame"))
DF
#> # A tibble: 6 × 4
#>   month   country_orig city_dest tourist
#>   <chr>   <chr>        <chr>       <dbl>
#> 1 2022-07 Germany      Bruxelles     103
#> 2 2022-07 Total Europe Berlin        101
#> 3 2022-07 Total UE     Barcelona      99
#> 4 2022-07 France       Barcelona      30
#> 5 2022-07 Belgium      Madrid         55
#> 6 2022-07 Italy        Madrid        165
DF |> group_by(country_orig,city_dest) |> 
  summarize(Total=sum(tourist))
#> `summarise()` has grouped output by 'country_orig'. You can override using the
#> `.groups` argument.
#> # A tibble: 6 × 3
#> # Groups:   country_orig [6]
#>   country_orig city_dest Total
#>   <chr>        <chr>     <dbl>
#> 1 Belgium      Madrid       55
#> 2 France       Barcelona    30
#> 3 Germany      Bruxelles   103
#> 4 Italy        Madrid      165
#> 5 Total Europe Berlin      101
#> 6 Total UE     Barcelona    99

^{Created on 2022-09-30 with reprex v2.0.2}