Python 2.7下用Pandas实现双值列CSV数据透视（指定变量列表）

Got it, let's tackle this dual-value column pivoting problem with Pandas in Python 2.7. Your original code handles single-value pivoting nicely, so we'll extend that logic to cover both var_value1 and var_value2 while meeting all your requirements—including preserving variable order, filling missing values, and ignoring unwanted variables.

Step-by-Step Modified Code #

import pandas as pd

# Replace this with your actual CSV file path
csvfile = "your_data.csv"
# Define your target variable list (can be in any order you need)
variable_list = ['ABC','ABC_2','GHI','GHI_2']

# 1. Read CSV and set column headers
df = pd.read_csv(csvfile, header=None)
df.columns = ['ID1','ID2','Var_name','var_value1','var_value2']

# 2. Pivot var_value1 (maps to base variables like ABC, GHI)
pivot_val1 = df.pivot_table(
    index=['ID1', 'ID2'],
    columns='Var_name',
    values='var_value1',
    aggfunc='first'  # Handles duplicate rows (use 'last'/'join' if needed)
).fillna('')

# 3. Pivot var_value2 and rename columns to add _2 suffix (maps to ABC_2, GHI_2)
pivot_val2 = df.pivot_table(
    index=['ID1', 'ID2'],
    columns='Var_name',
    values='var_value2',
    aggfunc='first'
).fillna('')
pivot_val2.columns = [f"{col}_2" for col in pivot_val2.columns]

# 4. Merge both pivoted tables to combine all values, keep all ID pairs
merged_df = pd.merge(
    pivot_val1,
    pivot_val2,
    left_index=True,
    right_index=True,
    how='outer'
).fillna('')

# 5. Filter columns to match your variable list (preserves order, ignores unwanted vars)
final_df = merged_df[variable_list].reset_index()

# 6. Convert to nested list (same structure as your original output)
L1 = [final_df.columns.tolist()] + final_df.values.tolist()

Key Explanations #

• Handling Duplicates: We use aggfunc='first' to pick the first value when there are duplicate ID1+ID2+Var_name entries (like the 3,02,ABC rows in your sample). If you need to combine duplicates (e.g., concatenate values), replace this with lambda x: ', '.join(x) or another aggregation logic.
• Variable Suffix Matching: By renaming pivot_val2 columns with _2, we directly map var_value2 to variables like ABC_2 as required.
• Preserving Order: Using merged_df[variable_list] ensures the final columns follow exactly the order you specify in your variable list—no extra sorting needed.
• Missing Values: Every step uses fillna('') to replace missing entries with empty strings, and how='outer' in the merge ensures all ID1+ID2 pairs from your dataset are retained.
• Ignoring Unwanted Variables: Any variables not in variable_list (like DEF in your sample) are automatically excluded when we filter the merged DataFrame.

Example Output #

For your sample data and variable_list = ['GHI','GHI_2','ABC','ABC_2'], the nested list L1 will start with:

['ID1', 'ID2', 'GHI', 'GHI_2', 'ABC', 'ABC_2']
[1, '01', '', '', 'This is abc101', 'This is another abc101']
[1, '02', 'This is ghi102', 'This is another ghi102', 'This is abc102', 'This is another abc102']
...

内容的提问来源于stack exchange，提问作者Sourajit Roy Chowdhury