In this video, I'll show you how to iterate over
rows in a pandas DataFrame,
and then we're going to talk about why you probably
don't want to do that,
and I'm going to show you a better way to
do it.
I've got a set of tweets where people have posted
the score from the game Wordle,
and if we run this and pull in this file
we can pull up the variable explorer in Visual Studio
Code and we can take a look at this DataFrame.
Here we can see that it has about 17,000
rows in it,
so let's look at this DataFrame and I'll show
you what I'm trying to do and why
I need to iterate over this DataFrame.
Here we have in each row
the posting that the person made to Twitter. If we
mouse over you can see this is their Wordle score
right there where it says 4/6 4 is the number of
attempts out of 6. That was 4 out of 6.
This one is 3 out of 6.
This one is 3 out of 6 so we need
to do is find the slash and then find the
number right before that for every single row.
So the most straightforward way to do this is to
just loop over the DataFrame itself so we can
say for index row in and then the DataFrame
itself and then we want to use the
iterrows method here. And then we can just do
our logic right here.
So what we want to do is find the index
of that slash and then we want to select the
item right before that which will be the number of
attempts which we're going to assign to the attempts variable
there. Let's close this. And then lastly,
we're just going to add another column to the DataFrame
called "attempts" and put that item there.
That number of attempts, and then we can just print
out the mean,
calculating the mean directly on the DataFrame.
So let's go ahead and run this and see how
this works.
All right, so the mean score for all of the
attempts in this file is 3.9 attempts and it took
about 3.3 seconds.
As you can see in Visual Studio Code,
it tells you the execution time.
Now this is fine when you have small sets of
data, there's no problem iterating over a DataFrame,
but usually you're working with massive sources of data and
I just so happen to have one of those.
The other file that I have is a very large
file. It's called tweetsBig,
so let's go ahead and load this into Visual Studio Code
and we'll take a look at the Variable Explorer
again and we can see that our DataFrame is
quite a bit bigger now.
154,000 rows in this one.
Let's do the same thing using the for loop
and just iterating over the DataFrame and watch what
happens.
All right, so that took about 47.5 seconds.
As you can see here from the execution time,
which is a long time and you can see we
got a mean of about four tries.
So apparently Wordle takes about four attempts just on average.
But we can speed this up dramatically using something called
vectorization.
Now vectorization is a fancy word,
but all it means is there are certain features of
Python And pandas that are optimized to let you work
with arrays without having to iterate over them.
And they do that by having code that is optimized
to take full advantage of your CPU,
which can do a lot of different things at one
time. You don't have to process one row at a time,
you can process multiple rows at one time.
So there are a lot of ways to do this.
I'm going to show you how to do it with
something called a Series Apply,
so let's take a look at how to do that.
So in our notebook here,
let's add another cell and then the first thing that
we need is
a function that we can apply to the DataFrame
to do this calculation,
and you can see that we're going to pass in
this tweet text series as a pandas series.
Now all this is is it's the tweet text column
and all of the rows,
so that's all we're passing in is just that column
of data.
OK, so the next thing we want to do is
really run exactly the same logic that we ran in
the for loop,
but we want to change so that we have this
tweet_text_series instead of this row["tweetText"].
So let's go ahead and pull that out here.
Go ahead and make that change and then the last
thing we want to do here is instead of assigning
it to the DataFrame because modifying a DataFrame
on the fly as you're looping over it is not
a great idea,
we're going to return the attempts
value, which will return as a series.
So in other words, we're passing in the column of
tweet text.
We're pulling out the attempts,
and we're returning a series of the attempts which we're
going to append on the DataFrame as a new
column here.
So how do we call this?
Well, first we need to take the DataFrame,
and we need to tell it that we're adding a
new column called attempts,
and we're going to set that equal to the return
value of this function,
which we're going to call like this.
So we're going to take this data frame here,
and we're going to pull off just the tweet
teext field here just to tweet text column rather and
then we're going to call apply on this OK.
And then when we apply what we're going to pass
in, is this function that we've called count_attempts_apply OK,
and that's going to call the function and apply it
to this column of data.
And then lastly, of course we just want to compute
the mean just like we did before.
So let's go ahead and bring that line down.
Now the other one ran in 47.5 seconds.
How long do you think this one's going to take
to run?
Any guesses? Let's find out.
.2 seconds, 200 milliseconds. Wow and you can see we
get the exact same scores so
you know it's right and just so you believe me,
let's just completely restart the kernel.
We'll erase all variables. Pull up the Variable Explorer so
you can see everything is gone.
There are no more variables.
OK, then we'll load in the
big data set here into the DataFrame and then
let's run it again and see how long it takes
after our CSV loads in,
which is actually slower than iterating over the CSV.
Here we go. .1 seconds it's getting faster.
So remember, you can use the iterrows method on
a DataFrame to iterate over each row,
but that's slow, so you want to avoid doing that
and instead check out vectorized methods like a Series Apply,
and there's even faster ways to do it.
In fact, did I do it correctly?
How could I make this code faster?
Let us know in the comments below.
Hey, if you like this video like this video and
subscribe,
check out our other videos on data science,
Python, Jupyter Notebooks and Visual Studio Code.
Happy coding.