0

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
Laura Walker
  • 307
  • 2
  • 6
  • 16
  • Thank you! I'm not sure I understand what you're saying though. Convert to numbers? The total is in R as Numbers. Can you break the two steps down a little? – Laura Walker Feb 15 '17 at 03:06
  • 1
    `$5,000` is not a number in R. It has a `$` and `,` in it, therefore, not a number. – thelatemail Feb 15 '17 at 03:19
  • possible duplicate of http://stackoverflow.com/questions/1660124/how-to-sum-a-variable-by-group – akrun Feb 15 '17 at 03:36
  • Thank you for this! I had coded it to format it to a proper currency beforehand. How would the code change if it were a number to begin with? – Laura Walker Feb 15 '17 at 16:15

2 Answers2

2

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
Rich Scriven
  • 97,041
  • 11
  • 181
  • 245
  • Thank you for this! I had coded it to format it to a proper currency beforehand. How would the code change if it were a number to begin with? – Laura Walker Feb 15 '17 at 17:48
1

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]))
akrun
  • 874,273
  • 37
  • 540
  • 662
  • Thank you for this! I had coded it to format it to a proper currency beforehand. How would the code change if it were a number to begin with? – Laura Walker Feb 15 '17 at 17:48
  • @laura I think the `parse_number` will still extract the numeric part even if it is of other format with characters interspersed. Have you tested it? – akrun Feb 16 '17 at 00:16