We can use the pandas module read_excel() function to read the excel file data into a DataFrame object. If you look at an excel sheet, it’s a two-dimensional table. The DataFrame object also represents a two-dimensional tabular data structure.
Let’s say we have an excel file with two sheets - Employees and Cars. The top row contains the header of the table.
Here is the example to read the “Employees” sheet data and printing it.
Output:
We can get the list of column headers using the columns
property of the dataframe object.
Output:
We can get the column data and convert it into a list of values.
Output:
We can specify the column names to be read from the excel file. It’s useful when you are interested in only a few of the columns of the excel sheet.
Output:
If the excel sheet doesn’t have any header row, pass the header parameter value as None.
If you pass the header value as an integer, let’s say 3. Then the third row will be treated as the header row and the values will be read from the next row onwards. Any data before the header row will be discarded.
The DataFrame object has various utility methods to convert the tabular data into Dict, CSV, or JSON format.
Output:
Thanks for learning with the DigitalOcean Community. Check out our offerings for compute, storage, networking, and managed databases.
While we believe that this content benefits our community, we have not yet thoroughly reviewed it. If you have any suggestions for improvements, please let us know by clicking the “report an issue“ button at the bottom of the tutorial.
What is we have an xlsb file instead of xlsx?
- Shilpa Bindra
Thanks it helped me
- firozsahib
thanks this was very helpful!
- byli
Thank you, it was very helpful
- mila
Thanks, it was very helpful.
- Havi
I must say this is ‘awesome’
- Prabhat Kumar
Thanks, useful post. excel_data_df = pandas.read_excel(‘records.xlsx’, sheet_name=‘Cars’, usecols=[‘Car Name’, ‘Car Price’]) results in an empty dataframe for me. Do we need to do some setup?
- Rahul Misra
Thanks pankaj… It saved my data. I wanted to read from a specific folder so I tweaked it for the location. pd.read_excel(‘C:/Source/Datafile.xlsx’, sheet_name=‘sheet_name’)
- Rajjan
how can we remove a specific row? I have a ‘Total’ row at the end of my Excel file that I would want to remove. Thanks for the helpful info in this article!
- Josh Rodriguez
great work… thanks a lot for posting
- Rengifo