How to do operations on specific columns using pandas python Part 2

Question

I have the following dataframe:

import pandas as pd
idx = pd.IndexSlice
data = {'Col1': [4, 5, 6, 7, 8], 'Col2': [1, 2, 3, 4, 5], 'Col3': [10, 9, 8, 7, 6],
        'Col4': [5, 8, 9, 3, 10], 'Col5': [7, 6, 4, 5, 8], 'Col6': [4, 5, 6, 7, 8],
        'Col7': [5, 8, 54, 3, 10], 'Col8': [7, 6, 32, 5, 8], 'Col9': [4, 5, 2, 23, 8], 'Col10': [13, 5, 6, 15, 8]}
col = pd.MultiIndex.from_tuples([('Monday', 'Water', 'Cold'), ('Monday', 'Water', 'Hot'),
                                 ('Monday', 'Ice', 'Cold'), ('Monday', 'Ice', 'Hot'), ('Monday', 'Earth', '-'),
                                 ('Tuesday', 'Water', 'Cold'), ('Tuesday', 'Water', 'Hot'),
                                 ('Tuesday', 'Ice', 'Cold'), ('Tuesday', 'Ice', 'Hot'), ('Tuesday', 'Earth', '-')])
df = pd.DataFrame(data)
df.columns = col

I would like to do the following operation Element which should do the following operations:

df[('Monday', 'Element', 'Cold')] = df[('Monday', 'Ice', 'Cold')] - df[('Monday', 'Earth', '-')]
df[('Monday', 'Element', 'Hot')] = df[('Monday', 'Ice', 'Hot')] - df[('Monday', 'Earth', '-')]
df[('Tuesday', 'Element', 'Hot')] = df[('Tuesday', 'Ice', 'Hot')] - df[('Tuesday', 'Earth', '-')]
df[('Tuesday', 'Element', 'Cold')] = df[('Tuesday', 'Ice', 'Cold')] - df[('Tuesday', 'Earth', '-')]

For every available level of Ice, in that case Cold and Hot, it deducts the - level of Earth

Desired output:

  Monday                    Tuesday                     Monday     Tuesday     
   Water      Ice     Earth   Water      Ice     Earth Element     Element     
    Cold Hot Cold Hot     -    Cold Hot Cold Hot     -    Cold Hot     Hot Cold
0      4   1   10   5     7       4   5    7   4    13       3  -2      -9   -6
1      5   2    9   8     6       5   8    6   5     5       3   2       0    1
2      6   3    8   9     4       6  54   32   2     6       4   5      -4   26
3      7   4    7   3     5       7   3    5  23    15       2  -2       8  -10
4      8   5    6  10     8       8  10    8   8     8      -2   2       0    0

jezrael · Accepted Answer · 2021-03-16 10:41:37Z

You can rename Ice by new level Element and create final MultiIndex DataFrame first and then subtract by DataFrame.sub selected Earth, - MultiIndex with remove this repeated values by Index.droplevel:

a =  df.rename(columns={'Ice':'Element'}).loc[:, idx[:, 'Element', :]]
b =  df.loc[:, idx[:, 'Earth', '-']].droplevel([1,2], axis=1)

print (a)
   Monday     Tuesday    
  Element     Element    
     Cold Hot    Cold Hot
0      10   5       7   4
1       9   8       6   5
2       8   9      32   2
3       7   3       5  23
4       6  10       8   8

print (b)
   Monday  Tuesday
0       7       13
1       6        5
2       4        6
3       5       15
4       8        8

c = a.sub(b, level=0)
print (c)
   Monday     Tuesday    
  Element     Element    
     Cold Hot    Cold Hot
0       3  -2      -6  -9
1       3   2       1   0
2       4   5      26  -4
3       2  -2     -10   8
4      -2   2       0   0

And last add to original:

df = pd.concat([df, c], axis=1).sort_index(axis=1, level=0, sort_remaining=False)
print (df)
  Monday                                Tuesday                               
   Water      Ice     Earth Element       Water      Ice     Earth Element    
    Cold Hot Cold Hot     -    Cold Hot    Cold Hot Cold Hot     -    Cold Hot
0      4   1   10   5     7       3  -2       4   5    7   4    13      -6  -9
1      5   2    9   8     6       3   2       5   8    6   5     5       1   0
2      6   3    8   9     4       4   5       6  54   32   2     6      26  -4
3      7   4    7   3     5       2  -2       7   3    5  23    15     -10   8
4      8   5    6  10     8      -2   2       8  10    8   8     8       0   0

Collectives™ on Stack Overflow

How to do operations on specific columns using pandas python Part 2

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related