如何用Python Pandas将行中值按名称拆分至独立列？

Let's break down why you're hitting that ValueError and how to fix it to get your desired output.

Why the Error Happens #

Your original code fails for two key reasons:

1. Duplicate column names: Your CSV has two date columns. Pandas automatically renames the second one to date.1 behind the scenes, which creates confusion when you specify index="date".
2. Non-unique index + column combinations: When using pivot without specifying values, Pandas tries to reshape all columns. For your date index, there are conflicting values in columns like datetime (e.g., 2017-04-30 18:30:00 and 2018-02-07 18:30:00 for the same date=20170430), which makes it impossible to collapse into a single row.

Step-by-Step Solution #

1. Read Data with Clean Column Names #

First, fix the duplicate date columns by renaming them explicitly to avoid ambiguity:

import pandas as pd

# Read CSV and rename duplicate date columns
df = pd.read_csv(
    "D:/datagenicAPI/finalCSV.csv",
    header=0,
    names=["group_date", "datetime", "year", "month", "day", "value", "name"]
)

2. Use pivot_table Instead of pivot #

pivot_table is more flexible than pivot and handles duplicate entries gracefully using aggregation functions. We'll use it to reshape your data:

# Create the pivot table
pivoted_df = df.pivot_table(
    # Columns to keep as rows (adjust based on which you want to retain)
    index=["group_date", "datetime", "year", "month", "day"],
    # Column to expand into new columns
    columns="name",
    # Values to fill into the new columns
    values="value",
    # Aggregation: take the first value for each unique group (works for your sample data)
    aggfunc="first"
).reset_index()

# Flatten the multi-level column names
pivoted_df.columns = ["group_date", "datetime", "year", "month", "day", "A1", "B1", "C1"]

# Check the result
print(pivoted_df)

3. Save the Output #

pivoted_df.to_csv("d:/datagenicAPI/test1.csv", sep=",", index=False)

Alternative: Deduplicate First #

If you're certain each group_date + name combination is unique (only other columns have conflicting values), you can deduplicate first then use pivot:

# Remove duplicate group_date + name entries
df_unique = df.drop_duplicates(subset=["group_date", "name"])

# Pivot the cleaned data
pivoted_df = df_unique.pivot(
    index=["group_date", "datetime", "year", "month", "day"],
    columns="name",
    values="value"
).reset_index()

# Clean up column names
pivoted_df.columns = ["group_date", "datetime", "year", "month", "day", "A1", "B1", "C1"]

Note on Data Inconsistencies #

In your sample data, the B1 entry for group_date=20170430 has a datetime of 2018-02-07 (mismatched with the group_date). This will result in two rows for 20170430 in the output. If this is a data error, fix it first; if it's intentional, you may need to adjust your grouping logic (e.g., use datetime as the index instead).

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