r aggregate function -- how to display additional columns

Question

I have two data frames: City and Country. I am trying to find out the most popular city per country. City and Country have a common field, City.CountryCode and Country.Code. These two data frames were merged to one called CityCountry. I have tried the aggregate command like so:

aggregate(Population.x~CountryCode, CityCountry, max)

This aggregate command only shows the CountryCode and Population.X columns. How would I show the name of the Country and the name of the City? Is aggregate the wrong command to use here?

In that case, use `ave` i.e. `CityCountry[with(CityCountry, Population.x == ave(Population.x, CountryCode, FUN = max)),]` — akrun, Dec 07 '17 at 02:20
Possible duplicate of [How to select the rows with maximum values in each group with dplyr?](https://stackoverflow.com/questions/24237399/how-to-select-the-rows-with-maximum-values-in-each-group-with-dplyr) OR more generally [How to select the row with the maximum value in each group](https://stackoverflow.com/questions/24558328/how-to-select-the-row-with-the-maximum-value-in-each-group). Also [Extract the maximum value within each group in a dataframe](https://stackoverflow.com/questions/25314336/extract-the-maximum-value-within-each-group-in-a-dataframe) — Ronak Shah, Dec 07 '17 at 02:43

score 1 · Answer 1 · answered Dec 07 '17 at 02:27

Could also use dplyr to group by Country, then filter by max(Population.x).

library(dplyr)

set.seed(123)
CityCountry <- data.frame(Population.x = sample(1000:2000, 10, replace = TRUE), 
                          CountryCode = rep(LETTERS[1:5], 2), 
                          Country = rep(letters[1:5], 2), 
                          City = letters[11:20], 
                          stringsAsFactors = FALSE)

CityCountry %>% 
  group_by(Country) %>% 
  filter(Population.x == max(Population.x)) %>%
  ungroup()

# A tibble: 5 x 4
  Population.x CountryCode Country  City
         <int>       <chr>   <chr> <chr>
1         1287           A       a     k
2         1789           B       b     l
3         1883           D       d     n
4         1941           E       e     o
5         1893           C       c     r

r aggregate function -- how to display additional columns

1 Answers1