Build a column with previous month information in a dataframe-CodePudding

I have this example of df

I do some transformations on it and I need to get my mark value from the previous month in a new column to make comparisons. that column should have the maximum value of the column 'mark' according to the different values in 'id' column.

here is an example of the dataframe

df = pd.DataFrame({'date':['202301','202301','202301','202301','202302','202302','202302','202302','202303','202303','202303','202304','202304'], 
            'mark': [1,1,2,3,1,1,1,1,1,3,1,1,1
],
                  'id':[20,20,21,21,20,20,21,21,20,20,21,20,21
]})

and here is the desired output

date    mark    id  mark_previous
202301  1   20  0
202301  1   20  0
202301  2   21  0
202301  3   21  0
202302  1   20  1
202302  1   20  1
202302  1   21  3
202302  1   21  3
202303  1   20  1
202303  3   20  1
202303  1   21  1
202304  1   20  3
202304  1   21  1

What do you recommend to obtain that column?

Best regards!

CodePudding user response：

Code

g = df.groupby(['date', 'id'])
df['mark_previous'] = g.tail(1).groupby('id')['mark'].shift()
df['mark_previous'] = g['mark_previous'].transform('max').fillna(0).astype('int')

df :

    date    mark    id  mark_previous
0   202301  1       20  0
1   202301  1       20  0
2   202301  2       21  0
3   202301  3       21  0
4   202302  1       20  1
5   202302  1       20  1
6   202302  1       21  3
7   202302  1       21  3
8   202303  1       20  1
9   202303  3       20  1
10  202303  1       21  1
11  202304  1       20  3
12  202304  1       21  1

Intermediate

g.tail(1).groupby('id')['mark'].shift()

1       NaN
3       NaN
5    1.0000
7    3.0000
9    1.0000
10   1.0000
11   3.0000
12   1.0000
Name: mark, dtype: float64

CodePudding user response：

You can achieve what you're looking for with the following steps:

Convert the date to a datetime format, such as

df['date'] = pd.to_datetime(df['date'], format='%Y%m')

Sort the DataFrame by 'id' and 'date'

df = df.sort_values(by=['id', 'date'])

Group the DataFrame by 'id' and use the groupby and shift functions

df['mark_shifted'] = df.groupby('id')['mark'].shift(1)

Create and use a cumulative maximum

df['mark_cummax'] = df.groupby(['id', df['date'].dt.to_period('M')])['mark_shifted'].cummax()

Clean things up

df['mark_previous'] = df['mark_cummax'].fillna(0).astype(int)
df.drop(columns=['mark_shifted', 'mark_cummax'], inplace=True)

Give this a try!

Source: My article https://ioflood.com/blog/pandas-dataframe/