如何按时间戳与列合并Pandas DataFrame行？

Hey there! Let's break down how to merge your DataFrame rows based on timestamps and the product column. I'll cover two common scenarios you might be aiming for, using your sample data.

First, Let's Set Up the DataFrame #

First, let's make sure we're working with the same starting point. Here's how to load your data into a pandas DataFrame:

import pandas as pd

data = [
    {"start_ts": "2018-05-14 10:54:33", "end_ts": "2018-05-14 11:54:33", "product": "a", "value": 1},
    {"start_ts": "2018-05-14 11:54:33", "end_ts": "2018-05-14 12:54:33", "product": "a", "value": 1},
    {"start_ts": "2018-05-14 13:54:33", "end_ts": "2018-05-14 14:54:33", "product": "a", "value": 1},
    {"start_ts": "2018-05-14 10:54:33", "end_ts": "2018-05-14 11:54:33", "product": "b", "value": 1}
]

df = pd.DataFrame(data)

Scenario 1: Merge Rows with the Same Time Interval (Across Products) #

If you want to combine rows that share the exact same start_ts and end_ts into a single row, with each product as a separate column, use pivot_table:

# Pivot the DataFrame to group by time intervals, with products as columns
pivoted_df = df.pivot_table(
    index=['start_ts', 'end_ts'],
    columns='product',
    values='value',
    fill_value=0  # Fill missing product values with 0
).reset_index()

# Remove the "product" header from columns (optional cleanup)
pivoted_df.columns.name = None

print(pivoted_df)

Output: #

start_ts             end_ts  a  b
0  2018-05-14 10:54:33  2018-05-14 11:54:33  1  1
1  2018-05-14 11:54:33  2018-05-14 12:54:33  1  0
2  2018-05-14 13:54:33  2018-05-14 14:54:33  1  0

This gives you one row per time interval, with columns for each product showing their value (or 0 if the product isn't present in that interval).

Scenario 2: Merge Consecutive Time Intervals (For the Same Product) #

If you want to consolidate consecutive time intervals for the same product (where the end time of one interval is the start time of the next), follow these steps:

# Sort the DataFrame by product and start time first
df_sorted = df.sort_values(by=['product', 'start_ts'])

# Flag rows that are part of a consecutive interval
df_sorted['is_consecutive'] = (
    df_sorted.groupby('product')['start_ts'].shift() == df_sorted['end_ts'].shift(1)
)

# Create a group ID for each block of consecutive intervals
df_sorted['group_id'] = df_sorted.groupby('product')['is_consecutive'].cumsum()

# Aggregate the groups to merge consecutive intervals
merged_df = df_sorted.groupby(['product', 'group_id']).agg(
    start_ts=('start_ts', 'first'),  # Take the earliest start time
    end_ts=('end_ts', 'last'),       # Take the latest end time
    total_value=('value', 'sum')     # Sum the values (adjust aggregation as needed)
).reset_index(drop=True)

print(merged_df)

Output: #

product           start_ts             end_ts  total_value
0       a  2018-05-14 10:54:33  2018-05-14 12:54:33            2
1       a  2018-05-14 13:54:33  2018-05-14 14:54:33            1
2       b  2018-05-14 10:54:33  2018-05-14 11:54:33            1

Here, the first two intervals for product a (10:54-11:54 and 11:54-12:54) are merged into a single row with a total value of 2, since they're consecutive. The non-consecutive interval (13:54-14:54) stays as a separate row.

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