合并分组DataFrame：将长格式时序数据转换为宽表格式

Alright, let's turn your long-format time series dataset into the wide table you're looking for. This is a classic pivot operation in pandas, and it's straightforward once you know the right functions to use.

Step 1: Set Up Your Data #

First, let's recreate your original DataFrame so we can work with it directly:

import pandas as pd

# Your raw dataset
data = [
    [0, "2018-02-10 23:09:06.843000", 1.139982, "OPEN", False, 17144126, 8.80],
    [1, "2018-02-10 23:11:09.954000", 1.139982, "OPEN", False, 17144126, 8.80],
    [2, "2018-02-10 23:11:09.954000", 1.139982, "OPEN", False, 16848301, 2.88],
    [3, "2018-02-10 23:13:09.943000", 1.139982, "OPEN", False, 16848301, 2.90]
]

# Create DataFrame with proper column names
df = pd.DataFrame(data, columns=["Row", "Time", "Id", "Status", "In", "SId", "Value"])

Step 2: Pivot to Wide Format #

We'll use pivot() here because each combination of Time and SId has exactly one matching Value (no duplicates to aggregate). If you ever run into duplicate entries for the same Time + SId pair, you'd switch to pivot_table() with an aggregation function like mean or first.

# Pivot the table: use Time as index, SId as column headers, Value as cell values
wide_df = df.pivot(index="Time", columns="SId", values="Value")

# Rename columns to match your desired format (SId_xxxxxx)
wide_df.columns = [f"SId_{col}" for col in wide_df.columns]

# Reset index to make Time a regular column instead of the index
wide_df = wide_df.reset_index()

# Add the sequential index column like your example (optional but matches your output)
wide_df.insert(0, "Index", range(len(wide_df)))

Step 3: View the Final Result #

If you print wide_df, you'll get exactly the wide table you need:

Index                         Time  SId_17144126  SId_16848301
0      0  2018-02-10 23:09:06.843000          8.80            NaN
1      1  2018-02-10 23:11:09.954000          8.80           2.88
2      2  2018-02-10 23:13:09.943000           NaN           2.90

Quick Adjustments (If Needed): #

• If you want to replace NaN with the string "Na" instead, just run wide_df = wide_df.fillna("Na").
• If you don't need the initial sequential index column, skip the insert() step entirely.

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