Exploring Your Dataset With Pandas and Python

Key Takeaways

hello and welcome in this course you will learn how to use the python package pandas to discover interesting insights about data my name is douglas starnes and i'll be your host as you slice and dice data to query clean and more before getting started there are a few prerequisites that you should have installed first is python 3. the current version of python as of recording is python 3.9 but earlier versions will work just fine i wouldn't go back any further than 3.5 or 3.6 but 3.7 and up will be okay i strongly recommend that if you don't have python on your machine to get it with the anaconda distribution this is a one-stop shop for all of your data science and python needs you can download the free open source version of anaconda from www.anaconda.com products slash individual click the download button and select an installer for your operating system after the installation is complete you will be able to open a terminal or command prompt and everything will be ready to go anaconda will pre-configure an environment which includes pandas but anaconda also does more in the data science community is a tool called jupiter notebook this is an interactive computing tool that basically stores the output of a python session in a web page i'll be using it for the demo in this course and i strongly suggest you do too as jupiter notebook is a good habit to develop if you want to work in data science to start a jupyter notebook server simply run the command jupyter notebook at the prompt the default web browser on the system will open to this page if it doesn't simply copy and paste this url from the output of the jupyter notebook server be sure to copy the entire token as this is required for security purposes in case the server were exposed on the public web click the new button and then select python 3 under notebook this will create a new jupyter notebook that understands python in the first cell enter the following python code to import the pandas package press shift enter to execute the cell now this cell won't have any output but if you execute this python code in the next cell you'll see the version of the pandas package that was installed of course you don't have to use anaconda you could create a virtual environment using thepython.org installation and then install pandas and jupyter notebook using pip basically to follow along in this course you will need just python and pandas jupyter notebook and anaconda just make it easier you could also run the demo on google colab a free service for hosting jupyter notebooks from google i'll include a link to a jupyter notebook on google colab that will have all of the demo code ready to run in the next lesson you'll see how to take advantage of pandas and jupiter notebook to load and explore a dataset let's load up a dataset here is the url for a csv or comma separated file containing basketball data from the website 538 you can use another package requests to download that file request is a package that wraps the url live api provided by the python standard library it makes networking tasks with http much easier in fact the author calls it http for humans if you've installed anaconda request is included in the default environment and if not you can install it with pip first import requests then call the get function and pass it the download url store the response check the status code of the response if it is 200 then everything should be good to go open a file and write the content of the response to it now the contents of the file are stored locally excellent it's time to load the csv file into pandas go ahead and import pandas notice that the pandas package is aliased as pd this is not a requirement but it is often how pandas is imported you'll be making significant use of the pandas package and while shortening the package name by four letters might not seem like a lot right now over time it will reduce the amount that you need to type data set can be loaded from the csc file use the function csv and pass it the path of the csv file and look at the type of nba so what is this data frame you'll learn more about it later in the course but for now think of a data frame as a way to store tabular data that is rows and columns in fact you can see how many rows are in the data frame by getting its length and you can see there are 126 314 rows the rows and columns can be found in the shape of nba the shape attribute is a tuple the first value is the number of rows and the second value is the number of columns this means there are 23 columns in the data set to see the first five rows get the head of nba if you wanted to see 10 rows you could pass 10 to head the default number is 5 and you can see one of the benefits of using jupiter notebook with pandas the notebook is displayed in a web page and it takes advantage of rich formatting using html css and in some cases interactivity with javascript the column names are bold and the rows are zebra striped so they are easier to distinguish but where did the column names come from go back to the tab with the directory listing you should see the csv file click on it to open it notice that the first row of the file contains the column names also referred to as the header row by default the read csv function will assume the first row of the csv file to be the column names something else interesting about this data frame is that not all of the columns are displayed the columns in the middle have been omitted and an ellipse is used as a placeholder to save space you can force pandas to show all of the columns by setting the maximum number of columns also notice that some of the numeric columns are showing up with 6 decimal places fix the number of decimal places to 2 with this option now get the last 5 rows of the data frame with the tail function you can see pandas has applied the formatting also you can get a specific number of rows using tail the same as with head to get the last 10 rows pass the value 10 to the function tail in the next lesson you'll start to explore your data using the statistics methods supplied by the data frame start off by calling the info method on the nba data frame this reveals some interesting data about the data set first it lists all 23 columns for each one it provides the number of non-null values and the data type so game order is an integer and game id is an object more on that in a second at the bottom you can see that of the 23 columns 6 are floats 7 are integers and 10 are objects also none of the columns have null values except for notes so what is this object notice that some of the values in the data frame are strings in pandas the raw values in a data frame are stored using numpy data types there is no string data type in numpy so it uses the generic object going further pandas can compute statistics about the data frame with the describe method here you can see that the average number of points scored in a game for this data set is 102 and that the highest scoring game had 186 points but for some of the columns it doesn't make sense to take these stats for example the average year is not going to be useful although you can see that the year range is from 1947 to 2015. and notice that the stats are computed only for the numeric columns the objects were omitted pandas is a great tool for exploratory data analysis this means looking around the data set and navigating it to answer questions take a look at the number of games played by each team the value counts method will count the number of times each value occurs in a particular column team id in this example so the team bos played the most games with 5997 and the team sds played the fewest with 11. do the same with the franchise id column the franchise lakers played 6024 games and the franchise huskies played 60. now when the team franchise lakers is mentioned most people will think of the los angeles lakers in this data set the team id for the los angeles lakers is lal and the team lal only has 5078 games so who are the other 1000 games played by this can be done in several steps that can be expressed with a single line of code first you want to find all of the rows where the franchise id is lakers this will simply return a series of bools you will learn more about series later in the course but for now think of it as a list with an index the values indicate if that row had a value of lakers in the franchise id column you can now use those bools to filter the data frame with the loc attribute you'll also learn more about loc later in the course but notice the number of rows returned there are six thousand twenty four rows returned and six thousand twenty four values with lakers in the franchise id column now you don't want all of the columns just the team id and while you can already see that there is more than one team associated with the lakers franchise use the value counts method to count them again the lal team the los angeles lakers played 5078 games but there was also a team called the minnesota lakers that played 946 games for a total of 6024 games played by the lakers franchise it's unlikely you have heard of the minnesota lakers and here is why get all the rows that have a team id of mnl that's the id for the minnesota lakers and then get only the date game column which is the date that the game was played as you can see it's been a while since the minnesota lakers have played basketball but just how long intuitively you would just get the most recent date or maximum value and that's stored in the date game column the problem is that the dates are stored as strings or objects in the data frame this causes some issues first here is the code to get the maximum value from the date game column with a team id of mnl this code works and it works correctly it returns the maximum value in the column and the maximum value in the column is the string four slash nine slash one nine five nine however strings and dates are interpreted differently by python and pandas does not implicitly cast a string in date format to a date however it provides a helper function to do just that the pd.two datetime function will convert a column of strings into dates assuming the strings are valid date formats so that you can compare the strings and dates store the dates in a new column creating a new column on a data frame is as simple as assigning to the column name now if you run the same code again but on the date played column it returns a date of march 26 1960. however since the string representation of march 26 1960 which would be three slash 26-1960 precedes the string four slash nine slash one nine five nine the latter is incorrectly returned as the latest date in the date game column also notice that if you call nba.info again the date played column uses the numpy datetime 64 data type try one more exercise you can easily find the total number of points scored by the boston celtics by getting only rows where the team id is bos and the sum of the pts column the total number of points scored is 626 484 across the team history but what was the average number of points per year first get the dates of the games played by the boston celtics and you already know how to do this you're only interested in the years so you can apply a lambda function to each date the apply function will pass each value to the lambda and then all you need to do is extract the year attribute to remove the duplicates call the unique method take the length and you're almost finished to get the total number of points again and store it you can just modify the cell that calls the sub method and now you can calculate the average points per year and it comes up to about 9000 points each year now you just saw another powerful feature of the jupiter notebook you are able to modify a cell without retyping it this lets you experiment and iterate rapidly and that is a big part of exploratory data analysis so keep your eyes open for ways to use this and save yourself some trouble in the next lesson you'll dive under the hood and see how panda's data frames are assembled until now you've been looking at data frames in terms of rows and columns and while it makes sense to think of them intuitively using rows and columns the internal structure is a little different what you've been thinking of as a column so far is in reality another pandas data structure called a series you briefly saw a series in the previous lesson but it's time to take a closer look the series consists of two parts values and identifiers the values are a sequence similar to a list in python the identifiers are mapped to the values the collection of identifiers is called the index it's quite simple to create a series in pandas the values and index can be accessed with the values and index attributes the values are returned as a numpy array the index is a special type of range index with an upper and lower bound and there are other types of indexes that you'll see later on you can also explicitly declare the index with the index keyword argument this time the index is just an index type keep in mind that the range index is still valid as well every series will keep a numeric index by default you may have noticed some similarities with the series you have created and the primitive python collection types for example the revenue series is similar to a python list the city revenues series is like a python dictionary as you can use the index to retrieve the associated values the dictionary can also be used as the data for a series and the series also supports the keys method and the end keyword notice that the series can be used to create a data frame the keys of the dictionary are used for the column names notice that the series used to create the data frame must have the same index here the series share the identifiers tokyo and amsterdam toronto exists in the city's revenue but not city employee count and that is why the value for toronto in the employee count column is nan look at the axes of the data frame this is a list of two index objects the first is the rows and the second is the columns keep this in mind as you go through the course the axis of 0 is the row axis and the axis of 1 is the column axis try this exercise to test your knowledge of data frame internals the nba data frame has a column where the number of points scored in the game by a team with the column spelled poi nts or was it shortened to pts you can check using the in keyword recall that a data frame has two axes the first being rows and second being columns you will want to use the columns axis this shows that pts is the correct column and also you could have used the keys method to get the columns now that you understand how series and data frames work together in the next lesson you'll see how to drill down into the data that they hold for you