CODEWITHSUNDEEP
rdplyr
ant
ant

Reputation: 585

Finding the differences depending if a number is decreased or increased with Dplyr

> df
Date      User  Current_Coins
01/01      1     150
01/02      1     100
01/01      2     100
01/02      2     150
01/01      3     100
01/02      3     150
01/03      3     100
01/04      3     200
01/04      3       0

Based on how many coins the user currently has I want to summarize the sum of coins used and gained using dplyr.

Expected result:

> df
User    Coins_Gained    Coins_Used
 1           0              50
 2          50               0
 3         150             250

I tried using lag() but doesn't separate the usage and gains in coins. I can't think of an eloquent solution to this, any help would be appreciated.

Upvotes: 4

Views: 79

Answers (2)

akrun
akrun

Reputation: 887701

If you like to explore using data.table, here is one way. Here, I am using similar strategy as @docendo discimus, and used shift (a new function in data.table)

 library(data.table) #data.table_1.9.5
 setDT(df)[,{tmp=Current_Coins-shift(Current_Coins)
       list( Coins_gained=sum(tmp[tmp>0], na.rm=TRUE),
      Coins_Used=abs(sum(tmp[tmp<0], na.rm=TRUE)))} , User]
 #   User Coins_gained Coins_Used
 #1:    1            0         50
 #2:    2           50          0
 #3:    3          150        250

Upvotes: 3

talat
talat

Reputation: 70326

Here's one way to do it:

library(dplyr)
df %>% 
  group_by(User) %>% 
  mutate(x = Current_Coins - lag(Current_Coins)) %>%        # compute the differences
  summarise(Coin_gained = sum(x[x>0], na.rm = TRUE),        # sum up positives
            Coin_used = abs(sum(x[x<0], na.rm = TRUE)))     # sum up negatives

#Source: local data frame [3 x 3]
#
#  User Coin_gained Coin_used
#1    1           0        50
#2    2          50         0
#3    3         150       250

Upvotes: 6

Related Questions