0
330
ViewsReturn previous row value based on "ID" column to find differences between current row and previous row
I am new to python. I am having a problem trying to look up previous row values based on "ID" and "timestamp" in a python dataframe. Can anyone please advise how I should derive the solution?
Below is a simple example:
For example:
id: timestamp: sapStock laststockdate laststockvalue
167777 14/12/2021 184 13/12/2021 143 169406 14/12/2021 56 13/12/2021 60
#Import Libraries
import pandas as pd
#Read CSV
df = pd.read_csv(r'C:\Users\User\OneDrive\Desktop\Test.csv')
#Preview dataframe
df
#Get last value of sapStock based on "id" and "timestamp"
df['sapStockDifference'] = df.sapStock.diff(periods=1)
df
·
Santiago Trujillo
1 answers
Answer question0
I hope this may help:
import pandas as pd
import numpy as np
df = pd.DataFrame({'id': ['169653', '167777', '169406', '165253', '169653', '167777', '169406', '165253', '169653', '167777', '169406', '165253', '169653', '167777', '169406', '165253'],
'sapStock': [234, 162, 36, 47, 264, 158, 60, 46, 279, 143, 60, 46, 364, 184, 56, 46],
'timestamp': ['11/12/2021', '11/12/2021', '11/12/2021', '11/12/2021', '12/12/2021', '12/12/2021', '12/12/2021', '12/12/2021', '13/12/2021', '13/12/2021', '13/12/2021', '13/12/2021', '14/12/2021', '14/12/2021', '14/12/2021', '14/12/2021']})
df['timestamp'] = pd.to_datetime(df['timestamp'])
df['laststockvalue'] = df.sort_values(['timestamp']).groupby(df.id)['sapStock'].apply(lambda x: x.shift(1)).fillna(value=np.nan)
df['Difference'] = df.sort_values(['timestamp']).groupby(df.id)['sapStock'].apply(lambda x: x - x.shift(1)).fillna(value=np.nan)
print(df)
# id sapStock timestamp laststockvalue Difference
# 0 169653 234 2021-11-12 NaN NaN
# 1 167777 162 2021-11-12 NaN NaN
# 2 169406 36 2021-11-12 NaN NaN
# 3 165253 47 2021-11-12 NaN NaN
# 4 169653 264 2021-12-12 234.0 30.0
# 5 167777 158 2021-12-12 162.0 -4.0
# 6 169406 60 2021-12-12 36.0 24.0
# 7 165253 46 2021-12-12 47.0 -1.0
# 8 169653 279 2021-12-13 264.0 15.0
# 9 167777 143 2021-12-13 158.0 -15.0
# 10 169406 60 2021-12-13 60.0 0.0
# 11 165253 46 2021-12-13 46.0 0.0
# 12 169653 364 2021-12-14 279.0 85.0
# 13 167777 184 2021-12-14 143.0 41.0
# 14 169406 56 2021-12-14 60.0 -4.0
# 15 165253 46 2021-12-14 46.0 0.0
·
Santiago Trujillo
Report
Answer question