CODEWITHSUNDEEP
pythonpandas
rosefun
rosefun

Reputation: 1857

How to fillna the data according to the date in pandas

There are some data is missing according to the date, for example, in the following example, only 3 days of data is existing in October, but I expect everyday's record.

import pandas as pd

df = pd.DataFrame({'date':['2019-10-1','2019-10-2','2019-10-31'], 'times':[4,6,3]})
print(df)

Output：

         date  times
0   2019-10-1      4
1   2019-10-2      6
2  2019-10-31      3

Expected:

         date  times
0   2019-10-1      4
1   2019-10-2      6
2   2019-10-3      0
3   2019-10-4      0
    ...
30  2019-10-31     3

So how to add these intermediate samples ？

Upvotes: 3

Views: 66

Answers (2)

ansev
ansev

Reputation: 30920

You can aslo use pd.date_range:

df['date']=pd.to_datetime(df['date'])
dates=pd.date_range(df['date'].min(),df['date'].max())
df.set_index(df['date'])['times'].reindex(index=dates).fillna(0).reset_index()

        index  times
0  2019-10-01    4.0
1  2019-10-02    6.0
2  2019-10-03    0.0
3  2019-10-04    0.0
4  2019-10-05    0.0
5  2019-10-06    0.0
6  2019-10-07    0.0
7  2019-10-08    0.0
8  2019-10-09    0.0
9  2019-10-10    0.0
10 2019-10-11    0.0
11 2019-10-12    0.0
12 2019-10-13    0.0
13 2019-10-14    0.0
14 2019-10-15    0.0
15 2019-10-16    0.0
16 2019-10-17    0.0
17 2019-10-18    0.0
18 2019-10-19    0.0
19 2019-10-20    0.0
20 2019-10-21    0.0
21 2019-10-22    0.0
22 2019-10-23    0.0
23 2019-10-24    0.0
24 2019-10-25    0.0
25 2019-10-26    0.0
26 2019-10-27    0.0
27 2019-10-28    0.0
28 2019-10-29    0.0
29 2019-10-30    0.0
30 2019-10-31    3.0

Upvotes: 1

jezrael
jezrael

Reputation: 862441

Create DatetimeIndex first and for append missing datetimes use DataFrame.asfreq:

df['date'] = pd.to_datetime(df['date'])
df = df.set_index('date').asfreq('d', fill_value=0).reset_index()
print (df)
         date  times
0  2019-10-01      4
1  2019-10-02      6
2  2019-10-03      0
3  2019-10-04      0
4  2019-10-05      0
5  2019-10-06      0
...
...
20 2019-10-21      0
21 2019-10-22      0
22 2019-10-23      0
23 2019-10-24      0
24 2019-10-25      0
25 2019-10-26      0
26 2019-10-27      0
27 2019-10-28      0
28 2019-10-29      0
29 2019-10-30      0
30 2019-10-31      3

Upvotes: 3

Related Questions