CODEWITHSUNDEEP
pythonpython-3.xpandasnumpyquandl
user3476463
user3476463

Reputation: 4575

adjust pandas dollar column for inflation

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

Upvotes: 1

Views: 1516

Answers (1)

Yilmaz
Yilmaz

Reputation: 49182

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

Upvotes: 0

Related Questions