How can I fill a serie of dates with 0 value

I am counting how many times repeat a date in an excel sheet (filtering by month), but in the cases that the date doesnt exist, i want to fill it with a 0 in the value_counts() function. Then i need to plot it.

Imagine that is a bunch of trucks dispatching some product:2022-07-04 i have only 1 truck, and then 2022-07-06 I have another, but 2022-07-05 doesnt exist, so it was 0 trucks that day (see picture)

df_log_mel['Elecmetal Date'].loc()

july_log_mel = df_log_mel['Elecmetal Date'][2340:2378].value_counts(sort=False)

july_log_mel

Result:

2022-07-01    1
2022-07-02    2
2022-07-03    2
2022-07-04    1
2022-07-06    1
2022-07-07    1
2022-07-08    1
2022-07-09    2
2022-07-10    1
2022-07-11    1
2022-07-14    2
2022-07-15    1
2022-07-16    1
2022-07-17    2
2022-07-18    2
2022-07-19    2
2022-07-20    2
2022-07-21    2
2022-07-23    1
2022-07-24    1
2022-07-25    1
2022-07-26    1
2022-07-27    2
2022-07-29    2
2022-07-30    2
2022-07-31    1
Name: Elecmetal Date, dtype: int64

dates value_counts

Any ideas?

CodePudding user response：

Assuming you want daily value counts, use asfreq and fillna:

july_log_mel.index = pd.to_datetime(july_log_mel.index)
july_log_mel.asfreq('D').fillna(0)

CodePudding user response：

You can reindex your Series with date_range:

s = df['date'].value_counts()

s = s.reindex(pd.date_range(s.index.min(), s.index.max(), freq='D')
                .strftime('%Y-%m-%d'),
              fill_value=0)

output:

2022-07-04    2
2022-07-05    0
2022-07-06    1
2022-07-07    0
2022-07-08    1
Name: date, dtype: int64

used input:

         date
0  2022-07-04
1  2022-07-04
2  2022-07-06
3  2022-07-08