Add missing dates pandas dataframe

Question

I have a Pandas Dataframe that looks like below. I want to add the the missing dates in column B from today(2021-01-03) and 30days forward. And at the same time add 0 to column A and NaN in column C.

I have looked around and tried to look at some similar questions like this and this but I cant figure out how to do it. Please help me.

          A           B            C
0      4889  2021-01-03   2021-01-02 
1      4853  2021-01-04   2021-01-02 
2      -618  2021-01-10   2021-01-01
3    -25318  2021-01-18   2021-01-03 
4     -4064  2021-02-01   2021-01-02

Thanks in advance!

Erfan · Accepted Answer · 2021-01-03 20:17:21Z

4

We can create a date range for the next 30 days, then reindex our dataframe:

df['B'] = pd.to_datetime(df['B'])

dates = pd.date_range(start=pd.Timestamp('now'), freq='D', periods=30)
df = df.set_index('B').reindex(dates.normalize().rename('B')).reset_index()
df['A'] = df['A'].fillna(0)

            B        A           C
0  2021-01-03   4889.0  2021-01-02
1  2021-01-04   4853.0  2021-01-02
2  2021-01-05      0.0         NaN
3  2021-01-06      0.0         NaN
4  2021-01-07      0.0         NaN
5  2021-01-08      0.0         NaN
6  2021-01-09      0.0         NaN
7  2021-01-10   -618.0  2021-01-01
8  2021-01-11      0.0         NaN
9  2021-01-12      0.0         NaN
10 2021-01-13      0.0         NaN
11 2021-01-14      0.0         NaN
12 2021-01-15      0.0         NaN
13 2021-01-16      0.0         NaN
14 2021-01-17      0.0         NaN
15 2021-01-18 -25318.0  2021-01-03
16 2021-01-19      0.0         NaN
17 2021-01-20      0.0         NaN
18 2021-01-21      0.0         NaN
19 2021-01-22      0.0         NaN
20 2021-01-23      0.0         NaN
21 2021-01-24      0.0         NaN
22 2021-01-25      0.0         NaN
23 2021-01-26      0.0         NaN
24 2021-01-27      0.0         NaN
25 2021-01-28      0.0         NaN
26 2021-01-29      0.0         NaN
27 2021-01-30      0.0         NaN
28 2021-01-31      0.0         NaN
29 2021-02-01  -4064.0  2021-01-02

answered Jan 3, 2021 at 20:17

Erfan

43.3k10 gold badges75 silver badges86 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

MaxU - stand with Ukraine Over a year ago

@cha28, there is a better way to say “thank you” on StackOverflow ;)

cha28 Over a year ago

@MaxU, Done! :)

Collectives™ on Stack Overflow

Add missing dates pandas dataframe

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related