使用Excel或Python-Pandas实现动态数据透视/分组转换需求

Great question! Manually reshaping this kind of paired-column data is super tedious and error-prone—automating it is absolutely the right call. Here are two robust solutions to get your desired output:

Power Query (built into Excel 2016+) is perfect for this kind of data transformation without writing any code. Here's how to do it step-by-step:

1. Load your data into Power Query:

   • Select your entire data range (including headers).
   • Go to the Data tab → Click From Table/Range (this opens the Power Query Editor).

2. Clean up unnecessary columns:

   • Right-click the s.No column header → Select Remove (we don't need this for the final output).

3. Unpivot paired Source/Price columns:

   • Hold Ctrl to select all columns except Item Name.
   • Go to the Transform tab → Click Unpivot Columns → Unpivot Only Selected Columns. This creates two new columns: Attribute (e.g., "Source1", "Price1") and Value (the website name or price).

4. Add a helper column to pair Source/Price entries:

   • Go to the Add Column tab → Click Custom Column.
   • Enter this formula to extract the number suffix from the Attribute column:

     Text.Select([Attribute], {"0".."9"})
     

   • Name this new column PairID and click OK.

5. Pivot to combine Source/Price pairs:

   • Select the Attribute column.
   • Go to the Transform tab → Click Pivot Column.
   • Set Values Column to Value, check Don't aggregate under Advanced options, then click OK. You'll now have Source and Price columns paired by PairID.

6. Pivot to get websites as columns:

   • Select the Source column.
   • Go to the Transform tab → Click Pivot Column.
   • Set Values Column to Price, check Don't aggregate, then click OK.

7. Final cleanup:

   • Replace null values with "na": Go to Transform → Replace Values, set Value To Find to null and Replace With to na.
   • Click Close & Load to export the transformed data back to an Excel sheet.

If you prefer using code, Pandas has built-in functions to handle this reshaping in just a few lines:

1. Import Pandas and load your data:

   import pandas as pd
   
   # Load data (use pd.read_excel if your data is in an Excel file)
   df = pd.read_csv("your_data_file.csv")
   

2. Reshape to pair Source/Price columns:
   Use wide_to_long to convert the wide-format paired columns into a long format where each Source is matched with its Price:

   df_long = pd.wide_to_long(
       df.drop("s.No", axis=1),  # Remove the unused s.No column
       stubnames=["Source", "Price"],  # Prefixes of the paired columns
       i="Item Name",  # Column to group rows by
       j="pair",  # Temporary column to track pair numbers
       sep=""  # No separator between prefix and number (e.g., Source1 instead of Source_1)
   ).reset_index()
   

3. Pivot to get websites as columns:
   Convert the long format back to a wide format with websites as columns:

   df_pivot = df_long.pivot(
       index="Item Name",
       columns="Source",
       values="Price"
   ).reset_index()
   

4. Clean up the output:
   Replace missing values with "na" and tidy up column names:

   df_final = df_pivot.fillna("na")
   df_final.columns.name = None  # Remove the "Source" header label
   
   # Print or save the result
   print(df_final)
   df_final.to_csv("transformed_data.csv", index=False)
   

This will give you exactly the output format you're looking for, with zero manual data entry errors.

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