4: Sequences and Tables

Sequences or Arrays

In data science, a common way to store a sequence of data values is with an array. An array can store multiple data values under one variable name, making it easier to refer to the sequence of data in our code.

To create an array from a sequence of data, we use the format:

np.array([ list of comma-separated data values ])

In the code below we create an array named cities that can store the 15 city names in Santa Clara County.

import numpy as np

cities = np.array(['Campbell', 'Cupertino', 'Gilroy', 'Los Altos',
                   'Los Altos Hills', 'Los Gatos', 'Milpitas', 'Monte Sereno',
                   'Morgan Hill', 'Mountain View', 'Palo Alto', 'San Jose',
                   'Santa Clara', 'Saratoga', 'Sunnyvale'])

print("Array of Cities:")
print(cities)

Output?

Array of Cities:
['Campbell' 'Cupertino' 'Gilroy' 'Los Altos' 'Los Altos Hills' 'Los Gatos'
 'Milpitas' 'Monte Sereno' 'Morgan Hill' 'Mountain View' 'Palo Alto'
 'San Jose' 'Santa Clara' 'Saratoga' 'Sunnyvale']

From the code above we observe that:

• We need to import a Python module named Numpy, and typically Numpy is shortened to np.
• The array data type is defined in Numpy, therefore np.array( ) is a function that tells Python to create an array, ready to store a sequence of multiple data values.
• We only need to create one variable named cities, and it can store 15 strings because it is an array data type.
• The input to the function np.array( ) is the sequence of city names, separated by comma, and surrounded by [ ] to group the names together.
  In Python when we use [ ] to group multiple values together, the grouping is called a list.

A Numpy array is most useful when it contains numbers, because a Numpy array comes with many math functions that can be applied to all the numbers in the array.

In the Code cell below, we create an array called temps and store the following temperatures: 61, 63, 67, 70, 73, 79, 81, 81, 81, 76, 67, 60

The temperatures are the 12 monthly average high temperature for the city of San Jose.

# Create an array named temps
SJ_temps = np.array([61, 63, 67, 70, 73, 79, 81, 81, 81, 76, 67, 60])
# Print the temps array
print(SJ_temps)

Output?

[61 63 67 70 73 79 81 81 81 76 67 60]

Functions for Arrays of Numbers

Just as there are multiple useful functions for numbers and strings, there are multiple useful functions for arrays of numbers.

lower_temps = SJ_temps - 2
print("Original temp:", SJ_temps)
print("Lowered temps:", lower_temps)

Output?

Original temp: [61 63 67 70 73 79 81 81 81 76 67 60]
Lowered temps: [59 61 65 68 71 77 79 79 79 74 65 58]

# Create an array of average monthly high temperatures in Los Angeles
LA_temps = np.array([70, 69, 70, 74, 74, 78, 83, 84, 84, 81, 74, 68])

# Find the temperature difference between Los Angeles and San Jose
temp_diff = LA_temps - SJ_temps
print("Difference in temps:", temp_diff)

Output?

Difference in temps: [ 9  6  3  4  1 -1  2  3  3  5  7  8]

print("Number of temps:", np.size(SJ_temps))
print("Total of temps:", np.sum(SJ_temps))
print("Average of temps:", np.mean(SJ_temps))
print("Rounded Celsius temps:", np.round(SJ_temps - 32 * 9/5, 1))
print("Sorted temps:", np.sort(SJ_temps))

Output?

Number of temps: 12
Total of temps: 859
Average of temps: 71.58333333333333
Rounded Celsius temps: [ 3.4  5.4  9.4 12.4 15.4 21.4 23.4 23.4 23.4 18.4  9.4  2.4]
Sorted temps: [60 61 63 67 67 70 73 76 79 81 81 81]

# create an array from 0-7, step up by 1
print("range(8):", np.array(range(8)))

# create an array from 2-7, step up by 1
print("range(2, 8):", np.array(range(2, 8)))

# create an array from 2-7, step up by 3
print("range(2, 8, 3):", np.array(range(2, 8, 3)))

# create an array from 8-3, step down by 2
print("range(8, 2, -2):", np.array(range(8, 2, -2)))

Output?

range(8): [0 1 2 3 4 5 6 7]
range(2, 8): [2 3 4 5 6 7]
range(2, 8, 3): [2 5]
range(8, 2, -2): [8 6 4]

Tables or DataFrames

In data science one of the most commonly used Python data storage is the DataFrame. A DataFrame is a table with one or more rows and columns, making it easy to associate multiple data sequences together.

Internally a DataFrame is made of one or more arrays, where each array is one column of the DataFrame.

Create a DataFrame with Existing Data

Suppose we have 5 students with the following student data:

• Names: Emma, Tin, Ali, Jeff, Maria
• Quiz 1 scores, corresponding with the name order above: 18, 20, 15, 17, 18
• Quiz 2 scores, corresponding with the name order above: 19, 16, 15, 16, 17

A DataFrame or a table is a simple but effective way to organize data:

• Each row of the table is for one student
• Each column of the table is for one category of data: name, quiz 1 score, quiz 2 score

With a DataFrame we can label the columns with the categories of data, making it easy to distinguish between Quiz 1 scores and Quiz 2 scores.

To use a DataFrame, we need to work with the pandas module, which is commonly shortened to pd. Then we create a DataFrame data type with all the data we have above.

# Work with the pandas module
import pandas as pd

# 1. Create 3 lists to store data for the 3 categories
names = ['Emma', 'Tin', 'Ali', 'Jeff', 'Maria']
quiz1 = [18, 20, 15, 17, 18]
quiz2 = [19, 16, 15, 16, 17]

# 2. Create a DataFrame with the 3 lists, labeling the columns with the category names
students = pd.DataFrame({'Name': names,
                         'Quiz 1': quiz1,
                         'Quiz 2': quiz2 })

# 3. Print the DataFrame
display(students)

Output?

In the code above we observe:

• In step 1: once again the [ ] is used to group together multiple data values into a list. We name each list with a descriptive variable name.

• In step 2: we create the DataFrame with the function:

  pd.DataFrame( )

• Also in step 2: we put together the 3 lists and their corresponding labels by using { }. This format creates a dictionary in Python, allowing the computer to associate each label (such as Name) with its list (such as all the data in names). Then the dictionary becomes the input to the pd.DataFrame( ) function.

• In step 3: we use the function display( ) instead of the usual print. The display() function works with the DataFrame to print in graphical format, so that we can see the column labels and each row more clearly than using print.

Add a column to a DataFrame

To add a column of data to the students DataFrame, we use the following format:
DataFrame_name[ column_label ] = [ sequence of data ]

# add a new column for Quiz 3 scores
students['Quiz3'] = [18, 20, 19, 18, 19]
display(students)

Output?

Create a DataFrame from a File

Typing data into a DataFrame, as in the student example above, is fine when there are few data points. But often our input data is a large dataset that's stored in a file.

The pandas module has several functions to read in data from a file, depending on what type of file it is.

A common file type used in data science is a CSV file, or comma separated values file, where each data record is in a row, and each row has multiple values that are separated by commas. We will work with the data file cars.csv which is from the UCI Machine Learning Repository.

The data in the file are characteristics of different cars, such as number of cylinders, horsepower, weight, etc.

# Read data from a CSV file, where the file is stored online (at a URL)

# 1. Store the URL or location of the data file
url = "https://commons.libretexts.org/permalink/7byKbqzMFm/99f9cde5-b5a9-479b-9318-b232a7637cb5?direct=true"

# 2. Read the CSV file into a DataFrame
cars = pd.read_csv(url)

# 3. Print the DataFrame
display(cars)

In the above example the CSV file is stored online, therefore we use the pd.read_csv( ) function with the URL as the input.

If the CSV file is stored locally on our computer, we can also use pd.read_csv( ) but we use the name of the file instead:
DataFrame_name = pd.read_csv( CSV_filename )
where the CSV filename ends with the extension .csv

Get Properties of a DataFrame

As shown in the output of the cars DataFrame, the display function automatically shows the size of the DataFrame: the number rows and columns.

• To get the number of rows and columns without having to print the DataFrame:
  rows, columns = DataFrame_name.shape

rows, columns = cars.shape
print("Number of rows:", rows)
print("Number of columns:", columns)

Output?

• To display the first rows or last rows:
  First rows: DataFrame name.head( num of rows )
  Last rows: DataFrame name.tail( num of rows )
  
  The num of rows input is optional. If there is no input, then head and tail show 5 rows.

Number of rows: 392
Number of columns: 9

display(cars.head(3))

Output?

display(cars.tail())

Output?

• To get a list of the column labels:
  DataFrame_name.columns.to_list()

labels = cars.columns.to_list()
print("Column labels:", labels)

Output?

Column labels: ['mpg', 'cylinders', 'displacement', 'horsepower', 'weight', 'acceleration', 'model_year', 'origin', 'car_name']

Access Data in a DataFrame

There are three common ways to access data in a specific column:

1. DataFrame_name.column_name
2. DataFrame_name['column name']
3. DataFrame_name.iloc[:, column_index ]

For the last option we use the column index instead of the column name. Each column in a DataFrame is numbered from left to right with an index number, starting at 0 for the left most column, and counting up as we go to the columns on the right.

# look at the mpg or miles per gallon column

mpg = cars.mpg
display(mpg)

Output?

0      18.0
1      15.0
2      18.0
3      16.0
4      17.0
       ... 
387    27.0
388    44.0
389    32.0
390    28.0
391    31.0
Name: mpg, Length: 392, dtype: float64

# Can you predict what column will be printed?

column_index3 = cars.iloc[:,3]
display(column_index3)

Output?

0      130.0
1      165.0
2      150.0
3      150.0
4      140.0
       ...  
387     86.0
388     52.0
389     84.0
390     79.0
391     82.0
Name: horsepower, Length: 392, dtype: float64

# Can you predict what data will be printed?

last_mpg = cars['mpg'].tail(6)
display(last_mpg)

Output?

386    27.0
387    27.0
388    44.0
389    32.0
390    28.0
391    31.0
Name: mpg, dtype: float64

The code in the last example above shows a programming technique called function chaining. There are two distinct steps:

1. The output of cars['mpg'] is the mpg column of data.
2. This column then uses tail to get the last 6 mpg values.

The two steps are linked or chained together in one line of code.

To get a specific data value within a column:
DataFrame_name.iloc[ row_index, column_index ]
Each row in a DataFrame is also numbered with an index number, starting at 0 for the first row and counting up by one as we go down the rows.

third_mpg = cars.iloc[2, 0]  # note that the first row has index 0, so the third row has index 2
                             # and the first column has index 0
print("Third mpg:", third_mpg)

Output?

Third mpg: 18.0

Arithmetic in a DataFrame

Since each column of a DataFrame is an array, we can use arithmetic operations on a column and store the result as a new column in the DataFrame.

Suppose we want to add a new column that shows the weight of the cars in 1000 lb units. For example, a car that weighs 2,790 lbs will have the weight displayed as 2.790.

cars['weight_in_1000lbs'] = cars.weight / 1000
display(cars.head(10))

Sort Data

The DataFrame has a convenient method to sort data in ascending (lowest to highest) order or descending (highest to lowest) order.

To sort data we must select a column that will be used to determine the sorting order. During the sort, the DataFrame will automatically move an entire row based on the sorting order of the selected column. This way all data in one row always stay together.

new_DataFrame = existing_DataFrame.sort_values( by=column_name )
The default order is ascending order.

To sort in descending order, we need to request it:
new_DataFrame = existing_DataFrame.sort_values( by=column_name, ascending=False )

# create a new DataFrame with the first 5 rows of the cars DataFrame

first_five = cars.head()
display(first_five)

Output?

# display the first_five DataFrame sorted by car name

display(first_five.sort_values(by='car_name'))

Output?

# Can you write code to do the following?
# Display the first_five DataFrame sorted by weight_in_1000lbs, in descending order

Selecting Columns

Often in data analysis we need to work with only a section of a DataFrame and not the entire DataFrame. The DataFrame has several useful methods that we can use to select certain columns and rows that we want.

• Create a new DataFrame with selected columns:
  new_DataFrame = existing_DataFrame[ list of column names ]

# Recap of cars DataFrame by showing first 5 rows
display(cars.head())

Output?

# Create a DataFrame with only car names and mpg

car_mpg = cars[['car_name', 'mpg']]
display(car_mpg.head())
print("Type of car_mpg:", type(car_mpg))

Output?

Type of car_mpg: <class 'pandas.core.frame.DataFrame'>

weight_array = cars['weight_in_1000lbs'].to_numpy()
print("Weight array:", weight_array)
print("Type of array:", type(weight_array))

Weight array: [3.504 3.693 3.436 3.433 3.449 4.341 4.354 4.312 4.425 3.85  3.563 3.609
 3.761 3.086 2.372 2.833 2.774 2.587 2.13  1.835 2.672 2.43  2.375 2.234
 2.648 4.615 4.376 4.382 4.732 2.13  2.264 2.228 2.634 3.439 3.329 3.302
 3.288 4.209 4.464 4.154 4.096 4.955 4.746 5.14  2.962 2.408 3.282 3.139
 2.22  2.123 2.074 2.065 1.773 1.613 1.834 1.955 2.278 2.126 2.254 2.408
 2.226 4.274 4.385 4.135 4.129 3.672 4.633 4.502 4.456 4.422 2.33  3.892
 4.098 4.294 4.077 2.933 2.511 2.979 2.189 2.395 2.288 2.506 2.164 2.1
 4.1   3.672 3.988 4.042 3.777 4.952 4.464 4.363 4.237 4.735 4.951 3.821
 3.121 3.278 2.945 3.021 2.904 1.95  4.997 4.906 4.654 4.499 2.789 2.279
 2.401 2.379 2.124 2.31  2.472 2.265 4.082 4.278 1.867 2.158 2.582 2.868
 3.399 2.66  2.807 3.664 3.102 2.901 3.336 1.95  2.451 1.836 2.542 3.781
 3.632 3.613 4.141 4.699 4.457 4.638 4.257 2.219 1.963 2.3   1.649 2.003
 2.125 2.108 2.246 2.489 2.391 2.    3.264 3.459 3.432 3.158 4.668 4.44
 4.498 4.657 3.907 3.897 3.73  3.785 3.039 3.221 3.169 2.171 2.639 2.914
 2.592 2.702 2.223 2.545 2.984 1.937 3.211 2.694 2.957 2.945 2.671 1.795
 2.464 2.22  2.572 2.255 2.202 4.215 4.19  3.962 4.215 3.233 3.353 3.012
 3.085 2.035 2.164 1.937 1.795 3.651 3.574 3.645 3.193 1.825 1.99  2.155
 2.565 3.15  3.94  3.27  2.93  3.82  4.38  4.055 3.87  3.755 2.045 2.155
 1.825 2.3   1.945 3.88  4.06  4.14  4.295 3.52  3.425 3.63  3.525 4.22
 4.165 4.325 4.335 1.94  2.74  2.265 2.755 2.051 2.075 1.985 2.19  2.815
 2.6   2.72  1.985 1.8   1.985 2.07  1.8   3.365 3.735 3.57  3.535 3.155
 2.965 2.72  3.43  3.21  3.38  3.07  3.62  3.41  3.425 3.445 3.205 4.08
 2.155 2.56  2.3   2.23  2.515 2.745 2.855 2.405 2.83  3.14  2.795 3.41
 1.99  2.135 3.245 2.99  2.89  3.265 3.36  3.84  3.725 3.955 3.83  4.36
 4.054 3.605 3.94  1.925 1.975 1.915 2.67  3.53  3.9   3.19  3.42  2.2
 2.15  2.02  2.13  2.67  2.595 2.7   2.556 2.144 1.968 2.12  2.019 2.678
 2.87  3.003 3.381 2.188 2.711 2.542 2.434 2.265 2.11  2.8   2.11  2.085
 2.335 2.95  3.25  1.85  2.145 1.845 2.91  2.42  2.5   2.29  2.49  2.635
 2.62  2.725 2.385 1.755 1.875 1.76  2.065 1.975 2.05  1.985 2.215 2.045
 2.38  2.19  2.21  2.35  2.615 2.635 3.23  3.16  2.9   2.93  3.415 3.725
 3.06  3.465 2.605 2.64  2.395 2.575 2.525 2.735 2.865 1.98  2.025 1.97
 2.125 2.125 2.16  2.205 2.245 1.965 1.965 1.995 2.945 3.015 2.585 2.835
 2.665 2.37  2.95  2.79  2.13  2.295 2.625 2.72 ]
Type of array: <class 'numpy.ndarray'>

• Create a new DataFrame by dropping or removing columns:
  new_DataFrame = existing_DataFrame.drop(columns=[ column_names ])

# first we check the columns of the cars DataFrame
cars.columns

Output?

Index(['mpg', 'cylinders', 'displacement', 'horsepower', 'weight',
       'acceleration', 'model_year', 'origin', 'car_name', 'weight_in_tons'],
      dtype='object')

# suppose we only need the mpg, cylinders, horsepower, and car_name columns

mpg_cyl_hp= cars.drop(columns=['displacement', 'weight', 'acceleration', 'model_year', 'origin', 'weight_in_1000lbs'])
display(mpg_cyl_hp.head())
print("Type of mpg_cyl_hp:", type(mpg_cyl_hp))

Type of mpg_cyl_hp: <class 'pandas.core.frame.DataFrame'>

Selecting Rows

Sometimes we only want certain rows of a DataFrame. We can use the row index to select one or more rows to create a new array or DataFrame.

• Select one row: resulting in a new array
  new_array = existing_DataFrame.iloc[ row_index ].to_numpy()
• Selecting a range of rows: resulting in a new DataFrame
  new_DataFrame = existing_DataFrame.iloc[ np.arange( start_index, end_index ) ]
• Selecting specific rows: resulting in a new DataFrame
  new_DataFrame = existing_DataFrame.iloc( list of index values )

first_car = cars.iloc[0].to_numpy()
print(first_car)
print(type(first_car))

Output?

[np.float64(18.0) np.int64(8) np.float64(307.0) np.float64(130.0)
 np.float64(3504.0) np.float64(12.0) np.int64(70) np.int64(1)
 'chevrolet chevelle malibu' np.float64(3.504)]
<class 'numpy.ndarray'>

first_three = cars.iloc[np.arange(0,3)]
display(first_three)
print(type(first_three))

Output?

<class 'pandas.core.frame.DataFrame'>

row5_row10 = cars.iloc[[4,9]]
display(row5_row10)
print(type(row5_row10))

Output?

<class 'pandas.core.frame.DataFrame'>

Conditional Selection

Often we want to select the rows of the DataFrame based on a certain condition, such as all cars with 4 cylinders or all cars weighing more than 3 tons.

• Select based on one condition

We can use relational operators to compare data in a specific column and select only the True output.
Recall that relational operators are <, >=, ==, etc.
DataFrame_name[ DataFrame_name.column_name <relational_operator> value ]

# All cars with 4 cylinders

display(cars[cars.cylinders == 4])

Output?

# All cars that weigh more than 3000 lbs

display(cars[cars.weight_in_1000lbs > 3])

# Can you write a line of code to print all data for the car named ford ranger?

Output?

# All cars that weigh more than 3000lbs and have 4 cylinders

display(cars[(cars.weight_in_1000lbs > 3) & (cars.cylinders == 4)])

# All cars that are model year 70 or 80
display(cars[(cars.model_year == 70) | (cars.model_year == 80)])

Output?

# All cars that are from mazda

display(cars[cars.car_name.str.contains("Mazda", case=False)])

Output?

Summary

In this chapter we learn two commonly used data types in data science: the array for a sequence of data, and the DataFrame for multiple data records, where each record has many data fields.

These data types, together with numbers and strings, provide the tools to analyze and perform calculations with large datasets.