Panda Examples

1 Pandas Practice Questions: Forty-Eight Examples to Make You an Expert

Pandas is a hugely popular tool for machine learning. It builds on the strengths and speed of Numpy to allow for mixed column types in a two-dimensional DataFrame that is indexable by column or row.

As popular as it is, Pandas offers so many different ways to do things that it helps to have examples and practice exercises to refresh our memory from time to time. Some of these will be challenging to you if you’re new to Pandas (or, like me, you’re reviewing it).

Note that in addition to Pandas, we’ll also be taking advantage of the sample data sets from Seaborn. You’ll need to be able to load one of these data sets into a DataFrame to answer many of the questions. (One solution is provided for you since it’s critical to later answers). ¹

1.1 Creating Data Frames and Using Sample Data Sets

1. Using NumPy, create a Pandas DataFrame with five rows and three columms.
2. For a Pandas DataFrame created from a NumPy array, what is the default behavior for the labels for the columns? For the rows?
3. Create a second DataFrame as above with five rows and three columns, setting the row labels to the names of any five major US cities and the column labels to the first three months of the year.
4. You recall that the Seaborn package has some sample data sets built in, but can’t remember how to list and load them. Assuming the functions to do so have “data” in the name, how might you locate them? You can assume a Jupyter Notebook / IPython environment and explain the process, or write the code to do it in Python.

1.2 Loading Data From CSV

5. Zillow home data is available at this :(far fa-file-excel): URL 1 or :(far fa-file-excel): URL 2. How can you open this file as a DataFrame named df_homes in Pandas?
6. Save the DataFrame, df_homes, to a local CSV file, zillow_home_data.csv.
7. Load zillow_home_data.csv back into a new Dataframe, df_homes_2.
8. Compare the dimensions of the two DataFrames, df_homes and df_homes_2. Are they equal? If not, how can you fix it?
9. A remote spreadsheet showing how a snapshot of how traffic increased for a hypothetical website is available here: :(far fa-file-excel): URL. Load the worksheet page of the spreasheet data labelled “February 2022” as a DataFrame named “feb“. Note: the leftmost column in the spreadsheet is the index column.
10. The “Month to Month Increase” column is a bit hard to understand, so ignore it for now. Given the values for “This Month” and “Last Month”, create a new column, “Percentage Increase”.

1.3 Basic Operations on Data

11. Using Seaborn, get a dataset about penguins into a dataframe named df_penguins. Note that because all of the following questions depend on this example, we’ll provide the solution here so no one gets stuck:

import seaborn as sb
df_penguins = sb.load_dataset('penguins')

12. Write the code to show the the number of rows and columns in df_penguins.
13. How might you show the first few rows of df_penguins?
14. How can you return the unique species of penguins from df_penguins? How many unique species are there?
15. What function can we use to drop the rows that have missing data?
16. By default, will this modify df_penguins or will it return a copy?
17. How can we override the default?
18. Create a new DataFrame, df_penguins_full, with the missing data deleted.
19. What is the average bill length of a penguin, in millimeters, in this data set?
20. Which of the following is most strongly correlated with bill length? a) Body mass? b) Flipper length? c) Bill depth? Show how you arrived at the answer.
21. How could you show the median flipper length, grouped by species?
22. Which species as the longest flippers?
23. Which two species have the most similar mean weight? Show how you arrived at the answer.
24. How could you sort the rows by bill length?
25. How could you run the same sort in descending order?
26. How could you sort by species first, then by body mass?

1.4 Selecting Rows, Columns, and Cells

Let’s look at some precious stones now, and leave the poor penguins alone for a while.

27. Load the Seaborn “diamonds” dataset into a Pandas dataframe named diamonds.
28. Display the columns that are available.
29. If you select a single column from the diamonds DataFrame, what will be the type of the return value?
30. Select the ‘table’ column and show its type.
31. Select the first ten rows of the price and carat columns ten rows of the diamonds DataFrame into a variable called subset, and display them.
32. For a given column, show the code to display the datatype of the values in the column?
33. Select the first row of the diamonds DataFrame into a variable called row.
34. What would you expect the data type of the row to be? (Display it)
35. Can you discover the names of the columns using only the row returned in #33?Why or why not?
36. Select the row with the highest priced diamond.
37. Select the row with the lowest priced diamond.

1.5 Some Exercises Using Time Series

The seaborn “taxis” dataset has some datetime values for the time when the customer was picked up and dropped off.

38. Load the taxis dataset into a DataFrame, taxis.
39. The pickup column contains the date and time the customer picked up, but it’s a string. Add a column to the DataFrame, pickup_time, containing the value in pickup as a DateTime.
40. We have a hypothesis that as the day goes on, the tips get higher. We’ll need to wrangle the data a bit before testing this, however. First, now that we have a datetime column, pickup_time, create a subset of it to create a new DataFrame, taxis_one_day. This new DataFrame should have values between ‘2019-03-23 00:06:00’ (inclusive) and ‘2019-03-24 00:00:00’ (exlusive).
41. We now have a range from morning until midnight, but we to take the mean of the numeric columns, grouped at one hour intervals. Save the result as taxis_means, and display it.
42. Create a simple line plot of the value “distance”.
43. Overall, do riders seem to travel further or less far as the day progresses?
44. Create a new column in taxis_means, tip_in_percent. The source columns for this should be “fare” and “tip”.
45. Create a new column, time_interval, as a range of integer values beginning with zero.
46. Display the correlations between the following pairs of values:
    • tip_in_percent and distance.
    • tip_in_percent and passengers.
    • tip_in_percent and time_interval.
47. Admittedly, the size of the data set is fairly small given how we’ve subsetted it. But based on the values in #45, which of the three pairs show the strongest correlation.
48. Did our hypothesis that people tip more as the day goes on turn out to be warranted?

2 Panda Exercises Solutions

2.1 Creating DataFrames and Using Sample Data Sets

import pandas as pd
import numpy as np
import seaborn as sb

1. Using NumPy, create a Pandas DataFrame with five rows and three columms:

import numpy as np
import pandas as pd
from pandas import DataFrame

df = DataFrame(np.arange(15).reshape(5,3))
df

2. For a Pandas DataFrame created from a NumPy array, what is the default behavior for the labels for the columns? For the rows?

:(fas fa-lightbulb): Both the “columns” value and the “index” value (for the rows) are set to zero based numeric arrays.

3. Create a second DataFrame as above with five rows and three columns, setting the row labels to the names of any five major US cities and the column labels to the first three months of the year.

df = DataFrame(np.arange(15).reshape(5,3))
df.index = ["NewYork", "LosAngeles", "Atlanta", "Boston", "SanFrancisco"]
df.columns = ["January", "February", "March"]
df

4. You recall that the Seaborn package has some data sets built in, but can’t remember how to list and load them. Assuming the functions to do so have “data” in the name, how might you locate them? You can assume a Jupyter Notebook / IPython environment and explain the process, or write the code to do it in Python.

Method 1: In an empty code cell, type sb + tab to bring up a list of names. Type “data” to filter the names.

# Method 2:
[x for x in dir(sb) if "data" in x]

['get_data_home', 'get_dataset_names', 'load_dataset']

sb.get_dataset_names()

['anagrams',
 'anscombe',
 'attention',
 'brain_networks',
 'car_crashes',
 'diamonds',
 'dots',
 'exercise',
 'flights',
 'fmri',
 'gammas',
 'geyser',
 'iris',
 'mpg',
 'penguins',
 'planets',
 'taxis',
 'tips',
 'titanic']

2.2 Loading data from CSV

5. Zillow home data is available at this URL: https://files.zillowstatic.com/research/public_csvs/zhvi/Metro_zhvi_uc_sfrcondo_tier_0.33_0.67_sm_sa_month.csv

Open this file as a DataFrame in Pandas.

df_homes = pd.read_csv("https://files.zillowstatic.com/research/public_csvs/zhvi/Metro_zhvi_uc_sfrcondo_tier_0.33_0.67_sm_sa_month.csv")

6. Save the DataFrame, df_homes, to a local CSV file, “zillow_home_data.csv”.

df_homes.to_csv("../data/zillow_home_data.csv")

7. Load zillow_home_data.csv back into a new Dataframe, df_homes_2

df_homes_2 = pd.read_csv("../data/zillow_home_data.csv")

8. Compare the dimensions of the two DataFrames, df_homes and df_homes_2. Are they equal? If not, how can you fix it?

print(df_homes.shape)
print(df_homes_2.shape)
print(df_homes.shape == df_homes_2.shape)
# (908, 271)
# (908, 272)
# False

To fix the fact that they’re not equal, save file again this time using index=False to avoid saving the index as a CSV column.

df_homes.to_csv("../data/zillow_home_data.csv", index=False)
df_homes_2 = pd.read_csv("../data/zillow_home_data.csv")
print(df_homes.shape == df_homes_2.shape)
# True

9. A remote spreadsheet showing how a snapshot of how traffic increased for a hypothetical website is available here: https://github.com/CodeSolid/CodeSolid.github.io/raw/main/booksource/data/AnalyticsSnapshot.xlsx. Load the worksheet page of the spreasheet data labelled “February 2022” as a DataFrame named “feb”. Note: the leftmost column in the spreadsheet is the index column.

url = "https://github.com/CodeSolid/CodeSolid.github.io/raw/main/booksource/data/AnalyticsSnapshot.xlsx"
feb = pd.read_excel(url, sheet_name="February 2022", index_col=0)
feb

10. The “Month to Month Increase” column is a bit hard to understand, so ignore it for now. Given the values for “This Month” and “Last Month”, create a new column, “Percentage Increase”.

feb["Percentage Increase"] = (feb["This Month"] - feb["Last Month"]) / feb["Last Month"] * 100
feb

2.3 Basic Operations on Data

11. Using Seaborn, get a dataset about penguins into a dataframe named df_penguins. Note that because all of the following questions depend on this example, we’ll provide the solution here so no one gets stuck:

df_penguins = sb.load_dataset('penguins')

12. Write the code to show the the number of rows and columns in df_penguins

df_penguins.shape
# (344, 7)

13. How might you show the first few rows of df_penguins?

df_penguins.head()

14. How can you return the unique species of penguins from df_penguins? How many unique species are there?

species = df_penguins["species"].copy()
unique = species.fillna(0)
unique = unique.drop_duplicates()
nrows = unique.shape[0]
print(unique)
print(f"There are {nrows} unique species, {list(unique.values)}.")
# 0         Adelie
# 152    Chinstrap
# 220       Gentoo
# Name: species, dtype: object
# There are 3 unique species, ['Adelie', 'Chinstrap', 'Gentoo'].

15. What function can we use to drop the rows that have missing data?

df_penguins.dropna()

(333 rows × 7 columns)

16. By default, will this modify df_penguins or will it return a copy?

It will return a copy.

17. How can we override the default?

We can use df_penguins.dropna(inplace=True)

18. Create a new DataFrame, df_penguins_full, with the missing data deleted.

df_penguins_full = df_penguins.dropna()
# Expoloratory only
df_penguins_full.columns
# Index(['species', 'island', 'bill_length_mm', 'bill_depth_mm',
#       'flipper_length_mm', 'body_mass_g', 'sex'],
#      dtype='object')

19. What is the average bill length of a penguin, in millimeters, in this (df_full) data set?

df_penguins_full['bill_length_mm'].mean()
# 43.99279279279279

20. Which of the following is most strongly correlated with bill length? a) Body mass? b) Flipper length? c) Bill depth? Show how you arrived at the answer.

The answer is b) Flipper length. See below:

print(df_penguins_full['bill_length_mm'].corr(df_penguins_full['body_mass_g']))
print(df_penguins_full['bill_length_mm'].corr(df_penguins_full['flipper_length_mm']))
print(df_penguins_full['bill_length_mm'].corr(df_penguins_full['bill_depth_mm']))
# 0.589451110176949
# 0.6530956386670855
# -0.22862563591302923

21. How could you show the median flipper length, grouped by species?

df_penguins_full.groupby('species').mean()['flipper_length_mm']
# species
# Adelie       190.102740
# Chinstrap    195.823529
# Gentoo       217.235294
# Name: flipper_length_mm, dtype: float64

22. Which species has the longest flippers?

Gentoo

23. Which two species have the most similar mean weight? Show how you arrived at the answer.

Adelie and Chinstrap

df_penguins_full.groupby('species').mean()

24. How could you sort the rows by bill length?

df_penguins.sort_values('bill_length_mm')

(344 rows × 7 columns)

25. How could you run the same sort in descending order?

df_penguins.sort_values(['bill_length_mm'], ascending=False)

(344 rows × 7 columns)

26. How could you sort by species first, then by body mass?

df_penguins.sort_values(['species', 'body_mass_g'])

(344 rows × 7 columns)

2.4 Selecting Rows, Columns, and Cells

Let’s look at some precious stones now, and leave the poor penguins alone for a while. Let’s look at some precious stones now, and leave the poor penguins alone for a while.

27. Load the Seaborn diamonds dataset into a Pandas dataframe named diamonds.

diamonds = sb.load_dataset('diamonds')

28. Display the columns that are available.

diamonds.columns
# Index(['carat', 'cut', 'color', 'clarity', 'depth', 'table', 'price', 'x', 'y',
#       'z'],
#      dtype='object')

29. If you select a single column from the diamonds DataFrame, what will be the type of the return value?

A Pandas Series.

30. Select the table column and show its type

table = diamonds['table']
type(table)
# pandas.core.series.Series

31. Select the first ten rows of the price and carat columns ten rows of the diamonds DataFrame into a variable called subset, and display them.

subset = diamonds.loc[0:9, ['price', 'carat']]
subset

32. For a given column, show the code to display the datatype of the values in the column?

diamonds['price'].dtype
# dtype('int64')

33. Select the first row of the diamonds DataFrame into a variable called row.

row = diamonds.iloc[0,:]

34. What would you expect the data type of the row to be? Display it.

A Pandas series

type(row)
# pandas.core.series.Series

35. Can you discover the names of the columns using only the row returned in #33? Why or why not?Can you discover the names of the columns using only the row returned in #33? Why or why not?

Yes, because a row series should have the columns as the index (See below):

row.index
# Index(['carat', 'cut', 'color', 'clarity', 'depth', 'table', 'price', 'x', 'y',
#       'z'],
#      dtype='object')

36. Select the row with the highest priced diamond.

diamonds.loc[diamonds['price'].idxmax(), :]
# carat         2.29
# cut        Premium
# color            I
# clarity        VS2
# depth         60.8
# table         60.0
# price        18823
# x              8.5
# y             8.47
# z             5.16
# Name: 27749, dtype: object

37. Select the row with the lowest priced diamond.

diamonds.loc[diamonds['price'].idxmin(), :]
# carat       0.23
# cut        Ideal
# color          E
# clarity      SI2
# depth       61.5
# table       55.0
# price        326
# x           3.95
# y           3.98
# z           2.43
Name: 0, dtype: object

2.5 Some Exercises Using Time Series

38. Load the taxis dataset into a DataFrame, taxis.

taxis = sb.load_dataset('taxis')

39. The pickup column contains the date and time the customer picked up, but it’s a string. Add a column to the DataFrame, pickup_time, containing the value in pickup as a DateTime.

taxis['pickup_time'] = pd.to_datetime(taxis['pickup'])

40. We have a hypothesis that as the day goes on, the tips get higher. We’ll need to wrangle the data a bit before testing this, however. First, now that we have a datetime column, pickup_time, create a subset of it to create a new DataFrame, taxis_one_day. This new DataFrame should have values between ‘2019-03-23 06:00:00’ (inclusive) and ‘2019-03-24 00:00:00’ (exlusive).

mask = (taxis['pickup_time'] >= '2019-03-23 06:00:00') & (taxis['pickup_time'] < '2019-03-24 00:00:00')
taxis_one_day = taxis.loc[mask]

41. We now have a range from morning until midnight, but we to take the mean of the numeric columns, grouped at one hour intervals. Save the result as taxis_means, and display it.

taxis_means = taxis_one_day.groupby(pd.Grouper(key='pickup_time', freq='1h')).mean()
taxis_means

42. Create a simple line plot of the value “distance”.

taxis_means.plot(y='distance')
# <AxesSubplot:xlabel='pickup_time'>

43. Overall, do riders travel further or less far as the day progresses?

They travel further.

44. Create a new column in taxis_means, tip_in_percent. The source columns for this should be “fare” and “tip”

taxis_means['tip_in_percent'] = taxis_means.tip / taxis_means.fare * 100
taxis_means.tip_in_percent
# pickup_time
# 2019-03-23 06:00:00     0.000000
# 2019-03-23 07:00:00    22.190476
# 2019-03-23 08:00:00     0.000000
# 2019-03-23 09:00:00    22.621622
# 2019-03-23 10:00:00     8.314286
# 2019-03-23 11:00:00     7.245902
# 2019-03-23 12:00:00     6.293512
# 2019-03-23 13:00:00    27.470199
# 2019-03-23 14:00:00    10.612625
# 2019-03-23 15:00:00    14.450161
# 2019-03-23 16:00:00    10.608696
# 2019-03-23 17:00:00    18.141104
# 2019-03-23 18:00:00     6.584958
# 2019-03-23 19:00:00    11.461538
# 2019-03-23 20:00:00    13.909910
# 2019-03-23 21:00:00    13.432432
# 2019-03-23 22:00:00    12.267241
# 2019-03-23 23:00:00    11.221374
# Freq: H, Name: tip_in_percent, dtype: float64

45. Create a new column, time_interval, as a range of integer values beginning with zero.

taxis_means['time_interval'] = np.arange(0, taxis_means.shape[0])
taxis_means.time_interval
# pickup_time
# 2019-03-23 06:00:00     0
# 2019-03-23 07:00:00     1
# 2019-03-23 08:00:00     2
# 2019-03-23 09:00:00     3
# 2019-03-23 10:00:00     4
# 2019-03-23 11:00:00     5
# 2019-03-23 12:00:00     6
# 2019-03-23 13:00:00     7
# 2019-03-23 14:00:00     8
# 2019-03-23 15:00:00     9
# 2019-03-23 16:00:00    10
# 2019-03-23 17:00:00    11
# 2019-03-23 18:00:00    12
# 2019-03-23 19:00:00    13
# 2019-03-23 20:00:00    14
# 2019-03-23 21:00:00    15
# 2019-03-23 22:00:00    16
# 2019-03-23 23:00:00    17
# Freq: H, Name: time_interval, dtype: int64

46. Display the correlations between the following pairs of values:

• tip_in_percent and distance.
• tip_in_percent and passengers.
• tip_in_percent and time_interval.

print(taxis_means['tip_in_percent'].corr(taxis_means['distance']))
print(taxis_means['tip_in_percent'].corr(taxis_means['passengers']))
print(taxis_means['tip_in_percent'].corr(taxis_means['time_interval']))
# 0.05806855805213838
# 0.39614201273484234
# 0.11904714170082593

47. Admittedly, the size of the data set is fairly small given how we’ve subsetted it. But based on the values in #45, which of the three pairs show the strongest correlation.

tip_in_percent and passengers.

48. Did our hypothesis that people tip more as the day goes on turn out to be warranted?

Not based on this dataset, no.

:(far fa-eye): Solution ²

3 Reference