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.
Powered by WPeMatico
