Groupby and subtract columns in pandas

user_8416549 0
Viewed 14732

I have a time-series data with 4 columns and I would like to groupby the column FisherID, DateFishing and Total_Catch, and sum the column Weight. Also, I want to minus the value in column Total_catch with that in column Weight and its result will be kept in the new column named DIFF. And, I want to show the value in column DIFF that is higher than 0.1.

Here is my code.

df["DIFF"]=df.groupby(["FisherID", "DateFishing", "Total_Catch"]) ["Weight"].sum()-["Total_Catch"]>=0.1

My data:

FisherID    DateFishing Total_Catch Weight
1            24-Oct-11      0.9      0.2
1            24-Oct-11      0.9      0.264
1            24-Oct-11      0.9      0.37
2            25-Oct-11      0.7      0.144
2            27-Oct-11      8.2      0.084
2            27-Oct-11      8.2      0.45
3            27-Oct-11      8.2      0.61
3            27-Oct-11      8.2      7
3            29-Oct-11      0.64    0.184
pandas-groupby pandas dataframe group-by python
1 Answers

I think you're looking for a groupby + transform:

df['Sum'] = df.groupby(
    ["FisherID", "DateFishing", "Total_Catch"]
)["Weight"].transform('sum')

Then, find Diff by subtracting the Weight col from Total_Catch.

df['Diff'] = (df['Total_Catch'] - df['Weight'])

df

   FisherID DateFishing  Total_Catch  Weight    Sum   Diff
0         1   24-Oct-11         0.90   0.200  0.834  0.700
1         1   24-Oct-11         0.90   0.264  0.834  0.636
2         1   24-Oct-11         0.90   0.370  0.834  0.530
3         2   25-Oct-11         0.70   0.144  0.144  0.556
4         2   27-Oct-11         8.20   0.084  0.534  8.116
5         2   27-Oct-11         8.20   0.450  0.534  7.750
6         3   27-Oct-11         8.20   0.610  7.610  7.590
7         3   27-Oct-11         8.20   7.000  7.610  1.200
8         3   29-Oct-11         0.64   0.184  0.184  0.456

Alternatively, if you're trying to subtract the grouped Weight from Total_Catch, use:

df['Diff'] = df["Total_Catch"] -df.groupby(["FisherID", \
                   "DateFishing", "Total_Catch"])["Weight"].transform('sum')

df

   FisherID DateFishing  Total_Catch  Weight   Diff
0         1   24-Oct-11         0.90   0.200  0.066
1         1   24-Oct-11         0.90   0.264  0.066
2         1   24-Oct-11         0.90   0.370  0.066
3         2   25-Oct-11         0.70   0.144  0.556
4         2   27-Oct-11         8.20   0.084  7.666
5         2   27-Oct-11         8.20   0.450  7.666
6         3   27-Oct-11         8.20   0.610  0.590
7         3   27-Oct-11         8.20   7.000  0.590
8         3   29-Oct-11         0.64   0.184  0.456

Querying rows

This section builds upon the result of the second option. Note that all of these options apply a boolean mask to a dataframe. If all you want is the mask, don't apply it to the dataframe. Simply apply the condition and print:

df.Diff > 0.1

0    False
1    False
2    False
3     True
4     True
5     True
6     True
7     True
8     True
Name: Diff, dtype: bool

If you want to extract all valid rows, there are a few options.

df.query

df.query('Diff > 0.1')

   FisherID DateFishing  Total_Catch  Weight   Diff
3         2   25-Oct-11         0.70   0.144  0.556
4         2   27-Oct-11         8.20   0.084  7.666
5         2   27-Oct-11         8.20   0.450  7.666
6         3   27-Oct-11         8.20   0.610  0.590
7         3   27-Oct-11         8.20   7.000  0.590
8         3   29-Oct-11         0.64   0.184  0.456

boolean indexing

df[df.Diff > 0.1]

   FisherID DateFishing  Total_Catch  Weight   Diff
3         2   25-Oct-11         0.70   0.144  0.556
4         2   27-Oct-11         8.20   0.084  7.666
5         2   27-Oct-11         8.20   0.450  7.666
6         3   27-Oct-11         8.20   0.610  0.590
7         3   27-Oct-11         8.20   7.000  0.590
8         3   29-Oct-11         0.64   0.184  0.456

df.eval

df[df.eval('Diff > 0.1')]

   FisherID DateFishing  Total_Catch  Weight   Diff
3         2   25-Oct-11         0.70   0.144  0.556
4         2   27-Oct-11         8.20   0.084  7.666
5         2   27-Oct-11         8.20   0.450  7.666
6         3   27-Oct-11         8.20   0.610  0.590
7         3   27-Oct-11         8.20   7.000  0.590
8         3   29-Oct-11         0.64   0.184  0.456

df.where and dropna

df.where(df.Diff > 0.1).dropna(how='all')

   FisherID DateFishing  Total_Catch  Weight   Diff
3       2.0   25-Oct-11         0.70   0.144  0.556
4       2.0   27-Oct-11         8.20   0.084  7.666
5       2.0   27-Oct-11         8.20   0.450  7.666
6       3.0   27-Oct-11         8.20   0.610  0.590
7       3.0   27-Oct-11         8.20   7.000  0.590
8       3.0   29-Oct-11         0.64   0.184  0.456

np.where and df.iloc:

df.iloc[np.where(df.Diff > 0.1)[0]]

   FisherID DateFishing  Total_Catch  Weight   Diff
3         2   25-Oct-11         0.70   0.144  0.556
4         2   27-Oct-11         8.20   0.084  7.666
5         2   27-Oct-11         8.20   0.450  7.666
6         3   27-Oct-11         8.20   0.610  0.590
7         3   27-Oct-11         8.20   7.000  0.590
8         3   29-Oct-11         0.64   0.184  0.456

Note that these results have the index of the original df. If you want to reset the index, use reset_index:

df[df.Diff > 0.1].reset_index(drop=True)

   FisherID DateFishing  Total_Catch  Weight   Diff
0         2   25-Oct-11         0.70   0.144  0.556
1         2   27-Oct-11         8.20   0.084  7.666
2         2   27-Oct-11         8.20   0.450  7.666
3         3   27-Oct-11         8.20   0.610  0.590
4         3   27-Oct-11         8.20   7.000  0.590
5         3   29-Oct-11         0.64   0.184  0.456
user_49090870
Related