Creating a DataFrame from a Dictionary

You can create DataFrames from a list of dictionaries or from a dictionary, where each key is a column label with the values for that key holding the data for the column. Listing 9.1 shows how to create a DataFrame by creating a list of data for each column and then creating a dictionary with the column names as keys and these lists as the values. The listing shows how to then pass this dictionary to the DataFrame constructor to construct the DataFrame.

Listing 9.1 Creating a DataFrame from a Dictionary

Click here to view code image

first_names = ['shanda', 'rolly', 'molly', 'frank',
               'rip', 'steven', 'gwen', 'arthur']

last_names = ['smith', 'brocker', 'stein', 'bach',
              'spencer', 'de wilde', 'mason', 'davis']

ages = [43, 23, 78, 56, 26, 14, 46, 92]
data = {'first':first_names,
        'last':last_names,
        'ages':ages}

participants = pd.DataFrame(data)

The resulting DataFrame, participants, looks as follows in Colab or in a Jupyter notebook:

You can see the column labels across the top, the data in each row, and the index labels to the left.

Creating a DataFrame from a List of Lists

You can create a list of lists, with each sublist containing the data for one row, in the order of the columns:

Click here to view code image

data = [["shanda", "smith", 43],
        ["rolly", "brocker", 23],
        ["molly", "stein", 78],
        ["frank", "bach", 56],
        ["rip", "spencer", 26],
        ["steven", "de wilde", 14],
        ["gwen", "mason", 46],
        ["arthur", "davis", 92]]

Then you can use this as the data argument:

Click here to view code image

participants = pd.DataFrame(data)
participants

You get the same result as when creating a DataFrame from a dictionary:

Notice that the resulting DataFrame has been created with integer column names. This is the default if no column names are supplied. You can supply column names explicitly as a list of strings:

Click here to view code image

column_names = ['first', 'last', 'ages']

Similarly, you can supply index labels as a list:

Click here to view code image

index_labels = ['a', 'b', 'c', 'd', 'e', 'f', 'g', 'h']

These labels are then used during initialization, using the parameters columns and index:

Click here to view code image

participants = pd.DataFrame(data,
                            columns=column_names,
                            index=index_labels)

Creating a DataFrame from a File

While creating DataFrames from dictionaries and lists is possible, the vast majority of the time you will create DataFrames from existing data sources. Files are the most common of these data sources. Pandas supplies functions for creating DataFrames from files for many common file types, including CSV, Excel, HTML, JSON, and SQL database connections.

Say that you want to open a CSV file from the FiveThirtyEight website, https://data.fivethirtyeight.com, under the data set college_majors. After you unzip and upload the CSV file to Colab, you open it by simply supplying its path to the Pandas read_csv function:

Click here to view code image

college_majors = pd.read_csv('/content/all-ages.csv')
college_majors

Pandas uses the data in the CSV file to determine column labels and column types.

Heads and Tails

To see the top rows of a DataFrame, you can use the head method, which returns the top five rows:

college_majors.head()

The head method takes an optional argument, which specifies the number of rows to return. You would specify the top three rows like this:

college_majors.head(3)

The tail method works in a similar way to head but returns rows from the bottom. It also takes an optional argument that specifies the number of rows to return:

college_majors.tail()

Descriptive Statistics

Once I’ve taken a look at some rows from a DataFrame, I like to get a sense of the shape of the data. One tool for doing this is the DataFrame describe method, which produces various descriptive statistics about the data. You can call describe with no arguments, as shown here:

college_majors.describe()

This method calculates the count, mean, standard deviation, minimum, maximum, and quantiles for columns containing numeric data. It accepts optional arguments to control which data types are processed and the ranges of the quantiles returned. To change the quantiles, you use the percentiles argument:

Click here to view code image

college_majors.describe(percentiles=[0.1, 0.9])

This example specifies percentiles for 10% and 90% rather than the default 25% and 75%. Note that 50% is inserted regardless of the argument.

If you want to see statistics calculated from nonnumeric columns, you can specify which data types are processed. You do this by using the include keyword. The value passed to this keyword should be a sequence of data types, which can be NumPy data types, such as np.object. In Pandas, strings are of type object, so the following includes columns with string data types:

Click here to view code image

import numpy as np
college_majors.describe(include=[np.object])

This would also find the string name of the data type, which, in the case of np.object, would be object. The following returns statistics appropriately for the type:

Click here to view code image

college_majors.describe(include=['object'])

So, for strings, you get the count, the number of unique values, the top value, and the frequency of this top value:

You can pass the string all instead of a list of data types to produce statistics for all the columns:

Click here to view code image

college_majors.describe(include='all')

In case you want to exclude certain data types rather than specify which ones to include, Pandas supplies the exclude argument, which takes the same types of arguments as include:

Click here to view code image

college_majors.describe(exclude=['int'])

Accessing Data

Once you have taken an initial peek at a frame using head or tail and gotten a sense of the shape of the data using describe, you can start looking at the data and individual columns, rows, or cells.

This is the participants DataFrame from earlier in the chapter:

participants

Bracket Syntax

To access columns or rows in Pandas DataFrames, you need to use bracket syntax. This syntax is great for interactive sessions where you are exploring and playing with data, and using it is a best practice.

To access a single column, you supply the column name as an argument in brackets, much as you would a dictionary key:

Click here to view code image

participants['first']
a    shanda
b     rolly
c     molly
d     frank
e       rip
f    steven
g      gwen
h    arthur
Name: first, dtype: object

You can see that this returns the data for the column along with its index, label, and data type. If a column name does not contain dashes or special characters, and if the column name is not the same as an existing attribute of the DataFrame, you can access the column as an attribute.

For example, here is how you access the ages column:

Click here to view code image

participants.ages
a    43
b    23
c    78
d    56
e    26
f    14
g    46
h    92
Name: ages, dtype: int64

This would not work with the columns first or last, as these already exist as attributes of the DataFrame.

To access multiple columns, you specify the column label as a list:

Click here to view code image

participants[['last', 'first']]

This returns a DataFrame with only the requested columns.

The bracket syntax is overloaded to allow you to grab rows as well as columns. To specify rows, you use a slice as an argument. If the slice uses integers, then those integers represent the row numbers to return. To return rows 3, 4, and 5 of the DataFrame participants, for example, you can use the slice 3:6:

participants[3:6]

You can also slice using index labels. When you use labels to slice, the last value is included. So to get rows a, b, and c, you slice using a:c:

participants['a':'c']

You can indicate which rows to return by using a list of Booleans. The list should have one Boolean per row: True for the desired rows, and False for the others. The following example returns the second, third, and sixth rows:

Click here to view code image

mask = [False, True, True, False, False, True, False, False]
participants[mask]

The bracket syntax provides a very convenient and easy-to-read way to access data. It is often used in interactive sessions when experimenting with and exploring DataFrames, but it is not optimized for performance with large data sets. The recommended way to index into DataFrames in production code or for large data sets is to use the DataFrame loc and iloc indexers. These indexers use a bracket syntax very similar to what you have seen here. The loc indexer indexes using labels, and iloc uses index positions.

Optimized Access by Label

With the loc indexer, you can supply a single label, and the values for that row will be returned. To get the values from the row labeled c, for example, you simply supply c as an argument:

Click here to view code image

participants.loc['c']
first    molly
last     stein
ages        78
Name: c, dtype: object

You can provide a slice of labels, and once again, the last label is included:

participants.loc['c':'f']

Or you can provide a sequence of Booleans:

Click here to view code image

mask = [False, True, True, False, False, True, False, False]
participants.loc[mask]

An optional second argument can indicate which columns to return. If you want to return all the rows for the column first, for example, you specify all rows with a slice, a comma, and the column label:

Click here to view code image

participants.loc[:, 'first']
a    shanda
b     rolly
c     molly
d     frank
e       rip
f    steven
g      gwen
h    arthur
Name: first, dtype: object

You could provide a list of column labels:

Click here to view code image

participants.loc[:'c', ['ages', 'last']]

Or you could provide a list of Booleans:

Click here to view code image

participants.loc[:'c', [False, True, True]]

Optimized Access by Index

The iloc indexer enables you to use index positions to select rows and columns. Much as you’ve seen before with brackets, you can use a single value to specify a single row:

Click here to view code image

participants.iloc[3]
first    frank
last      bach
ages        56

Name: d, dtype: object

Or you can specify multiple rows by using a slice:

participants.iloc[1:4]

You can, optionally, indicate which column to return by using a second slice:

participants.iloc[1:4, :2]

Masking and Filtering

A powerful feature of DataFrames is the ability to select data based on values. You can use comparison operators with columns to see which values meet some condition. For example, if you want to see which rows of the college_majors DataFrame have the value Humanities & Liberal Arts as a major category, you can use the equality operator (==):

Click here to view code image

college_majors.Major_category == 'Humanities & Liberal Arts'
0      False
1      False
2      False
3      False
       ...
169    False
170    False
171     True
172     True
Name: Major_category, Length: 173, dtype: bool

This produces a pandas.Series object that contains True for every row that matches the condition. A series of Booleans is mildly interesting, but the real power comes when you combine it with an indexer to filter the results. Remember that loc returns rows for every True value of an input sequence. You can make a condition based on a comparison operator and a row, for example, as shown here for the greater-than operator and the row Total:

Click here to view code image

total_mask = college_majors.loc[:, 'Total'] > 1200000

You can use the result as a mask to select only the rows that meet this condition:

Click here to view code image

top_majors = college_majors.loc[total_mask]
top_majors

You can use the min() method to check whether the resulting DataFrame meets the condition:

top_majors.Total.min()
1438867

Now say that you want to see which major categories have the lowest unemployment rates. You can use describe on a single column as well as with a full DataFrame. If you use describe on the column Unemployment_rate, for example, you can see that the top rate for the bottom percentile is 0.046261:

Click here to view code image

college_majors.Unemployment_rate.describe()
count    173.000000
mean       0.057355
std        0.019177
min        0.000000
25%        0.046261
50%        0.054719
75%        0.069043
max        0.156147
Name: Unemployment_rate, dtype: float64

You can create a mask for all rows with an unemployment rate less than or equal to this:

Click here to view code image

employ_rate_mask = college_majors.loc[:, 'Unemployment_rate'] <= 0.046261

And you can use this mask to produce a DataFrame with only these rows:

Click here to view code image

employ_rate_majors = college_majors.loc[employ_rate_mask]

Then you can use the pandas.Series object’s unique method to see which major categories are in the resulting DataFrame:

Click here to view code image

employ_rate_majors.Major_category.unique()
array(['Agriculture & Natural Resources', 'Education', 'Engineering',
       'Biology & Life Science', 'Computers & Mathematics',
       'Humanities & Liberal Arts', 'Physical Sciences', 'Health',
       ‘Business’], dtype=object)

All these categories have at least one row with an employment rate that meets the condition.

Pandas Boolean Operators

You can use the three Boolean operators AND (&), OR (|), and NOT (~) with the results of your conditions. You can use & or | to combine conditions and create more complex ones. You can use ~ to create a mask that is the opposite of your condition.

For example, you can use AND to create a new mask based on the previous ones to see which major categories of the most popular majors have a low unemployment rate. To do this, you use the & operator between your existing masks to produce a new one:

Click here to view code image

total_rate_mask = employ_rate_mask & total_mask
total_rate_mask
0      False
1      False
2      False
3      False
4      False
      ...
168    False
169    False
170    False
171    False
172    False
Length: 173, dtype: bool

By looking at the resulting DataFrame, you can see which of the most popular majors have the lowest unemployment rates:

Click here to view code image

college_majors.loc[total_rate_mask]

You can use the ~ operator with your employment rate mask to create a DataFrame whose rows all have an employment rate higher than the bottom percentile:

Click here to view code image

lower_rate_mask = ~employ_rate_mask
lower_rate_majors = college_majors.loc[lower_rate_mask]

You can check this work by using the min method on the Unemployment_rate column to see that it is above the top rate for the bottom percentile:

Click here to view code image

lower_rate_majors.Unemployment_rate.min()
0.046261360999999994

To select all the rows that either fit the top majors condition or the employment rate condition, you can use the | operator:

Click here to view code image

college_majors.loc[total_mask | employ_rate_mask]

The resulting DataFrame contains all the rows that fit either condition.

The replace Method

The replace method finds and replaces values across a DataFrame. For example, you can use it to replace the name rolly with Smiley:

Click here to view code image

participants.replace('rolly', 'Smiley')

                       ...

This method also works with regular expressions. Here is how you construct a regular expression that matches words starting with s and replaces the s with S:

Click here to view code image

participants.replace(r'(s)([a-z]+)', r'S2', regex=True)

Both DataFrames and the pandas.Series object have an apply() method that can call a function on values. In the case of a pandas.Series object, the apply() method calls a function of your choosing on every value in the pandas.Series object individually.

Say that you define a function that capitalizes any string passed to it:

Click here to view code image

def cap_word(w):
    return w.capitalize()

Then, if you pass it as an argument to apply() on the column first, it capitalizes each first name:

Click here to view code image

participants.loc[:, 'first'].apply(cap_word)
a    Shanda
b     Rolly
c     Molly
d     Frank
e       Rip
f    Steven
g      Gwen
h      Paul
Name: first, dtype: object

In the case of a DataFrame, apply takes a row as an argument, enabling you to produce new values from the columns of that row. Say that you define a function that uses values from the columns first and Age:

Click here to view code image

def say_hello(row):
    return f'{row["first"]} is {row["Age"]} years old.'

You can then apply the function to the whole DataFrame:

Click here to view code image

participants.apply(say_hello, axis=1)
a    shanda is 42 years old.
b     rolly is 22 years old.
c     molly is 77 years old.
d     frank is 98 years old.
e       rip is 25 years old.
f    steven is 13 years old.
g      gwen is 45 years old.
h      paul is 91 years old.
dtype: object

You can use this method to call a function across rows or across columns. You use the axis argument to indicate whether your function should expect a row or a column.