How to resample using Python

Question

I have a dataframe as below.

import pandas as pd

frame = pd.DataFrame({"a":range(7),'b':range(7,0,-1),
'id':['one','one','two','two','two','three','four'],
'date':['2019-05-01','2019-05-08','2019-05-01','2019-05-08','2019-05-15','2019-05-01','2019-05-15']})
print(frame)
pd.to_datetime(frame['date'],yearfirst=True)

It looks like:

0  7    one  2019-05-01
1  6    one  2019-05-08
2  5    two  2019-05-01
3  4    two  2019-05-08
4  3    two  2019-05-15
5  2  three  2019-05-01
6  1   four  2019-05-15

I expect each id has three rows of date.

The expected dataframe is:

0  7    one  2019-05-01
1  6    one  2019-05-08
1  6    one  2019-05-15
2  5    two  2019-05-01
3  4    two  2019-05-08
4  3    two  2019-05-15
5  2  three  2019-05-01
5  2  three  2019-05-08
5  2  three  2019-05-15
NA NA   four  2019-05-01
NA NA   four  2019-05-08
6 1   four  2019-05-15

How can I get this dataframe by using resample? Thank you!

Why would it just invent data points when all you did was convert the strings to a datetime? It's not a table, it's a single column. — roganjosh
– roganjosh, Commented May 23, 2019 at 12:09

jezrael · Accepted Answer · 2019-05-23 12:31:28Z

1

Use:

frame['date'] = pd.to_datetime(frame['date'],yearfirst=True)

#create MultiIndex by unique values of both columns
mux = pd.MultiIndex.from_product([frame['id'].unique(), 
                                  frame['date'].unique()], names=['id','date'])

#add missing rows by reindex and per groups forward filling missing values
frame = (frame.set_index(['id','date'])
              .reindex(mux)
              .groupby(level=0)
              .ffill()
              .drop('id', axis=1)
              .reset_index()
              )

print (frame)
       id       date    a    b
0     one 2019-05-01  0.0  7.0
1     one 2019-05-08  1.0  6.0
2     one 2019-05-15  1.0  6.0
3     two 2019-05-01  2.0  5.0
4     two 2019-05-08  3.0  4.0
5     two 2019-05-15  4.0  3.0
6   three 2019-05-01  5.0  2.0
7   three 2019-05-08  5.0  2.0
8   three 2019-05-15  5.0  2.0
9    four 2019-05-01  NaN  NaN
10   four 2019-05-08  NaN  NaN
11   four 2019-05-15  6.0  1.0

edited May 23, 2019 at 12:31

answered May 23, 2019 at 12:20

jezrael

868k103 gold badges1.4k silver badges1.3k bronze badges

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

1 Comment

Lini Kuang Over a year ago

Thanks a lot! Really helpful!

Collectives™ on Stack Overflow

How to resample using Python

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related