Store frequency of multiple columns in a new dataframe

Question

Based on the code and data below how can I store only the frequencies of FIPS and County in a new dataframe like shown in the desired output:

Sample data:

        df = structure(list(Key = c("080012020", "120012020", "120012018", 
"120012017", "080012017", "120012016", "120012015", "080012014", 
"120012013", "120012012", "080012012", "080012011", "080012016"
), County = c("Adams County", "Alachua County", "Alachua County", 
"Alachua County", "Adams County", "Alachua County", "Alachua County", 
"Adams County", "Alachua County", "Alachua County", "Adams County", 
"Adams County", "Adams County"), State = c("CO", "FL", "FL", 
"FL", "CO", "FL", "FL", "CO", "FL", "FL", "CO", "CO", "CO"), 
    FIPS = c("08001", "12001", "12001", "12001", "08001", "12001", 
    "12001", "08001", "12001", "12001", "08001", "08001", "08001"
    ), Inflow = c(38L, 261L, 321L, 339L, 58L, 288L, 254L, 46L, 
    413L, 433L, 30L, 42L, NA), InAGI = c(1817L, 6287L, 8423L, 
    8364L, 1865L, 14720L, 5224L, 1074L, 11774L, 10151L, 921L, 
    500L, NA), FiscalYear = c("2019- 2020", "2019- 2020", "2017 - 2018", 
    "2016 - 2017", "2016 - 2017", "2015 - 2016", "2014 - 2015", 
    "2013 - 2014", "2012 - 2013", "2011 - 2012", "2011 - 2012", 
    "2010 - 2011", "2015 - 2016"), Year = c(2020L, 2020L, 2018L, 
    2017L, 2017L, 2016L, 2015L, 2014L, 2013L, 2012L, 2012L, 2011L, 
    2016L), Outflow = c(54L, 447L, 444L, 558L, 44L, 436L, 334L, 
    49L, 466L, 495L, 39L, 31L, 51L), OutAGI = c(1879L, 13106L, 
    15409L, 16496L, 2408L, 12675L, 7448L, 733L, 10309L, 11677L, 
    847L, 605L, 1114L), NetMigration = c(-16L, -186L, -123L, 
    -219L, 14L, -148L, -80L, -3L, -53L, -62L, -9L, 11L, NA)), row.names = c(NA, 
-13L), class = "data.frame")

Desired output:

   FIPS  County            Frequency
   12001 Alachua County    7         
   08001 Adams County      6

Your desired output does not match with the `table(...)` output — Jilber Urbina, Nov 09 '22 at 21:48
so basically `table(unlist(df[, -1])) |> as.data.frame() |> tibble::rowid_to_column()`? — mnist, Nov 09 '22 at 21:54
The edit is an entirely different question now which I think is covered by here - https://stackoverflow.com/questions/9809166/count-number-of-rows-within-each-group `df %>% count(FIPS, County)` as per the accepted answer there. — thelatemail, Nov 09 '22 at 21:59

score 1 · Accepted Answer · answered Nov 09 '22 at 22:01

1

Base R solution:

aggregate(State ~ FIPS + County, data = df, FUN = length)
#>     FIPS         County State
#>  1 08001   Adams County     6
#>  2 12001 Alachua County     7

answered Nov 09 '22 at 22:01

mnist

6,571
1
18
41

Store frequency of multiple columns in a new dataframe

1 Answers1

Linked