如何用Pandas的diff()函数计算DataFrame日期列差值

Got it, let's work through this. You want to compute the date difference between consecutive rows within each business group, with the first entry in each group showing 0 (or NaN, which you mentioned is acceptable). Here's how to do it:

Step 1: Group by business and compute date differences #

First, we'll use groupby('business') to isolate each business category, then apply diff() on the date column to get the time difference from the previous row in the same group. We'll convert the resulting Timedelta values to days using .dt.days:

import pandas as pd

# Your original DataFrame setup
d={'business':['FX','FX','IR','IR'],
   'name':['ed','ed','a','b'],
   'date':(['01/01/2018','05/02/2018','01/01/2018','05/01/2018']),
   'amt':[1,2,3,4]}
df=pd.DataFrame(data=d)
df['date'] = pd.to_datetime(df['date'],format='%d/%m/%Y')

# Calculate date differences within each business group
df['date diff'] = df.groupby('business')['date'].diff().dt.days

This will give you:

business name       date  amt  date diff
0       FX   ed 2018-01-01    1        NaN
1       FX   ed 2018-02-05    2       35.0
2       IR    a 2018-01-01    3        NaN
3       IR    b 2018-01-05    4        4.0

Step 2: Replace NaN with 0 (optional) #

If you prefer 0 instead of NaN for the first entry in each group (as your desired output shows), use fillna(0):

df['date diff'] = df.groupby('business')['date'].diff().dt.days.fillna(0)

Resulting in:

business name       date  amt  date diff
0       FX   ed 2018-01-01    1        0.0
1       FX   ed 2018-02-05    2       35.0
2       IR    a 2018-01-01    3        0.0
3       IR    b 2018-01-05    4        4.0

Note: The FX group's date difference is 35 days because 2018-02-05 minus 2018-01-01 is 35 days. If your desired output of 4 days for the FX group was intentional, double-check the date value in your original data (maybe it was supposed to be 05/01/2018 instead of 05/02/2018). The code will correctly compute the difference based on the dates provided.

内容的提问来源于stack exchange，提问作者Number Logic