# Q：如何从相关数据在帧的绝对日期列

I have a pandas DataFrame containing forward prices for future maturities, quoted on multiple different trading months ('trade date'). Trade dates are given in absolute terms ('January'). The maturities are given in relative terms ('M+1').

How can I convert the maturities into an absolute format, i.e. in trade date 'January' the maturity 'M+1' should say 'February'.

Here is example data:

``````import pandas as pd
import numpy as np

data_keys = ['trade date', 'm+1', 'm+2', 'm+3']
'm+1':np.random.randn(4),
'm+2':np.random.randn(4),
'm+3':np.random.randn(4)}

df = pd.DataFrame(data)
df = df[data_keys]
``````

Starting data:

``````  trade date       m+1       m+2       m+3
0        jan -0.446535 -1.012870 -0.839881
1        feb  0.013255  0.265500  1.130098
2        mar  0.406562 -1.122270 -1.851551
3        apr -0.890004  0.752648  0.778100
``````

Result:

Should have Feb, Mar, Apr, May, Jun, Jul in the columns. NaN will be shown in many instances.

``````import pandas as pd
import numpy as np

data_keys = ['trade date', 'm+1', 'm+2', 'm+3']
'm+1':np.random.randn(4),
'm+2':np.random.randn(4),
'm+3':np.random.randn(4)}

df = pd.DataFrame(data)
df = df[data_keys]
``````

``````  trade date       m+1       m+2       m+3
0        jan -0.446535 -1.012870 -0.839881
1        feb  0.013255  0.265500  1.130098
2        mar  0.406562 -1.122270 -1.851551
3        apr -0.890004  0.752648  0.778100
``````

The starting DataFrame:

``````  trade date       m+1       m+2       m+3
0        jan -1.350746  0.948835  0.579352
1        feb  0.011813  2.020158 -1.221110
2        mar -0.183187 -0.303099  1.323092
3        apr  0.081105  0.662628 -0.703152
``````

Solution:

• Define a list of all possible absolute dates you will encounter, in chronological order. Do the same for relative dates.
• Create a function to act on groups coming from df.groupby. The function will convert the column names of each group appropriately to an absolute format.
• Apply the function.
• Pandas handles the clever concatenation of all groups.

Code:

``````abs_in_order = ['jan','feb','mar','apr','may','jun','jul','aug']
rel_in_order = ['m+0','m+1','m+2','m+3','m+4']

def rel2abs(group, abs_in_order, rel_in_order):
l = len(rel_in_order)
i = abs_in_order.index(abs_date)
namesmap = dict(zip(rel_in_order, abs_in_order[i:i+l]))
group.rename(columns=namesmap, inplace=True)
return group

df = grouped.apply(rel2abs, abs_in_order, rel_in_order)
``````

Pandas may mess up the column order. Do this to get back to something in chronological order:

``````order = ['trade date'] + abs_in_order
cols = [e for e in order if e in df.columns]
df[cols]
``````

Result:

``````  trade date       feb       mar       apr       may       jun       jul
0        jan -1.350746  0.948835  0.579352       NaN       NaN       NaN
1        feb       NaN  0.011813  2.020158 -1.221110       NaN       NaN
2        mar       NaN       NaN -0.183187 -0.303099  1.323092       NaN
3        apr       NaN       NaN       NaN  0.081105  0.662628 -0.703152
``````

``````  trade date       m+1       m+2       m+3
0        jan -1.350746  0.948835  0.579352
1        feb  0.011813  2.020158 -1.221110
2        mar -0.183187 -0.303099  1.323092
3        apr  0.081105  0.662628 -0.703152
``````

• Define a list of all possible absolute dates you will encounter, in chronological order. Do the same for relative dates.
• Create a function to act on groups coming from df.groupby. The function will convert the column names of each group appropriately to an absolute format.
• Apply the function.
• Pandas handles the clever concatenation of all groups.

``````abs_in_order = ['jan','feb','mar','apr','may','jun','jul','aug']
rel_in_order = ['m+0','m+1','m+2','m+3','m+4']

def rel2abs(group, abs_in_order, rel_in_order):
l = len(rel_in_order)
i = abs_in_order.index(abs_date)
namesmap = dict(zip(rel_in_order, abs_in_order[i:i+l]))
group.rename(columns=namesmap, inplace=True)
return group

df = grouped.apply(rel2abs, abs_in_order, rel_in_order)
``````

``````order = ['trade date'] + abs_in_order
cols = [e for e in order if e in df.columns]
df[cols]
``````

``````  trade date       feb       mar       apr       may       jun       jul
0        jan -1.350746  0.948835  0.579352       NaN       NaN       NaN
1        feb       NaN  0.011813  2.020158 -1.221110       NaN       NaN
2        mar       NaN       NaN -0.183187 -0.303099  1.323092       NaN
3        apr       NaN       NaN       NaN  0.081105  0.662628 -0.703152
``````

You question doesn't contain enough information to answer it.

You say that the prices are quoted on dates given in absolute terms ('January').

January is not a date, but 2-Jan-2015 is.

What is your actual 'date' and what is its format (i.e. text, datetime.date, pd.Timestamp, etc.). You can use type(date) to check where date is an instance of whatever quote date it represents.

``````trade_date = pd.Timestamp('2015-1-15')

Timestamp('2015-02-15 00:00:00')
``````

``````trade_date = pd.Timestamp('2015-1-15')