Insert rows to fill gaps in year column in Pandas DataFrame-CodePudding

I have the following DataFrame:

import pandas as pd

data = {'id': ['A', 'A','B','C'],
        'location':['loc1', 'loc2','loc1','loc3'],
        'year_data': [2013,2015,2014,2015],
        'c': [10.5, 13.5,12.3,9.75]}

data = pd.DataFrame(data)

For each groupby(['id','location']), I want to insert rows in the DataFrame starting from min(year) till 2015.

The desired output:

data = {'id': ['A', 'A', 'A','A','B','B','C'],
        'location':['loc1', 'loc1', 'loc1', 'loc2','loc1','loc1','loc3'],
        'year_data': [2013,2014,2015,2015,2014,2015,2015],
        'c': [10.5,10.5,10.5, 13.5,12.3,12.3,9.75]}

data = pd.DataFrame(data)

CodePudding user response：

Use lambda function with get minimal year from index created by DataFrame.set_index in range for Series.reindex with method='ffill' per groups:

f = lambda x: x.reindex(range(x.index.min(), 2016), method='ffill')
df = data.set_index("year_data").groupby(['id','location'])['c'].apply(f).reset_index()
print (df)
  id location  year_data      c
0  A     loc1       2013  10.50
1  A     loc1       2014  10.50
2  A     loc1       2015  10.50
3  A     loc2       2015  13.50
4  B     loc1       2014  12.30
5  B     loc1       2015  12.30
6  C     loc3       2015   9.75