2

I have the following data in an Excel sheet and I want to read it as a multiindex dataframe:

    Y1     Y1      Y2           Y2
B   H1     H2      H1           H2
1   80     72      79.2         84.744
2   240    216     237.6        254.232
3   40     36      39.6         42.372
4   160    144     158.4        169.488
5   240    216     237.6        254.232
6   0      0       0            0

I am reading it as:

DATA = pd.read_excel('data.xlsx',sheet_name=None)

since I am reading other sheets too.

Question 1:

This data is not read as multi-index data. How do I make it read it as multi-index? Or maybe I should read it as a dataframe and then convert it to multi-index? Current result of reading as dataframe

DATA['Load']
      Y1 Y1.1     Y2     Y2.1
bus   H1   H2     H1       H2
1     80   72   79.2   84.744
2    240  216  237.6  254.232
3     40   36   39.6   42.372
4    160  144  158.4  169.488
5    240  216  237.6  254.232
6      0    0      0        0

Question 2 and probably the more fundamental question:

How do I deal with multi-indexing when one or more of the indexes are on the columns side? In this example, I want to access the data by specifying B, Y, H. I know how to work with multi-index when they are all as index, but can't get the hang of it when the indexes are on the columns.

Thank you very much for your help :)

PS:

Another sheet may look like the following:

from    to  x     ratea
1       2   0.4   10
1       4   0.6   80
1       5   0.2   10
2       3   0.2   10
2       4   0.4   10
2       6   0.3   10
3       5   0.2   10
4       6   0.3   10

where I will set from and to as set (set_index(['from','to']) to get a multi-index dataframe.

Improve this question

1 Answer 1

Reset to default
1

to read a dataframe like this to a multiindex users the header param in pd.read_excel()

df = pd.read_excel('myFile.xlsx', header=[0,1])

    Y1      Y2
B   H1  H2  H1  H2
1   80  72  79.2    84.744
2   240 216 237.6   254.232
3   40  36  39.6    42.372
4   160 144 158.4   169.488
5   240 216 237.6   254.232
6   0   0   0.0 0.000

this means that you are telling pandas that you have two header rows 0 and 1

after our conversation:

df = pd.read_excel('Book2.xlsx', header=[0,1])
df2 = df.unstack().to_frame()
idx = df2.swaplevel(0,2).swaplevel(1,2).index.set_names(['B', 'Y', 'H'])
df2.set_index(idx, inplace=True)

            0
B   Y   H   
1   Y1  H1  80.000
2   Y1  H1  240.000
3   Y1  H1  40.000
4   Y1  H1  160.000
5   Y1  H1  240.000
6   Y1  H1  0.000
1   Y1  H2  72.000
2   Y1  H2  216.000
3   Y1  H2  36.000
4   Y1  H2  144.000
5   Y1  H2  216.000
6   Y1  H2  0.000
1   Y2  H1  79.200
2   Y2  H1  237.600
3   Y2  H1  39.600
4   Y2  H1  158.400
5   Y2  H1  237.600
6   Y2  H1  0.000
1   Y2  H2  84.744
2   Y2  H2  254.232
3   Y2  H2  42.372
4   Y2  H2  169.488
5   Y2  H2  254.232
6   Y2  H2  0.000
Improve this answer
Sign up to request clarification or add additional context in comments.

8 Comments

Thank you. This works when I am reading only one sheet. If I understand you correctly, I need to read that sheet separately and I cannot read it with other sheets and later set the index, am I right? And assuming it is separately read as you said, when I write df.index I do NOT get those headers as index. I don't understand why. Any idea for question 2? Thank you for your time :)
@Ali I think I answered your second question in my updated answer. What do your other sheets look like?
Thank you very much for your answer. After the data is read (assuming one sheet at a time), how do I set the indexes? Currently, if I ask for df.index I get 1 to 6, and not those headers. The challenge for me is that Column B AND Y and H must be index, and not only Y and H.
@Ali just for clarification you want the whole sample dataframe to be the row index: all the columns?
I want to be able to access the data by specifying B,Y,H Similar to another sheet that I posted where I could specify 'from','to' and then I specify column 'x' and I get the x value, I want to be able to specify B,H,Y and get the corresponding value. (Maybe I am not understanding a concept properly, sorry if I am confusing you.)
|

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.