Pandas: The Swiss Army Knife for Your Data, Part 2

This is part two of a two-part tutorial about Pandas, the amazing Python data analytics toolkit. 

In part one, we covered the basic data types of Pandas: the series and the data frame. We imported and exported data, selected subsets of data, worked with metadata, and sorted the data. 

In this part, we’ll continue our journey and deal with missing data, data manipulation, data merging, data grouping, time series, and plotting.

Dealing With Missing Values

One of the strongest points of pandas is its handling of missing values. It will not just crash and burn in the presence of missing data. When data is missing, pandas replaces it with numpy’s np.nan (not a number), and it doesn’t participate in any computation.

Let’s reindex our data frame, adding more rows and columns, but without any new data. To make it interesting, we’ll populate some values.

>>> df = pd.DataFrame(np.random.randn(5,2),
                      index=index, 
                      columns=['a','b'])
>>> new_index = df.index.append(pd.Index(['six']))
>>> new_columns = list(df.columns) + ['c']
>>> df = df.reindex(index=new_index, columns=new_columns)
>>> df.loc['three'].c = 3
>>> df.loc['four'].c = 4
>>> df
              a         b    c
one   -0.042172  0.374922  NaN
two   -0.689523  1.411403  NaN
three  0.332707  0.307561  3.0
four   0.426519 -0.425181  4.0
five  -0.161095 -0.849932  NaN
six         NaN       NaN  NaN

Note that df.index.append() returns a new index and doesn’t modify the existing index. Also, df.reindex() returns a new data frame that I assign back to the df variable.

At this point, our data frame has six rows. The last row is all NaNs, and all other rows except the third and the fourth have NaN in the “c” column. What can you do with missing data? Here are options:

  • Keep it (but it will not participate in computations).
  • Drop it (the result of the computation will not contain the missing data).
  • Replace it with a default value.
Keep the missing data
---------------------
>>> df *= 2
>>> df
              a         b    c
one   -0.084345  0.749845  NaN
two   -1.379046  2.822806  NaN
three  0.665414  0.615123  6.0
four   0.853037 -0.850362  8.0
five  -0.322190 -1.699864  NaN
six         NaN       NaN  NaN


Drop rows with missing data
---------------------------
>>> df.dropna()
              a         b    c
three  0.665414  0.615123  6.0
four   0.853037 -0.850362  8.0

Replace with default value
--------------------------
>>> df.fillna(5)
              a         b    c
one   -0.084345  0.749845  5.0
two   -1.379046  2.822806  5.0
three  0.665414  0.615123  6.0
four   0.853037 -0.850362  8.0
five  -0.322190 -1.699864  5.0
six    5.000000  5.000000  5.0

If you just want to check if you have missing data in your data frame, use the isnull() method. This returns a boolean mask of your dataframe, which is True for missing values and False elsewhere.

>>> df.isnull()
           a      b      c
one    False  False   True
two    False  False   True
three  False  False  False
four   False  False  False
five   False  False   True
six     True   True   True

Manipulating Your Data

When you have a data frame, you often need to perform operations on the data. Let’s start with a new data frame that has four rows and three columns of random integers between 1 and 9 (inclusive).

>>> df = pd.DataFrame(np.random.randint(1, 10, size=(4, 3)), 
                      columns=['a','b', 'c'])
>>> df
   a  b  c
0  1  3  3
1  8  9  2
2  8  1  5
3  4  6  1

Now, you can start working on the data. Let’s sum up all the columns and assign the result to the last row, and then sum all the rows (dimension 1) and assign to the last column:

>>> df.loc[3] = df.sum()
>>> df
    a   b   c
0   1   3   3
1   8   9   2
2   8   1   5
3  21  19  11
>>> df.c = df.sum(1)
>>> df
    a   b   c
0   1   3   7
1   8   9  19
2   8   1  14
3  21  19  51

You can also perform operations on the entire data frame. Here is an example of subtracting 3 from each and every cell:

>>> df -= 3
>>> df
    a   b   c
0  -2   0   4
1   5   6  16
2   5  -2  11
3  18  16  48

For total control, you can apply arbitrary functions:

>>> df.apply(lambda x: x ** 2 + 5 * x - 4)
     a    b     c
0  -10   -4    32
1   46   62   332
2   46  -10   172
3  410  332  2540

Merging Data

Another common scenario when working with data frames is combining and merging data frames (and series) together. Pandas, as usual, gives you different options. Let’s create another data frame and explore the various options.

>>> df2 = df // 3
>>> df2
   a  b   c
0 -1  0   1
1  1  2   5
2  1 -1   3
3  6  5  16

Concat

When using pd.concat, pandas simply concatenates all the rows of the provided parts in order. There is no alignment of indexes. See in the following example how duplicate index values are created:

>>> pd.concat([df, df2])
    a   b   c
0  -2   0   4
1   5   6  16
2   5  -2  11
3  18  16  48
0  -1   0   1
1   1   2   5
2   1  -1   3
3   6   5  16

You can also concatenate columns by using the axis=1 argument:

>>> pd.concat([df[:2], df2], axis=1)
     a    b     c  a  b   c
0 -2.0  0.0   4.0 -1  0   1
1  5.0  6.0  16.0  1  2   5
2  NaN  NaN   NaN  1 -1   3
3  NaN  NaN   NaN  6  5  16

Note that because the first data frame (I used only two rows) didn’t have as many rows, the missing values were automatically populated with NaNs, which changed those column types from int to float.

It’s possible to concatenate any number of data frames in one call.

Merge

The merge function behaves in a similar way to SQL join. It merges all the columns from rows that have similar keys. Note that it operates on two data frames only:

>>> df = pd.DataFrame(dict(key=['start', 'finish'],x=[4, 8]))
>>> df
      key  x
0   start  4
1  finish  8
>>> df2 = pd.DataFrame(dict(key=['start', 'finish'],y=[2, 18]))
>>> df2
      key   y
0   start   2
1  finish  18

>>> pd.merge(df, df2, on='key')
      key  x   y
0   start  4   2
1  finish  8  18

Append

The data frame’s append() method is a little shortcut. It functionally behaves like concat(), but saves some key strokes.

>>> df
      key  x
0   start  4
1  finish  8

Appending one row using the append method()
-------------------------------------------

>>> df.append(dict(key='middle', x=9), ignore_index=True)
      key  x
0   start  4
1  finish  8
2  middle  9

Appending one row using the concat()
-------------------------------------------
>>> pd.concat([df, 
               pd.DataFrame(dict(key='middle', x=[9]))], 
               ignore_index=True)
      key  x
0   start  4
1  finish  8
2  middle  9

Grouping Your Data

Here is a data frame that contains the members and ages of two families: the Smiths and the Joneses. You can use the groupby() method to group data by last name and find information at the family level like the sum of ages and the mean age:

df = pd.DataFrame(
        dict(first='John Jim Jenny Jill Jack'.split(),
            last='Smith Jones Jones Smith Smith'.split(),
            age=[11, 13,  22,  44,  65]))
          
>>> df.groupby('last').sum()
       age
last
Jones   35
Smith  120
>>> df.groupby('last').mean()
        age
last
Jones  17.5
Smith  40.0          

Time Series

A lot of important data is time series data. Pandas has strong support for time series data starting with data ranges, going through localization and time conversion, and all the way to sophisticated frequency-based resampling.

The date_range() function can generate sequences of datetimes. Here is an example of generating a six-week period starting on 1 January 2017 using the UTC time zone.

>>> weeks = pd.date_range(start='1/1/2017', 
                          periods=6, 
                          freq='W', 
                          tz='UTC')
>>> weeks
DatetimeIndex(['2017-01-01', '2017-01-08', '2017-01-15', 
               '2017-01-22', '2017-01-29', '2017-02-05'],
              dtype='datetime64[ns, UTC]', freq='W-SUN')

Adding a timestamp to your data frames, either as data column or as the index, is great for organizing and grouping your data by time. It also allows resampling. Here is an example of resampling every minute data as five-minute aggregations.

>>> minutes = pd.date_range(start='1/1/2017', 
                            periods=10, 
                            freq='1Min', 
                            tz='UTC')
>>> ts = pd.Series(np.random.randn(len(minutes)), minutes)
>>> ts
2017-01-01 00:00:00+00:00    1.866913
2017-01-01 00:01:00+00:00    2.157201
2017-01-01 00:02:00+00:00   -0.439932
2017-01-01 00:03:00+00:00    0.777944
2017-01-01 00:04:00+00:00    0.755624
2017-01-01 00:05:00+00:00   -2.150276
2017-01-01 00:06:00+00:00    3.352880
2017-01-01 00:07:00+00:00   -1.657432
2017-01-01 00:08:00+00:00   -0.144666
2017-01-01 00:09:00+00:00   -0.667059
Freq: T, dtype: float64

>>> ts.resample('5Min').mean()
2017-01-01 00:00:00+00:00    1.023550
2017-01-01 00:05:00+00:00   -0.253311

Plotting

Pandas supports plotting with matplotlib. Make sure it’s installed: pip install matplotlib. To generate a plot, you can call the plot() of a series or a data frame. There are many options to control the plot, but the defaults work for simple visualization purposes. Here is how to generate a line graph and save it to a PDF file.

ts = pd.Series(np.random.randn(1000), 
               index=pd.date_range('1/1/2017', periods=1000))
ts = ts.cumsum()
ax = ts.plot()
fig = ax.get_figure()
fig.savefig('plot.pdf')

Note that on macOS, Python must be installed as a framework for plotting with Pandas.

Conclusion

Pandas is a very broad data analytics framework. It has a simple object model with the concepts of series and data frame and a wealth of built-in functionality. You can compose and mix pandas functions and your own algorithms. 

Additionally, don’t hesitate to see what we have available for sale and for study in the marketplace, and don’t hesitate to ask any questions and provide your valuable feedback using the feed below.

Data importing and exporting in pandas are very extensive too and ensure that you can integrate it easily into existing systems. If you’re doing any data processing in Python, pandas belongs in your toolbox.

Leave a Reply

Your email address will not be published. Required fields are marked *