Learning Pandas for Data Analysis? Start Here.

Key Takeaways

in this video you're going to learn all the essential tricks to using pandas to do data manipulation and wrangling pandas is one of the most popular python libraries and the tool for doing data manipulation and now with the ability to write python code directly in Excel pandas is reaching a whole new audience with that comes a lot of hidden functionality that you wouldn't necessarily know about without fully diving into the documentation that's why I've created this video using my years of experience with this library to boil it down to just the functions that you need to know to get started so if you're a seasoned veteran or if you're just learning pandas for the first time I suggest you watch this video all the way through and then go back and re-watch any parts that weren't clear to you the first time if you learned something new make sure you like this video write any comments down below and subscribe to the channel for more content of course the first thing we need to do is import pandas in order to work with it I'm also printing out here my system info and the pandas version I'm using which is the latest at the time of recording this video in most of my videos I get at least three or four comments asking about The Interpreter that I'm using this IDE is called Jupiter lab and I have a solarized dark theme on it but I talk all about Jupiter notebooks and Jupiter lab in a complete video that you should see a link for in the description with pandas you're typically going to be working with a pandas data frame you can think of a panda's data frame as being similar to a spreadsheet where you have a data set that contains columns and rows while you can make a data frame from scratch using raw data in Python the majority of the time you're going to be reading from either a file or a database luckily pandas has a lot of different methods for reading in different file types all of these methods start with read and they include reading from a CSV file X cell files and many other file formats each read method comes with its own parameters that can be set when reading in the files typically the default settings should work pretty well but especially with read CSV which is one of the most popular file formats you have a lot of parameters to choose from this could almost be a entire video in itself but some of the main reading parameters you may want to change is changing the delimiter say if you had a tab separated file setting use columns if you only wanted to read in a subset of the columns automatically parsing dates when reading in the file and reading in large files in chunks now in this tutorial we're going to be working almost entirely with this flights data set which I have saved as a parquet file we'll read this in as DF which stands for data frame if we do a type on this DF you can see it is a panda's core data frame and similar to reading there's a lot of different ways you can write your data frame to files or to databases using the two SQL most of these writing formats typically just take in the file path where you plan to write the file and each writing method has its own set of parameters you can choose from for instance when writing to a CSV I often find myself setting index defaults so that the index is not saved when it doesn't have relevant information now once you do have your data frame there are many techniques that you may want to use to understand what the data looks like head lets you see by default the first five rows of your data frame depending on how many columns you have it may hide the middle columns when displaying this but you can override this by setting this option to display a larger maximum columns and you can see here this data set has a lot of columns which are now displayed the head command can be given any number to show that number of columns there's also a tail command which as you might expect will show the last five rows in the data frame dot sample will pull a random subset of rows from your entire data set you can provide it a number which will return that number of rows or a fraction which will provide that fraction of the entire data set because the results of dot sample are random it can be helpful to add a random State and this will help with reproducibility since every data frame consists of columns and rows we can access the columns directly by using dot columns this can be helpful if you just want to see a list of all the columns in your data frame dot index will give you the index values in the data Frame data frames have a few methods built in that help you quickly get a top-down view of what the data looks like dot info will give you information about the data frame including the size listing each column and the data type of each column and it's also really helpful at the bottom of dot info you see the memory usage of this data set for a quicker summary you can set verbose equals to false in dot info the built-in dot describe function can be run on any data frame to provide some descriptive statistics about the data including the count values mean minimum some percentiles Maximum and the standard deviation for any numeric column describe can also handle non-numeric columns for instance running on the airline will show instead the count number of unique values top occurring value and the frequency many times you'll also find yourself wanting to just pull in the shape of the data frame and you can do that by just running dot shape similarly you can take the length of the data frame which will give you the same thing as the first value in the shape which is the number of rows subsetting a data frame is something that you'll often find yourself doing and you may want to either filter down to a subset a columns or a subset of rows to subset columns you can simply provide a list within a bracket at the end of the data frame and that will subset just to those columns so here we've subsetted just the flight date airline in the origin columns and if you remember from before running df.columbs will give us a list of the entire column names so using this in combination with a slice we could for instance take the first five columns and then subset our entire data frame just to those five or something like this would give us the last five columns and depending on how comfortable you are with list comprehensions it's pretty easy to filter Down The Columns based on certain criteria for instance here I'm filtering down just to columns that have the text time in their name and then I can provide that to the data frame and return only those columns a less commonly used way of filtering columns is by using the select D types method which will return a subset of the data frames columns based on the data type you provided here you can see we've selected only data types in this data set that are integers one thing to note about selecting a single column is that if you provide just that column name you will be returned with the series of that data column as seen here if instead you prefer to have a data frame with a single column you need to make sure to include double brackets around the column name and you can see here this is a data frame to filter a data frame based on its rows you'll typically be using either eye look or look both Loke and I look allow you to access elements in the data frame based on their location however iloc uses the index location and Loke uses the names because of that you'll find yourself using Loke a lot more than I look to show as an example if we wanted to use I Look to pull this value in the data frame we'd be looking for row number one and column number three remember that indexing starts at zero so we formulate our location with with one being the row and three being the column and we return that value this also works with slices so if we wanted to pull the first five rows in the first five columns we could do it like this using I look this way we are subsetting both the columns and the rows but we could also just provide a single value value which will only filter to the row here we're filtering to row number five if we put this in a list then we'll be filtering to row number five as a data frame similarly if we wanted to filter only to a column we can use a colon to State we'd like all rows and here we are saying we just would like the First Column putting this in a list Returns the subset as a data frame if we wanted to do this exact same subsetting using Loke instead of using numerical values for their locations we would actually provide in the name of the columns or rows that we would like to filter to here we have the airline and origin column selected one of the most powerful things of using the look method is that we're able to filter not only on names but on Boolean expressions for our rows how this works is by creating an expression on one or more columns we then return a true or false series here we are looking for for where Airline equals Spirit Airlines and then passing this expression into Loke then we'll filter our data frame down to only when this Boolean expression is true as you can see the result only contains rows with Spirit Airlines we can combine Boolean expressions with ands or or statements to filter on multiple things or columns here we're filtering on the airline and also to a specific date one helpful tip to be aware of when creating Boolean Expressions is that you can easily take the inverse of the expression by simply adding a tilde before it and if we use loc to filter to this expression we've now returned all rows except for the rows with Spirit Airlines and this specific date an alternate way to query your data frame based on Boolean Expressions is by using the dot query method the dot query method takes in a string representation of the Boolean expression you wish to filter on as an example I can filter for when the departure time is greater than 11 30. that query can take in multiple Expressions as seen here we're filtering on departure time and the origin any values in the query method are assumed to be columns but you can represent strings by wrapping the value in quotes or your query expression can access an external variable by using the at symbol before the name in your query string next let's talk about summarizing data in the data frame there are a number of summarization methods that can be run on either a single column or multiple columns they include the mean value the minimum value the maximum value standard deviation variance count which will show you the number of values you can also sum all the values in the column and Dot quantile will give you the quantile values quantile can also take a list as you can see here we have the 25th and 75th quantile for the departure time any of these summarization methods can be run on multiple columns this will return a pandas Series where the index are the column names and the values are the summary value statistics if you want to run multiple statistics on various columns you can use the AG method by providing AG a list of the statistics you'd like to run pandas will return a data frame with those statistics for each of the columns you've provided it the AG method can also take a dictionary where the keys are the column names and the values are a list of the aggregations and pandas will return only the summary statistics that you put in this dictionary they're additional summarizing methods that are useful for categorical variables but these also will work on numeric values dot unique will return an array of all the unique fields in the column and unique will give the number of unique values in a column and one of the methods that I use all the time when doing data analysis is value counts which just as the name suggests will give you the number of counts for each value in that field setting normalized to True will give you a fractional value of the count within that column running value counts on multiple columns will give you the count of those column combinations because we had multiple columns selected we now have a multi-index series but if we reset this index we then have a pandas data frame with the count for every combination now let's talk about some more advanced column methods that can be used on a panda series or pandas column some of these methods can be really helpful when doing Group by operations which I'll show next they include rank which will compute the numerical data rank of one through the max value in that column there are a few different types of methods for ranking including dents which might have multiple values for the same rank or first which will give the first value in that column the lower value shift just as the name States will shift all the values in that column by the number you provided this number can be positive or negative but note that when the values are shifted at the beginning or end of this series will be empty values and you can set a fill value to fill these in if you'd like you can also run the cumulative sum over a series or data frame by using cumulative sum there's also a cumulative Max and cumulative Min rolling methods can be really handy especially with time series data with a numeric column we can use the rolling method and provide it a window period where it will look at when running our aggregation so here if I do window equals five the result here is this rolling object but now by running something like mean we are return and with the average value at a rolling window of five there's a lot of advanced features with rolling like adding the number of periods you could set Center to true if you want the rolling window to center around the value for that column another method that can be handy with numeric values is clip this will clip the numeric value to a lower or upper window depending on what you provide it if we set the lower and upper clip value for this to one thousand and two thousand we could see anything below has been clipped to 1000 and above has been clipped to two thousand Group by methods are extremely powerful when working with data frames that have categorical types by using the group by Method we can provide a single column name or multiple column names in a list here we're grouping on the airline and then provided a column or a list of columns to run aggregations on using some of those aggregations from before here we've grouped by each Airline and then computed the average departure delay for that Airline or we can use the AG function to aggregate on multiple different statistics here we're aggregating on multiple columns note that the result of this is a data frame with multi-index columns these multi-index columns can be a little bit complicated you can run the two flat index method on them which will convert it to a list of tuples but often I like to use list comprehension to rename the columns like this that way the columns for your aggregations are flattened and have names that are meaningful so far we haven't done anything to actually modified our original data set but we can create new columns very easily by performing any operation on a single column or multiple columns let's take departure time here and divide it by 60 and then assigning this to a new column name now if we look at our pandas data frame we have a new call column named departure time 2. another way of creating new columns is by using the assign method the assign method is nice because it can be chained with other operations and it actually returns a new object with all the original columns in the new one you provide it the new column name and what that column should be equal to by using the sign like this we could see that there's now a new column called departure time 3. sorting your data can be really handy when you want to sort by a specific column type using sort values and providing it a column or a list of columns we'll sort that data frame by this column so we can see here the top row has the lowest arrival delay and it will put missing values at the bottom I've subsetted The Columns to make this a little easier to see but you can also set ascending equals false which will sort the data in the reverse order note that the missing values still remain at the bottom I'm going to put a backslash here which will let me chain on another command to this which I will put reset index and drop equals true this will do the sort on this column and then reset our index values so now our lowest index value is with the highest arrival delay time there's also a sort index method which will sort your index if it's numeric next let's talk about handling missing data let's look at just this subset of columns from our data frame and we can see that sometimes the Rival delay is missing values is an a is a helpful method that will return true or false for each value in your data frame if it's missing because Boolean values for true or false are actually ones and zeros we then can run sum on this which will give us a count of all the missing values in this arrival delay column the drop n a method will drop any of these rows that contain missing values you can provide it a sub set of columns then it will only drop the rows where missing values occur in these columns fill in a will fill any of those missing values with whatever value provided here you can see that all the missing values are now replaced with negative 999. we can use some of our summarization techniques like finding the average value for a column in conjunction with fill in a to then fill it with that average value next let's talk about combining data to show these techniques let's create two new data frames which we'll use in further examples here we're creating df1 and df2 for two of the different airlines in the data set whenever we create a new data set like this that we want to be independent from our original data set we should run dot copy now if we wanted to stack these two data frames on top of each other we can use Panda's concat method and provide it a list of the data frames that we would like to concat Panda's concat takes an axis which defaults to zero and that will stack the two data frames on top of each other if we set access to one it'll try to concatenate based on the index values which are not unique here but just to show you as an example we'll reset the indexes here and then we'll concatenate with an axis is one we can see here how the different concat methods worked if we look at the shape of our input data frames in the concatenated data frames this first concatenation stack the data frames on top of each other so it has the same number of columns but it's combined the rows concatenating with an axis of one has merged on the similar indexes it has the same number of rows of the larger data frame and it has doubled the number of columns concatenating with the axis of one can be a little tricky because now we have multiple columns with the same name usually instead of concatenating like this we'd want to merge our data based on some similar columns to show merging examples let's create two new data frames using what we've learned so far the first one will have the average departure delay for each Airline on each date in the second data frame will have the average arrival time for each Airline date we can run dot merge off of the First Data frame and provide it the second data frame this will take any similar columns between the two data sets and combine on those we can also provide it a different merging type like left right inner which is the default or outer another way to do the same thing is just to use the pd.merge method which takes a left and a right data frame both methods have an optional on variable and you can provide a subset of columns that it should merge on note that if we provide a specific column to merge on but there are other similarly named columns in each data frame it will automatically put a suffix X and Y to the new column names we can change this default Behavior by providing its specific suffixes here you can see the suffixes Now show us that this came from the departure data frame and this came from the rifle it's easy to use the on method when our columns from data frame 1 and 2 are the same but if they're different we can actually use a left on and right on method and this will work even if our column names from data frame 1 and 2 are different thanks for watching hopefully this tutorial was helpful for getting you started with data manipu relation using pandas there are much more features than what I showed here but it would make the video way too long so I hope that this was a good introduction for you let me know in the comments if there's anything specific you'd like me to cover in the future