大表转置技术问询：含start/end列的稀疏数据表转换需求

Got it, let's work through this table transformation problem together. From your description, you’ve got a sprawling, messy table with tons of columns—split between 'start' point columns and 'end' point columns—plus date data. Each row only has two 1s (one in a start column, one in an end column) and 0s everywhere else. The goal is to condense this into a clean, usable format, right?

Step 1: Clarify the Input Structure #

First, let's ground this with a tiny example of what your messy table might look like (scaled down for clarity):

Our end goal is to turn this into a compact table that links each date to its corresponding start and end points directly.

Step 2: Python + Pandas (Most Efficient for Large Tables) #

If you’re comfortable with Python, pandas is perfect for this—especially since your table is large. Here’s a step-by-step solution:

import pandas as pd

# Load your messy table (replace with your actual file path/data source)
df = pd.read_csv("your_messy_table.csv")

# Initialize a clean DataFrame to store results
cleaned_data = pd.DataFrame()
cleaned_data["Date"] = df["Date"]  # Keep the date column as-is

# Extract the start point for each row
# Filter columns with "Start" in the name, find which one has the 1, then strip the "Start_" prefix
cleaned_data["Start_Point"] = (
    df.filter(like="Start")
    .idxmax(axis=1)  # Gets the column name where the value is 1
    .str.replace("Start_", "")  # Remove the prefix to get just the point name
)

# Extract the end point using the same logic
cleaned_data["End_Point"] = (
    df.filter(like="End")
    .idxmax(axis=1)
    .str.replace("End_", "")
)

# Check the result
print(cleaned_data)

Running this will give you a clean table like this:

Step 3: Adjust for Edge Cases #

• If your start/end column prefixes aren’t exactly Start_ or End_ (e.g., StartPoint_A), use a regex to extract the point name instead:

  cleaned_data["Start_Point"] = df.filter(like="Start").idxmax(axis=1).str.extract(r'(Start.*)_(.*)')[1]
  

• If there’s a chance some rows are missing a 1 (even though you said each row has two), add a check to flag those:

  # Flag rows with invalid start counts
  cleaned_data["Invalid_Start"] = df.filter(like="Start").sum(axis=1) != 1
  # Flag rows with invalid end counts
  cleaned_data["Invalid_End"] = df.filter(like="End").sum(axis=1) != 1
  

Step 4: No-Code Option (Excel/Google Sheets) #

If you prefer not to use code, you can use spreadsheet formulas:

1. Extract Start Point: For a row where start columns are in B2:C2, use:

   =SUBSTITUTE(INDEX($B$1:$C$1, MATCH(1, B2:C2, 0)), "Start_", "")
   

2. Extract End Point: For end columns in D2:E2, use:

   =SUBSTITUTE(INDEX($D$1:$E$1, MATCH(1, D2:E2, 0)), "End_", "")
   

Drag these formulas down all rows to get your clean table.

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