Pandas - group by function and sum columns to extract rows where sum of other columns is 0-CodePudding

I have a data frame with over three million rows. I am trying to extract only those rows where sum of all rows in each group is zero.

My dataframe is as under:

Location    Bar_Code            SOH         Cost        Sold_Date
1           00000003589823      0           0.00        NULL
2           00000003589823      0           0.00        NULL
3           00000003589823      0           0.00        NULL
1           0000000151818       -102        0.00        NULL
2           0000000151818       0           8.00        NULL
3           0000000151818       0           0.00        2020-10-06T16:35:25.000
1           0000131604108       0           0.00        NULL
2           0000131604108       0           0.00        NULL
3           0000131604108       0           0.00        NULL
1           0000141073505       -53         3.00        2020-10-06T16:35:25.000
2           0000141073505       0           0.00        NULL
3           0000141073505       -20         20.00       2020-09-25T10:11:30.000

I have tried the below code:

df.groupby(['Bar_Code','SOH','Cost','Sold_Date']).sum()

but I am getting the below output:

Bar_Code        SOH         Cost            Sold_Date
0000000151818   -102.0      0.0000          2021-12-13T10:01:59.000
                 0.0        8.0000          2020-10-06T16:35:25.000
0000131604108    0.0        0.0000          NULL
0000141073505   -53.0       0.0000          2021-11-28T16:57:59.000
                            3.0000          2021-12-05T11:23:02.000
                 0.0        0.0000          2020-04-14T08:02:45.000
0000161604109   -8.0        4.1000          2020-09-25T10:11:30.000
00000003589823   0          0.00            NULL

I need to check if it is possible to get the below desired output to get only the specific rows where sum of SOH, Cost & Sold_Date is 0 or NULL, its safe that the code ignores first Column (Locations):

Bar_Code            SOH         Cost            Sold_Date
00000003589823      0           0.00            NULL
0000131604108       0.0         0.0000          NULL

CodePudding user response：

Idea is filter all groups if SOH, Cost and Sold_Date is 0 or NaN by filter rows if not match first, get Bar_Code and last invert mask for filter all groups in isin:

g = df.loc[df[['SOH','Cost','Sold_Date']].fillna(0).ne(0).any(axis=1), 'Bar_Code']
df1 = df[~df['Bar_Code'].isin(g)].drop_duplicates('Bar_Code').drop('Location', axis=1)
print (df1)
         Bar_Code  SOH  Cost Sold_Date
0  00000003589823    0   0.0       NaN
6   0000131604108    0   0.0       NaN