CODEWITHSUNDEEP
pythonpandasdataframepandas-groupbyinterpolation
Ank
Ank

Reputation: 1904

Pandas dataframe groupby id and interpolate values

I have a dataframe with decade wise values for many ids, like so:

id year value
1  2020  0.09
1  2030  0.1
1  2040  0.11
2  2020  0.09
2  2030  0.1
2  2040  0.11

I want to interpolate (maybe linearly) to get year wise values for each of the ids. How can I do that?

What if I want month wise values (year column itself should include month also)? How to do that?

Upvotes: 1

Views: 1919

Answers (1)

jezrael
jezrael

Reputation: 862921

I believe you need DataFrame.groupby with DataFrame.resample and Resampler.interpolate:

#for DatetimeIndex
df.index = pd.to_datetime(df['year'], format='%Y').rename('datetimes')

df = (df.groupby('id')['value']
        .apply(lambda x: x.resample('MS').interpolate())
        .reset_index())
print (df)
     id  datetimes     value
0     1 2020-01-01  0.090000
1     1 2020-02-01  0.090083
2     1 2020-03-01  0.090167
3     1 2020-04-01  0.090250
4     1 2020-05-01  0.090333
..   ..        ...       ...
477   2 2039-09-01  0.109667
478   2 2039-10-01  0.109750
479   2 2039-11-01  0.109833
480   2 2039-12-01  0.109917
481   2 2040-01-01  0.110000

[482 rows x 3 columns]

Upvotes: 2

Related Questions