0

I have a dataframe like this:

Month/Year  Value  
01/2018     100  
03/2018     200  
06/2018     800

The values for 02/2018, 04/2018 and 05/2018 is missing because the value did not change in those months. I would like to have a dataframe which incudes the missing months and values are increased proportionally: 

Month/Year  Value    
01/2018     100  
02/2018     150  
03/2018     200   
04/2018     400  
04/2018     600  
06/2018     800

Thank you for help with the solution!

rachelH
  • 41
  • 3
  • 1
    what did you mean by value did not change? Are you comparing such month's value with any previous dataframe or something, also how did you determine the value of 02/2018 was 150? – user2906838 Jul 26 '18 at 04:44
  • Possible duplicate of [Interpolation on DataFrame in pandas](https://stackoverflow.com/questions/10464738/interpolation-on-dataframe-in-pandas) – Will Jul 26 '18 at 04:53

1 Answers1

1

First convert column to DatetimeIndex, add missing datetimes by asfreq, interpolate and if necessary same format add strftime:

df['Month/Year'] = pd.to_datetime(df['Month/Year'], format='%m/%Y')
df = df.set_index('Month/Year').asfreq('MS').interpolate().reset_index()
df['Month/Year'] = df['Month/Year'].dt.strftime('%m/%Y')
print (df)
  Month/Year  Value
0    01/2018  100.0
1    02/2018  150.0
2    03/2018  200.0
3    04/2018  400.0
4    05/2018  600.0
5    06/2018  800.0
jezrael
  • 822,522
  • 95
  • 1,334
  • 1,252