I am trying to read an excel file this way :
newFile = pd.ExcelFile(PATH\FileName.xlsx)
ParsedData = pd.io.parsers.ExcelFile.parse(newFile)
which throws an error that says two arguments expected, I don't know what the second argument is and also what I am trying to achieve here is to convert an Excel file to a DataFrame, Am I doing it the right way? or is there any other way to do this using pandas?
This question is related to
python
python-2.7
pandas
I think this should satisfy your need:
import pandas as pd
# Read the excel sheet to pandas dataframe
df = pd.read_excel("PATH\FileName.xlsx", sheetname=0)
import pandas as pd
data = pd.read_excel (r'**YourPath**.xlsx')
print (data)
You just need to feed the path to your file to pd.read_excel
import pandas as pd
file_path = "./my_excel.xlsx"
data_frame = pd.read_excel(file_path)
Checkout the documentation to explore parameters like skiprows
to ignore rows when loading the excel
Thought i should add here, that if you want to access rows or columns to loop through them, you do this:
import pandas as pd
# open the file
xlsx = pd.ExcelFile("PATH\FileName.xlsx")
# get the first sheet as an object
sheet1 = xlsx.parse(0)
# get the first column as a list you can loop through
# where the is 0 in the code below change to the row or column number you want
column = sheet1.icol(0).real
# get the first row as a list you can loop through
row = sheet1.irow(0).real
Edit:
The methods icol(i)
and irow(i)
are deprecated now. You can use sheet1.iloc[:,i]
to get the i-th col and sheet1.iloc[i,:]
to get the i-th row.
This is much simple and easy way.
import pandas
df = pandas.read_excel(open('your_xls_xlsx_filename','rb'), sheetname='Sheet 1')
# or using sheet index starting 0
df = pandas.read_excel(open('your_xls_xlsx_filename','rb'), sheetname=2)
check out documentation full details http://pandas.pydata.org/pandas-docs/version/0.17.1/generated/pandas.read_excel.html
FutureWarning: The sheetname
keyword is deprecated for newer Pandas versions, use sheet_name
instead.
Here is an updated method with syntax that is more common in python code. It also prevents you from opening the same file multiple times.
import pandas as pd
sheet1, sheet2 = None, None
with pd.ExcelFile("PATH\FileName.xlsx") as reader:
sheet1 = pd.read_excel(reader, sheet_name='Sheet1')
sheet2 = pd.read_excel(reader, sheet_name='Sheet2')
https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.read_excel.html
Loading an excel file without explicitly naming a sheet but instead giving the number of the sheet order (often one will simply load the first sheet) goes like:
import pandas as pd
myexcel = pd.ExcelFile("C:/filename.xlsx")
myexcel = myexcel.parse(myexcel.sheet_names[0])
Since .sheet_names
returns a list of sheet names, it is easy to load one or more sheets by simply calling the list element(s).
Source: Stackoverflow.com