Python – Pandas – Date Rage in Dataframe with multiple date

  • A+
Category:Languages

Current df:

ID  Date 11  3/19/2018 22  1/5/2018 33  2/12/2018 ..  .. 

I have the df with ID and Date. ID is unique in the original df. I would like to create a new df based on date. Each ID has a Max Date, I would like to use that date and go back 4 days(5 rows each ID) There are thousands of IDs.

Expect to get:

ID  Date 11  3/15/2018 11  3/16/2018 11  3/17/2018 11  3/18/2018 11  3/19/2018 22  1/1/2018 22  1/2/2018 22  1/3/2018 22  1/4/2018 22  1/5/2018 33  2/8/2018 33  2/9/2018 33  2/10/2018 33  2/11/2018 33  2/12/2018 …   … 

I tried the following method, i think use date_range might be right direction, but I keep get error.

pd.date_range

def date_list(row):     list = pd.date_range(row["Date"], periods=5)     return list  df["Date_list"] = df.apply(date_list, axis = "columns") 


group by ID, select the column Date, and for each group generate a series of five days leading up to the greatest date.

rather than writing a long lambda, I've written a helper function.

def drange(x):      e = x.max()     s = e-pd.Timedelta(days=4)     return pd.Series(pd.date_range(s,e))  res = df.groupby('ID').Date.apply(drange) 

Then drop the extraneous level from the resulting multiindex and we get our desired output

res.reset_index(level=0).reset_index(drop=True) # outputs:      ID       Date 0   11 2018-03-15 1   11 2018-03-16 2   11 2018-03-17 3   11 2018-03-18 4   11 2018-03-19 5   22 2018-01-01 6   22 2018-01-02 7   22 2018-01-03 8   22 2018-01-04 9   22 2018-01-05 10  33 2018-02-08 11  33 2018-02-09 12  33 2018-02-10 13  33 2018-02-11 14  33 2018-02-12 

Compact alternative

# Help function to return Serie with daterange func = lambda x: pd.date_range(x.iloc[0]-pd.Timedelta(days=4), x.iloc[0]).to_series()  res = df.groupby('ID').Date.apply(func).reset_index().drop('level_1',1) 

Comment

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen: