将最后一行除以组的第一行

我有一个如下所示的数据框：

ID  Quantity Time
54  100      2020-01-01 00:00:05
54  87       2020-01-01 00:00:06
58  400      2020-01-01 00:00:08
58  390      2020-01-01 00:00:14
...

我已经按 ID 对数据帧进行了分组，并且只获取每个 ID 的第一个和最后一个值。

g = df.groupby('ID')

(pd.concat([g.head(1), g.tail(1)])
   .drop_duplicates()
   .sort_values('ID')
   .reset_index(drop=True))

现在，我想将每个 ID 最后一行的数量除以第一行的数量，并将其保存到仅最后一个条目的“结果”列中。

结果 df 应如下所示：

ID  Quantity  Time                  Result
54  100       2020-01-01 00:00:05   
54  87        2020-01-01 00:00:06   0.87
58  400       2020-01-01 00:00:08
58  390       2020-01-01 00:00:14   0.975

谢谢你的帮助！

如果总有成对的ID您可以通过索引选择所有对行[1::2]并除以移位值Series.shift http://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.Series.shift.html:

df['Result'] = df['Quantity'].iloc[1::2].div(df['Quantity'].shift())
print (df)
   ID  Quantity                 Time  Result
0  54       100  2020-01-01 00:00:05     NaN
1  54        87  2020-01-01 00:00:06   0.870
2  58       400  2020-01-01 00:00:08     NaN
3  58       390  2020-01-01 00:00:14   0.975

与小组合作的解决方案：

另一个想法是使用GroupBy.transform http://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.core.groupby.GroupBy.transform.html with GroupBy.first http://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.core.groupby.GroupBy.first.html并除以最后一个值ID by DataFrame.drop_duplicates http://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.DataFrame.drop_duplicates.html:

first = df.groupby('ID')['Quantity'].transform('first')
df['Result'] = df.drop_duplicates('ID', keep='last')['Quantity'].div(first)
print (df)
   ID  Quantity                 Time  Result
0  54       100  2020-01-01 00:00:05     NaN
1  54        87  2020-01-01 00:00:06   0.870
2  58       400  2020-01-01 00:00:08     NaN
3  58       390  2020-01-01 00:00:14   0.975

或者使用 lambda 函数并替换为NaN by Series.mask http://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.Series.mask.html:

df['Result'] = (df.groupby('ID')['Quantity'].transform(lambda x: x.iat[-1] / x.iat[0])
                  .mask(df['ID'].duplicated(keep='last')))
print (df)

   ID  Quantity                 Time  Result
0  54       100  2020-01-01 00:00:05     NaN
1  54        87  2020-01-01 00:00:06   0.870
2  58       400  2020-01-01 00:00:08     NaN
3  58       390  2020-01-01 00:00:14   0.975

如果至少有一组具有独特的ID?

print (df)
   ID  Quantity                 Time
0  54       100  2020-01-01 00:00:05
1  58       400  2020-01-01 00:00:08
2  58       390  2020-01-01 00:00:14

#failed
df['Result1'] = df['Quantity'].iloc[1::2].div(df['Quantity'].shift())
#working correct
first = df.groupby('ID')['Quantity'].transform('first')
df['Result2'] = df.drop_duplicates('ID', keep='last')['Quantity'].div(first)
#working correct
df['Result3'] = (df.groupby('ID')['Quantity'].transform(lambda x: x.iat[-1] / x.iat[0])
                  .mask(df['ID'].duplicated(keep='last')))
print (df)
   ID  Quantity                 Time  Result1  Result2  Result3
0  54       100  2020-01-01 00:00:05      NaN    1.000    1.000
1  58       400  2020-01-01 00:00:08      4.0      NaN      NaN
2  58       390  2020-01-01 00:00:14      NaN    0.975    0.975