Pandas: pairwise multiplication of columns based on column name

Question

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  as2_X
0      1      1      2      3
1      2      2      2      3
2      3      3      2      3
3      4      4      2      3

I would like to multiply ap1_X with as1_X and put that value in as1_X, similarly for ap2_X with as2_X. The common identifier here is the number that comes after the ap or as.

The final DataFrame should look like this

>>> df
   ap1_X  as1_X  ap2_X  as2_X
0      1      1      2      6
1      2      4      2      6
2      3      9      2      6
3      4      16     2      6

I know I can loop through the columns and multiply the columns that have the same 3rd character in the column name, but I was wondering if there is a more "pandas" way of doing this?

UPDATE: The number ID in the column name can be multiple digits (ex: 1, 2, ..., 12, ..., 100). So basically, the ID is the number between 'ap' or 'as', and '_X'.

Answer 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]: 
    1  2
0   1  6
1   4  6
2   9  6
3  16  6