Python将Excel解析为指定格式字典的实现方案（可修改Excel）

Let's break down how to get the exact dictionary structure you want, either by tweaking your Excel file or adjusting your Python code.

Why Your Current Output Isn't Matching #

Right now, your Excel's first column (with a/b/c/d) doesn't have a header, so pandas reads it as an unnamed column (Unnamed: 0). When you call to_dict(), it uses the default column-based structure, which gives you numeric indices instead of the a/b/c/d labels as inner keys.

Option 1: Modify Your Excel File (Simple Fix) #

The easiest way is to add a header to your first column. For example, name it row_id so your Excel looks like this:

Then update your Python code to use this column as the row index:

import pandas as pd

file_path = 'b.xlsx'
# Use the new header to set row index
df = pd.read_excel(file_path, index_col='row_id')
# Generate the dictionary - this will match your desired format!
target_dict = df.to_dict()

Running this will give you exactly the structure you want:

{'z1': {'a': '[2,7,14]', 'b': 4, 'c': 5, 'd': 10},
 'z2': {'a': '[2,7,14]', 'b': 8, 'c': 7, 'd': 10},
 'z3': {'a': '[3,4]', 'b': 7, 'c':5, 'd': 10}}

Option 2: Adjust Code Without Changing Excel #

If you don't want to edit your Excel file, you can reconfigure the DataFrame in code instead:

import pandas as pd

file_path = 'b.xlsx'
df = pd.read_excel(file_path)
# Set the first unnamed column as the row index
df = df.set_index(df.columns[0])
# Generate the target dictionary
target_dict = df.to_dict()

Bonus: Convert String Lists to Actual Python Lists #

If you want those [2,7,14] string values to be real Python lists instead of strings, add this step using ast.literal_eval:

import ast

for col in df.columns:
    # Convert the string list for row 'a' to an actual list
    df.loc['a', col] = ast.literal_eval(df.loc['a', col])

# Now the dictionary will have real lists instead of strings
target_dict = df.to_dict()

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