Sign in to continue learning
Data Analytics
Beginner
Topics
Data Engineering
Data Processing
Synthetic Data
In addition to charts, you'll typically want to examine the descriptive statistics for your segmented data. Spreadsheet power users employ pivot tables for this task. Let's see how they work. As a reminder, you were previously working with data about different loans to analyze potential investment opportunities. Different kinds of loans have different risk levels for the lender, which reflects the likelihood that the borrower will not be able to pay the loan, and the interest rate reflects those risk levels. Higher risk loans have higher interest rates, so these loans can be more profitable as long as the borrower is able to repay. Suppose you're interested in investigating the differences in interest rates to understand your potential loans better. In order to do this, you probably want to segment the interest rates by grade. Remember that grade is the classification system that involves assigning a quality score to a loan, where grade A represents the lowest risk and grade G is the riskiest. Remember to check the Downloads tab for this spreadsheet and the solution. So in order to perform this analysis, you're going to want to select the entire data set and then insert a pivot table. A pop-up menu appears. Select Insert to a new sheet. Alternatively, you could use an existing sheet for this and select the cell where you want the pivot table to be. On the right, you will get the pivot table editor. Here's where you set up the pivot table. So for rows, you're going to add the grade feature. This is the feature that you want to use for segmentation. You can see that all of the unique grades appear on the left side of the table. For columns, if you want to pivot over two features, this is the place to add it. But let's skip this for now. For values, you can add the feature that you want to aggregate. So you can click on Add and then select the interest rate. You can see after you selected the values that they're populated here in the pivot table. Hmm, this is interesting. What does an interest rate of 4786 mean for grade A? This is caused by the selection of the summarization function, which defaults to sum. So this is actually totaling the interest rates across all of the loans with grade A. This is not how you want to summarize the interest rates for each loan grade. To change this, you can change the summarize by option. In this case, you might want to select average. Now you can see that the result's updated and now you see more reasonable interest rates across the different grade categories. Let's change the format to a number so that you only get two decimal points. Be mindful that these represent percents, so you do not want to change the format to percent. As expected, grade A loans pay the lowest interest rate and it gets consistently higher for riskier grades. Again, higher risk means higher interest rate. If you accidentally close the pivot table editor, just click on the pencil button on the lower left corner of the table and you get all the options again. You can also add more values to your pivot table. Suppose you also wanted to explore the variability in the interest rate. So you can add the interest rate again, change the sum summarization function to standard deviation. This gives you a sense of the variability in the interest rates for each grade category for loans. There's not a simple pattern here, but it does appear that as you move towards riskier loan categories, the variability actually increases. This is to be expected because the interest rates are generally higher, which are often associated with higher variability. Notice that the standard deviation for grade G is actually zero. What do you think is happening here? Either every interest rate for G grade loans is the same as the average or there's only one data point. You can also add more features. So in this case, maybe you want to look at a different feature like total credit utilized. Change the summarization function to an average. You can format the results to dollars, and this represents the total amount of lended funds on the platform for each grade category. The total amount of credit utilized mostly lands in the higher quality grades. So it's interesting that if we assume there is only one loan with a grade of G, it's $288,000. So this is worth a check in the data to see what's actually going on with the G grade loans. So let's go back to the data. Here you can actually see that there's four G grade loans overall. So there's not just a single G grade loan, as you might have originally suspected. It turns out that there's four G grade loans and they all have the exact same interest rate. So it's possible that for this lower quality loans, there's a maximum interest rate and they all have this value. And then you can see that the four loans actually total up to the $288,000 you saw in the pivot table. It was pretty interesting to investigate all of these statistics segmented by grade. Excellent work with segmentation. You learned how to assemble two data sets into one, visualize the segments in your data, and calculate descriptive statistics for each one using pivot tables. Coming up, you'll take the practice assessment for this lesson. You'll also complete the two graded items for this module, including the graded assessment and the graded lab. In the lab, you'll use all the skills you learned in this module to help the Portuguese National Park Service prevent forest fires. After you've completed these items, you'll move on to the next module, probability and simulation. Once you've taken a sample of the population and described the distribution of that sample, you can apply the rules of probability and statistics to estimate features of the entire population. I'll see you in the next module to learn more.
Module 1: Foundational statistical techniques
Certificate