Note

This is a static copy of a Jupyter notebook.

You can access a live version allowing you to modify and execute the code using Binder.

7.1. Dataframes: Basics

7.1.1. Introduction

A Dataframe is a Pandas object for organizing data that acts much like a spreadsheet. You can think of as a spreadsheet. It is two dimesional in that it has both rows and columns. A Series is a single column of data with an index that identifies rows. A Dataframe is as a collection of Series, that you can think of as columns, that all share the same index.

The shared index is going to allow us to examine, organize, manipulate lots of data very efficiently, but it’s going to take some practice to get used to the idea of a Dataframe and what you can do with it. The good news is that if you’ve completed the previous section on Series, you’ll find most of what we are going to with Dataframes to be similar, if not identical.

We will explore just few things you can do with Dataframes in this course. If you want to see what else is available, the Pandas documentation covers the wide variety of methods available.

7.1.2. Creating and Copying Dataframes

# make sure you always do the import that makes pandas available
import pandas as pd

7.1.2.1. Creating Dataframes

To create a new Dataframe, we can use the pd.DataFrame method. Empty Dataframes can be constructed by using the .DataFrame() method without any arguments. Note that the ‘D’ and the ‘F’ in .DataFrame() are capitalized.

# constructing an empty Dataframe
my_df = pd.DataFrame()
type(my_df) # checking the datatype of the result
pandas.core.frame.DataFrame

7.1.2.2. Creating a Dataframe from a CSV

We usually won’t start with an empty Dataframe. Instead, we will create it by reading a .csv file. To do so, we use the pd.read_csv() method with the file name as an argument.

By convention, dataframes are often assigned to the variable df. I’d like you to stick with this convention, if you are working with one dataframe at a time.

# read the csv file, using the file name as a string, assign the result to df
urlg = 'https://raw.githubusercontent.com/'
repo = 'bsheese/CSDS125ExampleData/master/'
fnme = 'data_albums_sales_wikipedia.csv'
df = pd.read_csv(urlg + repo + fnme)

#check the result
df.head()
artist album year genre cert_sales claim_sales
0 Michael Jackson Thriller 1982 Pop, post-disco, funk, rock 47.3 66
1 AC/DC Back in Black 1980 Hard rock 29.4 50
2 Meat Loaf Bat Out of Hell 1977 Hard rock, glam rock, progressive rock 21.7 50
3 Pink Floyd The Dark Side of the Moon 1973 Progressive rock 24.4 45
4 Whitney Houston / Various artists The Bodyguard 1992 R&B, soul, pop, soundtrack 28.4 45

The Dataframe we have constructed is built out of data from the wikipedia page on best-selling music albums.

7.1.2.3. Copying Dataframes vs. Creating Views

Making a copy of a Dataframe is exactly like making a copy of a Series. If you want to duplicate a Dataframe, you need to be careful not to accidentally make an alias that refers to the same Dataframe object instead of making an entirely new object. To explicitly make a copy of a Dataframe you can use the .copy() method, just as you would with a list or Series.

# the code below does NOT make a copy of the Dataframe, it only makes an alias

df_alias = df # this just created a second name for the same object

df_alias is df # this checks to see if they are the same object
True
# the code below makes a copy of the dataframe

df_copy = df.copy() # notice we've added the copy method here

df_copy is df # this checks to see if they are the same object
False

7.1.3. Examining the Dataframe

7.1.3.1. .info()

We can use the Dataframe method, .info(), to get a breif summary of the Dataframe. This is a handy method that contains quite a bit of information that will eventually come in useful. For now, just pay attention to the number of entries which indicates the number of rows in the dataframe and the specific column names.

# view information about the dataframe
df.info()
<class 'pandas.core.frame.DataFrame'>
RangeIndex: 33 entries, 0 to 32
Data columns (total 6 columns):
 #   Column       Non-Null Count  Dtype
---  ------       --------------  -----
 0   artist       33 non-null     object
 1   album        33 non-null     object
 2   year         33 non-null     int64
 3   genre        33 non-null     object
 4   cert_sales   33 non-null     float64
 5   claim_sales  33 non-null     int64
dtypes: float64(1), int64(2), object(3)
memory usage: 1.7+ KB

7.1.3.2. .head() and .tail()

.head() and .tail() both work with Dataframes, but now show the first and last rows of all columns in the dataframe.

# view the first three rows
df.head(3)
artist album year genre cert_sales claim_sales
0 Michael Jackson Thriller 1982 Pop, post-disco, funk, rock 47.3 66
1 AC/DC Back in Black 1980 Hard rock 29.4 50
2 Meat Loaf Bat Out of Hell 1977 Hard rock, glam rock, progressive rock 21.7 50
# view the last three rows
df.tail(3)
artist album year genre cert_sales claim_sales
30 Santana Supernatural 1999 Latin rock 20.5 30
31 Guns N' Roses Appetite for Destruction 1987 Hard rock 21.9 30
32 Elton John Goodbye Yellow Brick Road 1973 rock, pop rock glam rock 8.5 30

7.1.3.3. Examining index and column attributes

As with a Series, we can examine the index of a Dataframe using dot notation. In this case, no data was specified as an index, so the index is a range object that produces integers for each row.

# view the dataframe's index
df.index
RangeIndex(start=0, stop=33, step=1)

The columns of a Dataframe can also be examined using dot notation to access the column attribute.

# view the dataframe's columns
df.columns
Index(['artist', 'album', 'year', 'genre', 'cert_sales', 'claim_sales'], dtype='object')

7.1.3.4. Views into the Dataframe

As with Series, we can modify how we look at a Dataframe without altering it, by creating a ‘view’. As Dataframes can sometimes get very large (think millions of rows) we will sometimes want to avoid making a copy of the dataframe if we don’t need to do so.

We will work on this issue more in subsequent readings. For now, just try to pay attention to the difference between creating a modified view into the Dataframe versus actually updating (changing) the Dataframe.

7.1.4. Working with the Index and Columns

7.1.4.1. Setting and Resetting the Index

We can set any series in the Dataframe to be the index of the Dataframe using .set_index(). If your analysis revolves around one series in particular, then using it as the index will make some operations a bit easier. If you have a really large amount of data to analyze, then both setting your index and sorting by it will speed up some kinds of Dataframe operations.

# the current index is just a range object because no index was specified
df.head(3)
artist album year genre cert_sales claim_sales
0 Michael Jackson Thriller 1982 Pop, post-disco, funk, rock 47.3 66
1 AC/DC Back in Black 1980 Hard rock 29.4 50
2 Meat Loaf Bat Out of Hell 1977 Hard rock, glam rock, progressive rock 21.7 50
# uses the label of the artist series to set the index
df = df.set_index('artist')

# the artist series now serves as the shared index for the Dataframe
df.head(3)
album year genre cert_sales claim_sales
artist
Michael Jackson Thriller 1982 Pop, post-disco, funk, rock 47.3 66
AC/DC Back in Black 1980 Hard rock 29.4 50
Meat Loaf Bat Out of Hell 1977 Hard rock, glam rock, progressive rock 21.7 50

If we want to move a series back out of the index, we can do so by using reset_index().

# moves whatever series is in the index back out into the regular columns
df = df.reset_index()

# with no specified index, the range object has been applied as the index
df.head(3)
artist album year genre cert_sales claim_sales
0 Michael Jackson Thriller 1982 Pop, post-disco, funk, rock 47.3 66
1 AC/DC Back in Black 1980 Hard rock 29.4 50
2 Meat Loaf Bat Out of Hell 1977 Hard rock, glam rock, progressive rock 21.7 50
# setting year as the index
df = df.set_index('year')

# sort by index (year)
df = df.sort_index()

#check the result
df.head()
artist album genre cert_sales claim_sales
year
1967 The Beatles Sgt. Pepper's Lonely Hearts Club Band Rock 18.2 32
1969 The Beatles Abbey Road Rock 14.4 30
1971 Led Zeppelin Led Zeppelin IV Hard rock, heavy metal, folk rock 29.0 37
1973 Elton John Goodbye Yellow Brick Road rock, pop rock glam rock 8.5 30
1973 Pink Floyd The Dark Side of the Moon Progressive rock 24.4 45

7.1.4.2. Updating Index Labels and Columns Label

Just like a Series, the Dataframe index object and the Dataframe column object are not mutable. However, we can modify either a list or a series and assign it to the index or columns. Note: The list or series you are using to replace the index of columns must be the same length as the index or columns in the existing dataframe.

# an example of updating the index labels

# copy the index into a list
year_list = df.index.to_list()

# create a list of decades from year_list
decade_list = []
for year in year_list:
  decade_list.append((year // 10) * 10)

# convert the list into a series, and set the series name
decade_series = pd.Series(data=decade_list, name='decade')

# kick year out of the index, so we don't overwrite it
df = df.reset_index()

# assign decade_series to the index
df.index = decade_series

# check the result
df.head()
year artist album genre cert_sales claim_sales
decade
1960 1967 The Beatles Sgt. Pepper's Lonely Hearts Club Band Rock 18.2 32
1960 1969 The Beatles Abbey Road Rock 14.4 30
1970 1971 Led Zeppelin Led Zeppelin IV Hard rock, heavy metal, folk rock 29.0 37
1970 1973 Elton John Goodbye Yellow Brick Road rock, pop rock glam rock 8.5 30
1970 1973 Pink Floyd The Dark Side of the Moon Progressive rock 24.4 45
# an example of updating the column index
df.columns = df.columns.str.capitalize()

# check the result
df.head()
Year Artist Album Genre Cert_sales Claim_sales
decade
1960 1967 The Beatles Sgt. Pepper's Lonely Hearts Club Band Rock 18.2 32
1960 1969 The Beatles Abbey Road Rock 14.4 30
1970 1971 Led Zeppelin Led Zeppelin IV Hard rock, heavy metal, folk rock 29.0 37
1970 1973 Elton John Goodbye Yellow Brick Road rock, pop rock glam rock 8.5 30
1970 1973 Pink Floyd The Dark Side of the Moon Progressive rock 24.4 45

Because ‘decade’ is the name of the index, it was not capitalized when we reassigned the column names. A quick way to address this with the code we’ve already written, would be to reset the index to move decade into the columns before we do the column labels update.

7.1.5. An Example

Capitalized column labels looks nice for display, but are just one extra thing to worry about typing correctly while coding, so I’m going to put them back for now. I’m also going to move decade out the index and put year into the index.

# reassign column labels to a non-capitalized form
df.columns = df.columns.str.lower()

# move decade out of the index and into the columns
df = df.reset_index()

# move year into the index
df = df.set_index('year')

# check the result
df.head(3)
decade artist album genre cert_sales claim_sales
year
1967 1960 The Beatles Sgt. Pepper's Lonely Hearts Club Band Rock 18.2 32
1969 1960 The Beatles Abbey Road Rock 14.4 30
1971 1970 Led Zeppelin Led Zeppelin IV Hard rock, heavy metal, folk rock 29.0 37