I have a pandas dataframe like the following:

import pandas as pd

pd.DataFrame({"AAA":["x1","x1","x1","x2","x2","x2"],
              "BBB":["y1","y1","y2","y2","y2","y1"],
              "CCC":["t1","t2","t3","t1","t1","t1"],
              "DDD":[10,11,18,17,21,30]})

Out[1]:
  AAA BBB CCC  DDD
0  x1  y1  t1   10
1  x1  y1  t2   11
2  x1  y2  t3   18
3  x2  y2  t1   17
4  x2  y2  t1   21
5  x2  y1  t1   30

The problem

What I want is to group on column AAA so I have 2 groups - x1, x2.

I want then calculate the ratio of y1 to y2 in column BBB for each group.

And assign this output to a new column Ratio of BBB

The desired output

So I want this as my output.

pd.DataFrame({"AAA":["x1","x1","x1","x2","x2","x2"],
              "BBB":["y1","y1","y2","y2","y2","y1"],
              "CCC":["t1","t2","t3","t1","t1","t1"],
              "DDD":[10,11,18,17,21,30],
              "Ratio of BBB":[0.33,0.33,0.33,0.66,0.66,0.66]})

Out[2]:
  AAA BBB CCC  DDD  Ratio of BBB
0  x1  y1  t1   10          0.33
1  x1  y1  t2   11          0.33
2  x1  y2  t3   18          0.33
3  x2  y2  t1   17          0.66
4  x2  y2  t1   21          0.66
5  x2  y1  t1   30          0.66

Current status

I have currently achieved it like so:

def f(df):
  df["y1"] = sum(df["BBB"] == "y1")
  df["y2"] = sum(df["BBB"] == "y2")
  df["Ratio of BBB"] = df["y2"] / df["y1"]
  return df

df.groupby(df.AAA).apply(f)

What I want to achieve

Is there anyway to achieve this with the .pipe() function?

I was thinking something like this:

df = (df
 .groupby(df.AAA) # groupby a column not included in the current series (df.colname)
 .BBB
 .value_counts()
 .pipe(lambda series: series["BBB"] == "y2" / series["BBB"] == "y1")
 )

Edit: One solution using pipe()

N.B: User jpp made clear comment below:

unstack / merge / reset_index operations are unnecessary and expensive

However, I initially intended to use this method i thought I would share it here!

df = (df
      .groupby(df.AAA)                     # groupby the column
      .BBB                                 # select the column with values to calculate ('BBB' with y1 & y2)
      .value_counts()                      # calculate the values (# of y1 per group, # of y2 per group)
      .unstack()                           # turn the rows into columns (y1, y2)
      .pipe(lambda df: df["y1"]/df["y2"])  # calculate the ratio of y1:y2 (outputs a Series)
      .rename("ratio")                     # rename the series 'ratio' so it will be ratio column in output df
      .reset_index()                       # turn the groupby series into a dataframe
      .merge(df)                           # merge with the original dataframe filling in the columns with the key (AAA)
      )