Reindex pandas DataFrame to fill missing dates

Question

I have daily data in the pandas DataFrame df with certain days missing (e.g. 1980-12-25 below). I would like to reindex the DataFrame to add those dates with NaN values.

           date  close
None                  
0    1980-12-12  28.75
1    1980-12-15  27.25
2    1980-12-16  25.25
3    1980-12-17  25.87
4    1980-12-18  26.63
5    1980-12-19  28.25
6    1980-12-22  29.63
7    1980-12-23  30.88
8    1980-12-24  32.50
9    1980-12-26  35.50

I have generated the list dates with the full set of dates I want.

[Timestamp('1980-12-12 00:00:00'), Timestamp('1980-12-15 00:00:00'), Timestamp('1980-12-16 00:00:00'), Timestamp('1980-12-17 00:00:00'), Timestamp('1980-12-18 00:00:00'), Timestamp('1980-12-19 00:00:00'), Timestamp('1980-12-22 00:00:00'), Timestamp('1980-12-23 00:00:00'), Timestamp('1980-12-24 00:00:00'), Timestamp('1980-12-25 00:00:00'), Timestamp('1980-12-26 00:00:00')]

Unfortunately when I run the reindex command below, the table becomes completely filled with NaN.

df.reindex(dates)

I ran the below checks, which all check out fine...

>>> type(df['date'][0])
<class 'pandas._libs.tslib.Timestamp'>

>>> type(dates[0])
<class 'pandas._libs.tslib.Timestamp'>

>>> dates[0] == df['date'][0]
True

Andrew L · Accepted Answer · 2017-07-17 13:26:39Z

1

From what I see in your question, you'll need to set_index():

df
         date  close
0  1980-12-12  28.75
1  1980-12-15  27.25
2  1980-12-16  25.25
3  1980-12-17  25.87
4  1980-12-18  26.63
5  1980-12-19  28.25
6  1980-12-22  29.63
7  1980-12-23  30.88
8  1980-12-24  32.50
9  1980-12-26  35.50

df['date']  = pd.to_datetime(df['date'])
df.set_index('date', inplace=True)
df.reindex(dates)

df
            close
date             
1980-12-12  28.75
1980-12-15  27.25
1980-12-16  25.25
1980-12-17  25.87
1980-12-18  26.63
1980-12-19  28.25
1980-12-22  29.63
1980-12-23  30.88
1980-12-24  32.50
1980-12-25    NaN
1980-12-26  35.50

You need to set index so it knows how to align your new index. Is this your expected output?

answered Jul 17, 2017 at 13:26

Andrew L

7,1083 gold badges28 silver badges30 bronze badges

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

3 Comments

M3RS Over a year ago

Thanks Andrew! That was exactly the problem. Didn't spot that the date column wasn't the index.

Andrew L Over a year ago

Great, glad to help.

Johnny Over a year ago

I have tried this exact solution but I receive a Value error that I cannot reindex from a duplicate axis

Collectives™ on Stack Overflow

Reindex pandas DataFrame to fill missing dates

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related