基于列值重排DataFrame的需求及数据示例

It sounds like you want to reshape or reorder your DataFrame based on specific column values—like grouping by hour, sorting by magnitude, or pivoting to a more readable wide format. Below are practical, reusable approaches using pandas, plus a custom method tailored to your dataset.

First: Define the Sample DataFrame #

Let’s start by properly structuring your sample data in pandas to work with:

import pandas as pd

data = [
    ["01 AM", 0, 5, 18, 0.277777778],
    ["01 AM", 1, 9, 18, 0.500000000],
    ["01 AM", 2, 2, 18, 0.111111111],
    ["01 AM", 3, 2, 18, 0.111111111],
    ["01 PM", 0, 76, 150, 0.506666667],
    ["01 PM", 1, 45, 150, 0.300000000],
    ["01 PM", 2, 21, 150, 0.140000000],
    ["01 PM", 3, 5, 150, 0.033333333],
    ["01 PM", 4, 3, 150, 0.020000000],
    ["02 AM", 0, 4, 22, 0.181818182],
    ["02 AM", 1, 6, 22, 0.272727273],
    ["02 AM", 2, 11, 22, 0.500000000]
]

df = pd.DataFrame(
    data,
    columns=["hour", "magnitude", "tornadoCount", "hourlyTornadoCount", "Percentage Tornadoes"]
)

1. Sort Rows by Column Values #

If you want to reorder rows to group like hours together and sort by tornado magnitude, use sort_values:

# Sort by hour (ascending), then magnitude (ascending)
sorted_df = df.sort_values(by=["hour", "magnitude"], ascending=[True, True])
print(sorted_df)

This organizes your data so all entries for the same hour are clustered, ordered from lowest to highest tornado magnitude.

2. Pivot to a Wide Format (Hour vs Magnitude) #

A common rearrangement is pivoting to create a table where rows are hours, columns are magnitudes, and cells show tornado counts:

pivoted_df = df.pivot(index="hour", columns="magnitude", values="tornadoCount")
# Fill missing magnitude values with 0 for clarity
pivoted_df = pivoted_df.fillna(0).astype(int)
print(pivoted_df)

This gives you a clean, summary view of how tornado counts vary by magnitude for each hour.

3. Custom Reusable Rearrangement Function #

To build a flexible method that adapts to your needs, here’s a custom function that combines sorting, grouping, and pivoting:

def rearrange_tornado_df(df, sort_by_cols=None, group_by_col=None, pivot=False, pivot_params=None):
    """
    Custom function to rearrange the tornado DataFrame based on column values.
    
    Parameters:
        df (pd.DataFrame): Input DataFrame
        sort_by_cols (list): Columns to sort rows by (optional)
        group_by_col (str): Column to group rows by (optional, includes aggregation)
        pivot (bool): Whether to pivot to wide format (optional)
        pivot_params (dict): Required if pivot=True; keys: index, columns, values
    
    Returns:
        pd.DataFrame: Rearranged DataFrame
    """
    # Sort rows if specified
    if sort_by_cols:
        df = df.sort_values(by=sort_by_cols)
    
    # Group and aggregate if specified
    if group_by_col:
        df = df.groupby(group_by_col).agg({
            "tornadoCount": "sum",
            "hourlyTornadoCount": "first",
            "Percentage Tornadoes": "mean"
        }).reset_index()
    
    # Pivot to wide format if specified
    if pivot:
        if not pivot_params or not all(key in pivot_params for key in ["index", "columns", "values"]):
            raise ValueError("pivot_params must include 'index', 'columns', and 'values'")
        df = df.pivot(**pivot_params).fillna(0).astype(int)
    
    return df

# Example: Sort by hour/magnitude, then pivot to hour vs magnitude view
rearranged_df = rearrange_tornado_df(
    df,
    sort_by_cols=["hour", "magnitude"],
    pivot=True,
    pivot_params={
        "index": "hour",
        "columns": "magnitude",
        "values": "tornadoCount"
    }
)

print(rearranged_df)

You can tweak this function to match your exact needs—for example, adjust aggregation methods in the groupby step, add filters, or change pivot values to Percentage Tornadoes instead of counts.

内容的提问来源于stack exchange，提问作者I.J. Abdul Hakeem