Handling Datasets

4.2. Handling Datasets#

After learning Python data structures (e.g., list, dictionary, tuple) and NumPy arrays, we are now ready to deal with data using Pandas. There are different file types used for datasets and we are going to learn how to import and export them in this section as part of data management using Pandas.

Hide code cell source

 
# if you have not installed pandas through the command line
# do so by opening a new command line tab/window, then
# activate the virtual environment, and issue "pip install pandas"
# or, you may do "%pip install pandas" in a Jupyter notebook.
# do remember to comment out the installation line afterwards
# so that it would not run every time you run the cell

# we also need NumPy sometimes for processing data as it's the foundation of Pandas

# %pip install pandas
# %pip install numpy
import numpy as np
import pandas as pd

4.2.1. Pandas I/O Functions#

Once you’ve set up your file paths, the next step is to actually read data into Pandas DataFrames. Pandas provides a rich collection of input/output (I/O) functions that can handle virtually any data format you’ll encounter in data science work. These functions are designed to be intuitive and flexible, allowing you to read data from files, databases, web pages, and more with just a single line of code.

The most commonly used function is pd.read_csv() for reading comma-separated values (CSV) files, which is the standard format for tabular data. However, Pandas also supports Excel files, JSON, HTML tables, SQL databases, and many other formats. Each read_ function returns a DataFrame object that you can immediately work with using all of Pandas’ powerful data manipulation capabilities.

4.2.1.1. Common Pandas I/O Functions#

Pandas provides a comprehensive set of functions for reading and writing data in various formats. Below is a summary of the most commonly used functions:

Function

Description

Common Parameters

Returns

pd.read_csv()

Read comma-separated values (CSV) file into DataFrame

filepath, sep, header, index_col, usecols, dtype

DataFrame

pd.read_excel()

Read Excel file into DataFrame

filepath, sheet_name, header, index_col, usecols

DataFrame

pd.read_html()

Read HTML tables into a list of DataFrames

url, match, header, index_col

List of DataFrames

pd.read_json()

Read JSON string or file into DataFrame

filepath, orient, typ, dtype

DataFrame or Series

pd.read_sql()

Read SQL query or database table into DataFrame

sql, con, index_col, columns

DataFrame

pd.read_sql_table()

Read SQL database table into DataFrame

table_name, con, schema, index_col

DataFrame

pd.read_sql_query()

Read SQL query into DataFrame

sql, con, index_col, params

DataFrame

Output Functions:

Function

Description

Common Parameters

df.to_csv()

Write DataFrame to CSV file

filepath, sep, index, header, mode

df.to_excel()

Write DataFrame to Excel file

filepath, sheet_name, index, header

df.to_json()

Write DataFrame to JSON file

filepath, orient, indent

df.to_sql()

Write DataFrame to SQL database

name, con, if_exists, index

Key Parameters:

  • filepath: Path to the file (string or file-like object)

  • sep: Delimiter to use (CSV, default is comma)

  • header: Row number(s) to use as column names

  • index_col: Column(s) to use as row labels

  • usecols: Subset of columns to read

  • dtype: Data type for data or columns

  • sheet_name: Excel sheet name or index

  • con: Database connection object

  • index: Whether to write row names (index)

4.2.2. Accessing Files#

You can also pass the path (full or relative) and file names of your data as arguments to the Pandas read_ functions to directly read the file. However, it is recommended to use a path variable for your data directory, which will make your life easier in the long run.

To access your data folder in Jupyter Notebook environment, you can either use a full path or a relative path.

  1. Full path:

    • macOS (and Linux): A full/absolute path starts with “/” (the “root” directory); e.g., data_path = "/Users/<user>/workspace/dsm/data/".

    • Windows: A full path includes a drive letter and backslashes (\) for path separator; e.g. data_path = "C:\Users\<user>\workspace\dsm\data\”) although forward slashes usually work as well.

  2. Relative path: Usually it’s just easier to use a relative path to access the data folders nearby (e.g., data_path = ../../data/).

  3. User home directory: Instead of starting from the system root level, you can usually use your user home directory as the starting point to reach your data file directory. Python os.path.expanduser('~') function resolves the directory passed to it (e.g., ~, representing the home directory) to a full path.

4.2.2.1. Building Path Variable#

To access your dataset files using the Pandas read_ functions, the path variable and the path.join function can be used together to pass the path/file as arguments:

### works on macOS, Linux, and Windows
import os
import pandas as pd

### works in your local computer (home directory)
# home_dir = os.path.expanduser('~') # get the user's home directory 
# data_path = os.path.expanduser(home_dir + '/workspace/dsm/data/')  ### concatenate project data path

### works in the online book (server path)
data_path = '../../data/'  ### use relative path to the data folder in the project directory

print(data_path)    ### output will vary based on your system and project data path

df = pd.read_csv(data_path + "data.csv")  ### read the CSV file into a DataFrame
df.head(3)          ### display the first 3 rows of the DataFrame ==> later

../../data/
Duration Pulse Maxpulse Calories
0 60 110 130 409.1
1 60 117 145 479.0
2 60 103 135 340.0

4.2.2.2. Accessing Dataset Files#

After you have your path variable (e.g. data_path) to your data directory, you join the path variable with the paths/filenames using os.path.join() to access the dataset file. It can take multiple arguments like os.path.join("folder1", "folder2", "file.txt"). This way you don’t have to worry about the path separator (”/” or “\”).

**In most cases, just use relative path (see below).

### or using os.path.join(path variable, filename)

data_file = os.path.join(data_path, "data.csv")
print(data_file)    ### output will vary based on your system and project data path

df = pd.read_csv(data_file)
df.head(3)

../../data/data.csv
Duration Pulse Maxpulse Calories
0 60 110 130 409.1
1 60 117 145 479.0
2 60 103 135 340.0 
### just to show the path variables we used in the book
### which is not locally available because the username is from the web server
### when you run this code in your local computer, you need to change the path to your own project data path
### and the username in the path will be different

data_path = os.path.expanduser('~/workspace/dsm/data')
data_path

'/home/runner/workspace/dsm/data'

### join the path and the filename
data_file = os.path.join(data_path, "data.csv")
print("data file once: ", data_file)  # print the full path to the data file

data file once:  /home/runner/workspace/dsm/data/data.csv

data_file = os.path.join(data_path, 'data.csv' )
data_file

'/home/runner/workspace/dsm/data/data.csv'

4.2.2.3. Using Relative Paths#

To use a relative path to access a dataset file in another folder (e.g., “data”), you need to know the relative location of the data folder and usually we use the special path character * (/* for macOS) and .. to build the path. .. means move up one level in the directory tree. For example, to access the data directory (see figure below):

  • If you are working on the YOU-ARE-HERE.ipynb notebook , you need to go up one level (..) above the notebooks directory, then go down into the data directory:

    data_path = pd.read_csv('../data/')

  • If you are working on the YOU-MAY-BE-HERE.ipynb notebook, you need to move up two levels (folder and notebooks, or ../..), then go down into the data directory: data_path = pd.read_csv('../../data/')

To visualize your project folder, you may use the tree command in the command line.

(.venv) PS C:\Users\[user]\workspace\dsm> tree
Folder PATH listing
Volume serial number is 420C-3FF4
C:.
├───.ipynb_checkpoints
├───.venv
├── data
│   ├── data.csv
│   ├── example
│   └── wine.csv
├── notebooks
│   ├── YOU-ARE-HERE.ipynb
│   └── folder
│       └── YOU-MAY-BE-HERE.ipynb

[user]@[host]:~/workspace/dsm$ tree
.
├── data
│   ├── data.csv
│   ├── example
│   └── wine.csv
├── notebooks
│   ├── YOU-ARE-HERE.ipynb
│   └── folder
│       └── YOU-MAY-BE-HERE.ipynb

You can see that a relative path can be easier to setup for accessing data if you know the relative position of the data directory to your current working directory (CWD). Often time we even use it in an ad hoc manner without building a folder path variable but accessing the datasets directly:

### You need to adjust the path depth (i.e., how many ".." you need depending on where you 
### run the code. If you run the code in the same directory as the data file, then 
### you can just use "data.csv" as the path.If you run the code in a sub-subdirectory, then you need to 
### use "../../data/data.csv" to go up two levels and then into the data directory, and so on.) 
### ./data/data.csv
### ../data/data.csv
### ../../data/data.csv
### ../../../data/data.csv

# data_file = ("../../data/data.csv")
df = pd.read_csv("../../data/data.csv")     ### df = dataframe ==> later
df
Duration Pulse Maxpulse Calories
0 60 110 130 409.1
1 60 117 145 479.0
2 60 103 135 340.0
3 45 109 175 282.4
4 45 117 148 406.0
... ... ... ... ...
160 30 85 120 250.4
161 45 90 130 260.4
162 45 95 130 270.0
163 45 100 140 280.9
164 60 105 140 290.8

165 rows × 4 columns

4.2.3. Data Input and Output#

This section serves as the reference code for obtaining input and output with Pandas. Pandas can read a variety of file types using its pd.read_ methods. Here we cover the most common data types:

  • CSV

  • Excel

  • HTML

  • SQL

4.2.3.1. CSV#

4.2.3.1.1. CSV Input#

4.2.3.2. pd.read_csv()#

Now let us read the dataset file (data_file) using Pandas pd.read_csv() function. You may read the data file directly or open it as a file and save it to a variable so you can manipulate the data.

# home = os.path.expanduser('~')
# data_path = os.path.join(home, 'workspace', 'dsm', 'data').  ###, or
# data_path = os.path.join(home, '/workspace/dsm/data/')       ### cross-platform
# data_file = os.path.join(data_path, 'data.csv')

### quotation marks are used to denote a string (file path here). 
df = pd.read_csv("../../data/data.csv")  ### read the csv file into a DataFrame
df.head()                    ### display the first 5 rows of the DataFrame

### note that you may need to change the path below to point to the correct location of the example file


### open a file and read its content in Python
# with open("../../data/data.csv") as file:
#     content = file.read()
#     print(content)
Duration Pulse Maxpulse Calories
0 60 110 130 409.1
1 60 117 145 479.0
2 60 103 135 340.0
3 45 109 175 282.4
4 45 117 148 406.0

4.2.3.2.1. CSV Output#

You can use Pandas I/O functions such as df.to_csv() to output file to .csv format. You will need to specify the path, otherwise the file will be in the same folder as the environment the code is run, i.g., where this notebook is.

import tempfile
from pathlib import Path

output_file = Path(tempfile.gettempdir()) / 'out-data.csv'
df.to_csv(output_file, index=False)
print(output_file)
df

/tmp/out-data.csv
Duration Pulse Maxpulse Calories
0 60 110 130 409.1
1 60 117 145 479.0
2 60 103 135 340.0
3 45 109 175 282.4
4 45 117 148 406.0
... ... ... ... ...
160 30 85 120 250.4
161 45 90 130 260.4
162 45 95 130 270.0
163 45 100 140 280.9
164 60 105 140 290.8

165 rows × 4 columns

4.2.3.3. Excel#

Pandas can read and write excel files, keep in mind, this only imports data. Not formulas or images, having images or macros may cause this read_excel method to crash.

4.2.3.3.1. Excel Input#

  • If you see the error message:

    ImportError: Missing optional dependency ‘openpyxl’. Use pip or conda to install openpyxl.

    Install the dependency by issuing %pip install openpyxl

### install dependencies if needed
# %pip install openpyxl

### read Excel file
df = pd.read_excel('../../data/Excel_Sample.xlsx', sheet_name='Sheet1')
df
Unnamed: 0.14 Unnamed: 0.13 Unnamed: 0.12 Unnamed: 0.11 Unnamed: 0.10 Unnamed: 0.9 Unnamed: 0.8 Unnamed: 0.7 Unnamed: 0.6 Unnamed: 0.5 Unnamed: 0.4 Unnamed: 0.3 Unnamed: 0.2 Unnamed: 0.1 Unnamed: 0 a b c d
0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 1 2 3
1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 4 5 6 7
2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 8 9 10 11
3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 12 13 14 15 
type(df)

pandas.DataFrame

4.2.3.3.2. Excel Output#

import tempfile
from pathlib import Path

output_file = Path(tempfile.gettempdir()) / 'dsm-ch04-Excel_Sample.xlsx'
df.to_excel(output_file, sheet_name='Sheet1')
print(output_file)

/tmp/dsm-ch04-Excel_Sample.xlsx

4.2.3.4. HTML#

You may need to install htmllib5,lxml, and BeautifulSoup4. In your terminal/command prompt run:

conda install lxml
conda install html5lib
conda install BeautifulSoup4

Then restart Jupyter Notebook. (or use pip install if you aren’t using the Anaconda Distribution)

Pandas can read table tabs off of html. For example:

4.2.3.4.1. HTML Input#

  • If you see the error message:

    ImportError: Missing optional dependency 'lxml'.  Use pip or conda to install lxml.

    Install the dependency: %pip install lxml

  • macOS users do this to get rid of a certificate error (4 steps in the (.venv) lines below; source):

(.venv) [user]@[host]ː~/workspace/dsm$ cd /Applications/Python 3.12/
(.venv) [user]@[host]ː/Applications/Python 3.12$ ls
        Icon?        Install Certificates.command Python Documentation.html        ReadMe.rtf
        IDLE.app     License.rtf      Python Launcher.app Update Shell Profile.command
(.venv) [user]@[host]ː/Applications/Python 3.12$ ./Install\ Certificates.command 
    -- pip install --upgrade certifi
    Collecting certifi
      Using cached certifi-2025.8.3-py3-none-any.whl.metadata (2.4 kB)
    Using cached certifi-2025.8.3-py3-none-any.whl (161 kB)
    Installing collected packages: certifi
    Successfully installed certifi-2025.8.3
(.venv) [user]@[host]ː/Applications/Python 3.12$ cd ~/workspace/dsm

Pandas read_html function will read tables off of a webpage and return a list of DataFrame objects:

##### install dependencies if needed
# %pip install lxml
# %pip install pandas

### read a table straight from a web page (needs an internet connection)
import pandas as pd
df = pd.read_html('https://www.fdic.gov/bank-failures/failed-bank-list')

pd.read_html() returns a list of DataFrames, one for each <table> on the page. Web pages change and go offline, so for the rest of this section we read a copy of the FDIC page saved in the data folder. read_html() works the same way on a local file:

### read the saved copy of the page
df = pd.read_html('../../data/fdic_failed_banks.html')
len(df)                  ### number of tables found on the page

1

df[0].head()
Bank Name City State Cert Acquiring Institution Closing Date Fund  Sort ascending
0 Nano Banc Irvine California 58590 Sunwest Bank September 25, 2026 10555
1 Tioga-Franklin Savings Bank Philadelphia Pennsylvania 33802 Second Federal Savings and Loan Association of... August 21, 2026 10554
2 Small Business Bank Lenexa Kansas 25744 The Farmers State Bank of Oakley, Kansas July 17, 2026 10553
3 Kentland Federal Savings and Loan Association Kentland Indiana 28722 Kentland Bank July 10, 2026 10552
4 Community Bank and Trust - West Georgia LaGrange Georgia 25796 Anchor Bank May 1, 2026 10551

4.2.3.5. SQL#

(source) The pandas.io.sql module provides a collection of query wrappers to both facilitate data retrieval and to reduce dependency on DB-specific API. Database abstraction is provided by SQLAlchemy if installed. In addition you will need a driver library for your database. Examples of such drivers are psycopg2 for PostgreSQL or pymysql for MySQL. For SQLite this is included in Python’s standard library by default. You can find an overview of supported drivers for each SQL dialect in the SQLAlchemy docs.

If SQLAlchemy is not installed, a fallback is only provided for sqlite (and for mysql for backwards compatibility, but this is deprecated and will be removed in a future version). This mode requires a Python database adapter which respect the Python DB-API.

See also some cookbook examples for some advanced strategies.

The key functions are:

  • read_sql_table(table_name, con[, schema, …])

    • Read SQL database table into a DataFrame.

  • read_sql_query(sql, con[, index_col, …])

    • Read SQL query into a DataFrame.

  • read_sql(sql, con[, index_col, …])

    • Read SQL query or database table into a DataFrame.

  • DataFrame.to_sql(name, con[, flavor, …])

    • Write records stored in a DataFrame to a SQL database.

# %pip install SQLAlchemy
from sqlalchemy import create_engine

engine = create_engine('sqlite:///:memory:')

print(type(df))
df = pd.DataFrame(df[0])

<class 'list'>

df.to_sql('data', engine)

25

sql_df = pd.read_sql('data',con=engine)

sql_df.head()
index Bank Name City State Cert Acquiring Institution Closing Date Fund  Sort ascending
0 0 Nano Banc Irvine California 58590 Sunwest Bank September 25, 2026 10555
1 1 Tioga-Franklin Savings Bank Philadelphia Pennsylvania 33802 Second Federal Savings and Loan Association of... August 21, 2026 10554
2 2 Small Business Bank Lenexa Kansas 25744 The Farmers State Bank of Oakley, Kansas July 17, 2026 10553
3 3 Kentland Federal Savings and Loan Association Kentland Indiana 28722 Kentland Bank July 10, 2026 10552
4 4 Community Bank and Trust - West Georgia LaGrange Georgia 25796 Anchor Bank May 1, 2026 10551

4.2.3.5.1. Reading from SQLite Databases#

Beyond CSV files, Pandas can read from many data sources. Let’s explore loading data from a SQLite database that we’ve used in previous SQL activities. You’ll appreciate how convenient Pandas makes this!

To connect to a SQLite database, we:

  1. Import the sqlite3 library (part of Python’s standard library)

  2. Connect to the database using sqlite3.connect()

  3. Use the Pandas function read_sql_query() to execute your query

  4. Close the connection using the close() function (or use with statement)

### List all tables in the SQLite database (similar to "SHOW TABLES;" in MySQL)

import sqlite3
conn = sqlite3.connect('../../data/chinook.db')

query = "SELECT name FROM sqlite_master WHERE type='table';"

pd.read_sql_query(query, conn)
name
0 Album
1 Artist
2 Customer
3 Employee
4 Genre
5 Invoice
6 InvoiceLine
7 MediaType
8 Playlist
9 PlaylistTrack
10 Track 
import sqlite3
conn = sqlite3.connect('../../data/chinook.db')

df_customer = pd.read_sql_query("SELECT * FROM customer;", conn)
df_customer.head()
CustomerId FirstName LastName Company Address City State Country PostalCode Phone Fax Email SupportRepId
0 1 Luís Gonçalves Embraer - Empresa Brasileira de Aeronáutica S.A. Av. Brigadeiro Faria Lima, 2170 São José dos Campos SP Brazil 12227-000 +55 (12) 3923-5555 +55 (12) 3923-5566 luisg@embraer.com.br 3
1 2 Leonie Köhler NaN Theodor-Heuss-Straße 34 Stuttgart NaN Germany 70174 +49 0711 2842222 NaN leonekohler@surfeu.de 5
2 3 François Tremblay NaN 1498 rue Bélanger Montréal QC Canada H2G 1A7 +1 (514) 721-4711 NaN ftremblay@gmail.com 3
3 4 Bjørn Hansen NaN Ullevålsveien 14 Oslo NaN Norway 0171 +47 22 44 22 22 NaN bjorn.hansen@yahoo.no 4
4 5 František Wichterlová JetBrains s.r.o. Klanova 9/506 Prague NaN Czech Republic 14700 +420 2 4172 5555 +420 2 4172 5555 frantisekw@jetbrains.com 4 
# BETTER PRACTICE: Use 'with' statement to automatically close connection
# The 'with' statement ensures the connection is closed even if an error occurs

sql = 'SELECT * FROM Artist LIMIT 10'
with sqlite3.connect("../../data/chinook.db") as conn:
    df_artist = pd.read_sql_query(sql, conn)

df_artist
ArtistId Name
0 1 AC/DC
1 2 Accept
2 3 Aerosmith
3 4 Alanis Morissette
4 5 Alice In Chains
5 6 Antônio Carlos Jobim
6 7 Apocalyptica
7 8 Audioslave
8 9 BackBeat
9 10 Billy Cobham

4.2.4. Section Practice#

Use this short exercise to check the main workflow from this section.

### Exercise: Practice Handling Datasets
# Create a list named monthly_sales with the values 120, 135, 150, and 145.
# Calculate the average monthly sales and print it rounded to one decimal place.
### Your code starts here




### Your code ends here

Hide code cell source

 
### Solution
monthly_sales = [120, 135, 150, 145]
average_sales = sum(monthly_sales) / len(monthly_sales)
print(round(average_sales, 1))

137.5