2

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']}
}

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 | 
----------------------------------------------------------------------

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'] | 
--------------------------------------------------------------------------------------

Which is pretty obvious.

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.

Here's an actual example:

{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', '']}}
Improve this question

1 Answer 1

Reset to default
1

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

EDIT for the last added example:

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
Improve this answer
Sign up to request clarification or add additional context in comments.

13 Comments

Thanks for your answer, but in my case I get an error: "Length mismatch: Expected axis has 83 elements, new values have 104 elements". This is because I have 83 keys in the first sub dictionary and 104 in both of them. How can I fix it?
@anki_91 UPD, your code works finally! But only if I comment the #final.index=df.index line. But without it I get numbers (indexes) for the first column instead of the Parameters. If I leave this line, I get this error, like in a previous comment.
@anki_91 I can't currently give you a link to file with dictionary (pastebin or whatever), only in several hours.
@anki_91 well, at the end of the question I have included same-looking dictionary, as I have. It works for some reason with me, but not with actual one. Sorry, I can't submit the data I actually have, because of code of conduct.
@anki_91 I have finally found the issue. In my case the second dictionary has some missing parameters (I have attached the new dictionary to the question). In this case Parameter 4 line in the second sub dictionary goes to the row with Parameter 3 in the first. So the values are not really attached to the parameters.
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.