Aggregate Data Frame Containing Strings and Numbers

user_14107765 0
Viewed 52

I am attempting to aggregate or group a data frame base on a common field. Beyond wanting to sum some demographic numbers with the aggregation, I am also trying to aggregate string fields. Within some of the string fields (ie, County, State, Countycode, etc) they are always the same amongst those with the same common field, but some of the string fields (ie, Congressional district, Senate House, et) are unique amongst those with the same common field.

The objective is to combine fields with the same value in the COMMON_FIELD. For the string fields that are unique within their common field group, I need R to select the values from one of the common field groups. It doesn't matter from which row it acquires the values as long as it is a row that shares the common field value.

Code Sample

IDENTIFICATION | COUNTY | TOTAL_POPULATION | YOUTH_POPULATION | COMMON_FIELD
Oakland-013  |  Alameda   |   3000        |        1000      |      A1-3
Oakland-012  |  Alameda   |   5000        |        2000       |     A1-3

Desired Results:

IDENTIFICATION | COUNTY | TOTAL_POPULATION | YOUTH_POPULATION | COMMON_FIELD
Oakland-013  |  Alameda   |   8000       |         3000      |      A1-3

Thus, although the identification field will be unique amongst groups with the same common field, I would like R to pick one from the groups in the common field and it doesn't matter which it picks.

aggregate r string
3 Answers

With dplyr, we can do multiple aggregates on blocks of columns by group. The 'IDENTIFICATION' values are showed to be different, based on the expected, we can select the first element of that column for each group

library(dplyr) # >= 1.0.0
df1 %>% 
   group_by(COUNTY, COMMON_FIELD) %>% 
   # // use across for more than one column
   # // checks the type of columns i.e. numeric to select and return the sum
   summarise(across(where(is.numeric), sum, na.rm = TRUE), 
               IDENTIFICATION = first(IDENTIFICATION))

The OP's original dataset code can be changed to

GAcatalistDupes %>% 
   group_by(FIPS, CAT_JOIN) %>%
   # // summarise numeric columns
   summarise(across(where(is.numeric), sum, na.rm = TRUE),
             # // get the first value for specified columns
             across(c(geography, CONG, SS, SH, Field23, FIPS), first))
user_37322710

Here is a base R option using aggregate

aggregate(
  . ~ COMMON_FIELD,
  df,
  function(x) {
    ifelse(any(is.na(suppressWarnings(as.numeric(x)))),
      sample(x, 1),
      sum(as.numeric(x))
    )
  }
)

which gives

  COMMON_FIELD IDENTIFICATION  COUNTY TOTAL_POPULATION YOUTH_POPULATION
1         A1-3    Oakland-012 Alameda             8000             3000
user_121587570

For the sake of completeness, here is also a data.table solution which applies the sum() function if a column is numeric and picks the first value if the column is non-numeric:

library(data.table)
setDT(dat)[, lapply(.SD, function(x) if (is.numeric(x)) sum(x, na.rm = TRUE) else first(x)), 
           by = COMMON_FIELD]

   COMMON_FIELD IDENTIFICATION  COUNTY TOTAL_POPULATION YOUTH_POPULATION
1:         A1-3    Oakland-013 Alameda             8000             3000

Data

library(data.table)
dat <- fread("IDENTIFICATION | COUNTY | TOTAL_POPULATION | YOUTH_POPULATION | COMMON_FIELD
Oakland-013  |  Alameda   |   3000        |        1000      |      A1-3
Oakland-012  |  Alameda   |   5000        |        2000       |     A1-3")
user_38170040
Related