R equivalent of Excel's "Sumif(s)" function across like columns

debugcn Published at Dev

DR_

I am fairly new to R (new to this site as well) and trying to understand how to aggregate data across columns in a situation where there is more than 1 identifier (in this case, two: PERSON_ID and PRODUCT_ID).

Please see my example below. To the right of the two identifiers within my data frame are five columns containing weekly sales figures. I need to aggregate the weekly data so that:

1: Week columns with the same name are summed (typically this is something I can easily accomplish in MS Excel using the sumif/sumifs function)

2: Any rows containing the same PERSON_ID and PRODUCT_ID combination are summed as well.

In this particular case, notice that the week of 6/2/2017 appears in more than one column. Meanwhile, PERSON_ID 0003603 appears twice for the same PRODUCT_ID, 3024.

PERSON_ID    PRODUCT_ID    6/23/2017   6/16/2017   6/9/2017   6/2/2017   6/2/2017
0003603      3024          10.000      5.000       4.000      3.000      2.000
0003603      3024          1.000       2.000       3.000      8.000      1.000     
0007654      2111          8.000       3.000       2.000      1.000      0.000
0008885      3025          0.000       0.000       1.000      3.000      9.000
0950645      3024          6.000       5.000       4.000      3.000      2.000

My actual data frame contains in excess of 1 million records, so an approach using the data.table package would be ideal, as far as I can tell.

Can someone please shed some light on how to solve this particular problem in R?

MichaelChirico

melting your data (reshaping long) is the way to go. If I understand what you're after correctly, it's simply:

x = fread('PERSON_ID    PRODUCT_ID  6/23/2017   6/16/2017   6/9/2017    6/2/2017    6/2/2017
0003603 3024    10.000  5.000   4.000   3.000   2.000
0003603 3024    1.000   2.000   3.000   8.000   1.000
0007654 2111    8.000   3.000   2.000   1.000   0.000
0008885 3025    0.000   0.000   1.000   3.000   9.000
0950645 3024    6.000   5.000   4.000   3.000   2.000',
          colClasses = c('character', 'character', rep('numeric', 5L)))

xmlt = 
  melt(x, id.vars = c('PERSON_ID', 'PRODUCT_ID'),
       variable.name = 'week', value.name = 'sales')

xmlt[ , week := as.IDate(week, format = '%m/%d/%Y')]

xmlt[ , .(total_sales = sum(sales)), 
      keyby = .(PERSON_ID, PRODUCT_ID, week)]
    PERSON_ID PRODUCT_ID       week total_sales
#  1:   0003603       3024 2017-06-02          14
#  2:   0003603       3024 2017-06-09           7
#  3:   0003603       3024 2017-06-16           7
#  4:   0003603       3024 2017-06-23          11
#  5:   0007654       2111 2017-06-02           1
#  6:   0007654       2111 2017-06-09           2
#  7:   0007654       2111 2017-06-16           3
#  8:   0007654       2111 2017-06-23           8
#  9:   0008885       3025 2017-06-02          12
# 10:   0008885       3025 2017-06-09           1
# 11:   0008885       3025 2017-06-16           0
# 12:   0008885       3025 2017-06-23           0
# 13:   0950645       3024 2017-06-02           5
# 14:   0950645       3024 2017-06-09           4
# 15:   0950645       3024 2017-06-16           5
# 16:   0950645       3024 2017-06-23           6

Collected from the Internet

Please contact [email protected] to delete if infringement.

edited at2021-07-30

Comments

0 comments

From Java

Related Related

Article

R equivalent of Excel's "Sumif(s)" function across like columns

R equivalent of Excel's "Sumif(s)" function across like columns

Pandas: Excel sumifs equivalent

Excel - SUMIFS for multiple columns

SUMIFS function across multiple sheets

SUMIFS function excel

R: Equivalent for Sumif and Countif by Categorical variable across columns in R

What is equivalent function of excel "fdist" function in R?

Excel SUMIFS function with complex criteria

SQL Server equivalent of Excel's TINV function

What is the MATLAB equivalent of Excel's NORMSDIST function?

Cognos equivalent of excel's sumif() function

C# equivalent of Excel's TINV function

What is the MATLAB equivalent of Excel's NORMSDIST function?

Cognos equivalent of excel's sumif() function

SQL Server equivalent of Excel's TINV function

Python's equivalent for R's dput() function

Is there a Python equivalent to R's sample() function?

Equivalent of R's factor function in Pandas

Python equivalent of R's head and tail function

R: Is there an equivalent of Stata's ibn. function?

Expand the for loop across n number of columns by making a function in R

Panda's equivalent of R's order() for arranging dataframe columns

What is the R equivalent of SQL's "LIKE '%searched_word%'"?

Passing multiple values to SUMIFS function in Excel

Using Max function result in SumIFS Excel

Excel SUMIFS function not returning results consistently

Equivalent of Excel Round function

SQL Server - any equivalent of Excel's CHOOSE function?

SQL round down by significance (equivalent of excel's floor function)

Grouped function across multiple columns