Pandas中基于Val列重塑DataFrame：按值排序转换日期列

To transform your DataFrame into the desired wide format where dates are ordered by descending Val for each ID_1/ID_2 group, follow these straightforward steps:

Step-by-Step Breakdown #

1. Sort the Data: First, sort the DataFrame by ID_1, ID_2, and Val (descending) to ensure the largest values appear first in each group.
2. Assign Group-Wide Ranks: Add a column that numbers each row within its ID_1/ID_2 group starting from 1—this helps map rows to the "Largest", "2nd Largest", etc., columns we need.
3. Pivot to Wide Format: Use pivot to turn the numeric rank values into columns, with Date as the cell content.
4. Rename Columns: Map the numeric rank columns to the user-friendly labels you specified.

Full Code Example #

import pandas as pd

# Create the sample DataFrame
data = {
    'ID_1': [1234, 1234, 1234, 1234, 5567, 5567, 5567, 8799, 8799, 8799],
    'ID_2': [1480, 1480, 1480, 1480, 1481, 1481, 1481, 1482, 1482, 1482],
    'Date': ['6/13/1970', '7/8/1970', '6/4/1970', '4/1/1970', '11/20/1970', '5/25/1970', '4/23/1970', '12/23/1970', '4/23/1970', '9/26/1970'],
    'Val': [10, 9, 8, 7, 25, 12, 9, 8, 7, 6]
}
df = pd.DataFrame(data)

# Step 1: Sort by ID_1, ID_2, and Val (descending)
df_sorted = df.sort_values(['ID_1', 'ID_2', 'Val'], ascending=[True, True, False])

# Step 2: Assign 1-based rank within each group
df_sorted['rank'] = df_sorted.groupby(['ID_1', 'ID_2']).cumcount() + 1

# Step 3: Pivot to wide format
pivoted_df = df_sorted.pivot(index=['ID_1', 'ID_2'], columns='rank', values='Date').reset_index()

# Step 4: Rename columns to desired labels
column_mapping = {
    1: 'Largest Event',
    2: '2nd Largest Event',
    3: '3rd Largest Event',
    4: '4th Largest Event'
}
pivoted_df.rename(columns=column_mapping, inplace=True)

# Display the result
print(pivoted_df)

Output #

ID_1  ID_2 Largest Event 2nd Largest Event 3rd Largest Event 4th Largest Event
0  1234  1480    6/13/1970        7/8/1970        6/4/1970        4/1/1970
1  5567  1481   11/20/1970       5/25/1970       4/23/1970                NaN
2  8799  1482   12/23/1970       4/23/1970       9/26/1970                NaN

Groups with fewer than 4 events will have NaN in the extra columns, which is standard for missing values in Pandas and aligns with your example's ... placeholder.

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