pandas中的Groupby和减法列

Question

我有一个有4列的时间序列数据，我想按列FisherID、DateFishing和{}分组，并求出Weight的总和。另外，我想用column Weight中的值减去列Total_catch中的值，其结果将保存在名为DIFF的新列中。并且，我想显示DIFF列中高于0.1的值。在

这是我的密码。在

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

我的数据：

^{pr2}$

Answer 1

我想你在找一个groupby+transform：

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

然后，通过从Total_Catch中减去Weight列来找到Diff。在

^{pr2}$

或者，如果要从Total_Catch中减去分组的Weight，请使用：

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

---

查询行

本节以第二个选项的结果为基础。请注意，所有这些选项都将布尔掩码应用于数据帧。如果您只需要掩码，请不要将其应用于数据帧。只需应用条件并打印：

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

如果要提取所有有效行，有几个选项。在

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和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和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

---

请注意，这些结果具有原始df的索引。如果要重置索引，请使用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