Filling missing date (day) values with 0

user_2993359 0
Viewed 185

I have a dataframe:

     day  Datavalue
    2020-06-01   3.179695
    2020-06-02   0.132487
    2020-06-08   3.179695
    2020-06-09   3.179695
    2020-06-10   3.179695

I would like to set a date range and add any dates that aren't in the dataframe as 0 for example:

     day  Datavalue
    2020-06-01   3.179695
    2020-06-02   0.132487
    2020-06-03   0
    2020-06-04   0
    2020-06-05   0
    2020-06-06   0
    2020-06-07   0
    2020-06-08   3.179695
    2020-06-09   3.179695
    2020-06-10   3.179695

I have tried

      mydates = pd.period_range(date - timedelta(40), date + timedelta(40)
      x = data.set_index('day') 
      x = data.reindex(mydates, fill_value=0)

but this just sets it all to zeros

y

what am I doing wrong?

thanks

pandas indexing python
2 Answers

Assuming this is to be done for the entire DataFrame, use asfreq:

df.set_index('day').asfreq('1D', fill_value=0)

            Datavalue
day                  
2020-06-01   3.179695
2020-06-02   0.132487
2020-06-03   0.000000
2020-06-04   0.000000
2020-06-05   0.000000
2020-06-06   0.000000
2020-06-07   0.000000
2020-06-08   3.179695
2020-06-09   3.179695
2020-06-10   3.179695
user_49090870

Something like this could work:

delta = 2 # number of days before first value and after last value (as it seems to be needed from your code)

mydates = pd.period_range(df.date.iloc[0] - timedelta(delta), df.date.iloc[-1] + timedelta(delta))

# Change PeriodIndex object to datetime type:
mydates = mydates.to_timestamp() 

# Create dates dataframe and merge with original df containing values
dates_df = pd.DataFrame(mydates, columns=['date'])
new_df= pd.merge(df, dates_df, on='date', how='outer').sort_values('date').fillna(0)
user_75988960
Related