Sum columns by condition

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

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

1 Like

This topic was automatically closed 7 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.