0

So i've created a summarised data frame from a larger df which i want to use to calculate percentages.

tibble::tribble(
  ~Year, ~Rider_Class, ~yearly_transactions,
   2021,     "casual",             2528702L,
   2021,     "member",             3065797L,
   2022,     "casual",             2321824L,
   2022,     "member",             3345462L
)

my goal is to get the total of both categories of rider class from each year, then divide that by each rider class to get my percentages, but I'm struggling with how to calculate the sum of transactions based on the conditions in the "year" column.

based on some reading, I attempted this code:

sum (Transactions_per_Year[which(Transactions_per_Year$Year == "2021"),Transactions_per_Year$yearly_transactions]

but my end result was

Error: Incomplete expression: sum (Transactions_per_Year[which(Transactions_per_Year$Year == "2021"),Transactions_per_Year$yearly_transactions]

Bill Disanza
  • 1
  • 1
  • You need a group by sum `aggregate(yearly_transactions ~ Year, Transactions_per_Year, sum)` or with `dplyr` - `Transactions_per_Year %>% reframe(year_transactions = sum(yearly_transactions), .by = Year)` – akrun Feb 27 '23 at 02:04
  • they gets me part of the way there. how can i use those results for a calculation? Example: dividing the rider class totals by total transactions to get a percentage based on rider class? – Bill Disanza Feb 27 '23 at 02:10
  • If you meant the proportions `Transactions_per_Year %>% mutate(prop = proportions(yearly_transactions), .by = Year)` or `Transactions_per_Year %>% mutate(prop = yearly_transactions/sum(yearly_transactions), .by = Year)` would work – akrun Feb 27 '23 at 02:10
  • Your code is actually doing the yearly subset. If you want it by Rider_class, the grouping should be on that `Transactions_per_Year %>% mutate(prop = yearly_transactions/sum(yearly_transactions), .by = Rider_Class)` – akrun Feb 27 '23 at 02:12
  • I tried running that code, adding a label for a new frame Percentages_per_Year<- Transactions_per_Year %>% mutate(prop = yearly_transactions/sum(yearly_transactions), .by = Rider_Class) ``` got the following error: Error in `mutate()`: ! Can't supply `.by` when `.data` is a grouped data frame. Backtrace: 1. Transactions_per_Year %>% ... 3. dplyr:::mutate.data.frame(., prop = yearly_transactions/sum(yearly_transactions), .by = Rider_Class) – Bill Disanza Feb 27 '23 at 02:19
  • What is the error. I assume you have a dplyr version of `1.1.0` i.e. the recent version or else you have to do `Transactions_per_Year %>% group_by(Year) %>% mutate(prop = yearly_transactions/sum(yearly_transactions)) %>% ungroup` – akrun Feb 27 '23 at 02:20
  • edited the above comment to add the error text. dplyr is running 1.1.0 – Bill Disanza Feb 27 '23 at 02:22
  • That error seems to be a result of already grouped data. You may try the edited code above or ungroup before the mutate i.e. `Transactions_per_Year %>% ungroup %>% mutate(prop = yearly_transactions/sum(yearly_transactions), .by = Rider_Class)` – akrun Feb 27 '23 at 02:23
  • wouldn't that sum up all transactions then? The issue is totaling the transactions by year, then dividing by the rider class subset to get a percentage for each year – Bill Disanza Feb 27 '23 at 02:28
  • If you want to sum by year, change the `.by = Rider_Class` to `.by = Year` as it is not clear what you r expected percentage is – akrun Feb 27 '23 at 02:29
  • i.e. I get these values as percentage `Transactions_per_Year %>% mutate(prop = yearly_transactions/sum(yearly_transactions), .by = Year) %>% pull(prop)# [1] 0.4519979 0.5480021 0.4096889 0.5903111` – akrun Feb 27 '23 at 02:30
  • that's what it looks like on my end too. as you can tell I'm pretty new at this. thanks so much for your patience. – Bill Disanza Feb 27 '23 at 02:32
  • It's okay. I was just confused by the logic you wanted to divide. I guess this should solve your problem – akrun Feb 27 '23 at 02:36
  • 1
    looks good to go! thank you for your help! If I save this as a frame and format the prop column to a percent, this should visualize in ggplot2 pretty easily – Bill Disanza Feb 27 '23 at 02:46

0 Answers0