An empty data frame error when using read_excel(),skiprow

Question

I have an excel with an explanation written in the first row as follows: I want to convert the second row as a header and the data below it into a dataframe format. So I wrote the following code, but the result is an empty data frame

df = pd.read_excel(filename, skiprows=1)

print(df) =
Empty DataFrame
Columns: []
Index: []

If I enter Excel and delete the first row and do not use skiprows, a correct dataframe appears. Which should I fix?

And since there is only one sheet, I did not set it

When opening the first file saved, the cursor is positioned at row=1 column[a]=1. If I change the cursor position and save the excel, the data frame comes out well. How do I move the cursor and save it?

factorThis · Accepted Answer · 2021-02-23 11:38:45Z

1

skiprows=1 works fine me.

pd.read_excel('book1.xlsx', skiprows=1)

Here is an alternative using openpyxl, maybe you can give this a try and see -

from openpyxl import load_workbook
wb = load_workbook('book1.xlsx')
ws = wb.get_sheet_by_name('Sheet1')
data = []
for row in ws.values:
    data.append([item for item in row])
df = pd.DataFrame(data[2:], columns=data[1])

answered Feb 23, 2021 at 11:38

factorThis

745 bronze badges

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

1 Comment

kwsong0314 Over a year ago

The first method failed, but the second method succeeded. Thank you

okpython · Accepted Answer · 2021-02-23 10:08:29Z

0

have you tried skiprows=[0]? This is outlined here: https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.read_excel.html

Then the default behaviour for headers should be taken as the new row 0

answered Feb 23, 2021 at 10:08

okpython

297 bronze badges

11 Comments

kwsong0314 Over a year ago

I tried it the way I said, but I got an empty data frame

kwsong0314 Over a year ago

In my opinion, the data frame seems to read 1 row because A, B, C of 1 row are merged. So it seems that there is no data when I skiprows

okpython Over a year ago

without using skiprows, if you do a describe() on the dataframe, what is the result. since you also are not uploading your data, can you send an image of the resulting df without skiprows?

kwsong0314 Over a year ago

df = pd.read_excel(filename) print(df) = Empty DataFrame Columns: [1.excelexplanation... 2.... 3... ] Index: []

kwsong0314 Over a year ago

It seems that only the first row of excel is being read. The rest of the rows are not visible

|

Edoardo Pericoli · Accepted Answer · 2021-02-23 10:09:45Z

0

The skiprow argument looks at rows starting from 0. So you have to put skiprows=0.

answered Feb 23, 2021 at 10:09

Edoardo Pericoli

3081 silver badge10 bronze badges

2 Comments

kwsong0314 Over a year ago

When I tried what I said, only the line with the description of excel is printed.( First row in excel)

kwsong0314 Over a year ago

In my opinion, the data frame seems to read 1 row because A, B, C of 1 row are merged. So it seems that there is no data when I skiprows

Collectives™ on Stack Overflow

An empty data frame error when using read_excel(),skiprow

3 Answers 3

1 Comment

11 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

11 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related