For more information about concepts covered in this lesson, you can check out:
Reading and Writing Excel Files
Reading and writing Excel files. Microsoft Excel is probably the most widely-used spreadsheet software. While older versions use binary
.xls files, Excel 2007 introduced the new XML-based
You can read and write Excel files in pandas similar to CSV files. However, you’ll need to install the following Python packages first:
xlwt to write to
XlsxWriter to write to
xlrd to read Excel files.
You can install them using
pip with a single command.
Please note that you don’t have to install all these packages, although doing so will allow you to get started with a minimum of fuss. For example, you don’t need both
XlsxWriter, and if you’re only ever going to write
.xlsx files, then you may want to just use
01:14 If you’re only going to work with the older binary Excel files, then you’ll want to use the appropriate packages depending on if you’ll be reading, writing, or both. Take some time to decide which packages are right for your project, and if you’re deploying an application, then only install the packages you’ll actually be using.
Once you have those packages installed, you can save your DataFrame in an Excel file with the
.to_excel() method, as seen onscreen.
'data.xlsx' represents the target file and, optionally, its path. The statement you see onscreen should create the file
data.xlsx in your current working directory. Opening it up in Excel, it should look like this.
The first column of the file contains the label of the rows, while the other columns store data. You can load data from Excel files with the
read_excel() returns a new DataFrame that contains the values from
You can also use
read_excel() with OpenDocument spreadsheets or
.ods files. You’ll learn more about working with Excel files later on in this course.
02:45 You can also check out Using Pandas to Read Large Excel Files in Python. In the next section of the course, you’ll take a deeper look at how pandas interacts with files, and work with more file types.
Become a Member to join the conversation.
Kuraffen on Nov. 28, 2021
When I create a dataframe from the data dictionary in the supporting material, the dataframe I get is a transposed version. Each country is stored as a column, not a row - opposite to the dataframe in the video.
I do this in a Jupyter Notebook.
The dictionary I use looks like this:
Ant this is how I create the dataframe