expand datetime data in pandas like interpolation

Question

I have the following data of dates and every date is assigned to the value 1

is there a way to somehow get a pandas list of hourly DateTime list such that all the values are 0 except for the one's I have in my xls file? it is similar to interpolating but interpolating just interpolates whereas here I want just the rest of the date to be filled as 0.I want the entire 24 hours of the below dates to be assigned as one.I tried to do it in a for loop method but it just takes longer than ever and is very much nonpractical

Please include input (no images) as well as expected output (no images). Please see: https://stackoverflow.com/questions/20109391/how-to-make-good-reproducible-pandas-examples which shows you how you can create a minimum reproducible pandas example, so that your question will have a higher chance of being answered. — David Erickson, Oct 21 '20 at 21:27

score 0 · Accepted Answer · answered Oct 21 '20 at 22:06

Use pandas datetime accessor pd.Series.dt.date to extract the date part from datetime objects. And then use .isin() to match the values.

# sample data
df = pd.DataFrame({  # list of dates
    "date": [date(2020,10,2), date(2020,10,4)]
})
df_hr = pd.DataFrame({  # list of hours from Oct.1 to 4
    "hr": [datetime(2020,10,1,0,0) + i * timedelta(hours=1) for i in range(24*4)]
})

df_hr["flag"] = 0
df_hr.loc[df_hr["hr"].dt.date.isin(df["date"]), "flag"] = 1

# show the first and last hour of each day
df_hr.loc[[0,23,24,47,48,71,72,95]]
Out[111]: 
                    hr  flag
0  2020-10-01 00:00:00     0
23 2020-10-01 23:00:00     0
24 2020-10-02 00:00:00     1
47 2020-10-02 23:00:00     1
48 2020-10-03 00:00:00     0
71 2020-10-03 23:00:00     0
72 2020-10-04 00:00:00     1
95 2020-10-04 23:00:00     1

expand datetime data in pandas like interpolation

1 Answers1