R Conditional Summarizing

Question

I have a column for company, one for sales and another column for country.I need to sum all the sales in each of the countries separately so that I would have one column for each of the companies(names) for the total sales in the country. The sales in all of the countries is expressed in the same currency.

I have tried several ways of doing so, but neither of them work:

df$total_country_sales = if(df$country[row] == df$country) { sum(df$sales)} 
This sums all valuations, not only the ones that I need.


Name  Sales  Country   I would like to have a new column Total Country Sales 
abc   122    US                                                5022
abc   100    Canada
aad   4900   US

I need to have the values in the same dataframe, but in a new column.

Since it is a large dataset, I cannot make a function to do so, but rather need to save it directly as a variable. (Or have I understood incorrectly that making functions is not the best way to solve such issues?)

I am new to R and programming in general, so I might be addressing the issue in an incorrect way.

Sorry for probably a stupid question.

Thanks!

Please see here https://stackoverflow.com/questions/5963269/how-to-make-a-great-r-reproducible-example for a guide on how to make a reproducible example. It would greatly help in answering your question if you could share some of your code and data (using dput). — Florian, Jul 16 '17 at 10:34

score 0 · Answer 1 · answered Jul 16 '17 at 10:50

If I understand your question correctly, this solves your problem:

df = data.frame(sales=c(1,3,2,4,5),region=c("A","A","B","B","B"))

library(dplyr)
totals = df %>% group_by(region) %>% summarize(total = sum(sales))
df = left_join(df,totals)

It adds the group totals as a separate column, like this:

  sales region total
1     1      A     4
2     3      A     4
3     2      B    11
4     4      B    11
5     5      B    11

Hope this helps.

score 0 · Answer 2 · answered Jul 16 '17 at 10:52

0

We can use base R to do this

df$total_country_sales <- with(df, ave(sales, country, FUN = sum))

answered Jul 16 '17 at 10:52

akrun

874,273
37
540
662

Prradep · Answer 3 · 2017-07-16T11:16:56.797

It can be achieved using dplyr's mutate()

df = data.frame(sales=c(1,3,2,4,5),country=c("A","A","B","B","B"))
df
#   sales country
# 1     1       A
# 2     3       A
# 3     2       B
# 4     4       B
# 5     5       B

df %>% group_by(country) %>% mutate(total_sales = sum(sales))
# Source: local data frame [5 x 3]
# Groups: country [2]
# 
# # A tibble: 5 x 3
#   sales country total_sales
#   <dbl>  <fctr>       <dbl>
# 1     1       A           4
# 2     3       A           4
# 3     2       B          11
# 4     4       B          11
# 5     5       B          11

using data.table

library(data.table)
setDT(df)[, total_sales := sum(sales), by = country]
df
#    sales country total_sales  
# 1:     1       A           4
# 2:     3       A           4
# 3:     2       B          11
# 4:     4       B          11
# 5:     5       B          11

R Conditional Summarizing

3 Answers3