How to do operations on specific columns using pandas python

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


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

I would like to do the following operation Ice - Water which should do the following operations:

('Monday', 'Ice - Water', 'Cold') = ('Monday', 'Ice', 'Cold') - ('Monday', 'Water', 'Cold')
('Monday', 'Ice - Water', 'Hot') = ('Monday', 'Ice', 'Hot') - ('Monday', 'Water', 'Hot')
('Tuesday', 'Ice - Water', 'Cold')  = ('Tuesday', 'Ice', 'Cold') - ('Tuesday', 'Water', 'Cold')
('Tuesday', 'Ice - Water', 'Hot')  = ('Tuesday', 'Ice', 'Hot') - ('Tuesday', 'Water', 'Hot')

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

Output:

      Monday                    Tuesday  ...                Monday         Tuesday     
   Water      Ice     Earth   Water  ... Ice Earth Ice - Water     Ice - Water     
    Cold Hot Cold Hot     -    Cold  ... Hot     -        Cold Hot         Hot Cold
0      4   1   10   5     7       4  ...   4    13           6   4          -1    3
1      5   2    9   8     6       5  ...   5     5           4   6          -3    1
2      6   3    8   9     4       6  ...   2     6           2   6         -52   26
3      7   4    7   3     5       7  ...  23    15           0  -1          20   -2
4      8   5    6  10     8       8  ...   8     8          -2   5          -2    0

I tried something like that but it fails:

df_temp = df.loc[:, idx[:, 'Ice', :]] - df.loc[:, idx[:, 'Water', :]]

Is it possible without many unnecessary for loops?

kindly post your expected output

sammywemmy
– sammywemmy

2021-01-18 13:04:51 +00:00
Commented Jan 18, 2021 at 13:04 — sammywemmy
– sammywemmy, Commented Jan 18, 2021 at 13:04

jezrael · Accepted Answer · 2021-01-18 13:06:42Z

1

You can use rename for correct align both DataFrames:

df_temp = (df.rename(columns={'Ice':'Ice - Water'}).loc[:, idx[:, 'Ice - Water', :]] - 
           df.rename(columns={'Water':'Ice - Water'}).loc[:, idx[:, 'Ice - Water', :]])

print (df_temp)
       Monday         Tuesday    
  Ice - Water     Ice - Water    
         Cold Hot        Cold Hot
0           6   4           3  -1
1           4   6           1  -3
2           2   6          26 -52
3           0  -1          -2  20
4          -2   5           0  -2

And then add to original with sorting by first level:

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

                   
  Ice - Water      
         Cold Hot  
0           3  -1  
1           1  -3  
2          26 -52  
3          -2  20  
4           0  -2

answered Jan 18, 2021 at 13:06

jezrael

868k103 gold badges1.4k silver badges1.3k bronze badges

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

7 Comments

Thanasis Over a year ago

Hi, what if I would like to substract all the Ice columns with the Earth? I added a part 2 in the question

jezrael Over a year ago

@Thanasis - Can you try idx = pd.IndexSlice df.loc[:, idx[:, 'Ice',:]] -= df.loc[:, idx[:, 'Earth',:]].rename(columns={'Earth':'Ice'}) ?

Thanasis Over a year ago

My target is to have a new bespoke name.

jezrael Over a year ago

@Thanasis - I see, then need solution above, only change

df_temp = (df.rename(columns={'Ice':'Element'}).loc[:, idx[:, 'Element', :]] -              df.rename(columns={'Earth':'Element'}).loc[:, idx[:, 'Element', :]])

jezrael Over a year ago

@Thanasis - Is possible create new question?

|

Collectives™ on Stack Overflow

How to do operations on specific columns using pandas python

1 Answer 1

7 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related