Multiplying multiple columns with the same single column in a Multi-index column Pandas

Question

I have this data frame

col = pd.MultiIndex.from_arrays([['Jan', 'Jan', 'Jan', 'Cost'],
                                ['WK1', 'WK2', 'WK3', 'Cost']])
data = pd.DataFrame(np.random.randn(4, 4), columns=col)
data

and I want to multiply all the column in Jan with cost so i tried it like this

data[['Jan']] * data[['Cost']]

but it fills my values with Nan. Is there a way i can do this?

Answer 1

Hi guys I made a for cycle to answer my question

for col in data['Jan']:
 data[('Jan', col)] = data[('Jan',col)] * data[('Cost','Cost')]

Hope someone can give me a feedback in what i can improve

Answer 2

From what I can guess, you have trouble accessing columns using multi level index. Is this what you are trying to do?

data[('one','a')] * data[('two','b')]
Out[1065]: 
0    0.528834
1   -0.287322
2   -0.255244
3    0.871213
dtype: float64

Answer 3

I think you need remove [] for selecting DataFrame with mul:

print (data['one'].mul(data['two']))

Or:

print (data['one'] * data['two'])

Also is possible use function xs:

print (data.xs('one', axis=1, level=0) * data.xs('two', axis=1, level=0))