英文:
Pandas dataframe read excel then convert to dictinary
问题
我有这段代码：
df = pd.read_excel('Simulation log.xlsx', sheet_name='Materials').transpose()
df = df.rename(columns=df.iloc[1])
df.to_dict()
{'tim': {'density': {'option': 'constant', 'value': 2300.0},
'specific_heat': {'option': 'constant', 'value': 1000.0},
'thermal_conductivity': {'option': 'constant', 'value': 2.7}}}
问题是我不想要字典值周围的双引号。我必须将它们传递为字典。我不确定如何和何时修复它。是当我读取Excel并创建数据帧时还是当我有字典时。有什么建议吗？
英文:
I have this code
df = pd.read_excel('Simulation log.xlsx', sheet_name='Materials').transpose()
df = df.rename(columns=df.iloc[1])
df.to_dict()
{'tim': {'density': "{'option': 'constant', 'value': 2300.0}",
'specific_heat': "{'option': 'constant', 'value': 1000.0}",
'thermal_conductivity': "{'option': 'constant', 'value': 2.7}"}}
the issue is that I dont want the " " around the dictinary values. I have to pass them along as dictionaries.
I am unsure how and when to to fix it. Is it when I read the excel and create the dataframe or when I have the dictionary. any suggestions?
答案1
得分: 2
如果需要转换一个以字符串表示的字典填充的列 tim：
import ast
df = pd.read_excel('Simulation log.xlsx', sheet_name='Materials').transpose()
df = df.rename(columns=df.iloc[1])
df['tim'] = df['tim'].apply(ast.literal_eval)
print(df.to_dict())
{'tim': {'density': {'option': 'constant', 'value': 2300.0},
'specific_heat': {'option': 'constant', 'value': 1000.0},
'thermal_conductivity': {'option': 'constant', 'value': 2.7}}}
如果所有列都由字符串表示的字典填充，需要转换它们：
df = df.applymap(ast.literal_eval)
print(df.to_dict())
如果只需要转换某些列，可以使用以下方法：
cols = ['tim', 'rob', 'john']
df[cols] = df[cols].applymap(ast.literal_eval)
print(df.to_dict())
英文:
If need convert one column tim filled by string repr of dictionaries:
import ast
df = pd.read_excel('Simulation log.xlsx', sheet_name='Materials').transpose()
df = df.rename(columns=df.iloc[1])
df['tim'] = df['tim'].apply(ast.literal_eval)
print (df.to_dict())
{'tim': {'density': {'option': 'constant', 'value': 2300.0},
'specific_heat': {'option': 'constant', 'value': 1000.0},
'thermal_conductivity': {'option': 'constant', 'value': 2.7}}}
If all columns filled by string repr of dictionaries and need convert them use:
df = df.applymap(ast.literal_eval)
print (df.to_dict())
If need convert only some columns:
cols = ['tim','rob', 'john']
df[cols] = df[cols].applymap(ast.literal_eval)
print (df.to_dict())
答案2
得分: 1
以下是代码的翻译部分：
问题可能是它将数据从Excel文件中以字符串形式读取，你可以尝试这样做（我无法证明它，因为我没有你的具体文件）：
import pandas as pd
import ast
df = pd.read_excel('Simulation log.xlsx', sheet_name='Materials').transpose()
df = df.rename(columns=df.iloc[1])
# 将字符串值转换为字典
for column in df.columns:
df[column] = df[column].apply(lambda x: ast.literal_eval(x))
# 将DataFrame转换为字典
result_dict = df.to_dict()
print(result_dict)
希望这对你有所帮助。
英文:
The issue might be that it reads the data as string from the excel file, you might give this a try (I cannot prove it as I don't have your specific file):
import pandas as pd
import ast
df = pd.read_excel('Simulation log.xlsx', sheet_name='Materials').transpose()
df = df.rename(columns=df.iloc[1])
# convert string values to dictionaries
for column in df.columns:
df[column] = df[column].apply(lambda x: ast.literal_eval(x))
# convert df to dict
result_dict = df.to_dict()
print(result_dict)
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论