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
Change Rows into Columns in R With Values Yes/No (1/0)
How to Generate the First N Terms in the Series:
How to Save a Plot as Image on the Disk
Add Count of Unique/Distinct Values by Group to the Original Data
Replace Missing Values (Na) With Most Recent Non-Na by Group
How to Sum a Variable by Group
Dynamically Select Data Frame Columns Using $ and a Character Value
How to Convert a Factor to Integer\Numeric Without Loss of Information
Transpose/Reshape Dataframe Without "Timevar" from Long to Wide Format
How to Deal With "Package 'Xxx' Is Not Available (For R Version X.Y.Z)" Warning
Split Data Frame String Column into Multiple Columns
Formatting Decimal Places in R
Select/Assign to Data.Table When Variable Names Are Stored in a Character Vector
Splitting a Dataframe String Column into Multiple Different Columns
Summarizing Multiple Columns With Dplyr
Create Grouping Variable For Consecutive Sequences and Split Vector