How to Insert excel data with multiple row headers into pandas dataframe

Question

I'm trying to convert and unstructured excel file to structured. My headers pan across two rows (1 AND 2) For headers SKU, Qty and sales

   A              B                  C             D
1                                    Qty          Sales 
2   SKU             
3   2345       Nail Varnish          2              $15 
4   2346       Eye liner             3              $18

and my desired output once converted to dataframe should be like below

    SKU           Description        Qty          Sales             
0   2345         Nail Varnish         2             $15 
1   2346         Eye liner            3            $18

I have tried passing multiple rows as header in read_excel function

df = pd.read_excel('D:\Sales.xlsx',header=[1,2])

but am getting the output as

    Unnamed:0_level_0  Unnamed:1_level_0         Qty               Sales  
         SKU                 SKU           Unnamed:2_level_1   Unnamed:3_level_1

  0   2345             Nail Varnish             2                      $15
  1   2346             Eyeliner                 3                      $18

The header SKU is also being applied to second column. Please guide on how to go about troubleshooting this.

Sociopath · Accepted Answer · 2019-03-27 07:26:49Z

1

If you know the column names beforehand then you can do

df = pd.read_excel('D:\Sales.xlsx', header=None, names=['SKU','Description','Qty','Sales'], skiprows=2)

explaination

header=None won't take default headers from excel file
names will assign column names to your dataframe in order
skiprows will skip first 2 rows from your excel.

answered Mar 27, 2019 at 7:26

Sociopath

13.4k22 gold badges53 silver badges82 bronze badges

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

1 Comment

Tejas Over a year ago

Thanks Akshay, it worked, i overlooked this possibility and was trying a hard way out. Appreciate taking time to answer this

Collectives™ on Stack Overflow

How to Insert excel data with multiple row headers into pandas dataframe

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related