Pandas: Pairwise Multiplication Of Columns Based On Column Name

I have the following DataFrame >>> df = pd.DataFrame({'ap1_X':[1,2,3,4], 'as1_X':[1,2,3,4], 'ap2_X':[2,2,2,2], 'as2_X':[3,3,3,3]}) >>> df ap1_X as1_X ap2_X

Solution 1:

You can do groupby with axis=1 and key is the common number

df.groupby(df.columns.str[2],axis=1).prod()
Out[73]: 
    120161462963166

Solution 2:

You can use filter here:

df.filter(like='p') * df.filter(like='s').values

   ap1_X  ap2_X
0161462963166

---

Another solution is to argsort the column names and slice. This should be very efficient.

idx = np.argsort(df.columns.str[1])
l = len(df) // 2
df.iloc[:, idx[:l]] * df.iloc[:, idx[l:]].values 

   ap1_X  ap2_X
0      1      6
1      4      6
2      9      6
3     16      6