CODEWITHSUNDEEP
rdataframe
Laura Walker
Laura Walker

Reputation: 307

Grouping and Totaling Data in R Based on Two Groups

Good Evening,

I have data that look like this

FY     Type     Total
2014   State    $5,000
2014   Federal  $2,596
2014   State    $5,123
2014   Federal  $2,567
2013   State    $5,555
2013   Federal  $2,784
2013   State    $5,562
2013   Federal  $2,556

How would I be able to get Totals by both FY and Type in R?

So that it can look like:

FY      Type    Total
2013    Federal $5,340
2013    State   $11,117
2014    Federal $5,163
2014    State   $10,123

Upvotes: 0

Views: 56

Answers (2)

akrun
akrun

Reputation: 886938

We can use tidyverse. After grouping by 'FY', 'Type', extract the numeric part of 'Total' with parse_number, get the sum and paste with $ as prefix

library(tidyverse)
df1 %>%
     group_by(FY, Type) %>% 
     summarise(Total = dollar_format()(sum(parse_number(Total))))
#    FY    Type   Total
#  <int>   <chr>   <chr>
#1  2013 Federal  $5,340
#2  2013   State $11,117
#3  2014 Federal  $5,163
#4  2014   State $10,123

NOTE: dollar_format is from scales, parse_number from readr and summarise, group_by from dplyr

We can also use rowsum from base R

rowsum(as.numeric(gsub("[^0-9.]+", "", df1$Total)), interaction(df1[-3]))

Upvotes: 1

Rich Scriven
Rich Scriven

Reputation: 99321

You will need to convert to numbers in order to get a sum, then it's a straightforward aggregation. Here transform is used to temporarily convert the Total column to numeric, leaving the original data unchanged.

aggregate(
    Total ~ Type + FY, 
    transform(df, Total = as.numeric(gsub("\\D", "", Total))), 
    sum
)
#      Type   FY Total
# 1 Federal 2013  5340
# 2   State 2013 11117
# 3 Federal 2014  5163
# 4   State 2014 10123

Or a slightly different output with xtabs.

xtabs(Total ~ ., transform(df, Total=as.numeric(gsub("\\D", "", Total))))
#       Type
# FY     Federal State
#   2013    5340 11117
#   2014    5163 10123

Upvotes: 2

Related Questions