Python:使用 df1 的 2 列中的信息模拟 Excel 的索引匹配，用于查找 df2 中的位置

Question

除此之外，我正在尝试处理大型数据集，这就是我从 Excel 切换到 Python 的原因。我设法解决了大多数问题，但是，对于我明显糟糕的谷歌技能来说，以下问题似乎很复杂。

可用数据:我有一个包含 50 万行的 pandas dataframe1。每行包含一个时间戳、一个机器编号和一个指示器(如果处于事件状态)。我有另一个 pandas dataframe2，大约有 3000 万行(一年每秒一次)和 60 列(每台机器 1 列)。

目标:我想通过查看 df1 中的每一行来填充 df2 的空矩阵。在 df1 中，我想使用时间戳信息在 df2 中查找相应的行，在 df1 中，我想使用机器编号在 df2 中查找相应的列，然后将值粘贴到 df2 中

import pandas as pd

df_1 = pd.DataFrame({'timestamp':[1, 1, 4, 5],
                 'machine_number':[123, 789, 789, 123],
                 'active_inactive':[1, 0, 1, 0]})    
df_2 = pd.DataFrame({'time':[1, 2, 3, 4, 5],
                 '123':['', '', '', '', ''],
                 '789':['', '', '', '', '']})

在索引匹配模拟之后，我的目标是以下结果

df_3 = pd.DataFrame({'time':[1, 2, 3, 4, 5],
                 '123':[1, '', '', '', 0],
                 '789':[0, '', '', 1, '']})

最终我想用最新的事件/非事件指示器填充空值，直到出现新指示器:

df_4 = pd.DataFrame({'time':[1, 2, 3, 4, 5],
                 '123':[1, 1, 1, 1, 0],
                 '789':[0, 0, 0, 1, 1]})

但这可能是另一天的问题，我可以尝试自己解决。

预先感谢您提供任何有用的信息。

最佳斯文

Answer 1

欢迎来到SO。所以这个问题实际上比看起来要复杂一些。首先要了解的是 pandas 最适合“整洁”的数据 ( https://en.wikipedia.org/wiki/Tidy_data )。虽然 Excel 非常适合数据输入，但它并没有针对分析进行优化(正如您所发现的)。当您更多地使用数据帧进行编码时，您将了解更多相关内容，但现在只需了解事物需要采用不同的格式才能正常工作。

因此，首先您需要将第二个数据名从宽格式转换为长格式(您需要整理它):

df_1 = pd.DataFrame({'timestamp':[1, 1, 4, 5],
                 'machine_number':[123, 789, 789, 123],
                 'active_inactive':[1, 0, 1, 0]})

df_2 = pd.DataFrame({'timestamp':[1, 2, 3, 4, 5], # note that I changed this name to 'timestamp' so
                                                  # it's the same in both dataframe
                 '123':['', '', '', '', ''],
                 '789':['', '', '', '', '']})

(df_2_melted = pd.melt(df_2, id_vars=['timestamp'], 
                             value_vars=['123', '789'], 
                             var_name='machine_number')
                 .drop(columns='value'))

df_2_melted['machine_number'] = df_2_melted['machine_number'].astype('int64')
# I had to change this column to 'int64' because the next operation won't work unless the
# columns are the same type

这就是你的:

    timestamp   machine_number
0   1           123
1   2           123
2   3           123
3   4           123
4   5           123
5   1           789
6   2           789
7   3           789
8   4           789
9   5           789

然后您可以将这些列“合并”在一起。这就像 sql 或 excel 中的 index_match/vlookup 的连接操作:

merged = pd.merge(df_2_melted, df_1, how='left', on=['timestamp', 'machine_number'])

merged

    timestamp   machine_number  active_inactive
0   1           123             1.0
1   2           123             NaN
2   3           123             NaN
3   4           123             NaN
4   5           123             0.0
5   1           789             0.0
6   2           789             NaN
7   3           789             NaN
8   4           789             1.0
9   5           789             NaN

然后，如果您想将其恢复为与原始数据帧中相同的格式，则需要将其透视(顾名思义，这就像 Excel 中的数据透视表) ):

merged.pivot(index='timestamp', columns='machine_number', values='active_inactive').reset_index(drop=True)

machine_number  123     789
0               1.0     0.0
1               NaN     NaN
2               NaN     NaN
3               NaN     1.0
4               0.0     NaN

话虽这么说，您将希望保持所有内容都以整洁的格式进行分析，因此，如果您想最终将其写成电子表格以提供给其他人，我仅建议您进行转换。