Manipulate Excel into Pandas Dataframe

Question

I am trying to replicate a bar chart using pandas. The problem I have run into is the merged cells. Pandas data frame returns unamed for the extra column. I have tried to select my excel data using read_excel() and then creating a dataframe for it using multiindexing methods but I cannot figure it out.

Can someone tell me how I can create/load similar data into a pd Dataframe.

Thank you!

EDIT: I had only been able to get to this point:

xls_file = pd.read_excel('MarkRuiz_COT1.xlsx',
                     sheet_name='Split8',usecols=[1,2,3,4,5,6,7,8,9,10,11,12])

xx = xls_file[1:]

Using jupyter notebook to display xx gives me

which is a (4,12) when running xx.shape. Next, what I did is try to create a multi index data frame of same size of load myt data but I did not index it correctly :/

outside = s7+s8
inside = nodes*2
pos = ['T01','T10']*6
hier_index = list(zip(outside,inside))
hier_index = pd.MultiIndex.from_tuples(hier_index)

df = pd.DataFrame(xx,index=hier_index,columns=pos)
df

Can you load it into pandas and paste the output here as raw text? — Umar.H
– Umar.H, Commented Nov 25, 2019 at 19:26
Device w20_d306 w20_d306.1 w20_d506 w20_d506.1 w20_d404 w20_d404.1 w20_d405 w20_d405.1 w20_d807 w20_d807.1 w20_d607 w20_d607.1 0 Location T01 T10 T01 T10 T01 T10 T01 T10 T01 T10 T01 T10 1 Blk0 2.41 2.4 2.46 2.47 2.59 2.6 2.49 2.49 2.47 2.43 2.53 2.49 2 Bdry0 2.44 2.53 2.51 2.62 2.64 2.75 2.54 2.61 2.53 2.61 2.58 2.64 3 Bdry1 2.53 2.43 2.6 2.51 2.68 2.6 2.58 2.54 2.57 2.51 2.64 2.56 4 Blk1 2.41 2.4 2.49 2.47 2.6 2.58 2.47 2.49 2.47 2.48 2.52 2.51 — Mark Ruiz
– Mark Ruiz, Commented Nov 25, 2019 at 19:33

Julien Roullé · Accepted Answer · 2019-11-25 19:28:54Z

1

You can use the header argument of the read_excel method, like this:

df = pd.read_excel('/path/to/file.xlsx', header=[0, 1])

answered Nov 25, 2019 at 19:28

Julien Roullé

6724 silver badges15 bronze badges

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

7 Comments

Umar.H Over a year ago

@Mark Ruiz this should work let us know if you need more help

Mark Ruiz Over a year ago

Thank you very much, I had tried header=[0] with no success. The method you provided is exactly what I was looking for I think. Do you have any suggestions on what is the best method to recreate my excel plot? I'm trying to figure out how to set the x-axis to include both 0 and 1 rows.

Julien Roullé Over a year ago

Is df.T.plot(kind='bar') what you are looking for?

Mark Ruiz Over a year ago

yes, exactly that. Sorry i just started using python last week and the amount of documentation is overwhelming. I'm sorry to pester you but is there a way to manipulate the x-axis to group T01 and T10 according to their respective device name?

Julien Roullé Over a year ago

I'm not sure I'm getting this right. Here is an answer I can give you: df.T.groupby('Device').plot(kind='bar')

|

Collectives™ on Stack Overflow

Manipulate Excel into Pandas Dataframe

1 Answer 1

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related