Calculate ratio using groupby

user_15632948 0
Viewed 44

IM new using python I created this dataframe:

d2= {'id': ['x2', 'x2', 'x2', 'x2', 'x3', 'x3', 'x3'], 'cod': [101001, 101001, 101001, 101001, 101002, 101002, 101002], 
 'flag': ['IN', 'IN', 'IN','CMP', 'IN', 'OUT', 'CMP'], 'col': [100, 100, 100, 300, 100, 300, 100]
}
df2 = pd.DataFrame(data=d2)

I want to calculate a ratio : (sum(IN)/sum(all) groupby id*cod. The expected output should be

  d2= {'id': ['x2', 'x2', 'x2', 'x2', 'x3', 'x3', 'x3'], 'cod': [101001, 101001, 101001, 101001, 101002, 101002, 101002], 
 'flag': ['IN', 'IN', 'IN','CMP', 'IN', 'OUT', 'CMP'], 'col': [0.5, 0.5, 0.5, 0.5, 0.2, 0.2, 0.2]
}
df2 = pd.DataFrame(data=d2)

Please tell me if im not clear. Thank you

pandas group-by
3 Answers

First replace non matched values to 0 in DataFrame.where, aggregate sum and ast divide columns:

df3 = (df2.assign(new = df2['col'].where(df2['flag'].eq('IN'), 0))
          .groupby(['id','cod'])
          .transform('sum'))

df2['rat'] = df3['new'].div(df3['col'])
print (df2)
   id     cod flag  col  rat
0  x2  101001   IN  100  0.5
1  x2  101001   IN  100  0.5
2  x2  101001   IN  100  0.5
3  x2  101001  CMP  300  0.5
4  x3  101002   IN  100  0.2
5  x3  101002  OUT  300  0.2
6  x3  101002  CMP  100  0.2
user_29010020

You could create a temporary column (new), and use the temporary column, combined with groupby and transform, to get the ratio for each row::

(df2
 .assign(
    new = np.where(df2.flag == "IN", df2.col, 0), 
    ratio = lambda df : df.groupby(['id', 'cod'])
                          .pipe(lambda df: df['new']
                                           .transform('sum')
                                           .div(df['col'].transform('sum'))
                                   )
            )
 )

   id     cod flag  col  new  ratio
0  x2  101001   IN  100  100    0.5
1  x2  101001   IN  100  100    0.5
2  x2  101001   IN  100  100    0.5
3  x2  101001  CMP  300    0    0.5
4  x3  101002   IN  100  100    0.2
5  x3  101002  OUT  300    0    0.2
6  x3  101002  CMP  100    0    0.2
user_71757130 
df2["col"] = df2.groupby(["id", "cod"], as_index=False)["col"].transform(
    lambda x: x[df2.iloc[x.index]["flag"] == "IN"].sum() / x.sum(),
)
print(df2)

Prints:

   id     cod flag  col
0  x2  101001   IN  0.5
1  x2  101001   IN  0.5
2  x2  101001   IN  0.5
3  x2  101001  CMP  0.5
4  x3  101002   IN  0.2
5  x3  101002  OUT  0.2
6  x3  101002  CMP  0.2
user_100359850
Related