If real world data-sets contain numeric, texts, alpha-numeric, time-stamps, and various other unstructured data types, then how does one store, retrieve and easily manipulate these multidimensional data-sets? The answer is a data science library like Pandas! Pandas is a powerful data analysis toolkit with high-performance and easy-to-use data structures. Unlike Excel and SQL, it carries a host of useful tools, methods and other functionalities that set it apart when it comes to row-wise and column-wise data manipulations. We will visit these functionalities in this tutorial.
In [7]: from IPython.display import Imagefrom IPython.core.display import HTMLImage(url="https://raw.githubusercontent.com/Back2Basics/Pandas-Talks/master/Python-datasci.jpg", width=400, height=300)Out[7]: <IPython.core.display.Image object>
In this article, we will traverse through the usefulness of pandas: starting with data exploration, followed by data analysis and then concluding with data visualization. Often, pandas is used for prepping data for machine learning and so we will take a quick dip into that at the very end.
You are encouraged to follow along with the tutorial and play around with Pandas, trying various things and making sure you're getting the hang of it. Let's get started!
Nearly everything that follows will require a basic understanding of the fundamental data structures: the Series, DataFrame, and Index.
Importing Pandas
We will start our code session with the standard NumPy and Pandas imports:
In []: import numpy as npimport pandas as pdimport matplotlib.pyplot as pltIn []: pd.__version__Out[]: '0.20.1'
The Pandas Index Object
In []: ind = pd.Index([2, 3, 5, 7, 11])indOut[]: Int64Index([2, 3, 5, 7, 11], dtype='int64')In []: ind[1]Out[]: 3
The index object is part of every Series and DataFrame in Pandas. It is used to identify locations within the Series / DataFrame, as we'll see in the coming sections.
The Pandas Series Object
The Pandas Series is a one dimensional labeled array capable of holding any data type with an index. The structure is similar to a single column of a DataFrame which we will come across momentarily. Here is an example of creating it from a list (you could also create it similarly starting with a NumPy array).
In []: data = pd.Series([0.25, 0.5, 0.75, 1.0])dataOut[]:0 0.251 0.502 0.753 1.00dtype: float64
And like any NumPy array, data is accessed by the associated index via the familiar Python square-bracket notation:
In []: data[1]Out[]: 0.5In []: data[1:3]Out[]:1 0.502 0.75dtype: float64
Pandas Series has an explicitly defined index associated with the values given which allows for renaming.
In []: data = pd.Series([0.25, 0.5, 0.75, 1.0], index=['a', 'b', 'c', 'd'])dataOut[]:a 0.25b 0.50c 0.75d 1.00dtype: float64In []: data['c']Out[]: 0.75
Pandas can also construct a Series object directly from a Python dictionary:
In []: population_dict = {'California': 38332521,'Texas': 26448193,'New York': 19651127,'Florida': 19552860,'Illinois': 12882135}population = pd.Series(population_dict)populationOut[]:California 38332521Florida 19552860Illinois 12882135New York 19651127Texas 26448193dtype: int64
Next we have Pandas DataFrame Object!
The Pandas DataFrame Object
The Pandas DataFrame is a two dimensional table, similar to excel spreadsheet or SQL table. It allows easy organization of data types such as lists, arrays and dictionaries and can also be constructed from them. Data in DataFrame is stored in memory as a collection of series.
Construction from Series:
In []: pd.DataFrame(population, columns=['population'])Out[]:California 38332521Florida 19552860Illinois 12882135New York 19651127Texas 26448193dtype: int64
From a list of dictionaries:
In []: pd.DataFrame([{'a': 1, 'b': 2}, {'b': 3, 'c': 4}])Out[]:a b c0 1.0 2 NaN1 NaN 3 4.0
Alternatively, one can as well import data onto a DataFrame from Excel or CSV document.
In []: data = pd.read_csv('UKretail.csv')The data is available for download at the following link: UKretail.csv. Do make sure you place it in the same directory as your Python working directory.
For an SQL or Excel document, use read_sql() or read_excel() respectively. When reading in your data, one can specify certain arguments to ensure that correct and relevant data is loaded.
This is achieved by cropping data in an organized way. For instance:
eg = pd.read_csv('UKretail.csv', header=0, skiprows=4, nrows=20, usecols=[1,2,3])Further information on adding arguments can be found here: csv — pandas documentation
Exploratory Data Analysis (EDA)
Now that we know how to import data, it's time to go exploring, look for trends, patterns, and anomalies both quantitatively and visually. Pandas offers an extensive suite of functions that allows you to explore data to your heart's content.
Ways of observing descriptive statistics, at first glance, is by checking the shape, info and describe methods as shown below.
In []: data.shapeOut[]: (325145, 8)
This shows that there are 325,145 rows and 8 columns in the data-set.
In []: data.info()<class 'pandas.core.frame.DataFrame'>RangeIndex: 325145 entries, 0 to 325144Data columns (total 8 columns):InvoiceNo 325145 non-null objectStockCode 325145 non-null objectDescription 324275 non-null objectQuantity 325145 non-null int64InvoiceDate 325145 non-null objectUnitPrice 325145 non-null float64CustomerID 244154 non-null float64Country 325145 non-null objectdtypes: float64(2), int64(1), object(5)memory usage: 19.8+ MB
Pandas stores data types as objects, floats64, null, boolean...
In []: type(data["CustomerID"])Out[]: pandas.core.series.SeriesIn []: data.describe()Out[]:Quantity UnitPrice CustomerIDcount 325145.000000 325145.000000 244154.000000mean 9.273340 4.845239 15288.823120std 154.394112 116.830451 1713.496816min -80995.000000 -11062.060000 12347.00000025% 1.000000 1.250000 NaN50% 3.000000 2.080000 NaN75% 10.000000 4.130000 NaNmax 12540.000000 38970.000000 18287.000000
The describe function only applies to numeric datatype in the dataset and finds its stats (mean, standard deviation, minimum, maximum, and 25th, 50th and 75th percentiles).
Slicing and Indexing
Pandas identifies the first line of input data as index headers. So whether you select the first row or ask for column values, you will receive the same answer. This shows that there are multiple ways of coming to the same answers, but the more you learn Pandas, the more elegant and pythonic your code will be.
In []: data[1:1]Out[]:Empty DataFrameColumns: [InvoiceNo, StockCode, Description, Quantity, InvoiceDate, UnitPrice, CustomerID, Country]Index: []In []: data.columns.valuesOut[]:array(['InvoiceNo', 'StockCode', 'Description', 'Quantity', 'InvoiceDate','UnitPrice', 'CustomerID', 'Country'], dtype=object)
Selecting columns is no different from selecting Index. One can simply call on the column label. Here are examples showing top 5 rows. As a refresher, python indexing starts with a zero and is no exception here in Pandas.
In []: data["CustomerID"].head()Out[]:0 17850.01 17850.02 17850.03 17850.04 17850.0Name: CustomerID, dtype: float64
Instead of the column name, we can also refer to columns by the location. In this case, we'll have to use the iloc attribute, which stands for integer location.
In []: data.iloc[:,[3]].head()Out[]:Quantity0 21 62 63 64 6
In order to select specific rows or range of rows by index values use these methods.
In []: data[:3]Out[]:InvoiceNo StockCode Description Quantity \0 536365 22752 SET 7 BABUSHKA NESTING BOXES 21 536365 71053 WHITE METAL LANTERN 62 536365 84029G KNITTED UNION FLAG HOT WATER BOTTLE 6InvoiceDate UnitPrice CustomerID Country0 2010-12-01 08:26:02 7.65 17850.0 United Kingdom1 2010-12-01 08:26:02 3.39 17850.0 United Kingdom2 2010-12-01 08:26:02 3.39 17850.0 United KingdomIn []: data.iloc[[3,5,9],:]Out[]:InvoiceNo StockCode Description Quantity \3 536365 85123A WHITE HANGING HEART T-LIGHT HOLDER 65 536367 21754 HOME BUILDING BLOCK WORD 39 536367 22745 POPPY'S PLAYHOUSE BEDROOM 6InvoiceDate UnitPrice CustomerID Country3 2010-12-01 08:26:02 2.55 17850.0 United Kingdom5 2010-12-01 08:33:59 5.95 13047.0 United Kingdom9 2010-12-01 08:33:59 2.10 13047.0 United Kingdom
Now that you understand how to select values by row-wise and column-wise manipulations, we can combine these methods to slice frames.
In []: data.iloc[0:6,2:3]Out[]:Description0 SET 7 BABUSHKA NESTING BOXES1 WHITE METAL LANTERN2 KNITTED UNION FLAG HOT WATER BOTTLE3 WHITE HANGING HEART T-LIGHT HOLDER4 HAND WARMER UNION JACK5 HOME BUILDING BLOCK WORD
In case you are looking for a way to quickly glance at the top 5 or bottom 5 rows, the handy methods are head() and tail(). These methods also accept arguments to specify the number of rows.
In []: data.head()Out[]:InvoiceNo StockCode Description Quantity \0 536365 22752 SET 7 BABUSHKA NESTING BOXES 21 536365 71053 WHITE METAL LANTERN 62 536365 84029G KNITTED UNION FLAG HOT WATER BOTTLE 63 536365 85123A WHITE HANGING HEART T-LIGHT HOLDER 64 536366 22633 HAND WARMER UNION JACK 6InvoiceDate UnitPrice CustomerID Country0 2010-12-01 08:26:02 7.65 17850.0 United Kingdom1 2010-12-01 08:26:02 3.39 17850.0 United Kingdom2 2010-12-01 08:26:02 3.39 17850.0 United Kingdom3 2010-12-01 08:26:02 2.55 17850.0 United Kingdom4 2010-12-01 08:28:02 1.85 17850.0 United KingdomIn []: data.tail(2)Out[]:InvoiceNo StockCode Description Quantity \325143 581587 23254 CHILDRENS CUTLERY DOLLY GIRL 4325144 581587 23256 CHILDRENS CUTLERY SPACEBOY 4InvoiceDate UnitPrice CustomerID Country325143 2011-12-09 12:49:59 4.15 12680.0 France325144 2011-12-09 12:49:59 4.15 12680.0 France
With this arsenal of selecting and slicing tools, you are good to go forward with adding, deleting and renaming components of your DataFrame.
Data Analysis & Visualization
Let's start by creating a new label that calculates the revenue for every transaction(row-wise). Formula:
In []: data['Revenue'] = data.Quantity*data.UnitPrice
Let's create a sub-frame with the "Quantity", "UnitPrice" and "Revenue" columns.
In []: rawdata = data[["Quantity","UnitPrice", "Revenue"]]In []: rawdata[1:3]Out[]:Quantity UnitPrice Revenue1 6 3.39 20.342 6 3.39 20.34
Now, we can check the performance of the revenue over time by plotting a trend-line.
In []: data.plot(x="InvoiceDate", y="Revenue", kind="line")Out[]: <matplotlib.axes._subplots.AxesSubplot at 0xe3540f0>In []: plt.show() # This might take a few seconds
Looks like there had been cancellations that caused the revenue to drop down to negative. We can clean our data-set to drop these cancels for a better understanding of our revenue trend-line.
In []: cancels = data[data["Revenue"]<0]In []: cancels.shapeOut[]: (5588, 9)In []: data.drop(data[data.Revenue < 0].index, inplace=True)In []: data.shapeOut[]: (319555, 9)In []: data.plot(x="InvoiceDate", y="Revenue", kind="line")Out[]: <matplotlib.axes._subplots.AxesSubplot at 0x153c33c8>In []: plt.show() # This might take a few seconds
And voila, here we have it!
Beyond trend-line observations, we can further analyse the revenue contribution by countries. For this we apply the groupby() method on the "Revenue" column and sum() the values obtained. The reset_index() method helps to create a dataframe from the grouped values under the original label.
In []: CountryGroups = data.groupby("Country")["Revenue"].sum().reset_index()Here is a sorted list of revenue per country with values ranging from highest to lowest.
In []: CountryGroups.sort_values(by= "Revenue", ascending=False)Out[]:Country Revenue36 United Kingdom 4737733.05124 Netherlands 164971.39010 EIRE 161755.43014 Germany 133836.48013 France 125011.2400 Australia 78247.60033 Switzerland 34012.33031 Spain 32205.1403 Belgium 23866.77025 Norway 22584.44032 Sweden 20185.70020 Japan 19302.71027 Portugal 17934.7506 Channel Islands 12521.19012 Finland 12147.7909 Denmark 11583.92019 Italy 10375.5607 Cyprus 7192.4601 Austria 6098.01016 Hong Kong 5236.02030 Singapore 4932.61018 Israel 4134.34026 Poland 3880.03037 Unspecified 2898.65015 Greece 2627.57017 Iceland 2461.2305 Canada 2093.39035 United Arab Emirates 1277.50034 USA 1261.85021 Lebanon 1120.53023 Malta 1099.93022 Lithuania 1038.56011 European Community 868.0504 Brazil 602.31028 RSA 573.1808 Czech Republic 441.0802 Bahrain 137.66029 Saudi Arabia 90.720
We can also plot histograms for revenue on a particular customer or for quantity on a product.
In []: data[data["CustomerID"] == 17850.0]["Revenue"].plot(kind="hist")Out[]: <matplotlib.axes._subplots.AxesSubplot at 0x12081898>In []: plt.show() # This might take a few seconds
In []: data[data["StockCode"] == 71053]["Quantity"].hist()Out[]: <matplotlib.axes._subplots.AxesSubplot at 0xe0ca4a8>In []: plt.show() # This might take a few seconds
Often, the data we start with has missing values or invalid values. For these cases, Pandas allows us to identify and insert values.
In []: empty = data[data.CustomerID.isnull()]In []: empty.head()Out[]:InvoiceNo StockCode Description Quantity \373 536414 22139 NaN 56856 536544 11001 ASSTD DESIGN RACING CAR PEN 3857 536544 16236 KITTY PENCIL ERASERS 1858 536544 16238 PARTY TIME PENCIL ERASERS 1859 536544 17003 BROCADE RING PURSE 3InvoiceDate UnitPrice CustomerID Country Revenue373 2010-12-01 11:51:59 0.00 NaN United Kingdom 0.00856 2010-12-01 14:32:01 3.36 NaN United Kingdom 10.08857 2010-12-01 14:32:01 0.43 NaN United Kingdom 0.43858 2010-12-01 14:32:01 0.43 NaN United Kingdom 0.43859 2010-12-01 14:32:01 0.43 NaN United Kingdom 1.29In []: data.CustomerID.fillna(0, inplace=True)In []: data[data.CustomerID.isnull()]Out[]:Empty DataFrameColumns: [InvoiceNo, StockCode, Description, Quantity, InvoiceDate, UnitPrice, CustomerID, Country, Revenue]Index: []
Mark that the above functions for cleaning data is part of pre-processing before inputting into any machine learning model. Other utilities involve mapping values to numbers. Here is an example showing countries mapped to integers using a dictionary.
In []: mymap = {'United Kingdom':1, 'Netherlands':2, 'Germany':3, 'France':4, 'USA':5}data = data.applymap(lambda s: mymap.get(s) if s in mymap else s)In []: data.head()Out[]:InvoiceNo StockCode Description Quantity \0 536365 22752 SET 7 BABUSHKA NESTING BOXES 21 536365 71053 WHITE METAL LANTERN 62 536365 84029G KNITTED UNION FLAG HOT WATER BOTTLE 63 536365 85123A WHITE HANGING HEART T-LIGHT HOLDER 64 536366 22633 HAND WARMER UNION JACK 6InvoiceDate UnitPrice CustomerID Country Revenue0 2010-12-01 08:26:02 7.65 17850.0 1 15.301 2010-12-01 08:26:02 3.39 17850.0 1 20.342 2010-12-01 08:26:02 3.39 17850.0 1 20.343 2010-12-01 08:26:02 2.55 17850.0 1 15.304 2010-12-01 08:28:02 1.85 17850.0 1 11.10
And, there you have it! All ready for feeding the Country data array into Machine Learning model.
Ending Note
Till this point, we have surfed through a few Pandas functionalities in order to get a gist of Pandas' usefulness. This list is definitely not exhaustive so for those willing to dive deeper to explore its depth and breadth please refer to the online documentation for Pandas found here: pandas documentation
Here is a fun little bonus for completing this tutorial:
In []: import thisThe Zen of Python, by Tim PetersBeautiful is better than ugly.Explicit is better than implicit.Simple is better than complex.Complex is better than complicated.Flat is better than nested.Sparse is better than dense.Readability counts.Special cases aren't special enough to break the rules.Although practicality beats purity.Errors should never pass silently.Unless explicitly silenced.In the face of ambiguity, refuse the temptation to guess.There should be one-- and preferably only one --obvious way to do it.Although that way may not be obvious at first unless you're Dutch.Now is better than never.Although never is often better than *right* now.If the implementation is hard to explain, it's a bad idea.If the implementation is easy to explain, it may be a good idea.Namespaces are one honking great idea -- let's do more of those!
