CODEWITHSUNDEEP
rgroup-byaggregate
Oscar
Oscar

Reputation: 457

R group or aggregate

I would like to do a group_by or aggregate. I have something like:

> head(affiliation_clean)
  Affiliation_ID                       Affiliation_Name          City       Country
1       000001   New Mexico State University Las Cruces    Las Cruces United States
2       000001   New Mexico State University Las Cruces    Las Cruces          <NA>
3       000001   New Mexico State University Las Cruces          <NA>          <NA>
4       000002   Palo Alto Research Center Incorporated     Palo Alto          <NA>
5       000002   Palo Alto Research Center Incorporated          <NA> United States
6       000002   Palo Alto Research Center Incorporated          <NA>          <NA>

Grouping by "Affiliation_ID" and taking the longest string of "Affiliation_Name", "City" and "Country", I would like to get:

> head(affiliation_clean)
  Affiliation_ID                       Affiliation_Name          City       Country
1       000001   New Mexico State University Las Cruces    Las Cruces United States
2       000002   Palo Alto Research Center Incorporated     Palo Alto United States

Thanks in advance.

Upvotes: 1

Views: 38

Answers (2)

www
www

Reputation: 39154

Here is a dplyr solution based on your description to select the longest string of each Affiliation_ID and column.

library(dplyr)

dat2 <- dat %>%
  group_by(Affiliation_ID) %>%
  summarise_all(funs(.[which.max(nchar(.))][1]))
dat2
# # A tibble: 2 x 4
#   Affiliation_ID Affiliation_Name                       City       Country      
#            <int> <chr>                                  <chr>      <chr>        
# 1              1 New Mexico State University Las Cruces Las Cruces United States
# 2              2 Palo Alto Research Center Incorporated Palo Alto  United States

DATA

dat <-read.table(text = "  Affiliation_ID                       Affiliation_Name          City       Country
1       '000001'   'New Mexico State University Las Cruces'    'Las Cruces' 'United States'
                 2       '000001'   'New Mexico State University Las Cruces'    'Las Cruces'          NA
                 3       '000001'   'New Mexico State University Las Cruces'          NA          NA
                 4       '000002'   'Palo Alto Research Center Incorporated'     'Palo Alto'          NA
                 5       '000002'   'Palo Alto Research Center Incorporated'          NA 'United States'
                 6       '000002'   'Palo Alto Research Center Incorporated'          NA          NA",
                 header = TRUE, stringsAsFactors = FALSE)

Upvotes: 1

akrun
akrun

Reputation: 886938

Assuming that there is a single unique 'City/Country' for each 'Affiliation_ID', 'Affiliation_Name', after grouping at the first two columns, get the unique non-NA element of all other columns with summarise_all

library(dplyr)
affiliation_clean %>%
   group_by(Affiliation_ID, Affiliation_Name) %>%
   summarise_all(funs(unique(.[!is.na(.)])) )
# A tibble: 2 x 4
# Groups: Affiliation_ID [?]
#  Affiliation_ID Affiliation_Name                       City       Country      
#  <chr>          <chr>                                  <chr>      <chr>        
#1 000001         New Mexico State University Las Cruces Las Cruces United States
#2 000002         Palo Alto Research Center Incorporated Palo Alto  United States

Upvotes: 1

Related Questions