Reading and Writing Excel Files
For more information about concepts covered in this lesson, you can check out:
00:00
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 .xlsx
file.
00:18
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 .xls
files, openpyxl
or XlsxWriter
to write to .xlsx
files, xlrd
to read Excel files.
00:42
You can install them using pip
with a single command.
00:53
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 openpyxl
and XlsxWriter
, and if you’re only ever going to write .xlsx
files, then you may want to just use XlsxWriter
.
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.
01:32
Once you have those packages installed, you can save your DataFrame in an Excel file with the .to_excel()
method, as seen onscreen.
01:45
The argument '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.
02:03
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()
function.
02:23
read_excel()
returns a new DataFrame that contains the values from data.xlsx
.
02:33
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