Join us and get access to hundreds of tutorials and a community of expert Pythonistas.

Unlock This Lesson

This lesson is for members only. Join us and get access to hundreds of tutorials and a community of expert Pythonistas.

Unlock This Lesson

Hint: You can adjust the default video playback speed in your account settings.
Hint: You can set the default subtitles language 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 see our video player troubleshooting guide to resolve the issue.

Making a Mapping File

00:00 Before you learn how to use pandas with openpyxl, we should cover one more topic related to making mapping files. You may have noticed that indexing columns can get a bit confusing since you’re only using the integer index in a lot of cases. To make your code more readable, you can put together a mapping file, which contains variable names for each of the field indexes.

00:21 Go ahead and create a new file called

00:28 Let’s take a look at what this would be like. To start off, you can have your product fields, and this is going to take into account the data that’s in the sample.xlsx file. So first, you’re going to have your PRODUCT_ID which will equal 3, your PRODUCT_PARENT, which will equal 4, PRODUCT_TITLE, which is going to be 5—and make sure that’s 4

00:52 and PRODUCT_CATEGORY, which is going to equal 6. To understand what’s going on here, you can go back and take a look at the Excel file.

01:03 Here, starting with the zero index, you can see that you have zero, one, two—and three is where the PRODUCT_ID is. That’s why PRODUCT_ID is storing a 3. Back here, the parent is in four, the title is in five,

01:22 and the category is in six. Now, if you need to call any of those indexes or fields, you can just import these variables and use them in the code to show which column you mean. Let’s go ahead and finish this up with the review fields.

01:39 Over here you’re going to have the REVIEW_ID, which is in 2, the REVIEW_CUSTOMER, which is in 1, REVIEW_STARS, in 7, the REVIEW_HEADLINE, which is in 12, the REVIEW_BODY in 13, and the REVIEW_DATE in 14.

02:02 So, this takes a little bit of extra work to set up, but if you’re going to be using the same structure of data consistently, this is a good idea to make your life easier in the future, and it will limit the amount of times you’re flipping back and forth between Excel and your text editor, especially if you’re trying to convert between letters and integers on the fly.

02:22 Not using files like this in the past is why I’ll never forget that M is the 13th letter in the English language, and T is the 20th. Hah.

02:32 Okay! Now that this is all set, you’ll import this file in the next lesson, and you’ll see how it all works. You’re only going to use the PRODUCT_ID field but being able to produce mapping files like this is a good habit to get into.

Become a Member to join the conversation.