Pandas multiply several multi-index columns in a dataframe by another column

Question

Given the following dataframe

## example of how to get a "specific" axis point using xs
import pandas as pd
date = pd.bdate_range('2013-01-14','2013-01-20').repeat(5)
sector = [1]*3
sector.extend([2]*2)
sector = sector * 5
instrument = list('ABCDE')*5
port = ['pf']*25
data = xrange(25)
df = pd.DataFrame(dict(port=port,sector=sector,instrument=instrument,date=date,data=data))
port = ['bm']*25
df1 = pd.DataFrame(dict(port=port,sector=sector,instrument=instrument,date=date,data=data))
df = pd.concat([df,df1],axis=0)
df = df.set_index(['port','sector','instrument','date'])
df = df.unstack('port')
df['pchg',''] = xrange(25)

how can I do the equivalent of

df['pchg'] * df[['pf','bm']]

The output is expected to be a dataframe with the same indices as df and with two columns. df.pchg * df.bm and df.pchg * df.pf

Thanks

Sorry what is the expected output here, you are trying to multiply a single column by a df with 2 columns which doesn't make sense. Are you trying to multiply by both columns, the sum of both, by the combination of both? — EdChum
– EdChum, Commented Feb 5, 2015 at 11:52

alacy · Accepted Answer · 2015-02-05 19:53:09Z

4

Pandas makes this very simple. Here's an example with some dummy data:

df['data']['pf'] = df['data']['pf'] * df['pchg']
df['data']['bm'] = df['data']['bm'] * df['pchg']

Before the multiplication:

                             data     pchg
port                           bm  pf     
sector instrument date                    
1      A          2013-01-14    0   0    0
                  2013-01-15    5   5    1
                  2013-01-16   10  10    2
                  2013-01-17   15  15    3
                  2013-01-18   20  20    4
       B          2013-01-14    1   1    5
                  2013-01-15    6   6    6
                  2013-01-16   11  11    7
                  2013-01-17   16  16    8
                  2013-01-18   21  21    9
       C          2013-01-14    2   2   10
                  2013-01-15    7   7   11
                  2013-01-16   12  12   12
                  2013-01-17   17  17   13
                  2013-01-18   22  22   14
2      D          2013-01-14    3   3   15
                  2013-01-15    8   8   16
                  2013-01-16   13  13   17
                  2013-01-17   18  18   18
                  2013-01-18   23  23   19
       E          2013-01-14    4   4   20
                  2013-01-15    9   9   21
                  2013-01-16   14  14   22
                  2013-01-17   19  19   23
                  2013-01-18   24  24   24

And after the multiplication:

                             data      pchg
port                           bm   pf     
sector instrument date                     
1      A          2013-01-14    0    0    0
                  2013-01-15    5    5    1
                  2013-01-16   20   20    2
                  2013-01-17   45   45    3
                  2013-01-18   80   80    4
       B          2013-01-14    5    5    5
                  2013-01-15   36   36    6
                  2013-01-16   77   77    7
                  2013-01-17  128  128    8
                  2013-01-18  189  189    9
       C          2013-01-14   20   20   10
                  2013-01-15   77   77   11
                  2013-01-16  144  144   12
                  2013-01-17  221  221   13
                  2013-01-18  308  308   14
2      D          2013-01-14   45   45   15
                  2013-01-15  128  128   16
                  2013-01-16  221  221   17
                  2013-01-17  324  324   18
                  2013-01-18  437  437   19
       E          2013-01-14   80   80   20
                  2013-01-15  189  189   21
                  2013-01-16  308  308   22
                  2013-01-17  437  437   23
                  2013-01-18  576  576   24

edited Feb 5, 2015 at 19:53

answered Feb 5, 2015 at 13:53

alacy

5,0948 gold badges33 silver badges47 bronze badges

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

3 Comments

Tahnoon Pasha Over a year ago

Thanks @aus_lacy I'm looking for a solution for multi index data frames where all the data is in the one dataframe in this case.

Tahnoon Pasha Over a year ago

Thanks @aus_lacy. I'll mark this as answered, but is there any way you know of to do this as a a single operation? I may have a number of columns and I know I can loop through them, I'm looking for something that takes advantage of pandas fast vector abilities.

alacy Over a year ago

@TahnoonPasha you might be able to use slicing to index a range of columns in a single operation and an apply. Check out the Pandas documentation as well as the numerous SO questions for better guidance.

Collectives™ on Stack Overflow

Pandas multiply several multi-index columns in a dataframe by another column

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related