Hint: You can adjust the default video playback speed in your account settings.
Hint: You can set your subtitle preferences in your account settings.
Sorry! Looks like there’s an issue with video playback 🙁 This might be due to a temporary outage or because of a configuration issue with your browser. Please refer to our video player troubleshooting guide for assistance.

Reading and Writing Excel Files

Darren Jones
Reading and Writing Files With pandas Darren Jones 02:59
Recommended Tutorial Course Slides (.pdf) Sample Code (.zip) Ask a Question

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.

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:

> data = {
    'CHN': {'COUNTRY': 'China', 'POP': 1_398.72, 'AREA': 9_596.96,
            'GDP': 12_234.78, 'CONT': 'Asia'},
    'IND': {'COUNTRY':...

Ant this is how I create the dataframe

df = pd.DataFrame(data=data)

Become a Member to join the conversation.