从大型数据集中的成对列中选取最后有效的数据日期

2024-03-28 16:09:30 发布

您现在位置:Python中文网/ 问答频道 /正文

我有一个如下所示的数据框,其中第一列包含日期,其他列包含这些日期的数据:

         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

因此,对于{}{}{},最后一个有效数据是{}日期{},对于{}{}{}其{}日期{}。然后,上述数据框中的列v1-lastv2-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'])

这一方法目前还可以使用,并给出了预期的结果,但在大数据集上运行时需要相当长的时间。是否可以避免循环,并以不同且高效的方式进行


Tags: 数据dfdataindexcolk2k1nan
3条回答

我们可以反转列的名称并使用pd.wide_to_long,其中stubnames将是v_j,identifier将是date,我们在结果中将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")

重命名列,然后使用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

溶液

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的索引设置为datestack以重新形状

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

Split使用level_1列中的字符串创建另外两列keysval

          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

Pivot数据帧,用于重新形状并向列名添加后缀-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

相关问题 更多 >