CODEWITHSUNDEEP
pythonpython-2.7group-bypandasdataframe
gt6989b
gt6989b

Reputation: 4233

subtracting pandas dataframe with

I have a pandas data frame (in Python 2.7) with 2-level indexing on Time and Rating, with Tenor and Value being the data columns:

Out[235]:
                  Tenor  Value
Time       Rating
2011-12-30 AAA       3M  0.343
           AAA       6M  0.404
           AAA       1Y  0.541
           AAA       2Y  0.684
           AAA       3Y  0.869
           AAA       4Y  1.254
           AAA       5Y  1.467
           AAA       7Y  2.051
           AAA       8Y  2.272
           AAA       9Y  2.575
           AAA      10Y  2.938
           AAA      15Y  3.757
           AAA      20Y  4.108
           AAA      30Y  4.377
           AA        3M  0.435
           AA        6M  0.547
           AA        1Y  0.620
           AA        2Y  0.737
           AA        3Y  0.925
           AA        4Y  1.321
           AA        5Y  1.545
           AA        7Y  2.132
           AA        8Y  2.420
           AA        9Y  2.628
           AA       10Y  3.108
           AA       15Y  3.920
           AA       20Y  4.219
           AA       30Y  4.490

I have another data frame

In [237]: treasDF
Out[237]:
     2013-09-20 12:01:00
1M                 0.008
3M                 0.013
6M                 0.043
1Y                 0.104
2Y                 0.332
3Y                 0.688
5Y                 1.478
7Y                 2.109
10Y                2.735
30Y                3.762

I need to subtract treasDF from each of the multi-index levels, - how could I do this? I tried all types of groupby and failed with errors, e.g. ratesDF.groupby(level=1).sub(treasDF.iloc[:,0], level=1)

Thank you.

Upvotes: 1

Views: 1210

Answers (2)

mgilbert
mgilbert

Reputation: 3665

I realize this is a bit late to the party but stumbled across this while trying to resolve the same issue, thought I'd leave my solution in case someone else finds this.

df1 = pd.DataFrame([[1, 2], [1, 2]], index=[0,1], columns=['a', 'b'])

mcols = pd.MultiIndex.from_product([['A', 'B'], ['a', 'b', 'c']])
df2 = pd.DataFrame([[1, 2, 3, 4, 5, 6], [1, 2, 3, 1, 2, 3]], index=[0,1], columns=mcols)

Which gives

df1
   a  b
0  1  2
1  1  2

df2
   A        B      
   a  b  c  a  b  c
0  1  2  3  4  5  6
1  1  2  3  1  2  3

and then use substract() and specify the level to broadcast across

df2.subtract(df1, level=1)
   A         B       
   a  b   c  a  b   c
0  0  0 NaN  3  3 NaN
1  0  0 NaN  0  0 NaN

Upvotes: 3

HYRY
HYRY

Reputation: 97331

I think you don't need groupby, does following code solve your problem?

ratesDF.Value - treasDF[ratesDF.Tenor].values

Upvotes: 1

Related Questions