- [Instructor] I think we can all agree that life doesn't always work out perfectly, right? Your prom may have been a disappointment, your boss may not praise you as efusively and frequently as you would like, and maybe every now and then, you get a flat tire, and when you do, it's probably raining out, right? But, the thing is, I believe, and I think evidence has shown me personally, that life usually does generally kind of work out okay and for the best. However, despite this rosy unicorn-and-rainbow-filled outlook, unfortunately, in data visualization, data never comes in the form that you need it in.
You will always, almost always, have to transform your data, convert your data, in order to make it usable for you to do the work that you need to do. So, there are a bunch of different things that you tend to have to do, and essentially, I find these five to be the most frequent, and I'm going to go through them one-by-one, starting off with indexes and ratios. So, what we're looking at here is data from the Bureau of Labor and Statistics, and what we have is, essentially, at the top, we have minimum wage data.
So, this is literally the minimum wage for every year from 1980 until 2013. We also have the poverty line. We have the price of gas and bread and eggs and electricity, as well as the consumer price index, which is just sort of an aggregated value of the cost of living, essentially the price of all kinds of things all mixed together, and then, an alternative to CPI, which essentially is just like the CPI, but minus some key numbers. Long story short, this is all about how much stuff can I buy with minimum wage, you know, what is minimum wage, that was sort of my idea behind collecting this type of data together.
And so, I looked at this data initially, and I said, "Hey, let me just sort of explore it a little bit," and I'm now going to just sort of jump to the right, Cmd right arrow, and then, Cmd-Shift-left arrow in order to select that entire row, and then, I can quickly generate a chart. This is what I did in Explore your Data. And I can see, essentially, what minimum wage was in 1980, and how it went up and sort of stayed the same, and went up and sort of stayed the same, all the way up until 2013. And, I can do the same thing with the price of gas. Go to the right, Cmd-Shift-left arrow to select that row, insert chart, and I'll do the same thing with eggs, why not? Take the whole row, insert a chart, and I get some stuff.
I can see these things, and I can see that minimum wage went up. I can see gas sort of generally stay the same, and then, sort of spiked up and spiked down and spiked back up, and eggs have also sort of generally gone up with some ups and downs along the way. So, this tells me something about the prices of these things, but I don't know what it means in the context of, "Well, is this good or bad? "Is it easy to live on minimum wage," etcetera. So, this wasn't enough to look at the data, so the next thing I did, if you remember in the data exploration video, is, I converted these numbers into ratios, right? I said, "How much gas can I buy on minimum wage?" And I did that by dividing the two numbers, right? That's what a ratio is.
A ratio of minimum wage to gas. And so, by dividing minimum wage by the price of gas for every single year, I got these numbers. So, I could buy 2.79 gallons of gas for minimum wage, you know, one hour of minimum wage work, in 1980. And when I chart that, it's a very different line, right? I could buy more and more and more gas, until I could I buy less and less and less gas, except for a spike, and then, less again, versus eggs, which sort of went up and down, but actually ended up slightly higher in 2013 compared to 1980.
So, I did that for all of my different numbers, so these ratios were very helpful, to look at these numbers, until I ran with this issue of scale, and then, I said, "Okay, how am I "going to solve the scale issue?" And I tried spark lines, is one way. And then, I also looked at creating an index, and indexes are a great way at looking at values like this, and I'm actually going to sort of delete the index that I precreated here and recreate it for you, and just show you how to do this, because, you know, you may find yourself having to create indexes fairly frequently when you look at data like this, and so, it's a good skill to know how to do.
So, to create an index, and I explained this briefly in the other video, but I'll explain it in more detail now, you select the value you want to index, and then, you divide by the maximum value of all the values that you're comparing it against. So, in this case, we can actually use an Excel formula called MAX. And so, if I say, "Okay, I want to select the maximum value "of the same exact row." I can just click on that row again, after typing in MAX, parenthesis, and then, right-click, sorry, Shift-right click, to select the entire range, and you can see up here, it said MAX B14 to AI14.
Finish the parenthesis, hit Enter, and now, I have that index value over here. Now, watch what happens if I try to click and drag this. It might do what I want it to do, but it might not, and the reason is, if I double click this guy, you'll see that now, it's taking the maximum value of all these, the same range, but it's moved over by one, because I haven't locked the cells, and this is one of those tricks to Excel. If you're not a heavy Excel user, you may or may not be aware of it. But when you create a formula like this, if you're going to copy-paste or click or drag, you have to realize that, I want, when I click and drag, I want this number to change, the first number, because I always want it be, you know, in 1980, I want to divide this number by whatever, but in 1981, I want to divide this number.
So, it's okay if my B14 changes to, in this case, C14. However, I don't want the maximum range to change, because I always want to divide by the maximum range from B14 all the way to AI14, and the way I can do that is simply by changing this, so I add a little dollar sign. It's always Column B, and it's always column AI. Now, I didn't add dollar signs in front of the numbers, and you'll see why in a second. So now, if I click and drag, now, you'll see, if I double-click on it, I'm dividing this number, the blue, by the same denominator all along the way.
So, I'm just going to click and drag this guy all the way to the right-hand edge of the screen here, and now, I'm going to go back to the left, I'm going to select this entire row, and I'm going to click and drag all the way down, six rows, and this is where those dollar signs that I did not add come into play, because essentially, by not putting dollar signs in front of the B19, it allowed that value to also shift down, just like I was letting it shift to the right, and by not putting dollar signs in front of the 14s up here, it allowed, when I clicked and dragged down, for it to change row by row by row.
It's still using the same column values, because the dollar signs in front of those guys, but by not putting a dollar sign in front of the 19, or in this case, the 14, when it was up here, it allowed it to sort of pull down. Definitely, this is not an Excel class, so if you don't understand what I'm talking about, go take an Excel class, and you can understand what cell locking is all about, but it's a very important part of the work you will do in Excel, so you don't make weird mistakes, cause it is easy to make mistakes in Excel. So, ratios allow us to see things about the data, and then, indexes allow us to compare the numbers within a much tighter scale, because now, all of these numbers have been converted to a number between zero and one.
So now, my charts all look exactly the same, the individual charts, as they did before, although the numbers on the axes are different. But now, when I look at a chart with them all together, they're all on the same scale. They're all between zero and one, so I can see the difference between the price of bread in red, versus electricity, all in the same scale, and so, it's much easier to make comparisons between them. So, you know, spark lines can do something similar, but you may find yourself using ratios and indexes quite frequently, so I definitely recommend that you take a look at, you know, really get familiar with how to do this type of work in Excel, or whatever tool you're using.
- Describe the process by which individuals’ interests are incorporated into data visualizations.
- Differentiate the use of the Ws in data visualization.
- Explain techniques involved in defining your narrative when visualizing data.
- Identify the factors that make data visualizations relatable to an audience’s interests and needs.
- Review the appropriate use of charts in data visualizations.
- Define the process involved in applying interactivity to data visualizations.