Subtract Value from Previous Row by Group

subtract value from previous row by group

With dplyr:

library(dplyr)

data %>%
group_by(id) %>%
arrange(date) %>%
mutate(diff = value - lag(value, default = first(value)))

For clarity you can arrange by date and grouping column (as per comment by lawyer)

data %>%
group_by(id) %>%
arrange(date, .by_group = TRUE) %>%
mutate(diff = value - lag(value, default = first(value)))

or lag with order_by:

data %>%
group_by(id) %>%
mutate(diff = value - lag(value, default = first(value), order_by = date))

With data.table:

library(data.table)

dt <- as.data.table(data)
setkey(dt, id, date)
dt[, diff := value - shift(value, fill = first(value)), by = id]

Subtract a row from previous row which has value from previous group in DataFrame

If in E column are unique groups use DataFrameGroupBy.diff, replace mising values by original with Series.fillna and use Series.where with mask for consecutive values (compared for not equal shifted values) and then forward filling missing values with ffill and last to integers:

df['A1'] = (df.groupby('user')['A'].diff()
.fillna(df['A'])
.where(df['E'].ne(df['E'].shift()))
.ffill()
.astype(int))
print (df)
A E user A1
0 0 0 1 0
1 12 1 1 12
2 12 1 1 12
3 13 2 1 1
4 15 3 1 2
5 15 3 1 2
6 15 3 1 2
7 19 4 2 19
8 20 5 2 1
9 25 6 2 5
10 25 6 2 5

Subtracting value from previous row sql by group

You seem to just want lag():

select el.*,
(lag(estoque, 0, estoque) over (partition by centro, c, m order by date_mif) -
estoque
) as diff
from energylog el;

EDIT:

For the edited question, only two columns seem to comprise a group

select el.*,
(lag(estoque, 0, value) over (partition by centro, canal, order by date_mif) -
estoque
) as diff
from energylog el;

Conditional shift: Subtract 'previous row value' from 'current row value' with multiple conditions in pandas

You may try something like this:

df['DiffHeartRate']=(df.groupby(['Disease', 'State', 
(df.MonthStart.dt.month.ne(df.MonthStart.dt.month.shift()+1)).cumsum()])['HeartRate']
.apply(lambda x: x.diff())).fillna(df.HeartRate)


    Disease HeartRate   State   MonthStart  MonthEnd    DiffHeartRate
0 Covid 89 Texas 2020-02-28 2020-03-31 89.0
1 Covid 91 Texas 2020-03-31 2020-04-30 2.0
2 Covid 87 Texas 2020-07-31 2020-08-30 87.0
3 Cancer 90 Texas 2020-02-28 2020-03-31 90.0
4 Cancer 88 Florida 2020-03-31 2020-04-30 88.0
5 Covid 89 Florida 2020-02-28 2020-03-31 89.0
6 Covid 87 Florida 2020-03-31 2020-04-30 -2.0
7 Flu 90 Florida 2020-02-28 2020-03-31 90.0

Logic is same as the other answers but different way of representing.

Subtract current row value by previous row value and get balance amount to current row

You can use window functions:

select
t.*,
sum(topupAmount - chargeAmount) over(order by row_num) balanceAmount
from mytable t

Actually by looking at your query it seems like row_num is a generated column, so you likely want:

select
t.*,
sum(topupAmount - chargeAmount) over(order by sortDate) balanceAmount
from mytable t


Related Topics



Leave a reply



Submit