如何将具有相同TEP列值的记录合并至同一行

Hey there! Let's figure out how to merge rows that share the same Tep value into a single row, just like your example shows. Below are two practical ways to do this using widely-used tools: SQL and Python's Pandas library.

Solution 1: Using SQL (Window Functions + Self-Join) #

This approach works with most modern databases that support window functions (like MySQL 8+, PostgreSQL, SQL Server). We'll first rank records within each Tep group, then join the first and second records in each group to build the merged row.

WITH ranked_data AS (
    SELECT 
        Tep,
        `ID number`,
        Date,
        Value,
        type,
        -- Assign a rank to each record in the same Tep group, ordered by ID number
        ROW_NUMBER() OVER (PARTITION BY Tep ORDER BY `ID number`) AS rn
    FROM your_table_name
)
SELECT 
    r1.Tep,
    r1.`ID number`,
    r1.Date,
    r1.Value,
    r1.type,
    r2.`ID number` AS `ID number -1`,
    r2.Value AS `Value -1`,
    r2.type AS `type -1`,
    -- Calculate total by adding values, handle cases where there's only one record per Tep
    r1.Value + COALESCE(r2.Value, 0) AS total
FROM ranked_data r1
-- Join the first record (rn=1) with the second record (rn=2) in the same Tep group
LEFT JOIN ranked_data r2 
    ON r1.Tep = r2.Tep AND r1.rn = 1 AND r2.rn = 2
-- Only keep the first record as the base for the merged row
WHERE r1.rn = 1;

How this works: #

1. The ranked_data CTE adds a row number (rn) to each record, grouped by Tep and ordered by ID number.
2. We self-join the CTE to pair the first record (rn=1) with the second record (rn=2) in the same Tep group.
3. The total column sums the values from both records, using COALESCE to avoid nulls if a Tep only has one record.

Solution 2: Using Python Pandas #

If you're working with data in a Python environment, Pandas makes it easy to group and transform your data to fit the desired format.

import pandas as pd

# Sample data (replace this with your actual data loading logic, e.g., pd.read_csv())
data = {
    'Tep': ['ABC', 'XYZ', 'ABC', 'WSH'],
    'ID number': [1, 2, 3, 4],
    'Date': ['22-09-2021', '22-10-2021', '22-10-2021', '22-10-2021'],
    'Value': [1.2, 3.2, 3.2, 3.2],
    'type': ['X', 'X', 'Y', 'X']
}

df = pd.DataFrame(data)

# Group by Tep and build the merged row for each group
merged_df = df.groupby('Tep').apply(lambda group: pd.Series({
    'ID number': group['ID number'].iloc[0],
    'Date': group['Date'].iloc[0],
    'Value': group['Value'].iloc[0],
    'type': group['type'].iloc[0],
    # Add fields from the second record if it exists
    'ID number -1': group['ID number'].iloc[1] if len(group) >= 2 else None,
    'Value -1': group['Value'].iloc[1] if len(group) >= 2 else None,
    'type -1': group['type'].iloc[1] if len(group) >= 2 else None,
    'total': group['Value'].sum()
})).reset_index()

# Reorder columns to match your desired output format
merged_df = merged_df[['Tep', 'ID number', 'Date', 'Value', 'type', 'ID number -1', 'Value -1', 'type -1', 'total']]

# Print or export the result
print(merged_df)

How this works: #

1. We group the DataFrame by Tep so we can process each group of records separately.
2. For each group, we extract the first record's fields as the base columns, then add fields from the second record (if it exists) as the "-1" columns.
3. The total column calculates the sum of all Value entries in the group.
4. Finally, we reorder the columns to match your desired output structure.

Note: #

Both solutions above handle cases where a Tep has up to 2 records. If you need to handle more than 2 records per Tep, you'll need to adjust the logic (e.g., dynamic pivoting in SQL, or expanding the apply function in Pandas to include additional "-N" columns).

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