Data manipulation with Pandas: From loading file to data visualization

Pandas is a library written for data manipulation and analysis in Python. It offers data structures and operations for manipulating numerical tables and time series.

We will use five techniques between a bunch of functionality that we could have. But let's try to place ourselves in the context of carrying out an exploratory study and will use five techniques most used during exploratory data analysis. To carry out learning from the beginning to the visualization without really using other dependencies.

The techniques that we will go through together are:

• Loading files

• Handle missing data

• Manipulation of string data

• Joining data

• Visualization data

Before loading files, we will import the Pandas library first.

import pandas as pd

Also, we will refer to some parts of the previous post.

Loading files

The Pandas library has many functions for reading a file, according to the file type.

Usually, the function name is read_XXXX, XXXX represents the file type in lower case.

Allows file types are the following: CSV, Excel, JSON, XML, HTML, Pickle, ...

If you want to learn more about file types, you should read the documentation.

In our example, we will try to read a JSON and HTML file.

• JSON

We will try to retrieve data from the current URI https://api.github.com/users/hadley/orgs. This URI returns JSON data.

github = pd.read_json("https://api.github.com/users/hadley/orgs")
github.info()

<class 'pandas.core.frame.DataFrame'>
RangeIndex: 10 entries, 0 to 9
Data columns (total 12 columns):
 #   Column              Non-Null Count  Dtype 
---  ------              --------------  ----- 
 0   login               10 non-null     object
 1   id                  10 non-null     int64 
 2   node_id             10 non-null     object
 3   url                 10 non-null     object
 4   repos_url           10 non-null     object
 5   events_url          10 non-null     object
 6   hooks_url           10 non-null     object
 7   issues_url          10 non-null     object
 8   members_url         10 non-null     object
 9   public_members_url  10 non-null     object
 10  avatar_url          10 non-null     object
 11  description         8 non-null      object
dtypes: int64(1), object(11)
memory usage: 1.1+ KB

• HTML

This time we will try to retrieve data from this URI https://fr.wikipedia.org/wiki/Population_mondiale. On this page, we have different types of data like text and tables. Thanks to the read_html, we can directly get the list of table data.

world_population = pd.read_html('https://fr.wikipedia.org/wiki/Population_mondiale') # list of table
world_population[0] # display the first item in list

Handle missing data

for this part, we will use the following dataset datasets/the_office_series.csv from Kaggle here

We already used this dataset in the previous post, so we know the GuestStars column has nullable values.

Using the function isna of pandas library, we will try to show where there is no value for the five first records.

office_df = pd.read_csv("datasets/the_office_series.csv")
office_df['GuestStars'].isna().head()

0    True
1    True
2    True
3    True
4    True
Name: GuestStars, dtype: bool

To count missing values, we can write the following code.

office_df['GuestStars'].isna().sum() # output 159

We can also fill missing values with other possible values according to the study. Has an example, we can have it.

office_df['GuestStars'].fillna('No value').head(10)

0         No value
1         No value
2         No value
3         No value
4         No value
5       Amy Adams
6         No value
7         No value
8    Nancy Carell
9       Amy Adams
Name: GuestStars, dtype: object

We will work with the GuestStars column, where the value is not empty.

available_guest =  office_df.loc[:,'GuestStars'].dropna()
available_guest.head()

5        Amy Adams
8     Nancy Carell
9        Amy Adams
12     Tim Meadows
14       Ken Jeong
Name: GuestStars, dtype: object

Manipulation of string data

Using the Pandas library, we can find a pattern in a string like this.

available_guest.str.match('Amy Adams').head()

5      True
8     False
9      True
12    False
14    False
Name: GuestStars, dtype: bool

There is another function for finding patterns in a string.

We also can change the string case. Currently, we have capitalized case for the GuestStars column.

available_guest.str.upper().head()

5        AMY ADAMS
8     NANCY CARELL
9        AMY ADAMS
12     TIM MEADOWS
14       KEN JEONG
Name: GuestStars, dtype: object

To learn more about the manipulation of string using the Pandas library, You should read the documentation.

Joining data

Like in the previous post, to use the merge function. We have to have at least two data frames.

The first will get the director, who made ten episodes at least.

director_df = office_df.groupby('Director')['EpisodeTitle'].agg(['count'])
director_df = director_df[director_df['count'] > 10 ].sort_values('count', ascending=False)
director_df

The second data frame will contain the rating means for all directors.

director_mean_df = office_df.groupby('Director')['Ratings'].agg(['mean'])
director_mean_df.head()

new_df = pd.merge(director_df, director_mean_df, left_index=True, right_index=True)
new_df.sort_values('mean', ascending=False)

Visualization data

We will use the last data frame from the previous post, created by the following code.

In this code, we create a function rangeGetData to get the mean from the provided column. Later we get data from the selected duration using the bool mask tip. Then, we create a data frame from filtered data.

# Function creation
def rangeGetData(column, df):
    return df[column].mean()

# Boolean mask trick to filter data
mean_viewers_19___27__2 = rangeGetData('Viewership', office_df[(office_df['Duration'] >= 19) & (office_df['Duration'] < 27.2)])
mean_ratings_19___27__2 = rangeGetData('Ratings', office_df[(office_df['Duration'] >= 19) & (office_df['Duration'] < 27.2)])

mean_viewers_27__2___35__4 = rangeGetData('Viewership', office_df[(office_df['Duration'] >= 27.2) & (office_df['Duration'] < 35.4)])
mean_ratings_27__2___35__4 = rangeGetData('Ratings', office_df[(office_df['Duration'] >= 27.2) & (office_df['Duration'] < 35.4)])

mean_viewers_35__4___43__6 = rangeGetData('Viewership', office_df[(office_df['Duration'] >= 35.4) & (office_df['Duration'] < 43.6)])
mean_ratings_35__4___43__6 = rangeGetData('Ratings', office_df[(office_df['Duration'] >= 35.4) & (office_df['Duration'] < 43.6)])

mean_viewers_43__6___51__8 = rangeGetData('Viewership', office_df[(office_df['Duration'] >= 43.6) & (office_df['Duration'] < 51.8)])
mean_ratings_43__6___51__8 = rangeGetData('Ratings', office_df[(office_df['Duration'] >= 43.6) & (office_df['Duration'] < 51.8)])

mean_viewers_51__8 = rangeGetData('Viewership', office_df[office_df['Duration'] >= 51.8])
mean_ratings_51__8 = rangeGetData('Ratings', office_df[office_df['Duration'] >= 51.8])

# Data frame creation
duration_df = pd.DataFrame(
    data={
        'viewers': [mean_viewers_19___27__2, mean_viewers_27__2___35__4, mean_viewers_35__4___43__6, mean_viewers_43__6___51__8, mean_viewers_51__8],
        'ratings': [mean_ratings_19___27__2, mean_ratings_27__2___35__4, mean_ratings_35__4___43__6, mean_ratings_43__6___51__8, mean_ratings_51__8]
        },
    index = ['19 to 27.2', '27.2 to 35.4', '35.4 to 43.6', '43.6 to 51.8', '51.8 to 60']
)

# Show data frame
duration_df

duration_df.plot.line(subplots=True, figsize=(11, 14))

duration_df.plot(kind='pie',subplots=True, figsize=(11, 14))

Conclusion

The Pandas library has enough functionality and allows us to do an exploratory study without worries and using external dependencies.

Reference

• DataCamp's Unguided Project: "Investigating Netflix Movies and Guest Stars in The Office" here.

• This article was originally written by Kossonou Kouamé Maïzan Alain Serge, as part of the Data Insight data scientist program.

• The full code on Github