Excel with multiple column indices and header rows into a Python dictionary via pandas

Question

I am working with Pyomo and I am trying to input some 4-D data for some parameters.

I have the data in an Excel spreadsheet that looks like this:

A link to the original data can be found here:

I would like to import the data in Python and have each column index and header value in a tuple as the key of a dictionary and the values as the dictionary's values.

Essentially, the expected output should look like:

p = {('Heat', 'Site 1', 1, 1): 14,
     ('Heat', 'Site 1', 1, 2): 16,
     ('Heat', 'Site 1', 1, 3): 10,
     ('Heat', 'Site 1', 2, 1): 13,
     ('Heat', 'Site 1', 2, 2): 13,
     ('Heat', 'Site 1', 2, 3): 13,
     ('Cool', 'Site 1', 1, 1): 5,
     ('Heat', 'Site 1', 1, 2): 6,
...
     ('Elec', 'Site 2', 2, 1): 11,
     ('Elec', 'Site 2', 2, 2): 15,
     ('Elec', 'Site 2', 2, 3): 15}

My idea was to import the excel file using pandas, first, and then use the to_dict method.

What I did is the following:

import pandas as pd
Loads = pd.read_excel("Time_series_parameters.xlsx", index_col=[0,1], header = [0,1])

That works well and I am able to get a data frame with two index columns and two header rows:

       Heat   Cool   Elec   Heat   Cool   Elec
Time Site 1 Site 1 Site 1 Site 2 Site 2 Site 2
1 1      14      5     13     10     20     14
  2      16      6     11     10     14     10
  3      10      7     14     11     18     11
2 1      13      8     14     20     19     11
  2      13      7     11     14     15     15
  3      13      6     13     12     19     15

However, whatever I have tried from there to get to the expected result has failed... All the settings in the to_dict method do not give me the expected result.

Hence, I would appreciate it if someone could be of some help here.

could you please post the data in code quotes after you read the data in pandas? — pythonic833
– pythonic833, Commented Aug 19, 2019 at 16:34

Tim Mironov · Accepted Answer · 2019-08-19 16:28:30Z

1

My solution for this would be:

import pandas as pd
Loads = pd.read_excel("Time_series_parameters.xlsx", index_col=[0, 1], header=[0, 1])

out = {}
for index, inner in Loads.iteritems():
    for sec_index, value in inner.iteritems():
        out[index[0], index[1], sec_index[0], sec_index[1]] = value

The resulting output is:

{('Heat', 'Site 1', 1, 1): 14,
 ('Cool', 'Site 1', 1, 1): 5,
 ('Elec', 'Site 1', 1, 1): 13,
 ('Heat', 'Site 2', 1, 1): 10,
 ('Cool', 'Site 2', 1, 1): 20,
 ('Elec', 'Site 2', 1, 1): 14,
 ('Heat', 'Site 1', 1, 2): 16,
 ('Cool', 'Site 1', 1, 2): 6,
 ('Elec', 'Site 1', 1, 2): 11,
 ('Heat', 'Site 2', 1, 2): 10,
 ...

edited Aug 19, 2019 at 16:28

answered Aug 19, 2019 at 16:22

Tim Mironov

8599 silver badges22 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

gmavrom Over a year ago

Yep, that worked and it is also a very concise answer! Thanks for that!

Tim Mironov Over a year ago

Glad it helped, you can up vote the answer if it was useful :)

gmavrom Over a year ago

I upvoted it, but my reputation is still low and my upvotes are counted but not yet visible :/

Tim Mironov Over a year ago

That's right, I forgot about this :D Good luck with your project!

gmavrom · Accepted Answer · 2019-08-21 11:26:04Z

0

I have also found another answer that essentially achieves the same results using some other pandas functionality. The code can be seen below:

Loads = pd.read_excel("Time_series_parameters.xlsx", sheet_name = "Loads", index_col=[0,1], header=[0, 1])
Loads = Loads.stack().stack()
Loads = Loads.reorder_levels([3,2,0,1])
p = Loads.to_dict()

The output looks again like this:

{('Cool', 'Site 1', 1, 1): 18,
 ('Elec', 'Site 1', 1, 1): 18,
 ('Heat', 'Site 1', 1, 1): 19,
 ('Cool', 'Site 2', 1, 1): 17,
...

answered Aug 21, 2019 at 11:26

gmavrom

4504 silver badges12 bronze badges

Collectives™ on Stack Overflow

Excel with multiple column indices and header rows into a Python dictionary via pandas

2 Answers 2

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related