pandas fill in missing data

[vorticalbox]

say i have a csv

date,total
2017-07-20,30
2017-07-21,40
2017-07-25,6
2017-08-05,25
2017-08-18,50

 

how can i get

date,total
2017-07-20,30
2017-07-21,40
2017-07-22,0
2017-07-23,0
2017-07-24,0
2017-07-25,6

    payments = ps.read_csv('payments.csv', header=0)
    df = ps.DataFrame()
    df['ds'] = ps.to_datetime(payments['date'])
    df['y'] = payments['total']

 

I ma planning to use fbprophet to do some predictions.

[tikker]

You could write a loop that checks expected dates and creates an entry if it doesn't exist.

[vorticalbox]

1 minute ago, tikker said:

You could write a loop that checks expected dates and creates an entry if it doesn't exist.

that i could but i just found this magic
 

 df = df.set_index('date').asfreq('d', fill_value=0)

 

[tikker]

5 minutes ago, vorticalbox said:

that i could but i just found this magic
 

 df = df.set_index('date').asfreq('d', fill_value=0)

 

Ah yeah Pandas is neat like that.

[vorticalbox]

40 minutes ago, tikker said:

Ah yeah Pandas is neat like that.

I have found a better way create a second data frame from start to end of the first, fill with 0 then merge with the original df

 

    payments = pd.read_csv('payments.csv', header=0)
    df = pd.DataFrame()
    df['ds'] = pd.to_datetime(payments['date'])
    df['y'] = payments['total']

    df2 = pd.DataFrame((pd.date_range(start=df.ds.min(), end=df.ds.max(), freq='1D')), columns=["ds"])
    df = pd.merge(df2, df, on="ds", how="left").fillna(0)