如何将具有多个值的嵌套字典导出到Excel中 [英] How to export a nested dictionary with multiple values into excel
本文介绍了如何将具有多个值的嵌套字典导出到Excel中的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我为子词典创建了一个嵌套的字典，它以列表形式包含多个值.简而言之，它看起来像这样:
I have created a nested dictionary with multiple values in a list form for the sub-dictionary. In a nutshell it looks like this:
{1:
{'Parameter 1': ['Value 1', 'Value 2', 'Value 3'],
'Parameter 2': ['Value 11', 'Value 22', 'Value 33'],
'Parameter 3': ['Num1', 'Num2', 'Num3']},
2:
{'Parameter 1': ['Data 1', 'Data 2', 'Data 3'],
'Parameter 2': ['Data 11', 'Data 22', 'Data 33'],
'Parameter 3': ['Numb1', 'Numb2', 'Numb3'],
'Parameter 4': ['Numb11', 'Numb22', 'Numb33']}
}
我需要将其导出到Excel工作表.我想得到什么:
I need to export it to the Excel sheet. What I want to get:
| 1 | 2 |
---------------------------------------------------------------------
Parameter 1 | Value 1 | Value 2 | Value 3 | Data 1 | Data 2 | Data 3 |
----------------------------------------------------------------------
Parameter 2 | Value 11| Value 22| Value 33| Data 1 | Data 2 | Data 3 |
----------------------------------------------------------------------
Parameter 3 | Num1 | Num2 | Num3 | Numb1 | Numb2 | Numb3 |
----------------------------------------------------------------------
Parameter 4 | | | | Numb11 | Numb22 | Numb33 |
----------------------------------------------------------------------
但是当我使用to_excel方法时，我得到以下信息:
But when I use to_excel method, i get the following:
| 1 | 2 |
--------------------------------------------------------------------------------------
Parameter 1 |['Value 1', 'Value 2', 'Value 3'] | ['Data 1', 'Data 2', 'Data 3'] |
--------------------------------------------------------------------------------------
Parameter 2 |['Value 11', 'Value 22', 'Value 33']| ['Data 11', 'Data 22', 'Data 33'] |
--------------------------------------------------------------------------------------
Parameter 3 | ['Num1', 'Num2', 'Num3'] | ['Numb1', 'Numb2', 'Numb3'] |
--------------------------------------------------------------------------------------
Parameter 4 | | ['Numb11', 'Numb22', 'Numb33'] |
--------------------------------------------------------------------------------------
这很明显.
将此类字典导出到.csv是没有用的，因为带有参数4的行中的列将向左移动.因此，我正在研究如何使值分别填充单元格，或者如何在工作表中具有多个列的情况下将文本拆分为列.我应该以某种方式重新排列源词典吗?
There is no use to export such dictionary to .csv, as the columns in the row with Parameter 4 will shift to the left. So I'm looking either how to make the values fill the cells separately, or how to split text to columns in case with multiple columns in the sheet. Should I rearrange the source dictionary somehow?
我还猜想，在我的情况下，不可能填充字典中的缺失"行，因为每次我们获得新的子字典时都会更新键.
I also guess, that it is not possible in my case to fill the "missing" rows in the dictionary, as the keys are updated each time we get new sub dictionary.
这是一个实际的例子:
{1: {'Field Cluster': ['This', 'This', 'This'], 'Exploration Block': ['Is', 'Is', 'Is'], 'Producing since': [1923.0, 1923.0, 1923.0], 'Fluids': ['A ', 'A ', 'A '], 'Reservoirs': ['Test', 'Test', 'Test'], 'Area (km2)': ['File', 'File', 'File'], 'Depth (m)': ['A\nHuge\nDepth', 'A\nHuge\nDepth', 'A\nHuge\nDepth'], 'Concession License No.': ['UNIX license', 'UNIX license', 'UNIX license'], 'License Expiry Date / Extension': ['Everlasting', 'Everlasting', 'Everlasting'], 'Working Interest': ['There is one\n', 'There is one\n', 'There is one\n'], 'Gouvernment approval:': ['It is!', 'It is!', 'It is!'], 'Last study:': ['Million years ago', 'Million years ago', 'Million years ago'], 'Parameters': ['Horizon1', 'Horizon2', 'Horizon3'], 'Reservoir rock': ['First', 'Second', 'Third'], 'Net pay thickness (m)': [1.0, 21.0, 41.0], 'Avr. porosity (%)': [2.0, 22.0, 42.0], 'Average absolute permeability (mD)': [3.0, 23.0, 43.0], 'Swi (%)': [4.0, 24.0, 44.0], 'Initial pressure (at)': [5.0, 25.0, 45.0], 'Bubble Pressure (at.)': [6.0, 26.0, 46.0], 'Dew Point Pressure (at)': [7.0, 27.0, 47.0], 'Initial Solution Ratio (Stm3/m3)': [8.0, 28.0, 48.0], 'Initial Condensate Gas Ratio (g/Stm3)': [9.0, 29.0, 49.0], 'Oil density (kg/cm)': [10.0, 30.0, 50.0], 'Oil viscosity (Pb) (cP)': [11.0, 31.0, 51.0], 'Contaminants (H2S, CO2)': [12.0, 32.0, 52.0], 'Initial Oil in Place (e3 to)': [13.0, 33.0, 53.0], 'Initial NGL in Place (e3 to)': [14.0, 34.0, 54.0], 'Initial Gas (assoc.) in Place (e6 m3) sol.gas/gas cap': [15.0, 35.0, 55.0], 'Initial Gas (non assoc.) in Place (e6 m3)': [16.0, 36.0, 56.0], 'Primary recovery / drive mechanism\nNone': ['Wow\nA', 'Recovery\nNone', 'Mechanism\nNone', ''], 'Secondary recovery': ['Another one', '', '', ''], 'Total Wells': ['1000', '-', '-', ''], 'Productive wells (oil/gas)': ['500', '-', '-', ''], 'Injection wells (water/gas)': ['500', '-', '-', ''], 'Rate of best producer in the field (tons / e3 Sm3/day)': ['30', '-', '-', ''], 'Water injection (e3 m3/day)': ['49', '-', '-', ''], 'Actual Pressure (at)': ['500', '434-3443', '48930', ''], 'Oil cumulative production (e3 tons)': ['37', '-', '-', ''], 'Associated gas cum. production (e6 sm3)': ['4535', '-', '-', ''], 'Non-associated gas cum. production (e6 sm3)': ['-', '-', '-', ''], 'NGL cumulative production (e3 tons)': ['', '-', '-', ''], 'Water Cut (%)': ['378', '-', '-', ''], 'Recovery Factor': ['I love hydrocarbons', '', '', ''], 'Current (%)': ['30 20 46 3', '', '', ''], 'Expected 2P (%)': ['Not really expected', '', '', ''], 'Oil production (tons/day)': ['44', '-', '-', ''], 'Associated gas production (e3 sm3/day)': ['-', '-', '-', ''], 'Non-associated gas production (e3 sm3/day)': ['-', '-', '-', ''], 'NGL production (tons/day)': ['-', '-', '-', '']},
2: {'Field Cluster': ['This', 'This', 'This'], 'Exploration Block': ['Is', 'Is', 'Is'], 'Producing since': [1923.0, 1923.0, 1923.0], 'Fluids': ['A ', 'A ', 'A '], 'Reservoirs': ['Test', 'Test', 'Test'], 'Area (km2)': ['File', 'File', 'File'], 'Depth (m)': ['A\nHuge\nDepth', 'A\nHuge\nDepth', 'A\nHuge\nDepth'], 'Concession License No.': ['UNIX license', 'UNIX license', 'UNIX license'], 'License Expiry Date / Extension': ['Everlasting', 'Everlasting', 'Everlasting'], 'Working Interest': ['There is one\n', 'There is one\n', 'There is one\n'], 'Gouvernment approval:': ['It is!', 'It is!', 'It is!'], 'Last study:': ['Million years ago', 'Million years ago', 'Million years ago'], 'Parameters': ['Horizon1', 'Horizon2', 'Horizon3'], 'Reservoir rock': ['First', 'Second', 'Third'], 'Net pay thickness (m)': [1.0, 21.0, 41.0], 'Avr. porosity (%)': [2.0, 22.0, 42.0], 'Average absolute permeability (mD)': [3.0, 23.0, 43.0], 'Swi (%)': [4.0, 24.0, 44.0], 'Initial pressure (at)': [5.0, 25.0, 45.0], 'Bubble Pressure (at.)': [6.0, 26.0, 46.0], 'Dew Point Pressure (at)': [7.0, 27.0, 47.0], 'Initial Solution Ratio (Stm3/m3)': [8.0, 28.0, 48.0], 'Initial Condensate Gas Ratio (g/Stm3)': [9.0, 29.0, 49.0], 'Oil density (kg/cm)': [10.0, 30.0, 50.0], 'Oil viscosity (Pb) (cP)': [11.0, 31.0, 51.0], 'Contaminants (H2S, CO2)': [12.0, 32.0, 52.0], 'Initial Oil in Place (e3 to)': [13.0, 33.0, 53.0], 'Initial NGL in Place (e3 to)': [14.0, 34.0, 54.0], 'Initial Gas (assoc.) in Place (e6 m3) sol.gas/gas cap': [15.0, 35.0, 55.0], 'Initial Gas (non assoc.) in Place (e6 m3)': [16.0, 36.0, 56.0], 'Primary recovery / drive mechanism\nNone': ['Wow\nA', 'Recovery\nNone', 'Mechanism\nNone', ''], 'Secondary recovery': ['Another one', '', '', ''], 'Total Wells': ['1000', '-', '-', ''], 'Productive wells (oil/gas)': ['500', '-', '-', ''], 'Injection wells (water/gas)': ['500', '-', '-', ''], 'Rate of best producer in the field (tons / e3 Sm3/day)': ['30', '-', '-', ''], 'Water injection (e3 m3/day)': ['49', '-', '-', ''], 'Actual Pressure (at)': ['500', '434-3443', '48930', ''], 'Oil cumulative production (e3 tons)': ['37', '-', '-', ''], 'Associated gas cum. production (e6 sm3)': ['4535', '-', '-', ''], 'Non-associated gas cum. production (e6 sm3)': ['-', '-', '-', ''], 'NGL cumulative production (e3 tons)': ['', '-', '-', ''], 'Water Cut (%)': ['378', '-', '-', ''], 'Recovery Factor': ['I love hydrocarbons', '', '', ''], 'Current (%)': ['30 20 46 3', '', '', ''], 'Expected 2P (%)': ['Not really expected', '', '', ''], 'Oil production (tons/day)': ['44', '-', '-', ''], 'Associated gas production (e3 sm3/day)': ['-', '-', '-', ''], 'Non-associated gas production (e3 sm3/day)': ['-', '-', '-', ''], 'NGL production (tons/day)': ['-', '-', '-', ''], 'WOW Production (Something)': ['1', 2.0, '3', '']}}
示例，它应该确实起作用
Example, which should really work
{1: {'Field Cluster': ['This', 'This', 'This'], 'Exploration Block': ['Is', 'Is', 'Is'], 'Producing since': [1923.0, 1923.0, 1923.0], 'Fluids': ['A ', 'A ', 'A '], 'Reservoirs': ['Test', 'Test', 'Test'], 'Area (km2)': ['File', 'File', 'File'], 'Depth (m)': ['A\nHuge\nDepth', 'A\nHuge\nDepth', 'A\nHuge\nDepth'], 'Concession License No.': ['UNIX license', 'UNIX license', 'UNIX license'], 'License Expiry Date / Extension': ['Everlasting', 'Everlasting', 'Everlasting'], 'Working Interest': ['There is one\n', 'There is one\n', 'There is one\n'], 'Gouvernment approval:': ['It is!', 'It is!', 'It is!'], 'Last study:': ['Million years ago', 'Million years ago', 'Million years ago'], 'Parameters': ['Horizon1', 'Horizon2', 'Horizon3'], 'Reservoir rock': ['First', 'Second', 'Third'], 'Net pay thickness (m)': [1.0, 21.0, 41.0], 'Avr. porosity (%)': [2.0, 22.0, 42.0], 'Average absolute permeability (mD)': [3.0, 23.0, 43.0], 'Swi (%)': [4.0, 24.0, 44.0], 'Initial pressure (at)': [5.0, 25.0, 45.0], 'Bubble Pressure (at.)': [6.0, 26.0, 46.0], 'Dew Point Pressure (at)': [7.0, 27.0, 47.0], 'Initial Solution Ratio (Stm3/m3)': [8.0, 28.0, 48.0], 'Initial Condensate Gas Ratio (g/Stm3)': [9.0, 29.0, 49.0], 'Oil density (kg/cm)': [10.0, 30.0, 50.0], 'Oil viscosity (Pb) (cP)': [11.0, 31.0, 51.0], 'Contaminants (H2S, CO2)': [12.0, 32.0, 52.0], 'Initial Oil in Place (e3 to)': [13.0, 33.0, 53.0], 'Initial NGL in Place (e3 to)': [14.0, 34.0, 54.0], 'Initial Gas (assoc.) in Place (e6 m3) sol.gas/gas cap': [15.0, 35.0, 55.0], 'Initial Gas (non assoc.) in Place (e6 m3)': [16.0, 36.0, 56.0], 'Primary recovery / drive mechanism\nNone': ['Wow\nA', 'Recovery\nNone', 'Mechanism\nNone', ''], 'Secondary recovery': ['Another one', '', '', ''], 'Total Wells': ['1000', '-', '-', ''], 'Productive wells (oil/gas)': ['500', '-', '-', ''], 'Injection wells (water/gas)': ['500', '-', '-', ''], 'Rate of best producer in the field (tons / e3 Sm3/day)': ['30', '-', '-', ''], 'Water injection (e3 m3/day)': ['49', '-', '-', ''], 'Actual Pressure (at)': ['500', '434-3443', '48930', ''], 'Oil cumulative production (e3 tons)': ['37', '-', '-', ''], 'Associated gas cum. production (e6 sm3)': ['4535', '-', '-', ''], 'Non-associated gas cum. production (e6 sm3)': ['-', '-', '-', ''], 'NGL cumulative production (e3 tons)': ['', '-', '-', ''], 'Water Cut (%)': ['378', '-', '-', ''], 'Recovery Factor': ['I love hydrocarbons', '', '', ''], 'Current (%)': ['30 20 46 3', '', '', ''], 'Expected 2P (%)': ['Not really expected', '', '', ''], 'Oil production (tons/day)': ['44', '-', '-', ''], 'Associated gas production (e3 sm3/day)': ['-', '-', '-', ''], 'Non-associated gas production (e3 sm3/day)': ['-', '-', '-', ''], 'NGL production (tons/day)': ['-', '-', '-', '']}, 2: {'Field Cluster': ['This fff', 'This fff', 'This fff', 'This fff'], 'Exploration Block': ['fff', 'fff', 'fff', 'fff'], 'Producing since': ['1923fff', '1923fff', '1923fff', '1923fff'], 'Fluids': ['A fff', 'A fff', 'A fff', 'A fff'], 'Reservoirs': ['Test', 'Test', 'Test', 'Test'], 'Area (km2)': ['File', 'File', 'File', 'File'], 'Depth (m)': ['A\nHuge\nDepthfff', 'A\nHuge\nDepthfff', 'A\nHuge\nDepthfff', 'A\nHuge\nDepthfff'], 'Concession License No.': ['UNIX license', 'UNIX license', 'UNIX license', 'UNIX license'], 'License Expiry Date / Extension': ['Everlastingfff', 'Everlastingfff', 'Everlastingfff', 'Everlastingfff'], 'Working Interest': ['There is one\n', 'There is one\n', 'There is one\n', 'There is one\n'], 'Gouvernment approval:': ['ffff', 'ffff', 'ffff', 'ffff'], 'Last study:': ['Million years fffff', 'Million years fffff', 'Million years fffff', 'Million years fffff'], 'Parameters': ['Horizon1', 'Horizon2', 'Horizon3', 'Horizon4'], 'Reservoir rock': ['First', 'Second', 'Third', 'Fourth'], 'Net pay thickness (m)': [1.0, 21.0, 41.0, 61.0], 'Avr. porosity (%)': [2.0, 22.0, 42.0, 62.0], 'Average absolute permeability (mD)': [3.0, 23.0, 43.0, 63.0], 'Swi (%)': [4.0, 24.0, 44.0, 64.0], 'Initial pressure (at)': [5.0, 25.0, 45.0, 65.0], 'Bubble Pressure (at.)': [6.0, 26.0, 46.0, 66.0], 'Dew Point Pressure (at)': [7.0, 27.0, 47.0, 67.0], 'Initial Solution Ratio (Stm3/m3)': [8.0, 28.0, 48.0, 68.0], 'Initial Condensate Gas Ratio (g/Stm3)': [9.0, 29.0, 49.0, 69.0], 'Oil density (kg/cm)': [10.0, 30.0, 50.0, 70.0], 'Oil viscosity (Pb) (cP)': [11.0, 31.0, 51.0, 71.0], 'Contaminants (H2S, CO2)': [12.0, 32.0, 52.0, 72.0], 'Initial Oil in Place (e3 to)': [13.0, 33.0, 53.0, 73.0], 'Initial NGL in Place (e3 to)': [14.0, 34.0, 54.0, 74.0], 'Initial Gas (assoc.) in Place (e6 m3) sol.gas/gas cap': [15.0, 35.0, 55.0, 75.0], 'Initial Gas (non assoc.) in Place (e6 m3)': [16.0, 36.0, 56.0, 76.0], 'Primary recovery / drive mechanism\nNone': ['Wow\nA', 'Recovery\nNone', 'Mechanism\nNone', 'Nice\nNone', ''], 'Secondary recovery': ['Another one', '', '', '', ''], 'Total Wells': ['1000', '-', '-', '-', ''], 'Productive wells (oil/gas)': ['500', '-', '-', '-', ''], 'Injection wells (water/gas)': ['500', '-', '-', '-', ''], 'Rate of best producer in the field (tons / e3 Sm3/day)': ['30', '-', '-', '-', ''], 'Water injection (e3 m3/day)': ['49', '-', '-', '-', ''], 'Actual Pressure (at)': ['500', '434-3443', '48930', '4433', ''], 'Oil cumulative production (e3 tons)': ['37', '-', '-', '-', ''], 'Associated gas cum. production (e6 sm3)': ['4535', '-', '-', '-', ''], 'Non-associated gas cum. production (e6 sm3)': ['-', '-', '-', '-', ''], 'NGL cumulative production (e3 tons)': ['', '-', '-', '-', ''], 'Water Cut (%)': ['378', '-', '-', '-', ''], 'Recovery Factor': ['I love hydrocarbons', '', '', '', ''], 'Current (%)': ['30 20 46 3', '', '', '', ''], 'Expected 2P (%)': ['Not really expected', '', '', '', ''], 'Oil production (tons/day)': ['44', '-', '-', '-', ''], 'Associated gas production (e3 sm3/day)': ['-', '-', '-', '-', ''], 'Non-associated gas production (e3 sm3/day)': ['-', '-', '-', '-', ''], 'NGL production (tons/day)': ['-', '-', '-', '-', ''], 'WOW Production (Something)': ['1', 2.0, '3', '4', '']}}
推荐答案
我正在寻找如何使这些值分别填充单元格
I'm looking either how to make the values fill the cells separately,
我认为您可以在此处创建多索引:
I think you can create a multi-index here:
df=pd.DataFrame(d) # assuming d is the name of the dict
cols=df.columns
final=pd.concat([pd.DataFrame(df[i].dropna().tolist()) for i in cols],axis=1,keys=cols)
final.index=df.index
print(final)
1 2
0 1 2 0 1 2
Parameter 1 Value 1 Value 2 Value 3 Data 1 Data 2 Data 3
Parameter 2 Value 11 Value 22 Value 33 Data 11 Data 22 Data 33
Parameter 3 Num1 Num2 Num3 Numb1 Numb2 Numb3
Parameter 4 NaN NaN NaN Numb11 Numb22 Numb33
编辑最后添加的示例:
final1=pd.concat([pd.DataFrame(v).T for k,v in d.items()],axis=1,sort=False,keys=d.keys())
1 2
0 1 2 0 1 2
Parameter 1 Data 1 Data 2 Data 3 Value 1 Value 2 Value 3
Parameter 2 Data 11 Data 22 Data 33 Value 11 Value 22 Value 33
Parameter 3 Numb1 Numb2 Numb3 NaN NaN NaN
Parameter 4 Numb11 Numb22 Numb33 Num1 Num2 Num3
这篇关于如何将具有多个值的嵌套字典导出到Excel中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
