Data Munging

Real world data is rarely clean or well-structured. The data scientist must know how and when to clean their data, and what it means to even have data that’s clean. We’ll get into that later when we talk about validating your data and accounting for bad rows.

Pandas

To even begin to clean your data well, it helps to have it well-organized. Pandas is a package that’s great for keeping your data well organized, giving you access to fast NumPy-like math capabilities with Excel-like access to your data in visible columns and rows. It accomplishes this through a new data structure called the DataFrame. DataFrames are fairly easy to build from a standard Python dict. Pandas is not part of the Python standard library, so it was included in the data_science_requirements.txt file that was downloaded yesterday. If you want to download it yourself for other environments just pip install pandas.

In [1]: import pandas as pd

In [2]: from datetime import datetime

In [3]: fmt = "%b %d, %Y"

In [4]: finances = {
   ...: "Name": ["Pablo", "Marcel", "Lisa", "Joanne"],
   ...: "Assets": [120000, 80000, 110000, 230000],
   ...: "Debts": [90000, 80000, 30000, 50000],
   ...: "Updated": [
   ...: datetime.strptime("Jun 10, 2011", fmt),
   ...: datetime.strptime("Dec 30, 2005", fmt),
   ...: datetime.strptime("May 4, 2000", fmt),
   ...: datetime.strptime("Feb 16, 2007", fmt),
   ...: ],
   ...: "Total Rating": [3.5, 2.5, 4.0, 5.0]
   ...: }
   ...: 

In [5]: finances_df = pd.DataFrame(finances)

In [6]: print(finances_df)
   Assets  Debts    Name  Total Rating    Updated
0  120000  90000   Pablo           3.5 2011-06-10
1   80000  80000  Marcel           2.5 2005-12-30
2  110000  30000    Lisa           4.0 2000-05-04
3  230000  50000  Joanne           5.0 2007-02-16

When creating a DataFrame from a dictionary, Pandas takes the keys from the dictionary and turns them into column names. You can then access the columns in a dict-like fashion with bracket notation, or in Javascript-like dot notation.

In [7]: print(finances_df["Total Rating"])
0    3.5
1    2.5
2    4.0
3    5.0
Name: Total Rating, dtype: float64

In [8]: print(finances_df.Debts)
0    90000
1    80000
2    30000
3    50000
Name: Debts, dtype: int64

The indices on the left side allow you to access individual rows or a series of rows. Accessing rows isn’t as simple as with list-like objects.

In [9]: finances_df[0]
---------------------------------------------------------------------------
KeyError                                  Traceback (most recent call last)
/Users/Nick/Documents/codefellows/courses/python-dev-accelerator/lib/python3.5/site-packages/pandas/indexes/base.py in get_loc(self, key, method, tolerance)
   1944             try:
-> 1945                 return self._engine.get_loc(key)
   1946             except KeyError:

pandas/index.pyx in pandas.index.IndexEngine.get_loc (pandas/index.c:4154)()

pandas/index.pyx in pandas.index.IndexEngine.get_loc (pandas/index.c:4018)()

pandas/hashtable.pyx in pandas.hashtable.PyObjectHashTable.get_item (pandas/hashtable.c:12368)()

pandas/hashtable.pyx in pandas.hashtable.PyObjectHashTable.get_item (pandas/hashtable.c:12322)()

KeyError: 0

During handling of the above exception, another exception occurred:

KeyError                                  Traceback (most recent call last)
<ipython-input-9-963331d931f8> in <module>()
----> 1 finances_df[0]

/Users/Nick/Documents/codefellows/courses/python-dev-accelerator/lib/python3.5/site-packages/pandas/core/frame.py in __getitem__(self, key)
   1995             return self._getitem_multilevel(key)
   1996         else:
-> 1997             return self._getitem_column(key)
   1998 
   1999     def _getitem_column(self, key):

/Users/Nick/Documents/codefellows/courses/python-dev-accelerator/lib/python3.5/site-packages/pandas/core/frame.py in _getitem_column(self, key)
   2002         # get column
   2003         if self.columns.is_unique:
-> 2004             return self._get_item_cache(key)
   2005 
   2006         # duplicate columns & possible reduce dimensionality

/Users/Nick/Documents/codefellows/courses/python-dev-accelerator/lib/python3.5/site-packages/pandas/core/generic.py in _get_item_cache(self, item)
   1348         res = cache.get(item)
   1349         if res is None:
-> 1350             values = self._data.get(item)
   1351             res = self._box_item_values(item, values)
   1352             cache[item] = res

/Users/Nick/Documents/codefellows/courses/python-dev-accelerator/lib/python3.5/site-packages/pandas/core/internals.py in get(self, item, fastpath)
   3288 
   3289             if not isnull(item):
-> 3290                 loc = self.items.get_loc(item)
   3291             else:
   3292                 indexer = np.arange(len(self.items))[isnull(self.items)]

/Users/Nick/Documents/codefellows/courses/python-dev-accelerator/lib/python3.5/site-packages/pandas/indexes/base.py in get_loc(self, key, method, tolerance)
   1945                 return self._engine.get_loc(key)
   1946             except KeyError:
-> 1947                 return self._engine.get_loc(self._maybe_cast_indexer(key))
   1948 
   1949         indexer = self.get_indexer([key], method=method, tolerance=tolerance)

pandas/index.pyx in pandas.index.IndexEngine.get_loc (pandas/index.c:4154)()

pandas/index.pyx in pandas.index.IndexEngine.get_loc (pandas/index.c:4018)()

pandas/hashtable.pyx in pandas.hashtable.PyObjectHashTable.get_item (pandas/hashtable.c:12368)()

pandas/hashtable.pyx in pandas.hashtable.PyObjectHashTable.get_item (pandas/hashtable.c:12322)()

KeyError: 0

This raises a KeyError because it’s attempting to look at the input “0” as a column name. To access the “ith” row, you must use the integer-location indexer on the DataFrame.

In [10]: finances_df.iloc[0]
Out[10]: 
Assets                       120000
Debts                         90000
Name                          Pablo
Total Rating                    3.5
Updated         2011-06-10 00:00:00
Name: 0, dtype: object

In [11]: finances_df.iloc[0:2]
Out[11]: 
   Assets  Debts    Name  Total Rating    Updated
0  120000  90000   Pablo           3.5 2011-06-10
1   80000  80000  Marcel           2.5 2005-12-30

Like NumPy arrays we can do quick aggregate statistics on individual columns.

In [12]: finances_df.Assets.mean()
Out[12]: 135000.0

In [13]: finances_df.Debts.median()
Out[13]: 65000.0

In [14]: finances_df["Total Rating"].min()
Out[14]: 2.5

In [15]: finances_df.Updated.max()
Out[15]: Timestamp('2011-06-10 00:00:00')

And math across the entirety of a column.

In [16]: finances_df.Assets / 1000.0
Out[16]: 
0    120.0
1     80.0
2    110.0
3    230.0
Name: Assets, dtype: float64

Also like NumPy arrays we can filter based on criteria that yields boolean values. The major difference though is that our cuts return not just pieces of an array, but pieces of the whole DataFrame.

In [17]: finances_df[finances_df.Assets > 100000]
Out[17]: 
   Assets  Debts    Name  Total Rating    Updated
0  120000  90000   Pablo           3.5 2011-06-10
2  110000  30000    Lisa           4.0 2000-05-04
3  230000  50000  Joanne           5.0 2007-02-16

This is all well and good, but you’ll likely want to work with data sets coming in from the outside world. They will be substantial and hopefully well-structured, and Pandas can read them for you if the columns are well-separated. For example, a CSV file like so:

# my_neighborhood_map.csv

City Feature,Common Name,Address,Website,Longitude,Latitude,Location
Alternative Schools,Pathfinder,1901 SW Genesee St,http://www.seattleschools.org/schools/pathfinder/welcome.html,-122.358,47.5636,"(47.5636, -122.358)"
Alternative Schools,The New School At Southshore,4800 S Henderson St,http://www.seattleschools.org/schools/southshore/,-122.27201,47.52374,"(47.52374, -122.27201)"
Alternative Schools,Catharine Blaine K-8,2550 34th Ave W,http://www.seattleschools.org/schools/blaine/,-122.399261,47.642257,"(47.642257, -122.399261)"
Alternative Schools,As #1 At Pinehurst,11530 12th Ave NE,http://as1web.com/,-122.31466,47.713431,"(47.713431, -122.31466)"
Alternative Schools,Nova,301 21st Ave E,,-122.30524,47.6216,"(47.6216, -122.30524)"
Alternative Schools,The Center School,305 Harrison,http://www.centerschoolseattle.org,-122.353983,47.622078,"(47.622078, -122.353983)"
Alternative Schools,Thornton Creek K-8,7711 43rd Ave NE,http://www.seattleschools.org/schools/ae2/,-122.282669,47.685032,"(47.685032, -122.282669)"
Alternative Schools,Orca K-8,5215 46th Ave S,http://www.orcapta.org,-122.27596,47.55478,"(47.55478, -122.27596)"
Alternative Schools,South Lake Hs,8601 Rainier Ave S,http://www.seattleschools.org/schools/southlake/,-122.27092,47.52526,"(47.52526, -122.27092)"
Alternative Schools,Madrona K-8,1121 33rd Ave,http://www.seattleschools.org/schools/madrona/,-122.290868,47.612237,"(47.612237, -122.290868)"
Alternative Schools,Salmon Bay,1810 NW 65th St,http://www.salmonbayschool.org/,-122.38051,47.676298,"(47.676298, -122.38051)"
Alternative Schools,Tops K-8,2500 Franklin Ave E,http://www.topsk8.org/,-122.324177,47.642286,"(47.642286, -122.324177)"
Basketball Courts,Hiawatha Playfield,2700 California Ave. SW,http://www.seattle.gov/parks/park_detail.asp?ID=456,-122.38523,47.578526,"(47.578526, -122.38523)"
Basketball Courts,E.C. Hughes Playground,2805 SW Holden St.,http://www.seattle.gov/parks/park_detail.asp?ID=458,-122.370211,47.532807,"(47.532807, -122.370211)"
Basketball Courts,Bryant Playground,4103 NE 65th St.,http://www.seattle.gov/parks/park_detail.asp?ID=482,-122.283764,47.675545,"(47.675545, -122.283764)"
...

This can be easily read with Pandas:

In [18]: sample_data = pd.read_csv("my_neighborhood_map.csv")

If it was separated with anything besides commas, like say tabs, I’d use

In [19]: sample_data = pd.read_csv("my_neighborhood_map.csv", sep="\t")

Since the data file itself is a little difficult to read from your shell, use Pandas to tell you stuff about it like what the columns are actually named.

In [20]: sample_data.columns
Out[20]:
Index(['City Feature', 'Common Name', 'Address', 'Website',
       'Longitude', 'Latitude', 'Location'],
      dtype='object')

Or how long it is.

In [21]: len(sample_data)
Out[21]: 2072

Or how many Basketball Courts exist in this city.

In [22]: sum(sample_data["City Feature"] == "Basketball Courts")
Out[22]: 47

Pandas provides a great structure for you to investigate a whole data set, instead of trying to read through your file line by line by eye. We’re going to use this moving forward to ensure our data is valid, and if it isn’t then to take some action so that our data is still usable.

What is Valid?

When working with a data set, a decent first approximation comes from removing records with invalid information. You can determine what’s valid based on auxiliary information about the set, or just from looking at the set itself. If you’re looking at data about people and it includes negative ages, those records with negative ages are to some degree invalid. If your data looks at rental prices of apartments around the city, chances are that prices won’t be in the 10’s of thousands of dollars are above. Whatever the data, think about it, think about values are plausible, and reject those rows with data that fall outside of that realm of plausability.

We’ll discuss two ways of handling bad data: removal, filling. You can also manually alter data, but you had better have a damn good reason to do so.

Removing Bad Data

Maybe the data got corrupted on entry? Perhaps some records aren’t relevant to the question at hand. Whatever the case, sometimes you just need to be able to filter out what you don’t want.

Note that this doesn’t mean that you should DELETE data. Your data is your data, whether it’s good or not. It’s valuable in some way, shape, or form, and to artificially remove data in its entirety is an unacceptable practice. When you filter out information, you must say why and how otherwise your analysis is seen as questionable at best.

Let’s say that we have the following data set.

In [23]: print(data)
         ages    heights
0   -1.000000  61.623766
1   34.850545  61.231887
2   -1.000000  62.012366
3   -1.000000  60.776548
4   -1.000000  71.331092
5   32.454987  70.932109
6   34.324364  70.600538
7   28.425111  66.755211
8   -1.000000  61.205107
9   -1.000000  69.932608
10  32.226644  70.130044
11  34.633665  70.428263
12  35.582740  68.087174
13  39.245149  69.507355
14  39.364419  71.366009
15  35.780590  61.853659
16  -1.000000  66.879470
17  -1.000000  66.495248
18  -1.000000  60.654402
19  -1.000000  63.833324
20  -1.000000  71.715755
...

Clearly, folks shouldn’t have negative ages, and those entries would be useless for an analysis of ages or ages vs heights. Recall from the previous section that we can filter out rows in Pandas fairly easily.

In [24]: print(data[data.ages != -1])
         ages    heights
1   34.850545  61.231887
5   32.454987  70.932109
6   34.324364  70.600538
7   28.425111  66.755211
10  32.226644  70.130044
11  34.633665  70.428263
12  35.582740  68.087174
13  39.245149  69.507355
14  39.364419  71.366009
15  35.780590  61.853659
22  34.967948  66.308030
23  26.754890  70.697276
24  39.388898  70.603496
27  40.060202  71.432532
29  42.883102  67.030701
32  30.602126  63.557139
36  37.374404  67.276436
40  43.892979  61.926341
41  38.357550  67.625260
42  28.814544  65.478372
46  31.351512  66.442712
47  36.443522  69.275935

You can assign this filtered DataFrame to a new variable and then move forward with your analysis from there. If of course you don’t care about the ages, then you could go with the data as it was and think about your heights.

Making Assumptions on Bad Data

Data tends to come in bad. However, we may not want to just throw it out wholesale; there may be some portions of bad data that are entirely valid. As an example, I have the following information in a file called “nmhw_sample_data.csv”:

Name,Age,Hometown,Country,Desired Income,Favorite Food,Number of Cats
James,22,New York,,75000,,0
Bob,25,Seattle,USA,0,pad thai,2
Annette,28,San Francisco,USA,,spaghetti,1
Florence,23,Beijing,China,60000,xiaolong bao,4
Martha,30,Kansas City,,,corn,3
Desean,27,Newark,USA,85000,,
Jamal,28,New York,,65000,pizza,3
Kaede,31,Kyoto,Japan,65000,sushi,4
Milton,23,Austin,,78000,bbq,
Ivana,29,Moscow,Russia,,borscht,1
Jorge,35,Rio de Janeiro,Brasil,90000,farofa,2

If I were to read it in as-is, I’d get the following

In [25]: data = pd.read_csv("./downloads/nmhw_sample_data.csv")

In [26]: print(data)
        Name  Age        Hometown Country  Desired Income Favorite Food  Number of Cats
0      James   22        New York     NaN         75000.0           NaN             0.0
1        Bob   25         Seattle     USA             0.0      pad thai             2.0
2    Annette   28   San Francisco     USA             NaN     spaghetti             1.0
3   Florence   23         Beijing   China         60000.0  xiaolong bao             4.0
4     Martha   30     Kansas City     NaN             NaN          corn             3.0
5     Desean   27          Newark     USA         85000.0           NaN             NaN
6      Jamal   28        New York     NaN         65000.0         pizza             3.0
7      Kaede   31           Kyoto   Japan         65000.0         sushi             4.0
8     Milton   23          Austin     NaN         78000.0           bbq             NaN
9      Ivana   29          Moscow  Russia             NaN       borscht             1.0
10     Jorge   35  Rio de Janeiro  Brasil         90000.0        farofa             2.0

Those “NaN”s are “Not a Number”s, the way for Pandas and NumPy to identify missing or otherwise invalid data. Here, they’re not consistent across rows. Some entries have no country, some have no income, etc. We can make some assumptions about what the missing data should be filled with based on the data that surrounds it. This works especially well if our data comes in sorted in some way. Pandas DataFrames have a method “.fillna()” for filling in data in a variety of ways.

In [27]: data.fillna(method="ffill")
Out[27]:
        Name  Age        Hometown Country  Desired Income Favorite Food  Number of Cats
0      James   22        New York     NaN         75000.0           NaN             0.0
1        Bob   25         Seattle     USA             0.0      pad thai             2.0
2    Annette   28   San Francisco     USA             0.0     spaghetti             1.0
3   Florence   23         Beijing   China         60000.0  xiaolong bao             4.0
4     Martha   30     Kansas City   China         60000.0          corn             3.0
5     Desean   27          Newark     USA         85000.0          corn             3.0
6      Jamal   28        New York     USA         65000.0         pizza             3.0
7      Kaede   31           Kyoto   Japan         65000.0         sushi             4.0
8     Milton   23          Austin   Japan         78000.0           bbq             4.0
9      Ivana   29          Moscow  Russia         78000.0       borscht             1.0
10     Jorge   35  Rio de Janeiro  Brasil         90000.0        farofa             2.0

The above is the result of a “forward fill”. Whatever data came before the invalid entry IN ITS OWN COLUMN will fill that invalid entry in with its own value.

Similarly, there’s a “backward fill”, which fills in missing data with information that comes after.

In [28]: data.fillna(method="bfill")
Out[28]:
        Name  Age        Hometown Country  Desired Income Favorite Food  Number of Cats
0      James   22        New York     USA         75000.0      pad thai             0.0
1        Bob   25         Seattle     USA             0.0      pad thai             2.0
2    Annette   28   San Francisco     USA         60000.0     spaghetti             1.0
3   Florence   23         Beijing   China         60000.0  xiaolong bao             4.0
4     Martha   30     Kansas City     USA         85000.0          corn             3.0
5     Desean   27          Newark     USA         85000.0         pizza             3.0
6      Jamal   28        New York   Japan         65000.0         pizza             3.0
7      Kaede   31           Kyoto   Japan         65000.0         sushi             4.0
8     Milton   23          Austin  Russia         78000.0           bbq             1.0
9      Ivana   29          Moscow  Russia         90000.0       borscht             1.0
10     Jorge   35  Rio de Janeiro  Brasil         90000.0        farofa             2.0

Finally, you can fill in individual columns instead of doing global fills.

In [29]: data["Desired Income"].fillna(data["Desired Income"].mean())

Doing the above with a mean or a median gives you plausible values based on the rest of the data set. This way you can still use all the data in the column and (more or less) not change how it’s distributed.

Note the above methods have their obvious consequences. Blindly filling with information can at times produce more data artifacts than you originally had. Be aware of what you’re filling with and how you’re doing it before you go ahead and do it. Perhaps re-sorting your data would be a better idea before forward or backward-filling. It’s never a perfect process, and you should always document what you do and why you do it. Use the Markdown cells in your Jupyter Notebooks to inject that rationale into your data report. That way, your choices don’t seem quite as arbitrary (even if they were entirely arbitrary to begin with).

Recap

Real data is without fail imperfect. As analysts, you need to use your chosen language to handle that data before you proceed with your work. Pandas gives us the tools to handle our data in a structured way, and the methods needed to fill and remove data as need be.

Tonight you will use these methods to clean some data of your own. Never forget to document your rationale; until you do, only you know why you did what you did. Keep those intermediate steps in mind while you finalize your report.