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 mapping.py.

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.