Welcome back to KnowledgeCity's course on using Excel for data analysis. I'm Cliff Brozo. I'm your instructor. And in today's lesson, we're going to talk about finding data sets so we can actually do some data analysis. Let's take a look at the best place to find these data sets. One of the most comprehensive locations for data sets is at a site called kaggle.com, and kaggle.com features data sets for just about whatever you're looking for. And it's actually fun to go and see what kind of data sets are out there. On the left hand side of the screen when you go to kaggle.com, and yes, you do have to register, but it is free, we can click on the word data sets. And it will bring us to all of the data sets that are located on Kaggle. There are some data sets that are trending, and as you look through them, see if you see something that does interest you. There are some popular data sets. There are some recently viewed data sets once you've been on the site, movies and TV show data sets, clothing and accessories, economics, NLP, which deals with neurolinguistics, some data sets on cancer, some data sets on COVID. And if you don't like what you see, you can explore all of the public data sets that are out there. When I click here, I get pages and pages, in fact, 152,000 different data sets. As I go through, I can see there are some that I might be interested. There are some that might not interest me. As I scroll and scroll and scroll, I can go and find a data set that I like. As you can see, there are at least nine pages. If I pick one in the middle, I can see that they're not in any kind of order. I can always type in something that I'm looking for if I do know what I'm looking for. And again as you go through them, these data sets are very varied. Here's one that kind of interests me, Walmart information. Let's take a look at what's in the Walmart data set. When I click on it, it gives me a description of what is in this particular data set, sales data from 45 stores of Walmart. They're facing a challenge, unforeseen demands, and they run out of stock of items. Walmart runs promotional markdowns throughout the year. They precede prominent holidays, the four largest of which are the Super Bowl, Labor Day, Thanksgiving, and Christmas. The weeks including those holidays are weighted five times higher than the evaluation of non-holiday weeks. Again, in order to understand what you're dealing with, you really have to read through the description. As I scroll down, it starts to tell me about the information that's in here. Gives me all of the field names. It tells me that it's showing me 10 of 17 columns. I can expand that to select all the columns and apply that filter. Get the same kind of information, but now I can scroll back and forth to see the type of data that is in this data set. As I scroll down further, it gives me some activity statistics about how many times people have downloaded it and how many times people have viewed it. I'm going to download this data set and take a look at what's in it. Click on the download button, and it opens up a file for me, and here is the Walmart cleaned data set. When I open the data set, I can see I have all of the data, and the first thing to do is to familiarize yourself with how many records are you dealing with. I'll come over to the right side and go all the way down to see that I've got 42,000 rows. That's a pretty decent amount of data. I go up to the beginning, and there are a couple of things that I'd like to do before I actually start looking at the data. Number one is I take row one, and I freeze the panes. This is in the view tab, and I freeze the panes, and I say freeze the top row. What this does for me is as I scroll down, I still have my headings. I also would like to wrap the text, so I go to my home tab and click on wrap text. And that gives me a little bit more of a view of what these columns represent. In looking at the data, I can see that I have store numbers, dates, whether or not it's a holiday, what department it's in, what weekly sales, the temperature of the day, the fuel price of the day, the five different markdown categories, a consumer price index, unemployment rate, type, and size, and if you don't remember what those things are, you can always go back to Kaggle and find out. In this case, the type represents a category of stores, and the size is the square footage of a particular store. Now it's time to come up with some sort of hypotheses to say based on the data that I have in front of me, what can I infer from the data that's available? In thinking about it, I'd like to know if temperature, the outside temperature, has any effect on daily sales. Will people go shopping when it's freezing cold? Will people go shopping when it's boiling hot? Let's see if we can find out. Once again, I'm going to insert a PivotTable. I make sure I'm somewhere in this table. Insert the PivotTable. It knows what my table is. I click on OK, and here are my PivotTable fields. I'm interested in the temperature, and I'm also interested in the weekly sales, and that's going to give me all of my different temperatures as well as the sales for that week. Next, I want to test my hypotheses and see if it matters based on the temperature. First thing I'll do is I'll take my temperature, and I will sort that data, go to my home tab. I wanna sort it from largest to smallest, and apparently the hottest day was just over 100 degrees. Now I'd like to figure out, do people shop when it's 100 degrees? When I look at all these numbers, I see that it's really hard to find out whether or not there's any kind of pattern. I'm going to apply some conditional formatting to my sum of weekly sales that will visually show me when people shop. I click on the entire column B. That's the column that I want to format. I choose conditional formatting, and I'm going to go into my top and bottom rules and say, "Give me the top 10%." I wanna figure out what the top 10% of sales are, so I go to conditional formatting, my top and bottom rules, choose top 10%, and format it so that it is the color I like. I'm gonna choose green. I click OK, and right away, I don't see anything. Before I get alarmed, I start scrolling down, and it's not until the temperature is 91 degrees do I have sales that are in the top 10. What that tells me is that people are not shopping when it is super hot because only one of these numbers, and I keep on scrolling down, I get just above 90 degrees. There's the second one. I keep scrolling down, 89 degrees. So out of 112 different temperatures ranging from 89 or 90 degrees and up, only three times were there large numbers of sales. I keep scrolling down and scrolling down and scrolling down. I jump down to almost 88 degrees, and I have another one. 87 degrees, I have another one. That's only five. 87 degrees. What this is showing me is that not a lot of people shop when it's super hot. As I scroll down further through the data set, I'll grab my scroll bar here, and as I get into the more temperate temperatures, I will have more and more data that is in the top 10% of sales. This is just one way that I can take a data set and pick a hypothesis and then prove whether my guess is going to be right or wrong. Now you know where to find data sets and what to do with them once you have one. I'll see you next time.
