How to read an Excel spreadsheet with Python
• 1 minI bet that your daily work require work with spreadsheet.
You might want to read an excel spreadsheet using Python.
The best library when dealing with spreadsheet is by far the pandas library
In case you don't have the pandas library
Open your terminal and type in :
pip3 install pandas
It will use pip3 package manager to install the pandas library
The example
import pandas as pd
df = pd.read_excel("my_file.xlsx") # We end up with a dataframe
Here we only specify the path to the file, but you can add some other parameters listed on the documentation here.
The most useful I've found are the following:
- sheet_name : The name of the spreadsheet of interest (e.g. Sheet1)
- headers : Usually the row where are the columns names (e.g. 0)
- index_col : The index of the columns we want as index. (e.g. 0)
- skiprows : Whether we should skip any rows (e.g. 1)
Where should you put your file ?
What usually matter when dealing with path is
- Where do you run your script
- Where is your excel file
Usually if you keep them in the same directory like so
current_dir/
├── my_file.xslx
└── my_python_script.py
The following code should work
import pandas as pd
df = pd.read_excel("./my_file.xslx") # Path ./ means current directory