动态转置列与行技术求助：参考同类资料仍无法解决

算力阿峰

2026-5-26

Hey there! Let's work through this dynamic row-column transposition problem you're stuck on. I'll cover solutions for two common tools—SQL (using MySQL as an example) and Python Pandas—since you didn't specify your tech stack.

First, let's clarify the target transposition format I'm assuming: turning your wide-format data (with metrics as columns) into a long-then-wide format where metrics are rows, and dates are columns. If you had a different target in mind, just let me know and I can adjust!

Your source data for reference:

+------------+--------------+--------+--------+------------+
| date       | business_day | total  | number | number_2   |
+------------+--------------+--------+--------+------------+
| 2018-04-03 | 1            | 325857 | 0      | 888        |
| 2018-04-04 | 2            | 196308 | 2      | 829        |
| 2018-04-05 | 3            | 434970 | 1      | 661        |
| 2018-04-06 | 4            | 665941 | 1      | 1135       |
| 2018-04-09 | 5            | 589334 | 0      | 837        |
+------------+--------------+--------+--------+------------+

1. SQL Dynamic Transposition (MySQL)

If you're working with SQL, dynamic transposition requires building a query dynamically because the number of date columns might grow over time. Here's a step-by-step approach:

-- Step 1: Unpivot metric columns into rows
WITH unpivoted AS (
    SELECT date, 'total' AS metric, total AS value FROM your_table
    UNION ALL
    SELECT date, 'number' AS metric, number AS value FROM your_table
    UNION ALL
    SELECT date, 'number_2' AS metric, number_2 AS value FROM your_table
),
-- Step 2: Generate dynamic column list from distinct dates
date_list AS (
    SELECT GROUP_CONCAT(DISTINCT CONCAT('MAX(CASE WHEN date = ''', date, ''' THEN value END) AS `', date, '`')) AS pivot_cols
    FROM unpivoted
)
-- Step 3: Build and run the pivot query
SELECT CONCAT(
    'SELECT metric, ', pivot_cols, ' FROM unpivoted GROUP BY metric'
) INTO @pivot_query FROM date_list;

PREPARE stmt FROM @pivot_query;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

This will output:

+------------+------------+------------+------------+------------+------------+
| metric     | 2018-04-03 | 2018-04-04 | 2018-04-05 | 2018-04-06 | 2018-04-09 |
+------------+------------+------------+------------+------------+------------+
| total      | 325857     | 196308     | 434970     | 665941     | 589334     |
| number     | 0          | 2          | 1          | 1          | 0          |
| number_2   | 888        | 829        | 661        | 1135       | 837        |
+------------+------------+------------+------------+------------+------------+

2. Python Pandas Solution

Pandas makes transposition super straightforward with melt() (to unpivot) and pivot_table() (to pivot back to wide format):

import pandas as pd

# Load your data into a DataFrame
data = {
    'date': ['2018-04-03', '2018-04-04', '2018-04-05', '2018-04-06', '2018-04-09'],
    'business_day': [1, 2, 3, 4, 5],
    'total': [325857, 196308, 434970, 665941, 589334],
    'number': [0, 2, 1, 1, 0],
    'number_2': [888, 829, 661, 1135, 837]
}
df = pd.DataFrame(data)

# Unpivot metric columns (keep date as the identifier)
unpivoted_df = df.melt(id_vars=['date'], value_vars=['total', 'number', 'number_2'], var_name='metric', value_name='value')

# Pivot to turn dates into columns
transposed_df = unpivoted_df.pivot_table(index='metric', columns='date', values='value', aggfunc='first').reset_index()

print(transposed_df)

The output will look like this:

date     metric  2018-04-03  2018-04-04  2018-04-05  2018-04-06  2018-04-09
0        number           0           2           1           1           0
1     number_2         888         829         661        1135         837
2        total      325857      196308      434970      665941      589334

If you wanted to transpose in the opposite direction (e.g., business_day as columns instead of dates), just tweak the id_vars and columns parameters in the code!

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