CODEWITHSUNDEEP
r
Natasha R.
Natasha R.

Reputation: 531

Perform a function on a subset of rows depending on month?

I have a dataset like this:

Company Month   Price   Quantity
A   1/1/2014    46  4788
B   1/1/2014    43  6242
C   1/1/2014    50  5432
D   1/1/2014    26  4153
A   2/1/2014    21  5301
B   2/1/2014    46  2706
C   2/1/2014    33  9803
D   2/1/2014    42  9208
A   3/1/2014    45  6309
B   3/1/2014    30  9457
C   3/1/2014    20  6050
D   3/1/2014    16  3151

I am attempting to perform a simple function sum(price * quantity) that is summed for each month in the dataset and send the results to a new dataframe.

The results would look like this:

Month   Result
1/1/2014    868232
2/1/2014    946032
3/1/2014    739031

I've tried the aggregate() function but I'm not having any luck. Is this as simple as the subset() function? Thanks in advance for your help.

Upvotes: 0

Views: 27

Answers (2)

TTR
TTR

Reputation: 129

The dplyr and lubridate package lets you group by month and then summarise:

library(dplyr)
library(lubridate)
sum_monthly <- df %>% 
  mutate(Month = mdy(Month)) %>% # Converting month to date format
  group_by(month(Month)) %>% 
  summarise(Result = sum(Quantity * Price))

Upvotes: 0

akrun
akrun

Reputation: 886948

After grouping by 'Month', get the product of 'Price', 'Quantity' and sum it

library(dplyr)
df1 %>%
  group_by(Month) %>% 
  summarise(Result = sum(Price*Quantity))
# A tibble: 3 x 2
#  Month    Result
#  <chr>     <int>
#1 1/1/2014 868232
#2 2/1/2014 946032
#3 3/1/2014 739031

Or using aggregate

aggregate(cbind(Result = Price*Quantity) ~ Month, df1, FUN = sum)
#      Month Result
#1 1/1/2014 868232
#2 2/1/2014 946032
#3 3/1/2014 739031

Upvotes: 1

Related Questions