Data Manipulation: Logic and Subsetting

All right. In this video we're going to continue on where we left off after merging our two state energy ranking data sets, the natural gas and total energy. And we're going to solve this issue of these repeated rows for different states through logical statements and sub setting. So, let's get to it. So the issue that we're having here is that we've got 18 states who have their full names here that don't have any data because they didn't produce natural gas and they weren't in the total energy data set. And we've got their two letter abbreviations coming from the total energy data set because they have the total energy produced data. So, what we want to do is get rid of basically all these rows that have the full names. You don't have any data attached to them. How do we do this? Well, we can do this using logical statements and filtering or subsetting our data frame. So, let's make a new data frame df2. And we'll start with df, this is the output of the outer merge done above in the previous video, and we're going to make use of our pd notnull function here. Because what we want to do is basically we want to keep all the rows that either have natural gas data, so do not have NaN in the natural gas column, or do not have NaN in the total energy column, or do not have any in in either column. In other words, we want to get rid of those rows that have any, and in both columns. So we will reference our natural gas column here, calling the data frame after the merge. So df square brackets natural gas. And let's just see how this notnull function works on the natural gas column alone. So let's go ahead and run this. And we'll print df2. Let me see how we have our columns there, but we only have 33 states represented here.

So these are our 33 states that produce natural gas from the natural gas data set. So we want to also include those states that had some total energy production even though they didn't have natural gas. So how do we do this? We say we want to keep those rows who have values in natural gas column, or those rows that have values or notnull in the total energy column. So, let's go ahead and run this. So we run this again. We saw the same columns. We scroll down and we now have all 50 states plus DC. I know this goes to 68 but that's just because the index, the indices here, have been broken. We've gotten rid of some of those rows in the middle that had some indices. If we want to reset that, we can just say df2 dot reset index, and we'll get our sequential numbering back. So we see now that these indices go down to 50. They start at zero, go down to 50, so there's 51. Again 50 states plus DC.

Furthermore, you'll note that we have our old indices as the second column here. We might want to get rid of that. We also might want to get rid of some other things here. For instance, you know these rankings don't really need them, we're just interested in these quantities. So if we want to filter by columns we can, we have some handy tools that are disposal here. So we can access values using in particular this look function for getting multiple columns or rows at the same time. So, for example, if we want to select one, or more than one column, let's make a new data frame, df3, now from our df dot loc. And we'll use colon to say all rows. So we're going to get all rows and then separated by a comma here we'll put in whatever columns we want so if we only want to keep say for example, state and total energy and natural gas we can enter them as a list. So we can close them in square brackets, separate these quoted values, text values, and commas here, df3. Let's see what we get. So, here's our data frame state, total energy, natural gas. Note that these columns are now in the order that I specified. So before we had natural gas coming before energy and I've switched that around just by the order that I've stated their names in here. And we've effectively gotten rid of those rankings and that old index here. So, now we have a much cleaner data set.

Furthermore, we can for example, with this look function select one or more rows. Conversely, we're not going to make use of this later on so we'll just say for example cases in the df3.loc we want to say, for example, the first five rows give you something like that specifies the rows. We need to say what columns we want. If we want to say all columns you could do that, the colon cases. And there we go, we have the first five rows. Note you can mix this up, you can do a combination of selecting different rows and different columns too if we wanted to, but I think you get the idea here. And then just last but not least, like to exemplify how to get, or easily display, I should say, the top energy producers here. So say for example, one of the top five total energy producers, we've got this handy function and largest where we say and largest five for the first five and then whatever variable we want the largest values from. And so here we see that the largest total energy producers are in, number one Texas, number two, Pennsylvania, number three, Wyoming, Oklahoma, and West Virginia, on down. So those are some handy tools to to get started with processing data changing data types, and in this video, subsetting the data into a clean data product. Okay, thank you.