Speed Up Your Pandas Dataframes

Key Takeaways

so if you're watching this video you probably use the python package pandas which is extremely powerful for working with data sets of various size in this video we're going to be talking about efficient memory use in pandas there are many reasons to make your pandas data frames memory efficient it can speed up the process that it takes to run code it reduces the amount of memory that you're using so you can use that memory for other things and depending on how you write your data to disk or to a database it can make it more efficient when you store it hi my name is rob i make videos about coding in python and machine learning and in this video we're going to talk about efficient memory use in pandas please consider liking and subscribing if you enjoy all right let's get on to it okay so here we are in a jupiter notebook and we are going to start out by importing the same things we do all the time in other videos import pandas and as pd import numpy as np and now we're gonna create our data we're gonna start out by making some fictitious data let's just pretend this data is for a sports team and we're gonna create a pandas data frame to start so we're gonna start out with a column that will be the position of the players each uh each row will just be a random value we'll use numpy random choice to give a random value either left middle or right and it's going to be of the size size then we're going to add another column called age this is going to be a random integer between 1 and 50. we'll make the team so the different teams could be red blue yellow or green we'll make a value for if they win or not so that's going to be np random choice yes or no and lastly let's make another column called prob for maybe the probability this is going to be a random uniform value between zero and one great so if we look at the head of this data set we can see that we have float integer as as prob yes or no the age position all of this looks good and the shape should be 1000 1000 rows let's just quickly wrap this in a function and this will return the data frame now we're going to want to test this on a fairly large example data set so let's create our fake data set that's uh 1 million rows now and if i create this and do df.info we're going to see some information about the data set we could see that in memory this data set is taking 38.1 megabytes of ram now imagine as this data gets bigger and bigger let's say it's 3.8 gigabytes or 3.8 terabytes the size that we have it in our data frame format is really going to matter and not only that we want to be working with our data set in a way that's as fast as possible so let's show as an example some group by aggregation so if we group by the team and the position and then we take the rank of the age we could add this as an age rank column similarly let's do something for the the probability rank and i need to rename that team so it's correct so after running this we have new columns for the rank and age group by team and position and the rank and probability group by team and position let's get an idea for how long this takes to run by running our time it now this will run several different loops of this each line of code and then tell us how long each of them take on average and the standard deviation so you can see this first group by aggregation took 333 milliseconds the next one took 430. so what it really comes down to is we're going to make our data frame efficient by the d types that we cast each column as and right now it's not very efficient and we're going to start by working with this position column so if you remember the position column can either be middle left or right and right now it is just an object of type string so each of these values are a string type but a much more efficient way to store this is as a categorical type so we're going to take it and take position and we're going to test it cast it as type category and overwrite that column let's do this from a clean data set using our get data set function and run our info on it to see what the data set size is so just by casting this data type as a category we reduce the size from 38 megabytes to 31 megabytes and the data itself has hasn't changed at all let's go ahead and do the same casting on our team because that we know is a value that's a category type even though it's as a string right now now keep in mind casting as a category wouldn't work if you have every row being a unique value in this column and it truly is a string but in this case both of these we know to be category types and we can get our data frame already down to 24.8 megabytes in size all right next we're going to deal with the age column the age column is stored as a integer and by default pandas will read in integer values as a n64 value and 64 just basically stands for how much memory or how many bits per row is it going to use to store this value and you can get away with changing this n64 to lower values depending on the range of your integer values and just for an example i'm going to show you int downcasting value range so depending on what we downcast this to we need to make sure that our values are between these ranges in order for us to not lose any data and we know that our age value has a max value of 49 and a minimum value of 1. so that could fall within this negative 128 to 127 range and we could actually down cast this as type int 8 and not lose any information and if we run df info on our data set now we can see that it's even smaller 18 megabytes as opposed to 24 with our casted categories and we're going to do something very similar to the probability column this is a type float 64. and for floats it's a little bit different because it really impacts the precision of how many decimal places that our data can store so if we look here between the probability and i show it cast it as a 16. it does change the values a little bit so we're gonna actually make this a float 32 to make sure that we have at least the same values here to six decimal places and if we look at the size of the data frame now we're down to 14.3 megabytes as upload as opposed to b4 so this is downcasting floats so let's just take everything we've done so far and put it all together and call a function let's call this function set d types for the data frame in this we'll cast those two columns as categories like we did before we'll cast the age as a int 8 and we'll cast the probability as a float oh and there's one more thing i forgot about to to mention before and that's going to be casting pool types or true false columns if you remember we have this column called win and the values in it are text fields string values that say either yes or no however in this case we can actually make this column the d type that uses the least amount of memory out of them all and that's a bool it's just a simple zero or one true or false and the way we can cast this is by taking this win strict string value and we can map a dictionary onto this so when the value is yes it'll be true and when the value is no it'll be false and you can see here that the d type of this column is now a pool and to show the info size on this it's down to seven megabytes from what it was before so a quarter of the size of what it was before we did all this memory reduction now to run the same test we did before with our down casting and specific d type selection we're just going to run this this cell before on a data set so first we're going to run it on our original data set this is doing all the group group by and ranking across the whole data set which is a million rows and then we're going to do the exact same thing but after setting our d types by running the set d types function so we can see that pretty much across the board we've made things run faster and this is not necessarily even linear so the even larger that you make the data the bigger this gap might be and it really depends on your data set so let's run the same test with an absolutely huge data frame so we're going to up the ante with the size by just running this as 10 times as large as we've been testing with so far okay it's finally done and you can see that these took a while to run each different timing and we're going to do the exact same thing after casting the d types pull the same data set and then cast the d types and run these exact same functions and see how fast it goes okay and that's done running and you can see pretty much across the board that we've saved time as well as memory by casting these d types on the larger data set now addition to the reduced memory use and the time saving for computation depending on how you save the data to disk you could save disk space by casting these d types in a future video i intend to talk about saving data frames to disk using parquet format and those would be much smaller if you cast your d types correctly thanks for taking the time to watch this video i hope you enjoyed it remember to like and subscribe and i'll see you all in the next one