Examining Data in Python via Pandas¶

One of the most common uses of SPSS is to quickly look at the descriptives of multiple variables at once. Below is a simple example output from SPSS.

Does this look familiar?¶

Aside from correlations this is one of the most common syntax commands I've traditionally used within SPSS. This article will walk you through how to produce extremely similar output in Python. I'll also show you how to use other common methods like crosstabs and correlation matrices. Finally, I'll introduce you to a great package that allows you to do even more investigation of the data with a few additional lines of code.

Let's explore some data!

first let's import pandas, numpy, and matplotlib inline

Quick note about Pandas¶

Pandas is a package that was started by Wes McKinney who worked as a quant in a hedge fund that wanted a better way to work with data than excel macros had to offer.

The Dataset¶

This is a loan dataset, not exactly perfectly suited for the social sciences, especially I/O, but you could easily see how paying or not paying loans is a human behavior, similar to many of the variables we study as social scientists. Job performance, employee engagement, job satisfaction, etc. all have predictors and the fact that this data involves human behavior as an outcome (whether or not the loan was fully paid) means this is very similar to problems we'd be likely to encounter.

This is actually lendingclub.com data which you can read more about here. Here is what each of the variables/columns represent:

• credit.policy: 1 if the customer meets the credit underwriting criteria of LendingClub.com, and 0 otherwise.
• purpose: The purpose of the loan (takes values "credit_card", "debt_consolidation", "educational", "major_purchase", "small_business", and "all_other").
• int.rate: The interest rate of the loan, as a proportion (a rate of 11% would be stored as 0.11). Borrowers judged by LendingClub.com to be more risky are assigned higher interest rates.
• installment: The monthly installments owed by the borrower if the loan is funded.
• log.annual.inc: The natural log of the self-reported annual income of the borrower.
• dti: The debt-to-income ratio of the borrower (amount of debt divided by annual income).
• fico: The FICO credit score of the borrower.
• days.with.cr.line: The number of days the borrower has had a credit line.
• revol.bal: The borrower's revolving balance (amount unpaid at the end of the credit card billing cycle).
• revol.util: The borrower's revolving line utilization rate (the amount of the credit line used relative to total credit available).
• inq.last.6mths: The borrower's number of inquiries by creditors in the last 6 months.
• delinq.2yrs: The number of times the borrower had been 30+ days past due on a payment in the past 2 years.
• pub.rec: The borrower's number of derogatory public records (bankruptcy filings, tax liens, or judgments).

Let's write a quick few lines of code to figure out which datatype each variable is, because we can't get descriptives on object type variables, which typically represent strings.

So we can quickly see all variables aside from purpose should produce us with usable descriptives.

Descriptive Statistics¶

How do we get descriptives in python? We can do this by using the .describe() method

Note: I will use the round function to make the data look nicer.

SPSS won't allow you to add columns with a data type that is not an int or float for examining descriptives. In Python you can add it, but it won't show anything. Given what we saw above we should expect to see descriptive statistics for every column except purpose and it appears we do in fact have descriptives for every column except purpose. This provides us more than the base descriptives command from SPSS as we also see the quartiles.

If we wanted to look at specific columns we would just subset them like this:

But I hear you saying, that's great and all, but I've used SPSS for so long I like the stacked output better than the wide output (variables as rows instead of columns)¶

Well....the great thing about object oriented programming is the table is an object that can be manipulated.

So....let's think about an easy way to do that. What if we just transposed the object?

Now it looks identical to the SPSS output above!

Crosstabs¶

Another really common output I've used in SPSS is the crosstabs view. It looks a little like this;

Just like descriptives, pandas has you covered for crosstabs as well. Now most of these variables are continuous by nature, so in order to make a crosstabs viewable let's quickly bin one that might be interesting to look at. What we want to do is use the following; np.linspace, np.digitize

• np.linspacelooks at the min and the max of the fico score and creates a linearly spaced grouping of them, so all numbers within the min and max are equally grouped into 5 categories.
• Then np.digitize returns the index for each bin.
• Finally, I build a crosstab so we can examine the number of individuals that either paid back their loan (0) or did not pay back their loan (1).

You can see a trend where the proportion of borrowers that did not pay back their loans tends to becom less and less as we move from the lowest bins to the highest bins. Which makes sense. People with lower fico scores typically have those for a reason, perhaps not fiscally responsible with credit?

Correlation Matrix¶

As an I/O this SPSS output is probably our bread and butter. Examining the correlations amongst your variables. Lucky for us that's also extremely simple to do in pandas. using the pandas.DataFrame.corr method.

We could also turn it into a nicer looking heatmap visual by importing the seaborn package.

There are many cmaps available, here are a few, but you can also create your own using matplotlibs linear segmented color map functionality.

Of course this would be great if this was all pandas had to offer.

Other important methods I've used over the past few years are:¶

• Groupby
• Pivot Tables
• Along with many more

There's also a great external package I want to talk about that allows you to quickly look at most of your data using the most common EDA (Exploratory Data Analysis) methods.

Pandas Profiling¶

Note in order to use pandas profiling (which we will explore next) you will need to install the package. You can do so, by using the following command in your terminal/anaconda prompt:

conda install -c conda-forge pandas-profiling

Let's see what additional features pandas profiling has to offer!

As you can see below the pandas profiling package offers just about everything you could ever want to quickly look through your data.

Here's a quick list:

• Number of variables
• Number of observations
• Percent of missing data
• Size of the data in memory
• data types (numerical, categorical, boolean, etc.)
• A flag on features/variables with a large # of zero values
• Common statistics (like seen in the .describe() pandas method)
• Histograms for each feature/variable
• Common values
• Extreme values
• Pearson correlation
• Spearman correlation

As far as understanding your data, this is about as good as it gets

You are going to have to scroll down a little ways given the amount of output pandas profiling produces from just 1 line of code, but please do, so we can see if anything jumps out at us.

Some quick notes from the descriptives:

1. We can clearly see that a number of these variables are incredibly skewed.
2. The correlation matrix provides us with a few interesting (but understandable) findings:
   • Pretty strong negative correlation between interest rate and fico score
   • Pretty strong positive correlation between credit policy and fico score

While this is great for a brief overview, we should probably do a bit more digging. For that let's use a package built on top of matplotlib called seaborn

Let's look at the relationship between fico score and credit policy a bit closer using graphs.

We can clearly see that there are a lot more people that have credit policies of 1 in this dataset, but even so, there is a skew towards lower fico scores for those with a credit policy of 0 and higher scores for those with a credit policy of 1.

We had one variable that was categorical, that we didn't get much data on. Let's use seaborn to create a quick categorical plot of that to see if anything stands out.

It doesn't look like anything really stands out to me, all of the bar charts look pretty proportional between non-paid loans and paid loans.

This was a quick overview of some of the features pandas, pandas profiling, matplotlib, and seaborn have to offer for exploring data. I plan to do a more full EDA in a future article, but I wanted to show you what pandas had to offer and how it provided many similar capablities to SPSS, plus a lot more.¶

If you want to learn exactly how to do everything I did in a bit more detail and a slower pace I'd highly recommend the python data science bootcamp by Jose Portilla I mentioned here as my plan for this site is to walk you through cool features I think add value to the social scientists tool belt, but not necessarily teach you how to use pandas, or the plotting libraries from scratch.