如何基于索引与另一DataFrame多列值对比用Pandas生成新DataFrame

现有两个数据源：

原始数值数据 #

raw_data = {'site_394$line_2420$tag_144': {1670231589000: 7,
  1671231589000: 7,
  1672231589000: 9,
  1673231589000: 7},
 'site_395$line_2420$tag_154': {1670231589000: 9,
  1671231589000: 10,
  1672231589000: 25,
  1673231589000: 6}}

事件数据 #

events_data=[
    {
      "tag":"site_394$line_2420$tag_144",
      "from_date": 1670231589000,
      "to_date": 1670232589000,
      "event_name": "Event One"
    },
    {
      "tag":"site_394$line_2420$tag_144",
      "from_date": 1671231589000,
      "to_date": 1671332589000,
      "event_name": "Event Two"
    },
    {
        "tag":"site_394$line_2420$tag_144",
      "from_date": 1671231589000,
      "to_date": 1671332589000,
      "event_name": "Event Two Update"
    },
    {
        "tag":"site_394$line_2420$tag_144",
      "from_date": 1670231589100,
      "to_date": 1670232589200,
      "event_name": "Event Three"
    },
     {
         "tag":"site_395$line_2420$tag_154",
      "from_date": 1670231589000,
      "to_date": 1670232589000,
      "event_name": "Event One"
    },
    {
        "tag":"site_395$line_2420$tag_154",
      "from_date": 1671231589000,
      "to_date": 1671332589000,
      "event_name": "Event Two"
    },
    {
      "tag":"site_395$line_2420$tag_154",
      "from_date": 1670231589100,
      "to_date": 1670232589200,
      "event_name": "Event Three"
    }
]

需要将两者合并为如下格式的DataFrame：

核心规则：

• 针对raw_data中的每个时间戳索引和标签列，若该时间戳落在events_data对应标签的from_date与to_date区间内，就用对应的event_name替换原数值；
• 同一时间戳匹配到多个事件时，用逗号拼接事件名称；
• 无匹配事件则保留原始数值。

使用Pandas分步实现，具体操作如下：

1. 转换原始数据为DataFrame #

先把字典格式的raw_data转为Pandas DataFrame，方便后续操作：

import pandas as pd

df_raw = pd.DataFrame(raw_data)

2. 构建事件映射关系 #

将events_data转为DataFrame后，按标签和时间戳筛选匹配事件，生成"标签-时间戳-拼接后事件名"的映射：

df_events = pd.DataFrame(events_data)

event_mapping = {}
# 遍历每个唯一标签
for tag in df_events['tag'].unique():
    tag_events = df_events[df_events['tag'] == tag]
    # 遍历原始数据的每个时间戳
    for timestamp in df_raw.index:
        # 筛选时间戳落在事件区间内的记录
        matched = tag_events[(tag_events['from_date'] <= timestamp) & (timestamp <= tag_events['to_date'])]
        if not matched.empty:
            # 去重后拼接事件名称
            event_str = ','.join(matched['event_name'].unique())
            if tag not in event_mapping:
                event_mapping[tag] = {}
            event_mapping[tag][timestamp] = event_str

3. 替换原始DataFrame中的数值 #

遍历每个标签列，用映射中的事件名称替换对应时间戳的数值：

for tag in df_raw.columns:
    if tag in event_mapping:
        for timestamp, event_name in event_mapping[tag].items():
            df_raw.loc[timestamp, tag] = event_name

4. 查看最终结果 #

执行上述代码后，df_raw即为目标DataFrame，打印结果：

print(df_raw)

输出结果：

site_394$line_2420$tag_144 site_395$line_2420$tag_154
1670231589000                  Event One                  Event One
1671231589000  Event Two,Event Two Update                  Event Two
1672231589000                            9                       25.0
1673231589000                            7                        6.0

内容的提问来源于stack exchange，提问作者Irfanuddin