Calculate Multiple Columns By Names Using Python Pandas

I have a dataframe similar like this, cat_A cat_B cat_C cat_D dog_A dog_B dog_C dog_D 3 2 4 1 9 8 10 6 ... ... I knew how to calculate

Solution 1:

IMO a good practice here would be to work with MultiIndexes instead of flat columns:

df.columns = pd.MultiIndex.from_tuples(map(tuple, df.columns.str.split('_')))
df
  cat          dog          
    A  B  C  D   A  B   C  D
0324198106

At this point, computing the ratio is very simple courtesy index alignment.

df['cat'] / df['dog']AB    C         D
00.3333330.250.40.166667

---

res =  df['cat'] / df['dog']
res.columns = pd.MultiIndex.from_product([['ratio'], res.columns])

pd.concat([df, res], axis=1)
  cat          dog               ratio                     
    A  B  C  D   A  B   C  D         A     B    C         D
0   3  2  4  1   9  8  10  6  0.333333  0.25  0.4  0.166667