We have already covered most of the fundamentals of working with data using the Pandas library. There is one more topic I'd like to discuss before concluding the series: The Apply function.

In the previous article, we learned how to create subgroups of data using the groupby function. This is quite useful when you want to gain a better understanding of certain subsets of data or perform group aggregations. Today we will add another resource to your toolbox that will let you use those groups for much more.

Apply lets you perform more complex computations on the groups you create, it works like this: The function you provide to apply is called on each of the groups, and the results are concatenated into a single final data structure.

Again, this is much easier to understand with practical examples, so let's get started!

Basic applications of apply

We will use the same table with Pokemon data we used in the last article.

First, let's import pandas and examine the contents of our DataFrame.

import pandas as pd

pdata = pd.read_csv('./sample_data/poke_colors.csv')
pdata

Apply's most important argument is a function. This function will be run on every group of data and the results will be concatenated in a final data structure. We will create a simple function that returns the two Pokemon with the highest attack value, something like this:

# Two pokes with the highest attack

def highest_attack(data_frame):
    # Remember how [] works, this selects the last two (highest) Attack entries after sorting
    return data_frame.sort_values(by='Attack')[-2:]

# Let's test it on the complete dataframe
highest_attack(pdata)

Now let's see how to use apply to do something a bit more interesting. We want to find the two pokemon with the highest attack value on a by-color basis. For doing this, we will group them by Color and then pass highest_attack to apply, something like this:

# Now, let's find which are the two pokemon with the highest attack on each color group:
pdata.groupby('Color').apply(highest_attack)

Notice how the final table is the result of concatenating together the results of running highest_attack on every group!

Functions with extra arguments

The functions you pass to the apply method can receive additional arguments. Let's create another version of our function, this time called highest_attribute, that lets you specify the attribute to take into consideration and the n highest pokemon you want to select from each group:

# We set the default attribute as HP and the default n to 2
def highest_attribute(data_frame, attribute='HP', n=2):
    return data_frame.sort_values(by=attribute)[-n:]

pdata.groupby('Color').apply(highest_attribute, 'Defense', 3)

Notice how the additional parameters are passed to the apply function, not to sort_values itself. Internally, apply makes sure that the right parameters are passed to whatever function it's applying.

Using lambdas as an argument for apply

As a final note, sometimes you won't want to write a complete function definition if what you want to accomplish is very simple. In this case, you can pass a lambda function. In our next example we will use this approach to select from each group the pokemon whose name appears first in alphabetical ordering in each group:

pdata.groupby('Color').apply(lambda df: df.sort_values('Name').head(1) )

Practice makes perfect

Apply is an incredibly flexible function that, if used in creative ways, lets you solve a huge variety of problems in data manipulation and transformation. This article exposed you to the basic concepts of the function, but make sure to study it further and experiment with real datasets.

As a closing remark, I'd like to share a quotation from the book Python For Data Analysis (2nd), in which this series is largely based on:

Beyond these basic usage mechanics, getting the most out of apply
may require some creativity. What occurs inside the function
passed is up to you; it only needs to return a pandas object or a
scalar value. The rest of this chapter will mainly consist of examples
showing you how to solve various problems using groupby

That's all the Pandas I can share, for now

With this article, we conclude our Hands-on Pandas series. It's been a lot of fun to write, and I really hope you learned one or two interesting things along the way.

Pandas, like every other software tool or skill, requires a good amount of practice before it becomes truly useful. Don't worry if you don't immediately know how to tackle a dataset or which function to call, with experience and continued exposure it will become second nature.

If you need help, remember that Pandas has some of the best docs around and a huge, helpful community that will guide you into finding a solution. I wish you a happy and productive learning process!

Thank you for reading!

What to do next

• Share this article with friends and colleagues. Thank you for helping me reach people who might find this information useful.
• You can find the source code for this series in this repo.
• This article is based on Python for Data Analysis. These and other very helpful books can be found in the recommended reading list.
• Send me an email with questions, comments, or suggestions (it's in the About Me page)

Sometimes you need to perform operations on subsets of data. Your rows might have attributes in common or somehow form logical groups based on other properties. Common operations like finding the average, maximum, count, or standard deviation of values from groups of data is a really common task, and Pandas makes this really easy to accomplish.

In this article, we will learn how to use the groupby function and study some of the built-in aggregations you can run on groups. This will give you another valuable tool for data analysis, and I hope it'll help you accomplish your tasks in a much simpler way.

Great, let's get started!

Loading our sample data

We will use data from a CSV file I created with info about 16 Pokemon. It contains attributes like the Name, Color (Green, Blue, Red, Yellow), and other stats like HP, Attack, Defense, and Speed.

We are interested in calculating some common aggregations over groups of Pokemon with different colors.

import pandas as pd

pdata = pd.read_csv('./sample_data/poke_colors.csv')
pdata

GroupBy

Pandas function groupby is used to create GroupBy objects. These objects can perform lots of useful built-in aggregations with just a single function call. groupby receives as argument a list of keys that decide how the grouping is performed. In our first example we will group the Pokemon by color:

pg = pdata.groupby('Color')
pg

<pandas.core.groupby.generic.DataFrameGroupBy object at 0x7ff848e80f28>

As you might have seen from the message on top, we got an object of type GroupBy. If you want to get a bit more information about it, use the size method:

pg.size()

Color
Blue      4
Green     4
Red       4
Yellow    4
dtype: int64

Another way of getting some basic information about the group is using the groups attribute (it's a dictionary):

pg.groups

{'Blue': Int64Index([4, 5, 6, 7], dtype='int64'),
 'Green': Int64Index([0, 1, 2, 3], dtype='int64'),
 'Red': Int64Index([8, 9, 10, 11], dtype='int64'),
 'Yellow': Int64Index([12, 13, 14, 15], dtype='int64')}

Great!

Now that you know how to create a basic group, let's check two important properties:

# The first one is that you can iterate over GroupBy objects.

for color, group in pg:
    print(color)
    print(group)
    print('\n\n')

Blue
        Name Color  Evolves  HP  Attack  Defense  SpAtk  SpDef  Speed
4    Dratini  Blue     True  41      64       45     50     50     50
5   Squirtle  Blue     True  44      48       65     50     64     43
6    Poliwag  Blue     True  40      50       40     40     40     90
7  Poliwhirl  Blue     True  65      65       65     50     50     90



Green
        Name  Color  Evolves  HP  Attack  Defense  SpAtk  SpDef  Speed
0   Caterpie  Green     True  45      30       35     20     20     45
1    Metapod  Green     True  50      20       55     25     25     30
2    Scyther  Green    False  70     110       80     55     80    105
3  Bulbasaur  Green     True  45      49       49     65     65     45



Red
          Name Color  Evolves  HP  Attack  Defense  SpAtk  SpDef  Speed
8   Charmander   Red     True  39      52       43     60     50     65
9       Magmar   Red    False  65      95       57    100     85     93
10       Paras   Red     True  35      70       55     45     55     25
11    Parasect   Red    False  60      95       80     60     80     30



Yellow
       Name   Color  Evolves  HP  Attack  Defense  SpAtk  SpDef  Speed
12  Pikachu  Yellow     True  35      55       40     50     50     90
13     Abra  Yellow     True  25      20       15    105     55     90
14  Psyduck  Yellow     True  50      52       48     65     50     55
15  Kadabra  Yellow     True  40      35       30    120     70     10

# The second is that you can access the subgroups by providing the right key to the get_group method
pg.get_group('Blue')

Groups are interesting because they let you calculate aggregations on user-defined subsections of your data. Let's calculate the mean of every stat for the color groups:

#Evolves is boolean, so True will be treated as a 1 and False as 0 when calculating the mean
pg.mean()

Grouping just a subset of columns

Sometimes what you want is to create groups with just one or two columns of interest. If this is the case, you can select them (and the column you want to group by) on the dataframe and then just call groupby:

# Let's create groups with just the HP values and then calculate the mean
pdata[['HP', 'Color']].groupby('Color').mean()

# Let's do the same for Attack, Defense and Speed
pdata[['Color', 'Attack', 'Defense', 'Speed']].groupby('Color').mean()

Groups from multiple keys

You can create groups using more than one column as key. If you pass a list of keys to the groupby method, you will create a hierarchical grouping. Let's group by both Color and Evolves attributes:

opg = pdata.groupby(['Color', 'Evolves'])
opg.size()

Color   Evolves
Blue    True       4
Green   False      1
        True       3
Red     False      2
        True       2
Yellow  True       4
dtype: int64

opg.groups

{('Blue', True): Int64Index([4, 5, 6, 7], dtype='int64'),
 ('Green', False): Int64Index([2], dtype='int64'),
 ('Green', True): Int64Index([0, 1, 3], dtype='int64'),
 ('Red', False): Int64Index([9, 11], dtype='int64'),
 ('Red', True): Int64Index([8, 10], dtype='int64'),
 ('Yellow', True): Int64Index([12, 13, 14, 15], dtype='int64')}

Common aggregations:

Some built-in aggregations already come implemented in Pandas, and you can also define your own if you need it. In the following section, we will run the following functions to get an idea of Pandas' default behavior. All these functions take into consideration only non-NA values:

• count: Number of elements in each group.
• sum: Sum of values in each group.
• mean: Arithmetic mean of each group.
• max: Maximum value of each group
• min: Minimum value of each group
• std: Standard deviation of each group.
• var: Variance of each group.

Also, it's important to know that you don't need to assign the group to a variable. Very often, you will just use it as an intermediate value to call an aggregation. So, instead of writing this:

pg = pdata.groupby('Color')
pg.mean()

You can write this:

pdata.groupby('Color').mean()

# Let's drop the Name and Evolves properties to create a new dataframe
spdata = pdata.drop(['Name', 'Evolves'], axis=1)
spdata

# Count the elements in each group
spdata.groupby('Color').count()

# Calculate the sum in each group
spdata.groupby('Color').sum()

# Calculate the mean in each group
spdata.groupby('Color').mean()

# Find the maximum of each stat for every group
spdata.groupby('Color').max()

# Find the minimum of each stat for every group
spdata.groupby('Color').min()

# Calculate the standard deviation of each stat for every group
spdata.groupby('Color').std()

# Calculate the variance of each stat for every subgroup
spdata.groupby('Color').var()

Custom-defined aggregations

Pandas has a method called agg that lets you define and run your own aggregations over groups. In the next example, we will define a function that calculates the sum of the squares of the stats in every group.

def sum_of_squares(arr):
    sos = 0
    for element in arr:
        sos += element**2
    return sos

spdata.groupby('Color').agg(sum_of_squares)

Aggregations are powerful!

Grouping data for analysis using Pandas is efficient, elegant, and powerful. Traditional relational databases owe part of their great popularity to their capacity to perform this sort of functionality, and now you can get much of the same work done using Python.

This last function (agg) might be one of the most useful capabilities Pandas provides. Custom-defined aggregations are incredibly powerful. They let you calculate any crazy function you can come up with, in a repeatable and consistent way.

This feature is so powerful that we will dedicate the next article in expanding the topic: We will talk about the powerful apply method.

Thank you for reading!

What to do next

• Share this article with friends and colleagues. Thank you for helping me reach people who might find this information useful.
• You can find the source code for this series in this repo.
• This article is based on Python for Data Analysis. These and other very helpful books can be found in the recommended reading list.
• Send me an email with questions, comments or suggestions (it's in the About Me page)

Merge/join operations in Pandas let you gather information from many tables into a single dataframe for further processing or analysis. This is another important skill that you will probably use a lot when working with data.

If you have some experience with relational databases you can recognize the analogous behavior with table joins. In this article, we will demo some of the most important behavior offered by Pandas' merge function. It will probably be more than enough to keep you going before you need to consult more documentation.

Great, let's get started!

Loading the data for the examples

For the demos, we will use data from two different files. One of them contains some non-numeric attributes of a few Pokemon, the other contains base stats like HP, Attack, and Speed.

import pandas as pd

attribs = pd.read_csv('./sample_data/poke_attributes.csv')
attribs

stats = pd.read_csv('./sample_data/poke_stats.csv')
stats

Notice that Ditto, Dratini, and Pikachu are on both tables, but Abra and Ekans appear only on the first one while Caterpie and Vulpix appear only on the second one.

Now let's go back to the programming bits: Pandas' merge function links the rows of two dataframes using one or more keys. In the following examples, we will use the Name column as key to show different types of merge/join.

Inner Joins

An inner join is performed on the intersection of the keys of the two dataframes. In this case, it will find the Names that are in both dataframes (Ditto, Dratini, and Pikachu) and create a new dataframe with all the columns on both original dataframes.

We will call the merge function specifying two additional arguments:

• how: Tells merge which type of join to perform.
• on: Tells merge which columns to use as key.

innerjoin = pd.merge(attribs, stats, on='Name', how='inner')
innerjoin

If you want to perform an inner join you can skip the how attribute: The default behavior of the merge function is an inner join:

innerjoin = pd.merge(attribs, stats, on='Name')
innerjoin

Outer joins

Outer joins are performed on the union of the keys of the two dataframes. In this case, it will use every single name in the original dataframes and fill the missing fields with NaN.

outerjoin = pd.merge(attribs, stats, on='Name', how='outer')
outerjoin

Left joins

Left joins take every single element on the left dataframes and fill-in with the keys in common on the right dataframe. This might be a bit easier to understand with an example:

leftjoin = pd.merge(attribs, stats, on='Name', how='left')
leftjoin

Notice how Abra and Ekans don't have values in HP, Attack, and Speed. This happens because the right table does not contain values for these particular Pokemon.

Right joins

Right joins take every single element on the right dataframes and fill-in with the keys in common on the left dataframe. This might be a bit easier to understand with an example:

rightjoin = pd.merge(attribs, stats, on='Name', how='right')
rightjoin

Notice how Caterpie and Vulpix don't have values in Type, Color, and Evolves. This happens because the left table does not contain values for these particular Pokemon.

Other considerations

Joins with key columns of different names

If the names of the columns are different, you can specify them with left_on and right_on

pd.merge(leftdataframe, rightdataframe, left_on='left_key', right_on='right_key')

Joining on indexes

Sometimes you want to use the index of the dataframes as key column to perform the join. For this to work, you just need to pass two additional parameters set to true: left_index=True, right_index=True

Remembering which join type you need

If you are having trouble recalling which type of join is which, take a look at the following image:

Multi-source data

In the previous article, we started talking about the reality of working with real data: You'd be really lucky to find a clean dataset in a single repository. In reality, you will probably need to select and aggregate data from lots of different sources.

Merge is a relatively simple function at a basic level, but it's an incredibly useful and rich tool. You just learned how to use the basics, but it's also a good idea to read the documentation to get an idea of the full capabilities of this function.

In the next article, we will learn how to perform group operations in Pandas data structures.

Thank you for reading!

What to do next

• Share this article with friends and colleagues. Thank you for helping me reach people who might find this information useful.
• You can find the source code for this series in this repo.
• This article is based on Python for Data Analysis. These and other very helpful books can be found in the recommended reading list.
• Send me an email with questions, comments or suggestions (it's in the About Me page)

In an ideal world, all the data you need is available in the right format and with complete content.

In the real world, you will probably need to scrape data from lots of different and incomplete sources. That's why it's important to learn how to clean your data before analyzing it or feeding it into a ML algorithm.

Data cleaning might not the most glamorous part of your work, but it's a crucial part of the development of data-based products. Not only it's important for the whole pipeline, but it's also one of the most time-consuming tasks in a project (Some estimate it to consume around 80% of a project's time).

In this article, we will learn some basic data cleaning techniques that will let you handle the most common situations.

Great, let's get started!

Dealing with missing values

Dealing with missing values is a basic (and extremely useful) technique. The data you'll have access to will probably miss the attributes of some entries, and it's better to spend some time explicitly handling each case.

Imagine you have a csv file with the following content:

Name,Type,Color,Evolves,HP
Abra,Psychic,,True,
Pikachu,Electric,,True,35
Ekans,,Purple,,35
Dratini,,Blue,,41
Ditto,Normal,Pink,False,48

As you can see, it's missing entries for several rows. If you load it using read_csv, the resulting dataframe will look like this:

import pandas as pd
frame = pd.read_csv('./sample_data/pokes_missing.csv')
frame

Pandas fills every entry without value with the default NaN sentinel value. In many situations, you just want to remove the rows with missing values and leave the ones that have exclusively non-NaN fields, for this, you can use the dropna function:

# dropna does not affect the original dataframe, it returns a new one.
frame.dropna()

#If you want to perform the dropna in place, specify the inplace attribute
frame.dropna(inplace=True)
frame

# Let's reload the data:
frame = pd.read_csv('./sample_data/pokes_missing.csv')

Dropna can be used differently: You can tell the function to drop only the rows that are composed of NaN values exclusively. This way you can do away with entries that don't provide any information. For doing this, provide the optional parameter * how='all' when calling dropna.

Dropping all rows with NaN values might not be the best option. Very often, the best option is to fill-in the missing entries with values you provide or with values calculated from the dataframe itself. Pandas provides a function called fillna that lets you handle missing values:

# Let's fill every missing entry with the value 0
frame.fillna(10)

Do you notice anything wrong with the dataframe? Well, for starters, the value 10 might work reasonably well for HP, but it doesn't make any sense for Type, Color or Evolves. fillna lets you specify how to fill NaN values for every column if you pass a dictionary instead of a single value:

frame.fillna({'Type': 'Unknown',
              'Color': 'Yellow',
              'Evolves': True,
              'HP': 30})

Now the dataframe makes a bit more sense! As with dropna, the change performed by fillna doesn't happen on the original dataframe, it just creates a new dataframe with new values. If you want to change the original dataframe provide the inplace argument:

frame.fillna({'Type': 'Unknown',
              'Color': 'Yellow',
              'Evolves': True,
              'HP': 30},
             inplace=True)
frame

Dealing with duplicates

Sometimes your data will contain duplicate rows or values with the same fields. Take a look at this dataframe:

frame = pd.read_csv('./sample_data/pokes_duplicates.csv')
frame

The frame has two Dratinis and two Abras, so let's get rid of them using drop_duplicates:

frame.drop_duplicates()

Sometimes you want to remove duplicates based on the value of an attribute. In this case, you can provide an extra argument (or list of arguments if you want to use multiple attributes) to specify the column to take into consideration. For example, the following call ensures we have only rows with unique colors:

# Abra and Pikachu are both yellow, so it's time to go for Pikachu
frame.drop_duplicates('Color')

Mappings and other transformations

In this section, we will learn about some other techniques for modifying your data. The first technique is to use the map function to alter a column in a dataframe:

frame = frame = pd.read_csv('./sample_data/pokes.csv')
frame

# Let's transform the type into an all-uppercase string
frame['Type'] = frame['Type'].map(lambda x: x.upper())
frame

The next thing we can do is to use the replace function to change values. In the following example we will replace the Evolves' column boolean values for 'Yes' and 'No' strings:

frame['Evolves'] = frame['Evolves'].replace([True, False], ['Yes','No'])
frame

We can also use conditional selection to get just the data we need. Suppose we are interested only on the elements with HP under 40:

frame[frame['HP'] < 40]

The last thing we will deal with is transforming categorical data into one-hot encoded columns. If you want a more in-depth explanation of one-hot encoding you can read this article:

# get_dummies transforms categorical data into a one-hot encoded dataframe
# we will concatenate the original dataframe and the one-hot encoded type columns

pd.concat([frame, pd.get_dummies(frame['Type'])], axis=1)

Keep it clean

Getting perfect data at the beginning of the project is extremely unlikely. In reality, you will probably need to put together a dataset from many sources, and in the process, there might be some malformed, erroneous, or missing data.

That's ok as long as you know how to clean your data and get it in proper shape for your analysis/algorithms. It is often said that you will probably spend 80% of your time cleaning and wrangling data, so it's a good idea to learn one or two tricks.

In the next article, we will learn how to put together data from different sources into a single collection.

Thank you for reading!

What to do next

• Share this article with friends and colleagues. Thank you for helping me reach people who might find this information useful.
• You can find the source code for this series in this repo.
• This article is based on Python for Data Analysis. These and other very helpful books can be found in the recommended reading list.
• Send me an email with questions, comments or suggestions (it's in the About Me page)

Data analysis usually starts by loading data into the structures of your library/tools of choice. Almost always this data will either come from a database, the web, or a collection of files.

The files that contain your data can come in many different formats: Comma-separated values in a text file, JSON files, excel files, or files with values separated by custom characters. In this article, we will learn how to read data from some of the common file formats using Pandas' built-in functions.

Great, let's get started!

Reading data from CSV files

CSV files are an incredibly common file format, and many tutorials and small file repos use them as default data format. Imagine you have a file with this contents at ./sample_data/pokes.csv:

Name,Type,Color,Evolves,HP
Abra,Psychic,Yellow,True,25
Pikachu,Electric,Yellow,True,35
Ekans,Poison,Purple,True,35
Dratini,Dragon,Blue,True,41
Ditto,Normal,Pink,False,48

You can easily put this data in a dataframe using read_csv:

import pandas as pd

poke_data = pd.read_csv('./sample_data/pokes.csv')
poke_data

Note that read_csv automatically assigns the first line as column index and adds its own default row index. You can specify which column to use as index with the index_col attribute:

poke_data = pd.read_csv('./sample_data/pokes.csv', index_col='Name')
poke_data

Sometimes files don't have column names on the first row. In this case, you can provide an additional parameter to read_csv to assign column names:

poke_data = pd.read_csv('./sample_data/pokes_no_header.csv',
                        names=['Name', 'Type', 'Color', 'Evolves', 'HP'],
                        index_col='Name')
poke_data

Reading data from tables

Pandas has a function called read_table that lets you load files with data in tabular form. This function receives a parameter sep that specifies the separator character in the table. Imagine you have a file with the following contents:

Name|Type|Color|Evolves|HP
Abra|Psychic|Yellow|True|25
Pikachu|Electric|Yellow|True|35
Ekans|Poison|Purple|True|35
Dratini|Dragon|Blue|True|41
Ditto|Normal|Pink|False|48

This table uses the '|' character as separator. We can load the data into pandas with the following call:

poke_data = pd.read_table('./sample_data/pokes_table', sep='|')
poke_data

# You can emulate the behavior of read_csv by passing sep=',' as attribute:
poke_data = pd.read_table('./sample_data/pokes.csv', sep=',')
poke_data

pd.read_table is very useful if you have a file with entries that are separated by a varying number of spaces or tabs. By passing the regular expression sep='\s+' you will be able to load such files. Let's load a file with the following contents:

Name  Type Color   Evolves      HP
Abra Psychic           Yellow  True  25
Pikachu  Electric  Yellow  True  35
Ekans   Poison       Purple  True  35
Dratini                 Dragon              Blue        True              41
Ditto    Normal  Pink  False  48

poke_data = pd.read_table('./sample_data/pokes_varspace', sep='\s+')
poke_data

Reading other formats

The two other common formats you will probably find in practice are Excel-like files and JSON files. Pandas has the functions read_excel and read_json to aid you when working with those files. You can read the official documentation here:

• read_excel
• read_json

Files are not everything, but it's a great start

While databases are probably the most common form of data storage you will deal with, knowing how to read data from files is a fundamental skill when working with Pandas. Lots of online repositories and tutorials offer huge libraries of data in several different file formats.

With the material covered in this article, and with access to Pandas' excellent documentation you will have no problem working with files.

Now that we can get the data into dataframes, the next step is learning how to clean it. In the next article, we will learn some techniques for preparing data for processing.

Thank you for reading!

What to do next

• Share this article with friends and colleagues. Thank you for helping me reach people who might find this information useful.
• You can find the source code for this series in this repo.
• This article is based on Python for Data Analysis. These and other very helpful books can be found in the recommended reading list.
• Send me an email with questions, comments or suggestions (it's in the About Me page)

Pandas provides many options for calculating descriptive statistics and other reduction operations with just a simple function call. You might want to calculate these values as part of a ML/Data Analysis pipeline, or just because you want to get a better understanding of the data you are dealing with.

Most of these operations are similar to NumPy reductions, as they compute and return a single value. In some cases, it returns a structure with equal-or-fewer dimensions than the original.

In this article, we will explore some of the most used functions and see some examples. Great, let's get started!

import pandas as pd
import numpy as np

frame = pd.DataFrame(np.random.rand(4,5),
                     index=['A', 'B', 'C', 'D'],
                     columns=['One', 'Two', 'Three', 'Four', 'Five'])
frame

The first thing we will learn is how to perform sums. The sum function performs sums along the rows axis by default (returns the sum of the values of every column). You can pass axis='columns' as an additional parameter to perform the sum along the columns axis:

frame.sum()

One      2.815213
Two      1.681874
Three    1.862111
Four     1.546057
Five     2.402276
dtype: float64

frame.sum(axis='columns')

A    2.905198
B    2.060995
C    2.842264
D    2.499073
dtype: float64

Pandas also lets you calculate the minimum and maximum values in a dataframe's columns or rows, for this, it provides the functions min and max. Like before, you can specify the axis:

frame.max()

One      0.973939
Two      0.891813
Three    0.790004
Four     0.815889
Five     0.825628
dtype: float64

frame.min(axis='columns')

A    0.027722
B    0.104428
C    0.081066
D    0.066052
dtype: float64

If instead, you are interested in the indexes where the minimum and maximum values are, just use idxmax and idxmin:

frame.idxmax() # All columns have their maximum values at row D

One      A
Two      B
Three    A
Four     C
Five     D
dtype: object

frame.idxmin(axis='columns') # All rows have their minimum at column One

A     Four
B     Five
C    Three
D     Four
dtype: object

Pandas also has functions for calculating (among many others) the mean, median, standard deviation and variance:

frame.mean()

One      0.703803
Two      0.420468
Three    0.465528
Four     0.386514
Five     0.600569
dtype: float64

frame.median(axis='columns')

A    0.686339
B    0.238110
C    0.785882
D    0.699541
dtype: float64

frame.var()

One      0.132691
Two      0.112631
Three    0.129139
Four     0.159410
Five     0.112835
dtype: float64

frame.var(axis='columns')

A    0.134738
B    0.113646
C    0.155681
D    0.129304
dtype: float64

frame.std()

One      0.364268
Two      0.335605
Three    0.359358
Four     0.399262
Five     0.335909
dtype: float64

frame.std(axis='columns')

A    0.367067
B    0.337114
C    0.394564
D    0.359588
dtype: float64

Pandas also has an incredibly useful function called describe. It will calculate a battery of standard reductions and show you the summary:

frame.describe()

The last thing we will learn about is correlation. You can use the corr method to calculate the correlation between two columns (or rows) of a dataframe. This is something you will probably do often if you are into data exploration/analysis:

# Calculate the correlation between the columns One and Three
frame['One'].corr(frame['Five'])

0.879646855332041

You can provide an additional parameter method to specify the correlation method used, the options are:

• pearson : Standard correlation coefficient
• kendall : Kendall Tau correlation coefficient
• spearman : Spearman rank correlation

frame['One'].corr(frame['Five'], method='spearman')

0.19999999999999998

Alternatively, you can calculate the correlation matrix of the dataframe by just calling the corr method:

frame.corr()

Understanding your data usually starts with a call to describe or corr

Calculating a few values from your data can grant you a better understanding of the phenomenon that generated it.

One of the first things you will do when selecting features for a ML algorithm is plotting the result of the correlation matrix. This will give you an idea of which features have a better shot at predicting labels if you intend to train a supervised model.

This, again, is just an example of the many applications of statistical analysis, and these are just some basic functions to aid you in the process.

Now that we learned the basics, we need to talk about pulling the data into Pandas. In the next article, we will learn how to create dataframes from common file formats.

Thank you for reading!

What to do next

• Share this article with friends and colleagues. Thank you for helping me reach people who might find this information useful.
• You can find the source code for this series in this repo.
• This article is based on Python for Data Analysis. These and other very helpful books can be found in the recommended reading list.
• Send me an email with questions, comments or suggestions (it's in the About Me page)

In this article, we will learn about mapping and the apply and applymap functions.

This technique will help you manipulate your data in very convenient ways, and is another important addition to your toolbox.

As always, we will explore the topic with examples that will help you understand what's going on.

Great, let's get started!

Mapping

Mapping means applying a function that transforms the elements of a domain into the elements of another domain. In this case, the entries, rows, and columns in a series or dataframe. Pandas lets you apply functions at element, row, and column level to create new series and dataframes.

Pandas is also compatible with many of the operations defined in NumPy. This lets you apply functions in a very convenient and performant fashion. Let's see some examples:

import numpy as np
import pandas as pd

frame = pd.DataFrame(np.random.randn(4,5),
                     columns=list('abcde'),
                     index=['one', 'two', 'three', 'four'])
frame

# You can apply NumPy functions directly on dataframes.
# You can, for example, calculate the absolute value of every entry
np.abs(frame)

# You can also calculate the 3rd power of every entry
np.power(frame, 3)

You can apply many of NumPy's ufuncs to Pandas data structures, in most situations they provide a result with the same dimensions of the original structure.

Another important (and quite common) operation creates a new structure after applying an operation to every row or column in the original dataframe. Let's see how to create a new structure whose entries are the result of summing every column/row of our frame:

# Panda's apply runs a function along an axis. 
# The default behavior is to run it using the rows axis (apply the operation on every column)

# Let's produce a Series where each entry is the sum of the values in every column:

ser = frame.apply(np.sum)
ser

a    4.274556
b   -0.298998
c    0.845934
d    0.257921
e   -2.676385
dtype: float64

# If you want to perform the operation using columns as an axis (the operation will be applied on a per-row basis)
# You can pass the optional argument axis

ser = frame.apply(np.sum, axis='columns')
ser

one      4.653047
two     -0.252900
three    0.859082
four    -2.856201
dtype: float64

Again, you can use most NumPy ufuncs as an argument for the apply function, but it doesn't end there: You can define your own functions and use them with applymap. The following example applies a function that adds 2 to every entry:

def sum_two(entry):
    return entry + 2

frame.applymap(sum_two)

# You can do this using lambdas, it's usually easier to read:

sum_three = lambda x: x+3

frame.apply(sum_three)

Simple concept, endless applications

Performing mappings lets you do almost anything you need with your data. Anything, from statistical aggregations to advanced machine learning tools are built upon this foundation.

As you may have noticed, the concept is very simple, but knowing how to apply NumPy functions to Pandas data structures will help you on a daily basis. This is even more obvious when you start to explore the potential of applying your own functions!

In the next article, we will learn about data summarization and descriptive statistics.

Thank you for reading!

What to do next

• Share this article with friends and colleagues. Thank you for helping me reach people who might find this information useful.
• You can find the source code for this series in this repo.
• This article is based on Python for Data Analysis. These and other very helpful books can be found in the recommended reading list.
• Send me an email with questions, comments or suggestions (it's in the About Me page)

Arithmetic operations are some of the most fundamental (and important) things you can do with series and dataframes. In this article, we will learn how to perform basic operations using both series and dataframes.

We are interested in the following scenarios:

• Operations between series with the same index.
• Operations between dataframes with the same index.
• Operations between dataframe/series with the same index.
• Operations between series with different indexes.
• Operations between dataframes with different indexes.
• Operations between dataframe/series with different indexes.

Good, let's get started!

Same index, obvious behavior

If two (or more) series/dataframes share the same index (both row and column index in the case of dataframes), operations follow the obvious element-wise behavior you would expect if you've used NumPy in the past:

import pandas as pd
ser_1 = pd.Series([1,2,3,4], index=['a', 'b', 'c', 'd'])
ser_2 = pd.Series([10,20,30,40], index=['a', 'b', 'c', 'd'])

print(ser_1)
print(ser_2)

a    1
b    2
c    3
d    4
dtype: int64
a    10
b    20
c    30
d    40
dtype: int64

# Addition of two series with the same index
ser_1 + ser_2

a    11
b    22
c    33
d    44
dtype: int64

# Subtraction of two series with the same index
ser_2 - ser_1

a     9
b    18
c    27
d    36
dtype: int64

# Multiplication of two series with the same index
ser_1 * ser_2

a     10
b     40
c     90
d    160
dtype: int64

# Division of two series with the same index
ser_2 / ser_1

a    10.0
b    10.0
c    10.0
d    10.0
dtype: float64

The same behavior is shown when you apply operations on two dataframes that share both the row and column index:

import numpy as np
df_1 = pd.DataFrame(np.arange(1,17).reshape(4,4),
                    index= ['Fi', 'Se', 'Th', 'Fo'],
                    columns = ['a', 'b', 'c', 'd'])

df_2 = pd.DataFrame(np.arange(1,17).reshape(4,4) * 10,
                    index= ['Fi', 'Se', 'Th', 'Fo'],
                    columns = ['a', 'b', 'c', 'd'])

df_1

df_2

# Addition of two dataframes with the same index
df_1 + df_2

# Multiplication of two dataframes with the same index
df_1 * df_2

It's also possible to perform operations between dataframes and series that share an index. The default behavior is to align the index of the series with the column index of the dataframe and perform the operations between each row and the series.

# Sum a series and a dataframe
ser_1 + df_1

Different index, outer joins

If you perform operations between series/dataframes with different index, the result will be a new data structure whose index is the union of the original indexes. If you have worked with databases before this is similar to an outer join using the indexes of the original series/dataframes. This is much easier to see with an example:

ser_1 = pd.Series([1,1,1,1,1], index=['a', 'b', 'c', 'd', 'e'])
ser_2 = pd.Series([5,5,5,5,5], index=['c', 'd', 'e', 'f', 'g'])

print(ser_1)
print(ser_2)

a    1
b    1
c    1
d    1
e    1
dtype: int64
c    5
d    5
e    5
f    5
g    5
dtype: int64

If the operation is performed on series with different indexes, the result will contain the result of the operation on all entries whose index is contained in the union of the original indexes. Elements outside of the union will be filled with NaN.

In this case, the union is ['c', 'd', 'e'].

ser_1 + ser_2

a    NaN
b    NaN
c    6.0
d    6.0
e    6.0
f    NaN
g    NaN
dtype: float64

ser_1 * ser_2

a    NaN
b    NaN
c    5.0
d    5.0
e    5.0
f    NaN
g    NaN
dtype: float64

Dataframes have the same behavior, but the unions are performed on both the row and column index.

import numpy as np

# In this case, the union are the elements [a,b,c] in the columns and [Fi,Fo,Th] in the rows

df_1 = pd.DataFrame(np.arange(1,17).reshape(4,4),
                    index= ['Fi', 'Ma', 'Th', 'Fo'],
                    columns = ['a', 'b', 'c', 'd'])

df_2 = pd.DataFrame(np.arange(1,17).reshape(4,4) * 10,
                    index= ['Fi', 'Se', 'Th', 'Fo'],
                    columns = ['a', 'b', 'c', 'e'])

df_1 + df_2

In the case of operations between dataframes and series with different indexes, a union will be performed between the column index of the dataframe and the index of the series:

df_1 + ser_2

Filling in missing values

Instead of using the normal arithmetic operators, you can use a set of built-in Pandas functions that accept an argument to fill-in missing values:

• add/radd
• sub/rsub
• div/rdiv
• mul/rmul
• pow/rpow

Let's revisit series addition and use 0 as placeholder value:

ser_1.add(ser_2, fill_value=1)

a    2.0
b    2.0
c    6.0
d    6.0
e    6.0
f    6.0
g    6.0
dtype: float64

If an entry is not in the overlap of the two series, the sum operation will be performed against a placeholder value of 0. For example, for indexes a/b, both are 1+0, and for f/g it is 5+0. The same behavior applies to dataframes.

Now you know maths

The toughest thing about working with arithmetic operations using pandas data structures is understanding how it works when indexes are not the same. As long as you remember that it behaves like an outer join, everything will be clear and easy.

In the next article, we will talk about mapping and function application, our first advance-y Pandas topics!

Thanks for reading!

What to do next

• Share this article with friends and colleagues. Thank you for helping me reach people who might find this information useful.
• You can find the source code for this series in this repo.
• This article is based on Python for Data Analysis. These and other very helpful books can be found in the recommended reading list.
• Send me an email with questions, comments or suggestions (it's in the About Me page)

Today we will deal with two techniques we need to cover before moving to more advanced Pandas topics: Reindexing and element deletion.

It will be a bit shorter than the first two articles in the series, but that doesn't mean it's not important. Both techniques are very useful, and you will probably use them in your day-to-day work if you become a Pandas practitioner.

Good, let's get started!

Reindexing

Reindexing is a fancy word for creating a new dataframe/series with an altered index.

import pandas as pd

ser = pd.Series([2,1,3,4,7,6,5], index=['b', 'a', 'c', 'd', 'g', 'f', 'e'])
print(ser)

b    2
a    1
c    3
d    4
g    7
f    6
e    5
dtype: int64

The reindex function receives a list of index elements and creates a new dataframe (or series) in which the rows/elements follow the order specified in that list.

For example, we can create a new series where the numbers are ordered in ascending order by providing the following input for reindex:

ordered_ser = ser.reindex(['a', 'b', 'c', 'd', 'e', 'f', 'g'])
print(ordered_ser)

a    1
b    2
c    3
d    4
e    5
f    6
g    7
dtype: int64

You don't need to pass every element in the original index, you can provide a list with only the elements you need:

# This will create a new dataframe with the last four elements, in descending order
ordered_ser = ser.reindex(['g', 'f', 'e', 'd'])
print(ordered_ser)

g    7
f    6
e    5
d    4
dtype: int64

Sometimes you want to reindex the series/dataframe to expand the range of elements. In this case, you will probably find that some of the elements are set to NaN:

ser = pd.Series(['azul', 'rojo', 'verde'], index=[0,4,8])
ser.reindex(range(12))

0      azul
1       NaN
2       NaN
3       NaN
4      rojo
5       NaN
6       NaN
7       NaN
8     verde
9       NaN
10      NaN
11      NaN
dtype: object

# In this case, you can specify a fill method to dictate what will happen to the empty entries
# ffill, for example, performs a forward fill

ser.reindex(range(12), method='ffill')

0      azul
1      azul
2      azul
3      azul
4      rojo
5      rojo
6      rojo
7      rojo
8     verde
9     verde
10    verde
11    verde
dtype: object

Frames behave pretty much the same way, but they also let you reindex by column. Let's take a look at a final reindexing example using a dataframe:

import numpy as np

frame = pd.DataFrame(np.arange(16).reshape(4,4),
                     index = ['First', 'Second', 'Third', 'Fourth'],
                     columns = ['Alpha', 'Beta', 'Gamma', 'Delta'])

frame

# We can reindex using the row index
frame.reindex(['Fourth', 'Second'])

# Or, reindex using the columns
frame.reindex(columns=['Alpha', 'Gamma'])

Deleting elements

Now we will learn how to remove elements from both series and dataframes. This is usually achieved using the drop method.

Note that calls to drop don't alter the original series/dataframe. Instead, they return a new one without the specified elements. If for some reason you need to alter the original series/dataframe, you can pass inplace=True as an argument.

ser = pd.Series([1,2,3,4], index=['a', 'b', 'c', 'd'])
print(ser)

a    1
b    2
c    3
d    4
dtype: int64

# You can pass to drop the index value of the element you want to delete
ser.drop('b')

a    1
c    3
d    4
dtype: int64

# You can also pass a list of index values
ser.drop(['a', 'c'])

b    2
d    4
dtype: int64

Dataframes let you drop elements using both the row index and the column index.

frame

# Let's drop the second and fourth rows
frame.drop(['Second', 'Fourth'])

# If you add an additional argument set to axis='columns' (or axis=1) you will drop using the column index
# Let's get rid of the Alpha and Beta columns
frame.drop(['Alpha', 'Beta'], axis='columns')

Data-wrangling basics

When exploring data, you will need to alter indexes and delete rows with elements you don't need. As with all previous articles, I'd like to encourage you to practice these techniques on your own until you feel comfortable with them.

In the next article, we will learn how to perform arithmetic operations with dataframes and series.

Thank you for reading!

What to do next

• Share this article with friends and colleagues. Thank you for helping me reach people who might find this information useful.
• You can find the source code for this series in this repo.
• This article is based on Python for Data Analysis. These and other very helpful books can be found in the recommended reading list.
• Send me an email with questions, comments or suggestions (it's in the About Me page)

In the last article, we learned about the two basic pandas data structures: Series and DataFrames. We also built a couple of them on our own and learned the basics of indexing and selection.

Today we will learn a bit more about selecting and filtering elements from Pandas data structures. This might seem like an incredibly basic topic, but it's very useful. That's why it's important to understand it well before tackling more advanced topics.

Knowing how to wrangle data is one of the most important skills for anyone working on data science and machine learning, and the foundation of those skills is data selection and filtering.

Good, let's get started!

Playing with Series

Selecting elements from a Series object is pretty straightforward, the next are examples of different ways of selecting elements from a small 8-element series

import pandas as pd
import numpy as np 

ser = pd.Series(np.arange(8), index=['a', 'b', 'c', 'd', 'e', 'f', 'g', 'h'])
print(ser)

a    0
b    1
c    2
d    3
e    4
f    5
g    6
h    7
dtype: int64

# You can select elements from a series using its index
ser['d']

3

# You can also pass a list of index elements if you need to retrieve more than one element
ser[['a', 'd', 'g']]

a    0
d    3
g    6
dtype: int64

Pandas is so cool that it even supports selection with index-based-slices! There is an important distinction between this and regular slices: The last element of the slice is included.

# Select all elements from b to g (both edges included)
ser['b':'g']

b    1
c    2
d    3
e    4
f    5
g    6
dtype: int64

The fact that you are not using the default index does not mean that position-based selection is not permitted. You can still select elements from a Series using integers.

# Select the third (index 2, remember? 0-indexed) from our series
ser[2]

2

# Now, select the elements at indexes 2, 3 and 6
ser[[2,3,6]]

c    2
d    3
g    6
dtype: int64

# And finally, slice selection is still supported (but in this case, the last element is excluded as usual)
ser[2:8]

c    2
d    3
e    4
f    5
g    6
h    7
dtype: int64

Playing with DataFrames

Because of an extra dimension, selecting elements from DataFrames is richer than from Series. We will start with the most basic scenario: Selecting whole columns.

pokedata = {'Name': ['Abra', 'Koffing', 'Milcery', 'Pikachu', 'Shellder', 'Vulpix'],
            'Type': ['Psychic', 'Poison', 'Fairy', 'Electric', 'Water', 'Fire'],
            'HP': [25, 40, 45, 35, 30, 38],
            'Speed': [90, 35, 34, 90, 40, 65],
            'Color': ['Yellow', 'Purple', 'White', 'Yellow', 'Purple', 'Red'],
            'FirstGen': [True, True, False, True, True, True]}

# We will use the Name column as index
pframe = pd.DataFrame(pokedata).set_index('Name')
pframe

# You can select a column from the frame by passing the name between brackets
pframe['Type']

Name
Abra         Psychic
Koffing       Poison
Milcery        Fairy
Pikachu     Electric
Shellder       Water
Vulpix          Fire
Name: Type, dtype: object

# If you pass a list of column names you will retrieve them in that order
pframe[['FirstGen', 'HP', 'Color']]

Square brackets also support selection based on content. Let's select rows that satisfy specific criteria to see how it works.

# Select all Pokemon with speed lower than 50
pframe[pframe['Speed'] < 50]

# Select all yelloe Pokemon
pframe[pframe['Color'] == 'Yellow']

# Select all first generation Pokemon with HP greater than 37
pframe[(pframe['FirstGen'] == True) & (pframe['HP'] > 37)]

You can go as specific as you want with this form of filtering. Selecting subsets of rows is a very useful skill, so play a bit selecting based on your own conditions.

Good, I think we are good when it comes to selecting based on column tags, now let's select specific rows based on the index. For this, Pandas offers you two very valuable functions: loc and iloc.

loc lets you select based on axis labels, whereas iloc lets you select based on integers that represent the position of the row. Again it's easier to understand with examples:

# Select the row with index Shellder
pframe.loc['Shellder']

Type         Water
HP              30
Speed           40
Color       Purple
FirstGen      True
Name: Shellder, dtype: object

# You can pass a list of index values and get the rows in the specified order
pframe.loc[['Shellder', 'Abra', 'Pikachu']]

# It's also possible to get only a subset of columns using loc
# Let's get data for Shellder, but only the Type and Color
pframe.loc['Shellder', ['Type', 'Color']]

Type      Water
Color    Purple
Name: Shellder, dtype: object

# If instead, you need to select elements based on order, you can use iloc
# For example, the following line selects the third row (index 2, because 0-indexed)
pframe.iloc[2]

Type        Fairy
HP             45
Speed          34
Color       White
FirstGen    False
Name: Milcery, dtype: object

# Just like loc, you can pass a list of indexes and it will return a dataframe with rows in that order
pframe.iloc[[2,4,0]]

# Remember that little trick for selecting just a subset of columns? It also works for iloc
# This selects the third row, and only the Type (column at position 0) and HP (column at position 1)
pframe.iloc[2, [0, 1]]

Type    Fairy
HP         45
Name: Milcery, dtype: object

A word on numeric indexes

loc and iloc are pretty straightforward, but it's important to understand the difference between them. This is especially true when dealing with numeric indexes. A dataframe with numeric indexes that are not in order, starting at 0 and without interruption will behave weird unless you remember how those function differ. Take the following dataframe as example:

frame = pd.DataFrame(np.arange(36).reshape(6,6), 
                     columns = ['a', 'b', 'c', 'd', 'e', 'f' ],
                     index = [5, 3, 1, 4, 2, 0])
frame

# Now, let's check what loc[2] and iloc[2] return
frame.loc[2]

a    24
b    25
c    26
d    27
e    28
f    29
Name: 2, dtype: int64

frame.iloc[2]

a    12
b    13
c    14
d    15
e    16
f    17
Name: 1, dtype: int64

Can you see they return different rows? This happens because loc[2] looks for a row with an index with a value of two, in this case, the penultimate row. On the other hand, iloc[2] just looks for the third row, the one with positional index 2, starting from 0. If you remember this, you will have no problem dealing with dataframes with numeric indexes!

Selection is a rich topic

One of the great things about Pandas is how easy it makes selecting only the data you need. As you may already know, almost every advanced application rests on this foundation, and know you know how to use it!

Now that we can select data and understand how indexes work, we can deal with two interesting topics: Reindexing and deletion of entries. The next article will talk about these topics, so make sure to come back to check it.

Thanks for reading!

What to do next

• Share this article with friends and colleagues. Thank you for helping me reach people who might find this information useful.
• You can find the source code for this series in this repo.
• This article is based on Python for Data Analysis. These and other very helpful books can be found in the recommended reading list.
• Send me an email with questions, comments or suggestions (it's in the About Me page)