如何用Pandas将多索引表头Excel转为Pyomo所需4维字典？

I’ve run into this exact scenario before when working with Pyomo parameters—turning multi-dimensional Excel data into a dictionary with tuple keys can feel tricky at first, but pandas has all the tools to make it straightforward. Here’s how to do it step by step:

Step 1: Verify Your DataFrame Structure #

First, confirm your DataFrame’s index and column levels are correctly named and ordered. Run these checks:

print("Row index names:", df.index.names)  # Should output ['能源类型', '站点']
print("Column index names:", df.columns.names)  # Should output ['时间', '时段']

If the names are wrong or levels are reversed, fix them first:

# Rename index levels to match your desired key order
df.index.names = ['能源类型', '站点']
df.columns.names = ['时间', '时段']

# Reorder row levels if they’re in the wrong sequence (e.g., ['站点', '能源类型'])
df = df.reorder_levels(['能源类型', '站点'], axis=0)

Step 2: Stack Column Levels into the Row Index #

We need to move both column levels (时间 and 时段) into the row index to create a 4-level MultiIndex that aligns with your key tuple. Do this by stacking twice:

# First stack moves the innermost column level ('时段') to the row index
stacked_once = df.stack()
# Second stack moves the remaining column level ('时间') to the row index
stacked_twice = stacked_once.stack()

At this point, your Series index will be (能源类型, 站点, 时段, 时间)—almost perfect, just the last two levels are reversed.

Step 3: Swap Index Levels for Correct Tuple Order #

Swap the 时段 and 时间 levels to get your desired key order (能源类型, 站点, 时间, 时段):

# Use level names instead of indices for robustness (avoids issues if level order changes)
final_series = stacked_twice.swaplevel('时段', '时间')

# Optional: Sort the index for readability (not required for Pyomo)
final_series = final_series.sort_index()

Step 4: Convert to Dictionary #

Finally, convert the Series to a dictionary. Add dropna=True to exclude any missing values from the result:

# Generate the dictionary in your desired format
p = final_series.dropna().to_dict()

Example Output #

Your resulting dictionary p will look exactly like what you need:

{
    ('Heat', 'Site 1', 1, 1): 14,
    ('Heat', 'Site 1', 1, 2): 15,
    ('Heat', 'Site 2', 1, 1): 16,
    # ... all other key-value pairs
}

Quick One-Liner (For Conciseness) #

If you prefer to combine all steps into a single readable line:

p = df.stack().stack().swaplevel('时段', '时间').sort_index().dropna().to_dict()

This method works reliably for multi-dimensional data and integrates seamlessly with Pyomo—you can directly pass this dictionary to a Pyomo Param or IndexedParam without extra formatting.

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