求助：将表格特定列从横向转纵向并忽略零值实现方案

Hey there! Let’s wrap up that table transformation problem you were working on with DisplayName earlier—here’s a full, step-by-step solution tailored to your needs: converting specific columns from horizontal to vertical, inserting those rows right below the original ones, and automatically ignoring any columns with a value of 0 (even if the zero columns vary per row).

This method is built right into Excel and handles the zero-filtering and row insertion seamlessly:

• Step 1: Import your table into Power Query
  Go to the Data tab → click From Table/Range (make sure your table has headers).
• Step 2: Unpivot the target columns
  Keep the columns you want to stay fixed (like IDs, names, etc.) selected. Then go to the Transform tab → Unpivot Columns → Unpivot Other Columns. This will turn all your target horizontal columns into two new columns: Attribute (original column name) and Value.
• Step 3: Filter out zero values
  Click the filter arrow on the Value column → uncheck the box next to 0 → click OK. This removes all rows where the value was zero.
• Step 4: Load the result back to Excel
  Click Close & Load To → choose to load the data either below your original table or to a new sheet (you can then copy-paste it into place if needed). The output will have each original row followed by its non-zero vertical entries.

If you need to automate this process or work with bigger tables, Pandas is perfect. Here’s a complete script that matches your requirements:

First, let’s use a sample table to demonstrate (replace this with your actual data import):

import pandas as pd

# Sample data (replace with pd.read_excel("your_file.xlsx") or pd.read_csv("your_file.csv"))
df = pd.DataFrame({
    "EmployeeID": [101, 102, 103],
    "Name": ["Alice", "Bob", "Charlie"],
    "Q1_Sales": [4500, 0, 6200],
    "Q2_Sales": [0, 5800, 0],
    "Q3_Sales": [7100, 6500, 4900]
})

Now run the transformation:

# Define which columns stay fixed (don't get transposed)
fixed_columns = ["EmployeeID", "Name"]
# Define which columns to transpose (all columns not in fixed_columns)
transpose_columns = [col for col in df.columns if col not in fixed_columns]

# Build the final result: original row + its non-zero transposed rows
final_rows = []
for _, original_row in df.iterrows():
    # Add the original horizontal row first
    final_rows.append(original_row.to_dict())
    # Find all non-zero columns in this row
    non_zero_cols = [col for col in transpose_columns if original_row[col] != 0]
    # Add a vertical row for each non-zero column
    for col in non_zero_cols:
        transposed_row = {
            **{c: original_row[c] for c in fixed_columns},
            "Period": col,  # Rename this to match your column needs
            "Sales": original_row[col]  # Rename this to match your value needs
        }
        final_rows.append(transposed_row)

# Convert to DataFrame and view the result
final_df = pd.DataFrame(final_rows)
print(final_df)

What this does: #

• Iterates through each original row, adds it to the result first
• For each row, only keeps columns with non-zero values
• Creates a vertical row for each non-zero entry, keeping the fixed column values (like EmployeeID/Name) intact
• Places all transposed rows directly below their original parent row

Adjust the column names (fixed_columns, Period, Sales) to match your actual table structure, and you’re good to go!

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