dataframe - Is there a way to get the previously received message by id in pandas?

Question

I have a dataframe like this:

ID  Message     week

10 A            1
11 A            1
12 C            1
10 B            2
12 B            2

How can I get one like this?:

ID  Message     week  previous

10 A            1     nan
11 A            1     nan
12 C            1     nan
10 B            2     A
12 B            2     A

question from:https://stackoverflow.com/questions/65852161/is-there-a-way-to-get-the-previously-received-message-by-id-in-pandas

Answer 1

Use an asof merge to bring the closest message in the past. allow_exact_matches=False prevents merging on the same week.

df = df.sort_values('week')  # Only b/c merge_asof requires sorted input

res = (pd.merge_asof(df, df.rename(columns={'Message': 'previous'}), 
                     on='week', by='ID',
                     direction='backward', allow_exact_matches=False))

---

   ID Message  week previous
0  10       A     1      NaN
1  11       A     1      NaN
2  12       C     1      NaN
3  10       B     2        A
4  12       B     2        C