Divide elements from columns with similar prefix-CodePudding

I have the following DataFrame:

df = pd.DataFrame(list(zip([1,2],
           [5,6],
           [9,10],
           [13,14])))
df.columns =['x_A', 'y_A', 'x_B', 'y_B']
df:
    x_A y_A x_B y_B
  0 1   5   9   13
  1 2   6   10  14

I would like to divide columns with similar prefixes to get the following:

df: 
    x    y
 0  1/9  5/13
 1  2/10 6/14

Can this be done with a single line if possible?

Thank you.

CodePudding user response：

If the prefixes are always of the format *_A and *_B, you can use filter and division to this effect:

df.filter(like='_A') / df.filter(like='_B').to_numpy()

        x_A       y_A
0  0.111111  0.384615
1  0.200000  0.428571

The second subframe needs the .to_numpy() call to force division and avoid NaNs in the result (due to unalignable column indices).

CodePudding user response：

One approach:

def divide_reduce(x):
    y = x.to_numpy().astype(np.float64)
    return np.divide.reduce(y, axis=1)

res = df.groupby(df.columns.str[0], axis=1).agg(divide_reduce)
print(res)

Output

          x         y
0  0.111111  0.384615
1  0.200000  0.428571

If you prefer a single line approach, you could use:

res = df.astype(np.float64).groupby(df.columns.str[0], axis=1).agg(np.divide.reduce, axis=1)