Using Pandas Data Frames and Pivot Tables in Python

Before we can use pandas to analyze our employee data we'll need to make sure that it is installed.

Our pay_analysis.py script already sets up a list of dictionary objects with our employee information named employee_info. We need to use this list to create a pandas.DataFrame for us to be able to perform our analysis. Get the column names from an item in the list and create a new data frame called employee_frame.

Note: We also need to import pandas.

To determine the average pay difference between men and women with the same job title, we'll first need to determine which jobs have both men and women doing them. We can do this by grouping our data frame information by the job title first and then creating a pandas.Series that has the job titles as the index values and the number of unique genders as the values.

Next, using job_title_unique_gender_count, we'll take all of the items that have a value of 2, drop the items that will be adjusted to a NaN (because they aren't a 2) and then get a pandas.Index object with just the names by accessing the index of our pandas.Series. This seems a little complicated, but it's a good way for us to get just a list of the job titles where the number of genders in that role is 2.

After that, we create a new data frame from our employee_frame data that only contains employees in a job that includes both genders.

The last two things we do are creating our pivot table based on the job title where we have a column for each gender and adding a column to the table to hold the pay difference between female and male employees.

Our final pivot table will work a lot like the gender_difference_table, except we need to categorize each of our employees into an age range before we start making our comparisons. To do this, we'll use the pandas.cut function and add the resulting series as a new column on our employee_frame object, calling it age_range. There are 2 possible categories for each employee to fall under "18 - 40" and "41 - 80". We're going to also add a third, unused category to the list of categories for this column so that we can add a column to our pivot table later.

The data type of the age_range column is category, and it is important that when we're adding a category to it that we use the inplace=True option. If we don't do this, then we'll get a new category returned to us and won't modify the employee_frame at all.

Next, we need to determine which job titles have at least one employee from each of the age ranges. This code is virtually identical to what we did to determine while job titles had employees from each of the gender options.

Now that we have a new data frame, we're ready to create our pivot table and populate a new 'difference' column. This will look very similar to our other pivot table, except we want to create columns for each of the values in our age_range column. Here's the rest of the code that we need to write to create our table and calculate the differences in pay: