14. Dataset tutorial with Pandas 👽(UFO example)👽
Oct 8, 2023
Most scientists and engineers are very familiar with Microsoft Excel for managing tabular datasets like comma separated values (.csv). However, excel is very limited in what it can do and does not do well with large datasets either. Instead, engineers should learn to work with the Pandas library in python. Pandas is a powerful tool for managing, sorting, grouping, describing, and all around working with data. Here's a tutorial performed on a dataset of UFO sightings!
All right, in this video, we're going to dive into pandas.
Now, when we hear pandas, pandas data frames,
you should be thinking of these as an alternative
to Excel spreadsheets, right?
Excel can do a lot of things, and we're really used to it,
but it has some major limitations, too.
For one thing, think about when you're using huge amounts of data,
data with 100,000 entries.
You've probably experienced this. Excel gets really, really slow.
There's also some fundamental limitations that it can't do.
When it comes to working with large amounts of data,
you want to learn pandas, right?
So let's go ahead and start.
We're going to jump over here to our spider,
and the first thing we need to do is we need to import pandas.
So we're going to import pandas as pd.
That's just the standard shortcut.
We call it pd instead of typing out pandas each time.
That's just what you do, okay?
We're also going to grab some other useful code, right?
So we're going to grab numpy in case we need to use it.
We're going to grab matplotlib pyplot
so we can make a couple basic plots,
and then we're going to grab this interesting thing.
We're going to import profile report, right?
I'll show you what that does in just a moment, okay?
So now that we've got the libraries that we need,
let's go ahead and grab our data.
So we're going to create a data frame.
We're going to call it data frame df, right?
To do this, we're going to use a pandas command function.
So we're going to do pandas.readcsv.
Now we have to tell it where we're going to read it from.
Now this one, I've actually downloaded from Kaggle,
so I have it in the data for exercises.
It's called this ufo.csv.
Now this is pretty interesting.
It's all the UFO sightings
that people have allegedly had over time,
so it's a pretty interesting data set.
Opening up Excel, you can see what sort of data it has in it.
Okay, so here's what the data looks like
if you were to open this CSV in Excel.
It's got several different columns,
you know, eight or nine different columns
with things like the date, the city, the state,
the shape of the UFO, how long they saw it in seconds
versus hours and minutes, comments,
and then the latitude and longitude, right?
So that's the sort of information that's available here.
So let's go ahead and grab that.
All right.
So this is in the data for exercises folder,
and the file name was ufo.csv.
So let's go ahead and run it so far.
All right.
Right off the bat, we now have our data frame,
which has the following information in it.
It's got exactly what we saw in the Excel spreadsheet, okay?
All right.
So when I look at this data,
one of the first things I notice in the Variable Explorer
is that we don't need all of these columns.
So one of the things you might want to do in Pandas
is add or delete columns.
So for example, they have this column here
that shows duration in seconds,
and then they have the same thing,
duration in hours or minutes,
but it has these funky, you know,
the units are changing from minutes to hours.
That's not very useful.
Why don't we just keep the one with seconds and drop this one?
So there's a couple different ways that we could drop that.
We could drop it by calling the name of the column,
or we could do it by index.
Coming over here, this is the 0th position, right?
So 0, 1, 2, 3, 4, 5, 6.
So if we drop the one at the 6th position,
that would be one way to drop it.
Let's try that.
So df equals df.drop.
We're going to use the drop method.
We're going to drop the one at columns 6, right?
So we're saying that df, we're going to pull up the columns,
and one at 6 is the one that we're going to drop.
Now the last thing we need to do
is we need to tell it how to drop it.
Are we dropping along the row?
No, we're dropping along the column,
so the axis needs to be 1, not 0.
So axis is equal to 1.
Let's go ahead and run that command now.
And when we pull this up, we should have a new data frame
which doesn't have that same column that we saw before.
Yeah, sure enough, now we have the duration in seconds,
but we don't have the other column,
so we've gone ahead and dropped it.
Great.
Now we could also drop it a different way.
We could say that df equals df.drop,
and then we could just give it the name of that column,
which was duration, duration, one space,
and then it was hours, minutes, things like that.
So this should work as well.
We just need to tell it to drop it along axis 1.
So if we comment this one out
by putting the pound sign in front of it
and run this again,
we should be able to get the same result as before
where we've dropped that column.
Let's take a look and make sure that it worked.
Yeah, sure enough, we have the correct duration,
and we dropped the other one.
So you can drop things by either of these approaches.
They're both totally fine.
Now that we've shown you how to delete a column,
let's go ahead and take a look at our data.
Let's take a look at it by using the describe method.
Describe is useful because it gives us things like the count,
the mean, the standard deviation,
and then the quartiles,
the 25th percentile, 50th percentile, 75th percentile,
and the maximum.
So let me show you what I mean.
So we're going to take our df,
and then we're going to give it a column,
such as, say, longitude.
Okay?
So now we're looking at the longitude column,
and we can use the describe method.
So let's go ahead and run that.
If we run just that one,
here's what it pulls up.
It says that, okay, there are 80,000 entries, right?
The averages are given here.
The different quartiles are given.
Pretty interesting.
Now what about this one?
What if we do the same thing, but we do it with latitude?
You would expect something pretty similar,
but notice how it's different.
Check this out.
Before it gave us the mean, the standard deviation,
these quartiles, but this time it doesn't,
and yet we're still talking about a number, right?
The latitude and longitude should be numbers,
and so something's wrong here.
Well, right away we see that this one was telling us
that these were floats,
and these ones it's saying it's an object.
What's happening is this is actually a mixture.
Just like in Excel, you can type in a number as numbers,
or you could type in numbers as a string.
There's a mixture of numbers and strings here,
and that's what's getting us wrong on this.
There's a really cool tool that Pandas has
with this profile report
that helps us catch things like this.
Let's show you how to use it.
Okay, I've just copied the code over
from when I did it last time.
You can save the profile,
so we're going to run the profile report
by making a copy of our data frame,
so df.copy, right?
Copy makes sure that it makes a copy of it
so we don't modify the original.
We're going to name it whatever we want,
so this gives it a title.
We're calling it the Pandas Profiling Report
of the UFO dataset.
We're going to save that as profile,
and then we're going to print that as an HTML.
Let's go ahead and run that.
What this does is it gives us
much more descriptive statistics
than just the describe method is able to do.
That took a minute for it to run, but now it's done,
and we can go ahead and open this up.
It saved it to the same folder
where I've been working out of,
and it called it profilereport.html.
When I open this up,
it's going to open it up in my browser,
and it has just a ton of information.
It says here's all the information
that's in that Pandas data frame.
It's telling me for the different variables
that are out there.
It says which ones are high cardinality,
meaning that those are categorical,
like cities or state.
Very high cardinality
because there's not that many states.
There's 50 states in the United States.
It also has a lot of really cool things like warnings.
If you come here,
it'll show you where there are missing entries.
Here's the warnings it's giving us.
It says, hey, under country,
12% of the countries have missing values,
so it's just not a number there.
That's really important.
Maybe you need to know the country name,
and so this would tell us
that we need to either fix those
or drop them or something.
This just gives you much more information
than the simple describe method.
It takes a little bit longer to run,
but a very, very cool tool
and one you should be aware of.
I'm going to go ahead and comment these out.
We're not going to be using that right now.
You just hit Control-1.
You highlight it and then hit Control-1,
and that will comment the section out.
Let's show you some other cool things
that this can do.
Let's say we wanted to rearrange the columns.
You wanted to change their order.
You could do that pretty easily.
Here's how you go about it.
You could come right up here,
and you open up your data frame,
and you'll notice that it has the names of these things.
You could save those as a list.
You could literally type them out by hand
if you wanted to,
and then you could rearrange them.
There's some easier ways to do this, though.
Let's go ahead and save this.
Let's make a new variable called columns.
We're going to make a list
of df.columns.values.
We're asking it to go to the data frame,
find the columns, grab those values,
and then we're going to make a list out of those
and save that into a variable
that we're calling calls.
When we run that,
we should now get a variable up here
which is named calls,
and sure enough,
this is a list that has all of these things.
We could actually pull this up over here.
We could say our calls list,
and it would give us all the values.
Here's all those.
You could literally just grab these things
and come over here,
and you could define a new variable
if you wanted to.
You could say calls to,
a different list of columns,
and you could paste that in there,
but you could change the order.
For example, let's say you wanted to move
latitude and longitude up
because those are ones that we care about.
Well, you can grab those,
and let's say we want to put them
right after country.
Great.
We could do that.
You can move those there,
make sure that your commas are all right.
Now we have a new list,
columns2.
When we run this,
we're going to get a new variable
which is just another list,
but it's been reordered.
It has a different order.
Now we can come over here
and we can create a new data frame.
Another pandas data frame
is going to be df
is now going to be equal to df,
and we're going to put calls2 there.
It's smart enough to know
that we're taking our old version of the data frame,
and all we're doing
is we're feeding it new names of the columns,
and so it's going to reorder it.
Sure enough, when we open this up now,
we'll see that the order has now been updated.
Again, we had date, city, state,
and country of the UFO sightings,
and now we've put latitude and longitude right there.
Otherwise, the information is retained.
We've just reordered them.
That's how you can reorder data.
There's other ways to do it.
There are more eloquent than this,
but this does the job.
Okay, what do we do with the problem
where we had that mixture of strings and text with latitude?
Remember when we came over here to our latitude
and we ran that column?
It told us that it wasn't just floats.
It was a mixture,
and so it couldn't give us things like the max and the min.
It had to give us other information.
Well, we can convert these.
We can force them to be numbers.
So let's go ahead and do that.
We're going to come down here.
We're going to do data frame.
All right, in the latitude column,
we're now going to have it equal to a pandas.
We're going to do to numeric.
That's a special method that we can use, right,
in the pandas where we're going to convert something to numeric.
So what are we going to convert to numeric?
We're going to use round brackets.
We're going to say we're going to take the data frame
in the latitude column,
and we're going to force that to be numeric.
Now, there are some additional arguments
that this to numeric function can ask for.
For example, it says errors.
What do I do when I run into an error?
What is it going to do when it runs into something
that it can't force to a numeric value?
Is it just going to raise an error and say,
hey, there's a problem, or are you going to skip it, or what?
Let's go ahead and have errors equal coerce.
What that does is it forces it to be a number.
If it can't, then it drops it, okay?
So let's go ahead and run this now.
When we run it,
we now should have converted all of our latitudes to numbers.
So when we come up and run this line by hitting F9,
it should now give us the numbers
and things that we're looking for.
It's giving us the mean, the standard, the minimum, and so forth.
So we've done a good job of that.
Let's see if there's anything else that needs to be fixed.
Let's come up here and do data frame,
and let's do duration seconds.
So the column for time, how long they saw the UFO in seconds.
Let's go ahead and run describe on that
and make sure that it's working as well.
And we get the same problem.
It looks like our duration also has a mixture of integers and strings.
So let's go ahead and fix that.
We're going to grab this line of code,
and we're just going to repeat it.
But this time, instead of latitude,
it's going to be our duration in seconds.
And again, if you wanted to rename this, you could.
We're just going to leave it as is.
It's okay.
Okay, so now we will run that,
and we should have fixed both of those.
All right, the next thing I want to show you
is now that we've forced these things to be numbers,
because they ought to be,
we can do cool things like sorting them.
Let's go ahead and sort them.
We're going to say our data frame is equal to
data frame.sort underscore values.
So this method allows us to sort things.
So we can sort by the column that we choose.
We're going to sort things by duration, right?
Okay, so if we run this now,
we're going to get our exact same data frame as before,
except it's going to be ranked from very short times
when they saw things up to larger ones, right?
Okay, sure enough, under duration over here,
you can see things that took like a millisecond
all the way up to, you know, some much, much higher numbers.
Now, something I think that's really cool
is you can, using this sort values method,
you can sort by more than one thing at a time.
So let's say we want to first sort by seconds,
but then we want to sort by latitude, okay?
So, great, we can just add another argument here,
and we can put latitude.
And what it's going to do is sort first by duration,
then latitude.
So let's go ahead and run it.
Because we're sorting by two things,
it's now a list.
We have to give it the square brackets
so it knows that it's a list coming in.
Sure enough, it's still sorting first by duration,
but then, look at all these ones.
Going from all the way down here,
these all have the same duration,
but it's sorting by latitude now.
It's going from 41 to 42, 44, 45,
all the way up to 60, right?
So that is a really powerful tool
to be able to sort things in a built-in way.
Think about trying to do this in Excel.
We can easily grab a single column in Excel
and sort things,
but trying to have different things
sort categorically like that is not nearly as simple.
So we're already doing things that Excel can't do.
Now, what if you try and sort something
that isn't a number?
For example, in our data frame,
we had the shape of the object.
Was it a circle?
Was it a triangle?
Well, we can sort those as well.
So let's take this exact same thing as before.
Let's grab this,
and let's sort first by shape
and then by latitude.
So it's going to sort by shape.
When it sorts by shape,
since that's not a number,
what it's going to do is sort them alphabetically.
So let's go ahead and run it.
We open this up.
We'll find that it is now sorted by shape.
Let's find our shape column right here.
First thing is changed and then changing.
The next thing we asked it to sort by was latitude.
So if we open that up,
sure enough, we see that the latitude is increasing
from negative 45 all the way up
until it's going to reach whatever its maximum value is.
And again, you could do multiple arguments.
If two wasn't enough, you could keep on going.
So this is a really great way to organize your data.
All right, another thing that you might want to do
is create a new data frame
as a subset of your previous data frame.
So what we're going to do
is we're going to make a data frame 2.
Now, it's very important.
You can't just say that data frame 2
equals your previous data frame df
because this is object-oriented programming.
And so the object, this group of data,
exists as an object
and they just have a label, df, pointing to that object.
And if we make another label, df2,
it still points to the same object.
So if I modify the second one, df2,
and I think that I've made its own individual copy,
I'm still actually modifying the original.
So if you actually want to make a copy,
then you need to do this copy command.
So df2 is equal to a copy of our original data frame.
We want to do that.
Now, in data frame 2, let's assume in data frame 2
we want to only capture instances of UFO sightings
above a certain latitude, say 45 degrees.
Well, we can do that.
We can say that df2 equals df2.loc.
So that's a method where you're going to locate
under certain conditions.
Now, the condition is when the df2 latitude,
wherever that is greater than 42,
we want to capture that.
So let's go ahead and run that.
And we should see that df2
is going to be a new pandas data frame.
And it's already smaller.
Look, the original was 80,000 entries.
This one's only 24,000 entries.
And when we look at it, sure enough,
all these latitude values are over 42,000.
Okay?
So that would be one way to do it.
There's other ways, though.
For example, let's say we want to do
the exact same thing,
but we also wanted to only keep instances
where the duration was over 10,000 seconds, right?
Let's do this a different way.
Let's first by creating a list of Booleans
that satisfy the conditions, right?
So we're going to create one list of Booleans.
We'll call this Boolean for latitude.
Okay?
And this is equal to df2 in the latitude column
where it is greater than 42.
So what this is going to return
if we run this line of code,
let's go ahead and run it.
If you look at this,
this is now going to provide a series, right?
And if you open it,
it's going to be a bunch of trues and falses
that represent where these things were happening at.
And now it keeps the original index
that was stored in the pandas data frame
that we had previously, right?
Now we're going to do the exact same thing,
but this time it's not going to be Boolean for latitude.
It's going to be for duration.
So let's rename this variable for duration.
It's going to be not based off of latitude,
but for duration in seconds, that column.
And it's going to be any time
that it was longer than 10,000 seconds.
Okay?
Now that we have those two,
let's use these to create a new variable
by dropping any row where that wasn't true.
So we're going to say that df2 equals df2.drop.
So that's a method that allows you to drop things.
You can drop either by column or by row.
We're going to drop by row.
So what are we going to drop?
We're going to drop by df2.location,
and we're going to do so
where the Boolean latitude is, right?
In other words, where that shows up as true,
we're going to drop those.
So it's going to drop actually everything over 42.
Okay?
Now we need to tell it how we're going to drop things.
So we're going to put axis equals zero.
That means it's going to drop along the row.
Okay.
That didn't work because we forgot to put index here.
It's basically saying drop it at these indexes,
at the index in this Boolean latitude
where that was set to true.
Now let's go ahead and run that.
Our original data set was 80,000.
We know that 24,000 of those were above 42,
and now we've dropped all of those that were above 42.
So we're left with the 56,000 or so that are less than 42.
So if we open that up,
we better not see any with latitude over 42.
Let's just do a quick double check.
Yeah, sure enough.
All of our latitude values are less than 42.
Let's rank it just to be sure.
Yeah, it goes up to 42 but not above.
Okay?
Now let's go ahead and do the same thing
and drop all the ones that had shorter durations
than 10,000 seconds.
So we're going to grab this exact same line of code.
We're going to copy it in,
and now instead of dropping it based off of Boolean of latitude,
we're going to drop it off of the Boolean duration.
Okay?
We run that,
and that's going to shrink our data set even further.
It goes from 56,000.
It's going to be now smaller.
It's down to 55,000.
Okay?
So that is how we go about...
Again, it dropped everything over 10,000.
It kept everything under 10,000.
If we wanted to,
we could actually print the shape of our new thing here.
So we're going to call this our cleaned data frame
where we've changed things based off of conditions.
We're going to print the cleaned shape,
and then we're going to use these curly brackets.
By putting F in front of this, what we print,
it allows us to do a really slick way of printing things
where you can just use curly brackets
and then put an actual variable in here,
df2.shape.
It's a much slicker way to do this.
So that allows us to now print the shape of this thing,
and our cleaned data frame shape is 55,075,
just like it is above.
Great.
Well, what else can we do with pandas that's awesome?
Something really great that it can do is value counts, right?
So let's come down here and show you how value counts work.
Value counts is a cool method that it can do
that's counting things based off of how many values
have the same value.
So for example, there's different shapes of UFOs.
So let's go ahead and count
how many of the different shapes there are, right?
We'll call it X, right?
And this will be equal to df2.
Well, let's go back to our original data frame, df,
and then we're going to do this on the shapes column,
and then we're going to do value underscore counts.
So let's go ahead and run that.
We should get a new variable, X,
which is going to tell us how many there are of each type.
So it doesn't like shapes.
I think it's actually called shape, not shapes.
So let's run that again.
We should get our now variable X popping up.
Oh, okay.
Value counts, you have to put the close parentheses after it.
Open and close parentheses to make sure it's a method.
So let's run that one more time.
Now we should get our variable popping up.
Sure enough, here's X,
and it's going to show us by shape
how many instances there are.
So by far, when people saw UFOs,
calling it a light as the shape was the most common.
16,000 entries, right?
Then triangle, then circle, then fireball,
and it goes down from there,
all the way down to weird things like a hexagon, okay?
All right, so that's value counts, a great tool.
Well, we could take that,
and we could actually plot with it, right?
We could do, let's grab this line from above.
We're going to take those value counts,
and then we're going to do dot plot,
and then dot bar.
So this uses a function built into Pandas,
which allows us to do a bar plot.
So let's go ahead and plot that.
Then we come over here once it's done.
Under the plot window,
when you hit that,
it's going to show us, sure enough,
that same information that we saw before,
but now it's plotted in a bar plot,
which is useful, okay?
What else can we do?
There's another really cool function,
or method called group by, okay?
Group by allows you to group things first,
and then do subsequent actions.
For example, let's say we want to group things by shape,
and then we want to see the average value of
latitude, longitude, duration.
Great, we can do that.
Here's how we're going to do it.
We're going to say,
we're going to do data frame dot group by.
Now we have to tell it what to group it by,
so we're going to use these parentheses,
and we're going to tell it group by shape,
and then we want it to give us the mean.
Let's store this as a variable so we can explore it.
We'll just call it y for a moment.
Come over here to our variable explorer
once the code has a chance to run,
and here's our new variable.
Y is equal to,
notice that the index,
it's automatically taken shape,
which we grouped it by,
and made that as the new index, right?
It did that automatically.
Then any column where it could actually calculate a mean,
it did so.
Things like date or comment,
it can't calculate a mean of,
so it didn't bother,
but these numerical columns,
it went ahead and calculated a mean.
You could have done a maximum, though,
or anything else,
so this is a really great way
to get quantitative statistics by groups in your data,
so a very powerful tool.
All right.
What if we want to do some plotting with our data, right?
In your data set,
maybe you want to plot, say,
UFO sightings by latitude and longitude.
Let's try that.
So over here,
we're going to do plt.
Remember, up here,
we brought in matplotlib dot pyplot as plt,
so that's going to open up the pyplot library for us,
so we're going to do plt,
and let's just do a regular plot first.
When you plot things,
again, if you haven't done this before,
you can see that it will tell you what to expect
where you can click on the documentation and see it.
What I know is it's going to ask for things like x and y,
so for x,
let's give it df at the,
let's see,
longitude goes along the x-axis of the earth
and latitude goes up in the vertical direction,
so let's give it longitude first,
and then we'll plot for y-axis,
for y-axis,
df latitude.
Okay?
So let's just try that very first.
We're not giving it any other arguments.
We need to make sure we put a closing bracket over here.
Let's go ahead and just plot that first.
So when we do that,
under plots,
let's go ahead and take a look at it.
To see this,
we're going to need to delete our previous plot.
So over here,
let's just comment this out for a moment.
Okay?
So now we've commented out our previous plot,
so now let's go ahead and run this most recent plot,
and we should be able to see it.
So what do we see?
We're seeing longitude,
like it ought to be on the x-axis,
and latitude on the y-axis,
but it's showing lines.
That's not very helpful.
We don't need to connect the dots here.
We just want to see points.
So let's come down here to plot,
and let's say line style,
and again,
you'll learn these keywords by working with it a little bit,
but you can also look them up.
We're going to say line style is equal to none.
We don't want any line style.
Let's go ahead and give it a marker,
is equal to just a circular marker,
and let's do marker size
equal to, say, five,
and let's run that now.
When it creates the plot,
all of a sudden we see something
that looks a little bit like the world, right,
which it ought to,
because we're plotting latitude versus longitude
and UFO sightings,
and people think they see UFOs all the time,
but not all people are the same.
It turns out Americans and Central Americans
and Europeans really think that they see a lot of UFOs,
but Africans, South Americans, China,
not so much, right?
Pretty cool, okay?
So that's how you can go about plotting with your data
if you want to plot different aspects of it.
A whole other video on plotting is coming soon.
Okay, last thing I want to show you is
let's assume that we want to do
a more complicated subsection of our data.
Let's say we want to grab all of the data
that corresponds to my state, Utah, right?
So there'd be a couple ways to do this.
One thing you could do is you could just say,
all right, grab everywhere where in the state
it says Utah.
Let's try that first, right?
So let's call this,
we'll call this the Utah list,
or the Utah data frame,
Utah DF.
Okay, the way that we're going to find
the subset of our data where Utah is the state
is we're going to do DF.loc,
so we're going to locate the things
under a certain condition.
The condition that we're going to locate based off of
is if in the DF state column,
if that's equal to UT, for Utah,
is equal to equal to,
since we're doing a comparison,
we need the two equal signs,
and this should return a subset of,
sure enough over here in the Utah data frame,
the only ones where the state was equal to UT.
So that would be one way to do it.
Let's do a more complicated way.
What if we wanted to do it based off of
latitude and longitude?
Turns out that Utah is one of these states
that looks like this,
where this line right here,
and this line,
this line,
and that line
were all based off of latitude and longitude.
In terms of latitude,
this is 37,
that's 41,
and that's 42.
In terms of longitude,
this is negative 114,
this one right there is negative 111,
and this one is negative 109.
So let's go ahead and look at our latitude and longitude
and see if we can create the exact same list
by just looking at latitude and longitude
doing some Boolean operations on it.
Okay?
Should be straightforward.
Come over here.
Okay?
First thing we need to do
is we need to grab the latitude and longitude
for each one of these,
and we're going to grab the index too
to keep track of these things.
So we're going to have to do list comprehension.
So we're going to do for i,
that's going to be for our index,
and then latitude,
and then longitude.
We want to assign those three things in,
because we're doing list comprehension,
for those things that are in.
Now, when we do this,
because we're going to unpack three things
to three different variables,
because we're going to assign three things,
we need to have a tuple, right?
Remember tuples have this really awesome property
where you can unpack them
if there's three values in a tuple
and you assign them to three variables,
it automatically unpacks them.
So to create a tuple,
we're going to use the zip command,
and then we're going to zip together
three columns that we care about.
We're going to zip together df.index, right?
That'll be for the index.
We're going to zip together dflatitude,
and we're going to zip together dflongitude.
Okay?
Now, that is list comprehension.
It's going to go through,
and for each one of these values,
it's going to go row by row through them,
grab those three things,
the index, the latitude, and the longitude.
Now that we have the latitude and the longitude,
we can go ahead and apply some logic, right?
Let's say if...
Let's do latitude first, right?
We're going to do two conditions here.
If the latitude is greater than 37
and the latitude is less than 41,
what does that mean?
That means that we're in this region down here,
this big square block of Utah.
That means that we need the longitude
to be between negative 114 and negative 109.
So we're going to say,
and this other condition
where the longitude is greater than negative 114
and the longitude is less than negative 109,
then the condition of that can be that the data frame,
we're going to use the at method,
so at index i in the column Utah,
which wasn't even created yet.
We're creating a new column.
We're calling it Utah,
meaning is it a Utah or not,
and we can set that equal to true.
So that will catch some of the Utah UFO sightings,
but not all of them.
Now we need to do an else if, right?
So else if,
now we're going to have another one
that's very similar to this one,
but it's slightly different.
This is going to capture the top part of Utah.
So now if the latitude is greater than 41,
but less than 42,
and the longitude goes from negative 114,
but it doesn't go out to 109.
It only goes to 111 this time, okay?
Then we get the exact same thing,
that we're going to preserve that same value
at that same index,
and we're going to say that it's equal to true, right?
So, great.
And then the last thing we need is an else, right?
If it's not that case, right,
then we want the opposite.
We want it to be set to false,
so it's not including Utah, right?
So we're going to say false.
Okay.
When we use the at command,
that just says where it's located,
and now we can set the value out over here.
My mistake.
So with the at,
df.at,
at the position in that column,
we have to set it equal to that.
Let's try this again.
Close that.
Okay.
Let's try that again.
So we've created our new DataFrame from before,
which is just like the other one,
except we should now have a column
that's going to put it at the very end,
where it's called Utah,
and it says whether it's true or false.
So now let's go ahead and create a final DataFrame
with just these Utah entries.
To do that, we'll just call it DataFrame3.
This is equal to DataFrame.copy,
so we'll make a copy of our initial DataFrame,
and then we're going to say that DataFrame3
is equal to DataFrame3.drop.
Now, what are we going to drop?
We're going to drop df.loc.
Everywhere, we're going to drop where df.Utah.
It's going through, and it's checking for it.
Yeah, sure enough.
So check it out.
We made a DataFrame of Utah UFO sightings
a couple different ways.
One is we just had it check for the state column
to see if it was Utah.
But if that was missing for some reason,
if there was a bunch of missing values there,
but they really did occur in Utah,
but the state column had missing values,
that would have some errors.
Meanwhile, we could also do it
by looking at our DataFrame in this approach,
by looking at latitude and longitude.
Now, interestingly, these don't exactly match up.
733 versus 743.
I'm guessing what that came down to
is probably in some instances,
the latitude and longitude
was actually technically outside of the Utah boundaries,
but they called it a Utah sighting
because maybe it was close to the border or something.
So you'd have to take a look at those and see.
Okay, let's do one last thing with Pandas DataFrames.
Let's say you want to look for something
on whether it contains a certain text
and then take an action.
For example, let's say you're working for the government
and you don't like all these people
talking about alien sightings.
So you're going to go through this data set
and everywhere that it mentions UFO or alien
or something like that,
let's say you want to redact it.
Okay, to do that,
we're going to start with doing df.location.
All right, so we're going to look through
at these specific locations
of anywhere where it has df
in the comments column.string.contains.
Then we do the method for contains.
We're going to check for the text UFO.
We're going to throw out,
if there's nothing there,
if there's not an entry,
we don't want to call an error,
so we're just going to throw out
using na="false",
anything where there wasn't a comment.
And if there was the word UFO,
then we're going to modify the comment column
to say xxxredacted.
So let's go ahead and try that.
And now, when you open this up,
anywhere where it actually used the word UFO before,
we've now replaced it with this xxxredacted.
But you can do whatever you want there.
In fact, you can actually do multiple things.
If you wanted to modify two columns,
like let's say if it had the word UFO,
you wanted to get rid of the latitude and longitude,
you could get rid of those as well.
So let's go ahead and try that.
So instead of just changing the comments,
let's go ahead and make this a list of things
that we're going to change
by putting square brackets around it.
So we're going to get rid of the latitude, let's say.
So get rid of our caps lock.
So we're going to also change the latitude column.
It's going to say xxxredacted in that one,
and it's going to say xxxpositionhidden
in the other column, right, in the latitude column.
So now let's go ahead and run that,
see what we get.
When we open this up,
again, anywhere where it said the word UFO
in the comments originally,
we should see, sure enough, xxxxredacted in the comments,
and we now see positionhidden in the latitude.
So you can do some really powerful things here
that go far beyond what you can do in Excel.
I hope that this was a primer
on some of the cool things you can do
by working with pandas.
We're going to get a chance to work a lot with them
this semester.

