How to get 1 for 8 days after a date in pandas and 0 otherwise?

user_11304830 0
Viewed 62

I have two dataframes:


daily = pd.DataFrame({'Date': pd.date_range(start="2021-01-01",end="2021-04-29")})
pc21 = pd.DataFrame({'Date': ["2021-01-21", "2021-03-11", "2021-04-22"]})
pc21['Date'] = pd.to_datetime(pc21['Date'])

What I want to do is the following: for every date in pc21 and if the date in pc21 is in daily, I want to get, in a new column, values equal 1 for 8 days after the date and 0 otherwise.

This is an example of a desired output:

# 2021-01-21 is in either daframes so I want a new column in 'daily' that looks like this:

Date           newcol
.
.
.
2021-01-20       0
2021-01-21       1
2021-01-22       1 
2021-01-23       1
2021-01-24       1
2021-01-25       1
2021-01-26       1
2021-01-27       1
2021-01-28       1
2021-01-29       0
.
.
.

Can anyone help me achieve this?

Thanks!

pandas dataframe python
3 Answers

you can try the following approach:

res = (daily
       .merge(pd.concat([pd.date_range(d, freq="D", periods=8).to_frame(name="Date")
                         for d in pc21["Date"]]), 
                        how="left", indicator=True)
       .replace({"both": 1, "left_only":0})
       .rename(columns={"_merge":"newcol"}))

result

In [15]: res
Out[15]:
          Date  newcol
0   2021-01-01       0
1   2021-01-02       0
2   2021-01-03       0
3   2021-01-04       0
4   2021-01-05       0
..         ...     ...
114 2021-04-25       1
115 2021-04-26       1
116 2021-04-27       1
117 2021-04-28       1
118 2021-04-29       1

[119 rows x 2 columns]
user_57412050 
daily["new_col"] = np.where(daily.Date.isin(pc21.Date), 1, np.nan)
daily["new_col"] = daily["new_col"].fillna(method="ffill", limit=7).fillna(0)
  • We generate the new column first:
    • If the Date of daily is in Date of pc21
      • then put 1
    • else
      • put a NaN
  • Then forward fill that column but with a limit of 7 so that we have 8 consecutive 1s
  • Lastly forward fill again the remaining NaNs with 0.

(you can put an astype(int) at the end to have integers).

user_93321870 
daily['value'] = 0
pc21['value'] = 1
daily = pd.merge(daily, pc21, on='Date', how='left').rename(
    columns={'value_y':'value'}).drop('value_x', 1).fillna(method="ffill", limit=7).fillna(0)
pc21.drop('value',1)

Output Subset

daily.query('value.eq(1)')

    Date    value
20  2021-01-21  1.0
21  2021-01-22  1.0
22  2021-01-23  1.0
23  2021-01-24  1.0
24  2021-01-25  1.0
25  2021-01-26  1.0
26  2021-01-27  1.0
27  2021-01-28  1.0
69  2021-03-11  1.0
user_21069340
Related