1

I have a dataframe df that has a column inc in it. The inc column has US dollar amounts from past years. I would like to create a new column called adj that has the past dollar amounts all adjusted for inflation. Can anyone suggest how to do this? It would be fine to assume an average rate of inflation too, it doesn’t have to be exactly accurate. Also the example numbers in the adj column of the example output I’ve provided are totally made up. Any tips are greatly appreciated.

df:

      inc           date
0     50000.0       12/01/1992
1     39216.0       11/01/2005
2     65000.0       06/01/1970

output:

      inc           date            adj
0     50000.0       12/01/1992  6000.0
1     39216.0       11/01/2005  4100.0
2     65000.0       06/01/1970  100000.0
rafaelc
  • 57,686
  • 15
  • 58
  • 82
user3476463
  • 3,967
  • 22
  • 57
  • 117
  • This question is way to broad. – Quang Hoang May 14 '19 at 03:25
  • 1
    Use monthly CPI (consumer-price index) data from [CRSP](http://www.crsp.com). Then, add 1 to each to create a compound factor, and use `cumprod` to calculate the compounded value. Calculate it retroactively such that you have a discount factor for each month. Then, simple use `df.lookup` or `loc` using index matching to bring the compound factors to your data frame, and multiply `inc` by the compound factor – rafaelc May 14 '19 at 03:35
  • found this article that describes adjusting using the `cpi` library: https://towardsdatascience.com/the-easiest-way-to-adjust-your-data-for-inflation-in-python-365490c03969 – Pat Myron Aug 31 '23 at 06:03

1 Answers1

0

You need to get the Consumer Price Index from quandl. Nasdaq acquired quandl in 2018, so here is the link to sign up and get the api key. quandll signup

1- Configure quandl

pip install quandl

import quandl
QUANDL_KEY='Paste Your key here'
quandl.ApiConfig.api_key=QUANDL_KEY

2- Get the inflation data and merge with your df

start='any date'
end='2021-12-22'

df_cpi = quandl.get(dataset='RATEINF/CPI_USA',start_date=start,end_date=end)
df_cpi.rename(columns={'Value':'cpi'}, inplace=True)
# left join, which is a type of join (same use case in sql) that
# returns all rows from the left table and the matched rows from the right table while leaving the unmatched rows empty.
# df is your data frame
df_merged = df.join(df_cpi, how='left')
df_merged

3- add "simple_rtn" and "inflation_rate" columns to df_merged

# in your df, your price column is "inc"
df_merged['simple_rtn']=df_merged['inc'].pct_change()
df_merged['inflation_rate']=df_merged.cpi.pct_change()

4- add "real_rtn" to the df_merged

df_merged['real_rtn']=(df_merged.simple_rtn+1)/(df_merged.inflation_rate+1)-1
Yilmaz
  • 35,338
  • 10
  • 157
  • 202