If a row does not exist check and attribute a value accordingly in Python

user_10617194 0
Viewed 324

I have a dataframe containing 3 columns:

[in]:

import pandas as pd
import numpy as np
df = pd.DataFrame([['Circle', 'Circle', 'Polygon', 'Polygon',"Trapezoid"], 
                   [0, 1, 0, 1,1], [28152, 9168, 24741, 11402,5000]], 
                   ['nom_1', 'target', 'id']).T

[out]:

       nom_1 target     id
0     Circle      0  28152
1     Circle      1   9168
2    Polygon      0  24741
3    Polygon      1  11402
4  Trapezoid      1   5000

In theory every geometrical shape should have the value 0 or 1 in the target column. Id represents counts. I need the 1/(1+0) ratio for each geonetrical shape in id column.

For example "Circle" id count for target 1 is 9168 and for 0 is 28152. The calculation I need : (9168)/(9168+28152). I achieve this calculation with this piece of code.

[in]:

ColumnTarget = df[["id","nom_1","target"]]
ColumnGrouped = ColumnTarget.groupby(["nom_1","target"]).count()["id"].reset_index()
ColumnCalculation = ColumnGrouped.groupby("nom_1").apply(lambda row: (row[row.target ==1]["id"].iloc[0]) / (row[row.target ==0]["id"].iloc[0] + row[row.target ==1]["id"].iloc[0]))

[out]:

IndexError: single positional indexer is out-of-bounds

However when a geometrical shape does not have either 1 or 0 target row I get an IndexError. In this case "Trapezoid" is missing a 0 target row. So if both 0,1 targets are present for the geometrical shape I like the calculation I stated above. If 1 target is missing I want the result to be equal to 0 and if 0 target is missing the result should equal to 1. For example for "Trapezoid" the result should be 1.

Here is what I tried:

[in]:

ColumnTarget = df[["id","nom_1","target"]]
ColumnGrouped = ColumnTarget.groupby(["nom_1","target"]).count()["id"].reset_index()
ColumnCalculation = ColumnGrouped.groupby("nom_1").apply(lambda row: 0 if row[row.target ==1].all() is False else (1 if row[row.target ==0].all() is False else ((row[row.target ==1]["id"].iloc[0]) / (row[row.target ==0]["id"].iloc[0] + row[row.target ==1]["id"].iloc[0]))))

[out]:

IndexError: single positional indexer is out-of-bounds

output_df = pd.DataFrame({"nom_1":["Circle","Polygon","Trapezoid"],"result": [0.24565916398713827,0.3154691088177517,1]})
pandas-groupby pandas apply python
2 Answers

Use transform and div

df['id'].div(df.groupby('nom_1').id.transform('sum'), axis=0)

       nom_1 target     id     ratio
0     Circle      0  28152  0.754341
1     Circle      1   9168  0.245659
2    Polygon      0  24741  0.684531
3    Polygon      1  11402  0.315469
4  Trapezoid      1   5000         1

Obviously, you can edit this df to visualize only those rows with target == 1

df[df.target == 1]


       nom_1 target     id     ratio
1     Circle      1   9168  0.245659
3    Polygon      1  11402  0.315469
4  Trapezoid      1   5000         1
user_25356110

Use the index to align the calculation (I added a shape missing Target == 1). This assumes you don't have anything duplicated on ['nom_id', 'target']:

df = pd.DataFrame([['Circle', 'Circle', 'Polygon', 'Polygon',"Trapezoid", 'Octagon'], 
                   [0, 1, 0, 1, 1, 0], [28152, 9168, 24741, 11402,5000, 6000]], 
                   ['nom_1', 'target', 'id']).T 

df = df.set_index('nom_1')
u = df.loc[df.target.eq(1), 'id']
v = df.loc[df.target.eq(0), 'id']

                                    # - 0 When Target == 1 is missing
                                    # |
s = u.divide(u.add(v, fill_value=0)).fillna(0)
#nom_1
#Circle       0.245659
#Octagon      0.000000
#Polygon      0.315469
#Trapezoid    1.000000
#Name: id, dtype: float64
user_43333590
Related