Python Pandas宽表转特殊长表（类反向哑变量）求助

Hey there! Let's work through this problem together—since you're only 10 days into coding, it's totally normal to get stuck with data reshaping tasks like this. No stress, we'll break it down step by step 😊

Your Original Data Table #

First, let's clarify the input data you provided (formatted properly for clarity):

Goal #

Calculate Sum_values, which is the total of all values in the CS group (columns starting with CS_) and the SC group (columns starting with SC_), either per product or as an overall total.

Solution Using Pandas #

Since you tried stack, transpose, and groupby, let's use a more straightforward approach with data cleaning and row-wise summation:

import pandas as pd

# Step 1: Create your data as a DataFrame
data = {
    "Product": ["R123", "R234"],
    "Price": [1.18, 0.23],
    "CS_Medium": [0.15, None],
    "CS_Small": [None, "(0.03)"],
    "SC_A": [None, 0.04],
    "SC_B": [None, None],
    "SC_C": [0.38, 0.05]
}
df = pd.DataFrame(data)

# Step 2: Clean the data (handle empty values and negative numbers in parentheses)
def clean_cell(value):
    if pd.isna(value):
        return 0.0  # Replace empty cells with 0
    if isinstance(value, str) and value.startswith("(") and value.endswith(")"):
        return -float(value.strip("()"))  # Convert (0.03) to -0.03
    return float(value)

# Apply cleaning to CS and SC columns only
cs_columns = [col for col in df.columns if col.startswith("CS_")]
sc_columns = [col for col in df.columns if col.startswith("SC_")]

df[cs_columns] = df[cs_columns].applymap(clean_cell)
df[sc_columns] = df[sc_columns].applymap(clean_cell)

# Step 3: Calculate Sum_values per product
df["CS_Sum_values"] = df[cs_columns].sum(axis=1)  # Sum CS columns for each row
df["SC_Sum_values"] = df[sc_columns].sum(axis=1)  # Sum SC columns for each row

# Optional: Calculate overall total for CS and SC groups
total_cs = df[cs_columns].sum().sum()
total_sc = df[sc_columns].sum().sum()

# Print the results
print("Per-Product Sum Values:")
print(df[["Product", "Price", "CS_Sum_values", "SC_Sum_values"]])
print("\nOverall Total Sum Values:")
print(f"Total CS: {total_cs:.2f}")
print(f"Total SC: {total_sc:.2f}")

What This Does: #

• Data Cleaning: Converts empty cells to 0 and turns values like (0.03) into proper negative numbers so we can sum correctly.
• Row-wise Summation: Uses sum(axis=1) to add up all CS columns and all SC columns for each product.
• Optional Totals: Adds up all values across the entire CS or SC group if you need an overall sum.

Sample Output: #

Per-Product Sum Values:
  Product  Price  CS_Sum_values  SC_Sum_values
0    R123   1.18           0.15           0.38
1    R234   0.23          -0.03           0.09

Overall Total Sum Values:
Total CS: 0.12
Total SC: 0.47

Don't worry if this feels a bit tricky at first—pandas data manipulation takes practice, and you're already doing great by experimenting with different methods! If you had a slightly different Sum_values definition in mind (like cross-combining CS and SC categories), just let me know and we can adjust the code.

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