Data Input and Output

Real-world data rarely originates inside Python. It often lives in spreadsheets, CSV files, databases, or even public web pages. Before any meaningful analysis can begin, this data must be imported, cleaned, and prepared — and later, exported for sharing or reuse.

Pandas provides format-specific read_ and to_ functions. Choosing a reader does not remove the need to check types, missing-value rules, units and provenance. In this chapter, we will explore how to load and save data in four common formats: CSV, Excel, HTML, and SQL.

import numpy as np
import pandas as pd

Working with CSV Files

CSV (Comma-Separated Values) files are among the simplest and most widely used formats for data exchange. They are plain text files in which each row represents an observation and each column is separated by commas.

Let’s begin with an author-supplied teaching file. The CSV and Excel examples have no established statistical source, reference date or unit definition beyond their column labels; use them to learn file operations, not to make claims about current national economies. The following acquisition lesson introduces a documented institutional dataset.

File name: europe_economy.csv

Example File content:

Country,Population_M,GDP_Billion
France,67.4,2930
Germany,83.2,4100
Italy,59.0,2160
Spain,47.8,1780

Reading from a CSV File

import pandas as pd
df = pd.read_csv("data/legacy/europe_economy.csv")
print(df)

   Country  Population_M  GDP_Billion
0   France          67.4         2930
1  Germany          83.2         4100
2    Italy          59.0         2160
3    Spain          47.8         1780

Here, pd.read_csv() reads the file into a DataFrame, the central data structure of Pandas. Each column now has a name, and Pandas automatically infers numeric types for the data.

Writing to a CSV File

Once cleaned or transformed, the data can be exported to a new file:

The index=False argument prevents Pandas from adding row indices as an extra column.

df.to_csv("outputs/europe_economy_clean.csv", index=False)

Working with Excel Files

Many researchers and institutions still share data as Excel files. Pandas can both read and write Excel sheets — ideal for integrating digital research with existing workflows.

File name: European_Data.xlsx

Reading from Excel

df_excel = pd.read_excel("data/legacy/European_Data.xlsx")
df_excel.head()

Writing to Excel

df.to_excel("outputs/European_Data_copy.xlsx", index=False)

Note:

Pandas reads and writes data only — not formulas, charts, or macros. For most analytical tasks, however, these structures are sufficient and lightweight.

Reading Data from the Web (HTML Tables)

Some datasets exist directly online as HTML tables, such as government databases or institutional reports. Pandas can automatically find and import such tables using pd.read_html().

The supplied learner environment includes the lxml parser needed for this example. Run it after installing the package requirements; no additional parser installation is needed for the shipped fixture.

The following code parses an invented HTML table held in a string. It makes no request to a bank registry or live website, so you can reproduce the parsing experiment offline.

from io import StringIO
# Offline HTML parsing fixture: invented teaching rows, not a current bank registry.
html_table = '<table><tr><th>Institution</th><th>Records</th></tr><tr><td>North</td><td>12</td></tr><tr><td>South</td><td>8</td></tr></table>'
tables = pd.read_html(StringIO(html_table))
df_banks = tables[0]
df_banks.head()

This technique is useful for automatically collecting tabular data from public sources — for instance, digitized library catalogs, government reports, or online archives.

Working with SQL Databases

Large or structured datasets are often stored in databases. Pandas integrates smoothly with SQLAlchemy, allowing easy transfer of data between SQL databases and DataFrames.

The pandas.io.sql module provides several convenient methods for this purpose. Common functions include:

• read_sql_table() — reads an entire table into a DataFrame.
• read_sql_query() — runs an SQL query and returns the result as a DataFrame.
• read_sql() — automatically detects whether the input is a table or query.
• DataFrame.to_sql() — writes a DataFrame directly to a SQL table.

SQLite, a lightweight database included with Python, is ideal for small to medium-sized projects and reproducible experiments. It stores data either in memory or on disk, making it perfect for examples and teaching.

Example 1: In-Memory Database

An in-memory database exists only while the program is running, ideal for quick testing or prototyping. The example below demonstrates how to create, write, and query in a simple in-memory database containing libray records.

import pandas as pd
from sqlalchemy import create_engine

# Create sample data
books = pd.DataFrame({
    'Title': ['Madame Bovary', 'Faust', 'The Stranger', 'Don Quixote'],
    'Author': ['Gustave Flaubert', 'Johann W. Goethe', 'Albert Camus', 'Miguel de Cervantes'],
    'Year': [1856, 1808, 1942, 1605],
    'Rating': [4.3, 4.6, 4.4, 4.8]
})

# Create an in-memory SQLite engine
engine = create_engine('sqlite:///:memory:')

# Write DataFrame to SQL table
books.to_sql('library', con=engine, index=False)

# Read entire table back into a DataFrame
df_all = pd.read_sql_table('library', con=engine)
print(df_all)


query = "SELECT Title, Rating FROM library WHERE Rating > 4.5"
df_high = pd.read_sql_query(query, con=engine)
print(df_high)

           Title               Author  Year  Rating
0  Madame Bovary     Gustave Flaubert  1856     4.3
1          Faust     Johann W. Goethe  1808     4.6
2   The Stranger         Albert Camus  1942     4.4
3    Don Quixote  Miguel de Cervantes  1605     4.8
         Title  Rating
0        Faust     4.6
1  Don Quixote     4.8

This example shows how Pandas can store and query structured data directly in memory — efficiently and without the need for external files.

Example 2: File-Based SQLite Database

A file-based database persists on disk and can be shared between projects. Let’s simulate a more realistic use case. Let’s demonstrate by saving a library dataset inside a subfolder called data/.

import pandas as pd
from sqlalchemy import create_engine
import os

# Create the folder if it doesn't exist
os.makedirs('outputs', exist_ok=True)

# Create sample data
books = pd.DataFrame({
    'Title': ['Madame Bovary', 'Faust', 'The Stranger', 'Don Quixote'],
    'Author': ['Gustave Flaubert', 'Johann W. Goethe', 'Albert Camus', 'Miguel de Cervantes'],
    'Year': [1856, 1808, 1942, 1605],
    'Rating': [4.3, 4.6, 4.4, 4.8]
})

# Define database path
db_path = 'outputs/library_data.db'

# Create SQLite engine pointing to the file
engine = create_engine(f'sqlite:///{db_path}')

# Write DataFrame to SQL table
books.to_sql('books', con=engine, index=False, if_exists='replace')

# Read data back into a DataFrame
df_books = pd.read_sql('SELECT * FROM books', con=engine)
print(df_books)

           Title               Author  Year  Rating
0  Madame Bovary     Gustave Flaubert  1856     4.3
1          Faust     Johann W. Goethe  1808     4.6
2   The Stranger         Albert Camus  1942     4.4
3    Don Quixote  Miguel de Cervantes  1605     4.8

The file library_data.db is now saved in your working directory and can be reopened later or used in another project.

Summary

This chapter showed how Pandas connects seamlessly to real-world data — from CSV and Excel files to SQL databases and online tables. These tools make importing and sharing information simple and reliable, forming the bridge between raw data and analysis.

Next, we turn to Data Visualization, where datasets come to life through plots and charts — transforming numbers and tables into clear, visual stories.

Chapter exercisesWorked answers after an attempt