Replace Pandas Dataframe Values with Date

Question

I have a time series dataframe with dates as index and days to maturity "DTM" values. What is the best way to replace these DTM values with actual dates (adding the existing value to the date index row date)?

For example: Adding 20 days to 1990-01-02 = 1990-01-22 for the first row/first column value.

thanks!

gtomer · Accepted Answer · 2021-08-10 09:42:18Z

1

Creating similar dataframe to the one you posted:

import pandas as pd
array = {'Date': ['1990-01-02', '1990-01-03'],
         'CL1': [20,19],
         'CL2': [49,48], 'CL3': [77,76]}
df = pd.DataFrame(array)
df['Date'] = pd.to_datetime(df['Date'], format='%Y-%m-%d')
df = df.set_index('Date')
df

This gives:

           CL1  CL2 CL3
Date            
1990-01-02  20  49  77
1990-01-03  19  48  76

Now here is the solution to what you are after:

from datetime import timedelta
for col in df.columns:
    df[col] = df.index + pd.to_timedelta(df[col], unit='d') 
df

Which gives:

                CL1          CL2       CL3
Date            
1990-01-02  1990-01-22  1990-02-20  1990-03-20
1990-01-03  1990-01-22  1990-02-20  1990-03-20

edited Aug 10, 2021 at 9:42

answered Aug 9, 2021 at 15:32

gtomer

6,6041 gold badge14 silver badges29 bronze badges

Sign up to request clarification or add additional context in comments.

9 Comments

Ben Over a year ago

Thanks for the quick answer. I trief your code but I get this error: Invalid type <class 'pandas.core.series.Series'>. Must be int or float.

gtomer Over a year ago

Please post here the result of df.dtypes

Ben Over a year ago

CL1 float64 CL2 float64 CL3 float64 CL4 float64 CL5 float64 CL6 float64 CL7 float64 CL8 float64 CL9 float64 CL10 float64 CL11 float64 CL12 float64 dtype: object

gtomer Over a year ago

Try the code again. There was a spelling mistake

Ben Over a year ago

Yes, I have already changed that but it still gives me this error. (is the df1 also a typo?)

|

Collectives™ on Stack Overflow

Replace Pandas Dataframe Values with Date

1 Answer 1

9 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

9 Comments

Your Answer

Sign up or log in

Post as a guest

Related