CODEWITHSUNDEEP
pythonpandas
이승훈
이승훈

Reputation: 360

Pandas calculate mean of column minus in multi column data

For example I have dataframe as below

                       BTC               ETH
time(index)      high      low     high        low 
     1            3         1       4           2
     2            4         1       4           1

and I want mean on index, of minus between high & low

by each column like below..

                      BTC                ETH
                      2.5                2.5

Is there any way using pandas?

Upvotes: 3

Views: 71

Answers (1)

jezrael
jezrael

Reputation: 863166

Use xs for select by second level of MultiIndex, subtract by sub and get mean:

s = df.xs('high', axis=1, level=1).sub(df.xs('low', axis=1, level=1)).mean()
print (s)
BTC    2.5
ETH    2.5
dtype: float64

If want one row DataFrame add to_frame and T:

df = s.to_frame().T
print (df)
   BTC  ETH
0  2.5  2.5

or agg:

df = df.xs('high', axis=1, level=1).sub(df.xs('low', axis=1, level=1)).agg(['mean'])
print (df)
      BTC  ETH
mean  2.5  2.5

Details:

print (df.xs('high', axis=1, level=1))
   BTC  ETH
1    3    4
2    4    4

print (df.xs('high', axis=1, level=1).sub(df.xs('low', axis=1, level=1)))
   BTC  ETH
1    2    2
2    3    3

Upvotes: 4

Related Questions