将给定id的列中的最新值分组

Question

我有一个包含给定日期和用户的一些度量的数据框架

>>> pd.DataFrame({"user": ['juan','juan','juan','gonzalo'], "date": [1, 2, 3, 1], "var1": [1, 2, None, 1], "var2": [None, 4, 5, 6]})
      user  date  var1  var2
0     juan     1   1.0   NaN
1     juan     2   2.0   4.0
2     juan     3   NaN   5.0
3  gonzalo     1   1.0   6.0

现在，对于每个用户，我想为每个变量（var1，var2）提取2个最近的值，忽略NaN，除非没有足够的值来填充数据

作为参考，这应该是上面描述的数据的结果数据帧

user     var1_0  var1_1  var2_0  var2_1
juan       2.0     1.0     5.0    4.0
gonzalo    1.0      NaN    6.0    NaN

每个“历史”值被添加为带有_0或_1后缀的新列

Answer 1

您可以按user分组并聚合以获得最近的两个值。这几乎是所有的方式，但你有一个元素列表，而不是列。如果您想拥有实际的2列，则必须将新创建的列表拆分为列。完整代码：

import pandas as pd
import numpy as np

df = pd.DataFrame(
    {
        "user": ["juan", "juan", "juan", "gonzalo"],
        "date": [1, 2, 3, 1],
        "var1": [1, 2, None, 1],
        "var2": [None, 4, 5, 6],
    }
)

# This almost gets you there
df = (
    df.sort_values(by="date")
    .groupby("user")
    .agg({"var1": lambda x: x.dropna().head(2), "var2": lambda x: x.dropna().head(2)})
)

# Split the columns and get the correct column names
df[["var1_0", "var2_0"]] = df[["var1", "var2"]].apply(
    lambda row: pd.Series(el[0] if isinstance(el, np.ndarray) else el for el in row),
    axis=1,
)
df[["var1_1", "var2_1"]] = df[["var1", "var2"]].apply(
    lambda row: pd.Series(el[-1] if isinstance(el, np.ndarray) else None for el in row),
    axis=1,
)
print(df)

>>
               var1        var2  var1_0  var2_0  var1_1  var2_1
user                                                           
gonzalo         1.0         6.0     1.0     6.0     NaN     NaN
juan     [1.0, 2.0]  [4.0, 5.0]     1.0     4.0     2.0     5.0

Answer 2

如有必要，首先按^{}中的两列进行排序，并按^{}重新整形并删除缺少的值，按^{}过滤每个组的前2个错误，然后按^{}创建计数器列，并在^{}中使用展平MultiIndex旋转：

df1 = (df.sort_values(['user','date'])
         .melt(id_vars='user', value_vars=['var1','var2'])
         .dropna(subset=['value'])
        )


df1 = df1.groupby(['user','variable']).head(2)
df1['g'] = df1.groupby(['user','variable']).cumcount(ascending=False)

df1 = df1.pivot(index='user', columns=['variable', 'g'], values='value')
#oldier pandas versions
#df1 = df1.set_index(['user','variable', 'g'])['value'].unstack([1,2])
df1.columns = df1.columns.map(lambda x: f'{x[0]}_{x[1]}')
df1 = df1.reset_index()
print (df1)
      user  var1_0  var1_1  var2_0  var2_1
0  gonzalo     1.0     NaN     6.0     NaN
1     juan     2.0     1.0     5.0     4.0