Pandas Data Structures: DataFrame basics

In the previous post I’ve summed up some of the basics of using pandas Series data structure. Now, let’s take a quick look at DataFrame.

DataFrame is a 2-dimensional labeled data structure with columns of potentially different types. It’s sort of like an SQL table, or a dict of Series objects.

This is the object you use a lot when digging through some data with pandas. I’m not going to describe how to create a DataFrame from lists and dicts, because for me it’s more common that I need to dig through some existing data rather than create new data. As it happens with existing data, it comes in all kinds of formats, and for the most typical ones, it’s easy to read it into a DataFrame for further manipulation.

For my examples I’ll be using movie_metadata.csv that has been scraped from by Chuan Sun (@sundeepblue on Github) .

Read to DataFrame

Quite often your data comes as an excel or .csv file. To read it into a DataFrame, just use, well, read_excel or read_csv.

import pandas as pd
df = pd.read_csv('movie_metadata.csv')

In ideal world (and in this example) that would be it, but most of the time the original excel or csv files are full of mess. Luckily you don’t need to write any code to get only one sheet, or skip rows, or fix encoding - read_excel and read_csv have a bunch of options to help you fix things. There are also standard methods to read from an SQL query or database table, or JSON.
Unfortunately, if you have XML, you’ll need to do some parsing yourself. Perhaps, I’ll write an example of how I did it in some other post. But here, we have a nice clean csv.

What’s in a DataFrame?

Now, when you have a DataFrame (with loads of data), the fun part begins. Pandas allow you to do a lot with a DataFrame with a couple of lines of code. But first, let’s see what we’ve got in it.

Let’s see how big is the DataFrame we’ve got:

(5043, 28)

There you go - 5043 rows and 28 columns.

What are the column names?

['color', 'director_name', 'num_critic_for_reviews', 'duration', 'director_facebook_likes', 'actor_3_facebook_likes', 'actor_2_name', 'actor_1_facebook_likes', 'gross', 'genres', 'actor_1_name', 'movie_title', 'num_voted_users', 'cast_total_facebook_likes', 'actor_3_name', 'facenumber_in_poster', 'plot_keywords', 'movie_imdb_link', 'num_user_for_reviews', 'language', 'country', 'content_rating', 'budget', 'title_year', 'actor_2_facebook_likes', 'imdb_score', 'aspect_ratio', 'movie_facebook_likes']

Like with Series you can use .head() or tail() to “preview” what the data looks like:

    color		director_name  		num_critic_for_reviews  	duration  \
0  Color   	James Cameron                 723.0     			178.0   
1  Color  	Gore Verbinski                   	302.0     			169.0   
2  Color      	Sam Mendes                   	602.0     			148.0   

   	director_facebook_likes  	actor_3_facebook_likes      actor_2_name  \
0                      0.0                   	855.0  			Joel David Moore   
1                    563.0                  	1000.0     		Orlando Bloom   
2                      0.0                   	161.0      		Rory Kinnear   

   	actor_1_facebook_likes        gross                           genres  \
0                  1000.0  		760505847.0  	Action|Adventure|Fantasy|Sci-Fi   
1                 40000.0  		309404152.0       	Action|Adventure|Fantasy   
2                 11000.0  		200074175.0       	 Action|Adventure|Thriller   


Let’s get some data

Now that we have an idea of what’s in this DataFrame, we can start digging some data out of it.

We can start by getting a column by its name, and set a range of indices we want, say we want first three movie titles:

0                                      Avatar 
1    Pirates of the Caribbean: At Worlds End 
2                                     Spectre 
Name: movie_title, dtype: object

We can specify multiple columns too:

                                 movie_title   director_name
0                                    Avatar    James Cameron
1  Pirates of the Caribbean: At Worlds End   Gore Verbinski
2                                   Spectre       Sam Mendes

We can see top 5 directors with the most movies made by them (at least from these records).

Steven Spielberg    26
Woody Allen         22
Martin Scorsese     20
Clint Eastwood      20
Ridley Scott        17
Name: director_name, dtype: int64

We can see what’s the average number of movies for a director:


Let’s find all the movies where Woody Allen is a director and plays the main character:

df[(df['director_name'] =='Woody Allen') & (df['actor_1_name']=='Woody Allen')]['movie_title']
1861                      The Curse of the Jade Scorpion 
2287                                Deconstructing Harry 
2430                                   Small Time Crooks 
2457                                       Anything Else 
2577                                    Hollywood Ending 
2695                                    New York Stories 
3889                                          Annie Hall 
4250                                             Sleeper 
4252    Everything You Always Wanted to Know About Sex...
4324                                             Bananas 
Name: movie_title, dtype: object

How many movies are there where a director and the main actor are the same person?

len(df[(df['director_name'] == df['actor_1_name'])])

Let’s say we’d like to find out whose movies have brought more gross overall. We’ll need to use groupby and aggregate for that

movies = df[['director_name','gross']]

movies.groupby('director_name').aggregate(sum).sort_values(by="gross", ascending=False).head(10)
# This line means "Group the rows by director_name. 
# Add up all the values for each director_name, 
# then sort values in column gross in descending order. Show only first 10".

Steven Spielberg   4.114233e+09
Peter Jackson      2.592969e+09
Michael Bay        2.231243e+09
Tim Burton         2.071275e+09
Sam Raimi          2.049549e+09
James Cameron      1.948126e+09
Christopher Nolan  1.813228e+09
George Lucas       1.741418e+09
Joss Whedon        1.730887e+09
Robert Zemeckis    1.619309e+09

Just like that. One line, no loops. Isn’t it great?