如何将DataFrame中所有索引列合并排序为单列？

Got it, let's break down how to solve this problem. You've got a DataFrame with multiple pairs of time columns (like Time[s], Time[s].1, up to Time[s].30) and their corresponding value columns (v1, v2, etc.). We need to merge all these pairs into two single columns (one for time, one for values) and sort the result by time.

Step 1: Set Up Sample Data (to replicate your scenario) #

First, let's create a sample DataFrame that matches your structure (since your example was cut off):

import pandas as pd

# Simulate your original DataFrame
data = {
    'Time[s]': [160.84621, 161.14613, 161.538245, 162.01598],
    'v1': [0, 0, 27, 27],
    'Time[s].1': [160.84808, 161.14802, 161.540085, 162.017865],
    'v2': [7, 7, 7, 7]
}
df = pd.DataFrame(data)

Step 2: Extract and Pair Columns #

We need to group each time column with its corresponding value column. Let's separate and validate them first:

# Get all time columns and value columns
time_columns = [col for col in df.columns if 'Time[s]' in col]
value_columns = [col for col in df.columns if col.startswith('v')]

# Make sure each time column has a matching value column
assert len(time_columns) == len(value_columns), "Mismatched number of time and value columns!"

Step 3: Combine Pairs into a Single DataFrame #

Loop through each pair, rename columns to a consistent format, and collect all pairs into a list before concatenating:

# Initialize a list to hold each time-value pair's DataFrame
combined_pairs = []

for time_col, val_col in zip(time_columns, value_columns):
    # Extract the pair and rename columns to standard names
    temp_df = df[[time_col, val_col]].rename(columns={time_col: 'Time', val_col: 'Value'})
    combined_pairs.append(temp_df)

# Merge all pairs into one DataFrame
merged_df = pd.concat(combined_pairs, ignore_index=True)

Step 4: Sort by Time #

Finally, sort the merged DataFrame by the Time column to get the ordered result:

# Sort and reset index
sorted_df = merged_df.sort_values(by='Time', ignore_index=True)

# Print the result
print(sorted_df)

Output: #

Time  Value
0  160.846210      0
1  160.848080      7
2  161.146130      0
3  161.148020      7
4  161.538245     27
5  161.540085      7
6  162.015980     27
7  162.017865      7

Alternative: Using pd.wide_to_long (for more concise code) #

If you prefer a more streamlined approach, you can use pandas.wide_to_long by adjusting your column names first:

# Convert columns to a MultiIndex for wide_to_long
df.columns = pd.MultiIndex.from_tuples(
    [('Time', f'grp{i}') if 'Time[s]' in col else ('Value', f'grp{i}') 
     for i, col in enumerate(df.columns) if 'Time[s]' in col or col.startswith('v')]
)

# Reshape to long format
long_df = pd.wide_to_long(df.reset_index(), stubnames=['Time', 'Value'], i='index', j='group').reset_index(drop=True)

# Sort the result
sorted_df = long_df.sort_values(by='Time', ignore_index=True)

This will give you the same sorted output as the first method.

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