Explore your data: Visual exploration

- [Instructor] Most of the projects I work on, my clients do the data analysis, but sometimes I have to explore the data and do analysis myself. And I'm not a data analyst. I'm not an expert in this field. But you know it's always a good idea to know how to explore your data to some degree and you really have to think of yourself as an explorer. You really are trying to discover things in the data that you don't know going into it and that really sort of requires an explorer's mindset. Your mind is open. You're wandering around just looking and trying to find new things.

And there are all kinds of tools out in the universe to do data analysis and data exploration these days. Tableau is a huge tool that many many people are using across Enterprise and individuals. Tools like Plotly, sort of cloud-based data analysis tools. You know real statisticians and data scientists are using tools like Python or R. There are software tools like Gephi that anyone can download and run to do network analysis. And all kind of mapping tools like Carto which is an amazing open source online mapping tool.

So the tools are out there. There's a million and one tools. You can find them in a whole bunch of different ways. I just strongly recommend you think at the beginning of your data exploration as being really about how to think about data and leveraging tools that maybe you already know. And of course the tool that most of us have used at least once if not for many many years is Excel. Or if you're on a Mac, maybe you've used the Apple equivalent which is called Numbers. And so we're going to explore this dataset in front of us and what this is is data from the Bureau of Labor and Statistics, and we're looking at minimum wage data from 1980 up until 2003.

And I've also gone and found additional data to supplement what we're looking at to try to find an interesting story line. So in addition to this row which has minimum wage data, I also have data about the poverty line in each of those same years, the price of gas, the price of bread, the price of eggs, the price of electricity, and also the CPI which is the Consumer Price Index. It's sort of like an indexed average number of the cost of living, essentially the cost of sort of everything. And CPI minus which is sort of minus some key figures that they sometimes subtract from the CPI.

But long story short, if I'm going to explore this dataset, I'll just usually jump in and start trying things. And so first thing I'll do is I'll select the minimum wage row, and I'll do command shift right arrow or control shift right arrow on a PC to select the entire row. And then I'm going to say go to insert in Excel, and I'm just going to say insert a line chart. Let's just see what a line chart looks like of this data. And so I'm just going to sort of drag this over to the left so it's all in the same area over here.

And I can see that minimum wage has gone up and up and up, right? Essentially Congress will sort of pass a change in the minimum wage and then it stays the same for a long time, and then it jumps up and stays the same, jumps up, etc. So this line is interesting, but I don't know anything beyond what the shape is. I don't know what it means although I can start to make some guesses about it. So I'm going to do the same thing, but I'm going to look at let's say the price of gas next. And so I'm actually going to start on the right hand side and command shift left arrow so just so I end up in the left side of the tab here so I can actually put the charts in the same spot.

So I'm going to select that row once again, command shift left arrow since I start on the right. Go to Insert, drop in another line chart over here and boom I have a line chart. So I can see the price of gas has behaved very differently over time. I can see that it's actually stayed kind of the same for much of the time period, and then it sort of went up for you know about 10 or so years there, dropped down, went back up, and has sort of come back down again in 2013 is the dataset that I have here.

So let's do the same thing. Let's look at the price of let's say eggs. And I say insert line chart. And what do I see here? I see again sort of you know it's own set of information here. The price stayed pretty stable, went up, came back down a little bit. Let's do the last one. Let's look at the price of electricity out of curiosity. Actually let me do the CPI. So if I go to the CPI and I select that row and I say Insert chart.

This one's going to look very different because if you think about it, what I'm looking at now is the overall cost of living when I take the price of literally everything in the economy pretty much. And so yeah of course, this says what inflation is. I can see the price of everything just sort of rising steadily. So this is interesting. I can see stuff here, but I have no idea what it means other than the basic conclusions that I've just sort of explained. So another way to explore this is to look at things in a little bit more of a sort of a structured, maybe strategic way.

And one way to do that, especially when thinking about something like minimum wage, is to think of it about the minimum wage not as a dollar value, but as a purchasing power value, right. So in other words, how much gas can I buy with minimum wage? And so the way I do that is I just created a ratio. I just literally did a formula. So if I double click on it, you can see what I did is I took B2 which is a minimum wage and divided that in this case by B4 which is the price of gas.

And so then I did that for every single column. So for 1980, I could buy 2.79 gallons of gas for minimum wage. For 1981, it was 2.63, etc., etc. And I did the same thing for the price of bread, eggs, electricity, and all of my items here. And I just charted each one of them. So I can see that minimum wage could buy me more and more and more gas until eventually it was less and less and less gas with some spikes along the way. Bread has also sort of pretty much steadily gone down with a couple of minor spikes.

Eggs interestingly has sort of gone up and down and up and down, went way up at one point, went way down. And unlike bread and gas, I can actually buy a tiny bit more eggs, at least in 2013 dollars, than I could in 1980 dollars. So charting these things tells me something interesting. I'm beginning to get to something pretty interesting. But I get into trouble when I try to do a line chart of all of the data points all at the same time. And so this is the line chart that I generated to do that and as you can see, the reason it's problematic is that I have essentially all of my elements down here.

CPI, price of gas, eggs, etc. But then electricity is way up here just because the scale is completely different. So if I scroll back up to my data here, the price of electricity, you know minimum wage to electricity, I could buy 58 units, whatever is being measured here on minimum wage versus two units of gas, six units of bread, etc. So the numbers are just so different that this chart of all of them together is just really hard to read. So instead what I do is I generated spark lines and spark lines are kind of a cool feature in Excel, and I'll just show you how to do that real quick even though I'm not really here to teach you Excel.

If I select the entire dataset, and I can't use the labels, I have to just use the numbers here. And I command shift right arrow and then command shift down arrow, I get the entire range of data, the whole sort of table of data. And then I can go up to Insert and I can choose over here Spark lines and I'm going to do a Line spark lines. And so now it knows what data I want to select. Then I just have to select six rows to place it into because it always has to be the same number of rows, and once I say okay, I get these little spark lines.

Now if I scroll over and zoom way in on these, spark lines take the whole scale problem out of it because essentially it's like they're all on the same scale. So I can see in this case that the numbers sort of went up and then came back down. In this case they just sort of went steadily down. And down here I have two lines that look pretty much the same, and as you can imagine, these are my CPI scores, my CPI values. So what does this tell me? It tells me that on minimum wage compared to the entire consumer price index, my ability to purchase against all things in the economy, has sort of steadily gone down, went up a little bit here, and then back down.

So I haven't really done deep analysis here, but sometimes just creating visualizations, looking at data using different chart forms, in this case a bunch of line charts for the most part, has really started to reveal some trends in the data. And so next time, I'm going to explore other ways of looking at data in Excel.