从大型数据集的一对列中选择最后一个有效的数据日期。

Question

从大型数据集的一对列中选择最后一个有效的数据日期。

7

我有一个数据框，其中第一列包含日期，其他列包含这些日期上的数据：

         date  k1-v1  k1-v2  k2-v1  k2-v2  k1k3-v1  k1k3-v2  k4-v1  k4-v2
0  2021-01-05    2.0    7.0    NaN    NaN      NaN      NaN    9.0    6.0
1  2021-01-31    NaN    NaN    8.0    5.0      NaN      NaN    7.0    6.0
2  2021-02-15    9.0    5.0    NaN    3.0      4.0      NaN    NaN    NaN
3  2021-02-28    NaN    9.0    0.0    1.0      NaN      NaN    8.0    8.0
4  2021-03-20    7.0    NaN    NaN    NaN      NaN      NaN    NaN    NaN
5  2021-03-31    NaN    NaN    8.0    NaN      3.0      NaN    8.0    0.0
6  2021-04-10    NaN    NaN    7.0    6.0      NaN      NaN    NaN    9.0
7  2021-04-30    NaN    6.0    NaN    NaN      NaN      NaN    1.0    NaN
8  2021-05-14    8.0    NaN    3.0    3.0      4.0      NaN    NaN    NaN
9  2021-05-31    NaN    NaN    2.0    1.0      NaN      NaN    NaN    NaN

列总是成对出现：(k1-v1, k1-v2);(k2-v1, k2-v2);(k1k3-v1, k1k3-v2) 等N个对。但是这些列并不总是按照特定的顺序排列。因此，k1-v1 不一定仅跟随着 k1-v2，在数据框中还将存在某个位置上的k1-v2 列。为了简化，我将它们并排展示。

我需要找到每对列中最后一个有效数据日期，并将其汇总如下：

   keys     v1-last     v2-last
0    k1  2021-05-14  2021-04-30
1    k2  2021-05-31  2021-05-31
2  k1k3  2021-05-14         NaN
3    k4  2021-04-30  2021-04-10

因此，对于 (k1-v1)，最后一个有效数据是 8.0，日期为 2021-05-14；对于 (k2-v2)，其最后一个有效数据为 6.0，日期为 2021-04-30。上述数据框中的列 v1-last 和 v2-last 将相应地填充给 k1，其他类似。

目前我正在按照下面的方式操作，但对于较大的数据集来说并不是很高效：

df.set_index('date', inplace=True)
unique_cols = set([col[0] for col in df.columns.str.split('-')])
summarized_data = []
for col in unique_cols:
    pair_df = df.loc[:,[col+'-v1',col+'-v2']].dropna(how='all')
    v1_last_valid = pair_df.iloc[:,0].last_valid_index()
    v2_last_valid = pair_df.iloc[:,1].last_valid_index()
    summarized_data.append([col, v1_last_valid, v2_last_valid])

summarized_df = pd.DataFrame(summarized_data, columns=['keys','v1-last','v2-last'])

目前这种方法可以得到预期的结果，但是在处理大型数据集时需要耗费相当长的时间。是否可以避免循环，并以不同而高效的方式完成此任务？

- Ank

"对于（k2-v2）它在2021-04-30是6.0，您的意思是“对于（k1-v2）吗？”因为k2-v2在2021-04-10有一个6.0，但那不是最后有效的数据。" - joao

5个回答

3

解决方案

s = df.set_index('date').stack()
s = s.reset_index().drop_duplicates('level_1', keep='last')
s[['keys', 'val']] = s['level_1'].str.split('-', expand=True)
s = s.pivot('keys', 'val', 'date').add_suffix('-last')

解释

将DataFrame的索引设置为date，并使用stack函数重新塑形

date               
2021-01-05  k1-v1      2.0
            k1-v2      7.0
            k4-v1      9.0
            k4-v2      6.0
2021-01-31  k2-v1      8.0
            k2-v2      5.0
            k4-v1      7.0
            k4-v2      6.0
...
2021-05-31  k2-v1      2.0
            k2-v2      1.0
dtype: float64

重置索引并删除 level_1 中具有重复值的行

          date  level_1    0
24  2021-04-10    k4-v2  9.0
25  2021-04-30    k1-v2  6.0
26  2021-04-30    k4-v1  1.0
27  2021-05-14    k1-v1  8.0
30  2021-05-14  k1k3-v1  4.0
31  2021-05-31    k2-v1  2.0
32  2021-05-31    k2-v2  1.0

将level_1列中的字符串拆分，创建两个附加列keys和val。

          date  level_1    0  keys val
24  2021-04-10    k4-v2  9.0    k4  v2
25  2021-04-30    k1-v2  6.0    k1  v2
26  2021-04-30    k4-v1  1.0    k4  v1
27  2021-05-14    k1-v1  8.0    k1  v1
30  2021-05-14  k1k3-v1  4.0  k1k3  v1
31  2021-05-31    k2-v1  2.0    k2  v1
32  2021-05-31    k2-v2  1.0    k2  v2

将数据框架进行透视以重新整理，并在列名后添加后缀-last

val      v1-last     v2-last
keys                        
k1    2021-05-14  2021-04-30
k1k3  2021-05-14         NaN
k2    2021-05-31  2021-05-31
k4    2021-04-30  2021-04-10

- Shubham Sharma

2

谢谢。这个程序有效，并且几乎瞬间给我了期望的结果！ - Ank

@Ank 祝你编程愉快！ - Shubham Sharma

2

重命名列，然后使用wide_to_long重新构造数据框。使用Stack删除NAN。然后使用groupby-agg提取最后一个值。

df2 = (
    pd.wide_to_long(
        df2.rename(columns=(lambda x: ''.join(x.split('-')[::-1]))),
        stubnames=['v2', 'v1'],
        i='date',
        j='keys',
        suffix='.*'
    ).stack()
    .reset_index(0)
    .groupby(level=[0, 1])
    .agg({'date': 'last'})
    .unstack(-1)
).add_suffix('-last')

df2.columns = df2.columns.droplevel()

输出：

         v2-last     v1-last
keys                        
k1    2021-04-30  2021-05-14
k1k3         NaN  2021-05-14
k2    2021-05-31  2021-05-31
k4    2021-04-10  2021-04-30

- Nk03

1

你的解决方案对我来说有点复杂 :) 但是它仍然有效！ - Ank

2

结合@MustafaAydin和@ShubhamSharma的想法，我们可以这样做:

temp = df.set_index('date')

使用 pd.Series.last_valid_index:

# you could use `agg` since it is a reducer
temp = temp.apply(pd.Series.last_valid_index)

将索引转换为多级索引：

temp.index = temp.index.str.split("-", expand = True)

解除堆叠并添加后缀：

temp.unstack().add_suffix('_last')

         v1_last     v2_last
k1    2021-05-14  2021-04-30
k1k3  2021-05-14        None
k2    2021-05-31  2021-05-31
k4    2021-04-30  2021-04-10

- sammywemmy

这个方案更好，应该比其他答案快得多，非常棒的想法！ - Shubham Sharma

运行良好！在我的原始数据框上进行了测试，发现它只比@ShubhamSharma的解决方案慢一点。但对于我需要做的事情来说，这更具有自我记录性。 - Ank

1

首先，定义一个函数来获取任何给定列名所需的日期：

def last_date(col):
    idx = df[col].last_valid_index()
    return df.loc[idx, 'date'] if idx is not None else np.nan

然后将键分组到字典中，并使用它来构建结果数据框：

keys = {}
for col in df.columns[1:]:
    key, vn = col.split('-')
    keys.setdefault(key, [None]*2)[int(vn[-1])-1] = last_date(col)

pd.DataFrame.from_records([[k] + dt_list for k, dt_list in keys.items()], columns=['keys', 'v1-last', 'v2-last'])

这会产生所需的输出：

。

   keys     v1-last     v2-last
0    k1  2021-05-14  2021-04-30
1    k2  2021-05-31  2021-05-31
2  k1k3  2021-05-14         NaN
3    k4  2021-04-30  2021-04-10

- joao

谢谢你的解决方案。它与我的原始方案相当，并且运行时间大约为25秒左右。但很高兴知道另一种方法 :) - Ank

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Mustafa Aydın · Accepted Answer

我们可以反转列名并使用pd.wide_to_long，其中stubnames将是v_j，identifier将是日期，我们将在结果中将k*称为keys。然后，我们可以按keys进行分组，并使用DataFrame.last_valid_index进行聚合。

# reverse the column names
df.columns = df.columns.str.replace(r"(\w+)-(\w+)", r"\2-\1", regex=True)

# wide to long (and then make `keys` a column with reset_index)
long = pd.wide_to_long(df, stubnames=["v1", "v2"], i="date", j="keys",
                       sep="-", suffix=r"\w+").reset_index("keys")

# get the last valid dates & add a suffix
result = (long.groupby("keys")
              .agg(pd.DataFrame.last_valid_index)
              .add_suffix("-last"))

获取

>>> result

         v1-last     v2-last
keys
k1    2021-05-14  2021-04-30
k1k3  2021-05-14        None
k2    2021-05-31  2021-05-31
k4    2021-04-30  2021-04-10

为了让 v_j 的存根名称更加通用：

stubnames = df.columns.str.extract(r"^(\w+)-", expand=False).dropna().unique()
# Index(["v1", "v2"], dtype="object")