Convert quarterly dataframe to monthly and fill missing values for each ID

user_11074397 0
Viewed 162

I have a dataframe that, for each ID, contains a timestamp and a value. The timestamp is for a given quarter:

import pandas as pd
a = pd.DataFrame({'id': [1,1,1,1,1,2,2,2,2,2,3,3,3,3,3,3],
                  'date': ['2002Q1', '2002Q2', '2002Q3', '2002Q4', '2003Q1', '2002Q2', '2002Q3', '2002Q4', '2003Q1', '2002Q2', '2002Q3', '2002Q4', '2003Q1', '2002Q2', '2002Q3', '2002Q4'],
                  'value': [1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16]})

Now, I want to expand the dataframe to monthly frequencies. This means that each row is expanded to three rows (i.e., one quarter becomes 3 months) and all months in any given quarter should have the same value.

As an example, the first two rows in a we expand in to 6 rows:

pd.DataFrame({'id': [1,1,1,1,1,1],
              'date': ['2002-1', '2002-2', '2002-3', '2002-4', '2002-5', '2002-6'],
              'value': [1,1,1,2,2,2]})

So basically, I am doing the same as in this answer, but now an ID is involved.

Is it possible to do this?

EDIT: The last value per group also needs to be expanded. The current solution gives this result, which is wrong:

import pandas as pd
a = pd.DataFrame({'id': [1,1],
                  'date': ['2002Q1', '2002Q2'],
                  'value': [1,2]})

mask = a['id'].duplicated(keep='last')
dates = pd.to_datetime(a['date'])
a.index = dates.where(mask, dates + pd.DateOffset(months=2))

a = a.groupby('id')['value'].resample('MS').first().ffill().reset_index()
a['date'] = a['date'].dt.to_period('M')
a


    id  date    value
0   1   2002-01 1.0 # fine
1   1   2002-02 1.0 # fine
2   1   2002-03 1.0 # fine
3   1   2002-04 1.0 # should be 2
4   1   2002-05 1.0 # should be 2
5   1   2002-06 2.0 # fine
pandas dataframe python
2 Answers

I imagine you can groupby and resample:

a['date'] = pd.to_datetime(a['date'])
(a.set_index('date')
  .groupby('id')
   ['value']
  .resample('MS')
  .first().ffill()
  .reset_index()
)

output:

        date   id  value
0 2002-01-01  1.0    1.0
1 2002-02-01  1.0    1.0
2 2002-03-01  1.0    1.0
3 2002-04-01  1.0    2.0
4 2002-05-01  1.0    2.0
user_163434640

Create DatetimeIndex first, then groupby with resample with first and forward filling missing values, last convert column to month periods:

Because missing last 2 months per id are added manually before groupby:

import pandas as pd
a = pd.DataFrame({'id': [1,1],
                  'date': ['2002Q1', '2002Q2'],
                  'value': [1,2]})

a.index = pd.to_datetime(a['date'])
mask = a['id'].duplicated(keep='last')
a = pd.concat([a, a[~mask].rename(lambda x: x + pd.DateOffset(months=2))])


a = a.groupby('id')['value'].resample('MS').first().ffill().reset_index()
a['date'] = a['date'].dt.to_period('M')
print (a)
   id     date  value
0   1  2002-01    1.0
1   1  2002-02    1.0
2   1  2002-03    1.0
3   1  2002-04    2.0
4   1  2002-05    2.0
5   1  2002-06    2.0
user_29010020
Related