3.2 Ingesting Data — Read CSV, Excel, JSON & SQL into Pandas

3.2 Ingesting Data with Pandas

Data ingestion is the process of importing data from an external source into a form that can be processed and analyzed.

In Data Science, data rarely starts inside a Python program. It may be stored in:

• CSV files
• Excel workbooks
• JSON files or API responses
• SQL databases
• Other structured data sources

pandas provides convenient functions for reading many of these sources directly into a DataFrame.

1. What Is Data Ingestion?

Data ingestion is the first stage of many Data Science workflows. External data is brought into a Python environment so that it can subsequently be inspected, cleaned, transformed, visualized, and analyzed.

For example:

CSV File
   ↓
pd.read_csv()
   ↓
DataFrame
   ↓
Data Cleaning
   ↓
EDA / Visualization
   ↓
Insights

2. Import pandas

Start by importing pandas using the conventional alias pd.

import pandas as pd

The reading functions used in this lesson are available through the pandas namespace.

3. Reading CSV Files

CSV stands for Comma-Separated Values. It is one of the most common formats for exchanging tabular data.

A CSV file may look like this:

Name,Age,Score
Alice,20,85
Brian,21,91
Clara,19,78

pandas provides read_csv() to load this data.

import pandas as pd

df = pd.read_csv("students.csv")

print(df)

Output:

    Name  Age  Score
0  Alice   20     85
1  Brian   21     91
2  Clara   19     78

4. Specifying a File Path

The CSV file can be located in the current directory or in another directory.

df = pd.read_csv(
    "data/students.csv"
)

An absolute path can also be supplied when appropriate.

df = pd.read_csv(
    "/data/project/students.csv"
)

5. Reading CSV Files with a Different Separator

Not every CSV-style file uses a comma. Some use semicolons, tabs, or other delimiters.

Use the sep parameter to specify the separator.

df = pd.read_csv(
    "students.txt",
    sep=";"
)

For a tab-separated file:

df = pd.read_csv(
    "students.tsv",
    sep="\t"
)

6. Header and Column Names

By default, pandas treats the first row as column headers when reading a typical CSV file.

If the file does not contain a header, use:

df = pd.read_csv(
    "students.csv",
    header=None
)

print(df)

You can provide your own column names:

df = pd.read_csv(
    "students.csv",
    header=None,
    names=["Name", "Age", "Score"]
)

7. Reading Selected Columns

Large datasets may contain many columns. If you need only a few, usecols can be used to select columns during ingestion.

df = pd.read_csv(
    "students.csv",
    usecols=["Name", "Score"]
)

print(df)

This can reduce unnecessary data loading and keep the resulting DataFrame focused on the required fields.

8. Reading a Limited Number of Rows

The nrows parameter can be useful when testing or inspecting a large file.

df = pd.read_csv(
    "students.csv",
    nrows=10
)

print(df)

This reads only the specified number of rows.

9. Skipping Rows

skiprows can be used when unwanted rows appear at the beginning of a file.

df = pd.read_csv(
    "students.csv",
    skiprows=2
)

This skips the first two rows before reading the remaining data.

10. Using a Column as the Index

A source column can be designated as the DataFrame index using index_col.

df = pd.read_csv(
    "students.csv",
    index_col="StudentID"
)

print(df)

The selected column becomes the row label instead of remaining an ordinary data column.

11. Specifying Data Types During CSV Ingestion

Sometimes pandas may infer a column's type differently from what your application requires.

The dtype parameter can specify data types.

df = pd.read_csv(
    "students.csv",
    dtype={
        "StudentID": "string",
        "Age": "int64"
    }
)

12. Parsing Dates During CSV Ingestion

Date columns are often imported as strings unless pandas can infer their format.

You can request date parsing during ingestion.

df = pd.read_csv(
    "attendance.csv",
    parse_dates=["Date"]
)

This allows the selected column to be represented as datetime-like data for subsequent date-based analysis.

13. Handling File Encoding

Text files may use different character encodings. If the encoding is incompatible with the default used by your environment, a decoding error may occur.

df = pd.read_csv(
    "students.csv",
    encoding="utf-8"
)

Another encoding may be specified when the source file actually uses it.

df = pd.read_csv(
    "students.csv",
    encoding="latin-1"
)

14. Important read_csv() Parameters

15. Reading Excel Files

Excel workbooks commonly use .xlsx or .xls formats.

pandas provides read_excel() to load spreadsheet data into a DataFrame.

import pandas as pd

df = pd.read_excel(
    "students.xlsx"
)

print(df)

16. Reading a Specific Excel Sheet

An Excel workbook may contain multiple worksheets.

Use sheet_name to select a specific sheet.

df = pd.read_excel(
    "students.xlsx",
    sheet_name="Students"
)

print(df)

A sheet can also be selected by its position.

df = pd.read_excel(
    "students.xlsx",
    sheet_name=0
)

17. Reading Multiple Excel Sheets

You can request multiple sheets from an Excel workbook.

data = pd.read_excel(
    "school_data.xlsx",
    sheet_name=["Students", "Teachers"]
)

print(data.keys())

When multiple sheets are requested this way, pandas returns a dictionary-like collection mapping sheet names to DataFrames.

students_df = data["Students"]

teachers_df = data["Teachers"]

18. Reading All Excel Sheets

Passing sheet_name=None requests all worksheets.

all_sheets = pd.read_excel(
    "school_data.xlsx",
    sheet_name=None
)

print(all_sheets.keys())

Each sheet is represented by a DataFrame in the returned mapping.

19. Excel Header and Index Options

Similar to CSV ingestion, Excel reading supports parameters such as header, names, and index_col.

df = pd.read_excel(
    "students.xlsx",
    header=0,
    index_col="StudentID"
)

20. Excel Engine

pandas relies on appropriate spreadsheet engines to read different Excel formats. In many modern environments, the required engine for .xlsx files is installed separately when needed.

If pandas reports that an Excel-reading dependency is missing, install the appropriate package in the active Python environment.

pip install openpyxl

You may then read an .xlsx workbook with:

df = pd.read_excel(
    "students.xlsx",
    engine="openpyxl"
)

21. Understanding JSON Data

JSON stands for JavaScript Object Notation.

It is widely used for exchanging structured data between applications, websites, APIs, and services.

Example JSON:

[
    {
        "Name": "Alice",
        "Age": 20,
        "Score": 85
    },
    {
        "Name": "Brian",
        "Age": 21,
        "Score": 91
    }
]

22. Reading a JSON File

Use pd.read_json() when the JSON structure is compatible with pandas' JSON reader.

df = pd.read_json(
    "students.json"
)

print(df)

23. Reading a JSON String

JSON data may arrive as a string, for example from an API response or another application.

json_data = """
[
    {
        "Name": "Alice",
        "Age": 20,
        "Score": 85
    },
    {
        "Name": "Brian",
        "Age": 21,
        "Score": 91
    }
]
"""

df = pd.read_json(
    json_data
)

print(df)

24. Recommended Way to Parse a JSON String

Python's io.StringIO can make a JSON string behave like a file-like object.

from io import StringIO
import pandas as pd

json_data = """
[
    {
        "Name": "Alice",
        "Age": 20,
        "Score": 85
    },
    {
        "Name": "Brian",
        "Age": 21,
        "Score": 91
    }
]
"""

df = pd.read_json(
    StringIO(json_data)
)

print(df)

This approach clearly communicates that the input is a JSON string rather than a filename.

25. JSON Orientations

JSON data can represent tabular information in different structures. The orient parameter helps pandas interpret certain JSON layouts.

A common structure is records:

[
    {"Name": "Alice", "Score": 85},
    {"Name": "Brian", "Score": 91},
    {"Name": "Clara", "Score": 78}
]

Another common structure is:

{
    "Name": {
        "0": "Alice",
        "1": "Brian",
        "2": "Clara"
    },

    "Score": {
        "0": 85,
        "1": 91,
        "2": 78
    }
}

Understanding the source JSON structure is important before selecting an appropriate parsing strategy.

26. Nested JSON and json_normalize()

Real-world APIs often return nested JSON rather than a simple flat list of records.

pandas provides json_normalize() for flattening suitable nested structures.

from pandas import json_normalize

data = [
    {
        "name": "Alice",
        "address": {
            "city": "London",
            "country": "UK"
        }
    }
]

df = json_normalize(data)

print(df)

The nested fields can be represented as columns such as:

name
address.city
address.country

27. Reading Data from SQL Tables

Structured data is often stored in relational databases such as SQLite, PostgreSQL, MySQL, and other SQL-compatible systems.

pandas can execute SQL queries and load their results into a DataFrame using read_sql() or related functions.

The exact database connection mechanism depends on the database system and driver.

28. Reading a SQLite Database

SQLite is particularly convenient for demonstrations because Python includes the sqlite3 module.

import sqlite3
import pandas as pd

connection = sqlite3.connect(
    "school.db"
)

df = pd.read_sql(
    "SELECT * FROM students",
    connection
)

print(df)

connection.close()

The SQL query result is returned as a DataFrame.

29. Reading Selected SQL Data

You do not have to import an entire database table. A SQL query can retrieve only the required records and columns.

query = """
SELECT Name, Score
FROM students
WHERE Score >= 80
"""

df = pd.read_sql(
    query,
    connection
)

print(df)

This is often preferable for large databases because unnecessary records do not need to be transferred into memory.

30. read_sql_table()

When working with a supported SQLAlchemy connection, pandas also provides read_sql_table() for reading a complete database table.

df = pd.read_sql_table(
    "students",
    connection
)

This approach is useful when the intention is explicitly to load a table rather than execute an arbitrary SQL query.

31. Using SQLAlchemy for Database Connections

SQLAlchemy is commonly used to provide a database connection layer for Python applications.

Example pattern:

from sqlalchemy import create_engine
import pandas as pd

engine = create_engine(
    "sqlite:///school.db"
)

df = pd.read_sql(
    "SELECT * FROM students",
    engine
)

print(df)

For other database systems, the connection URL depends on the database type, driver, host, credentials, and database name.

32. Parameterized SQL Queries

When external values are incorporated into database queries, avoid constructing SQL by directly concatenating user-controlled input.

Prefer parameterized queries supported by the database interface.

query = """
SELECT *
FROM students
WHERE Score >= ?
"""

df = pd.read_sql(
    query,
    connection,
    params=[80]
)

33. Data Ingestion Functions at a Glance

34. CSV vs Excel vs JSON vs SQL

35. A Practical Data Ingestion Workflow

Importing data is only the first step. A professional workflow normally verifies the imported structure immediately.

import pandas as pd

df = pd.read_csv(
    "students.csv"
)

print(df.head())

print(df.shape)

print(df.columns)

print(df.dtypes)

This gives a quick first look at:

• The first records
• Dataset dimensions
• Column names
• Data types

Detailed inspection will be covered in the next lesson, 3.3 Data Inspection.

36. Practical Example — CSV Student Data

Suppose students.csv contains:

StudentID,Name,Math,Science
S001,Alice,82,85
S002,Brian,91,89
S003,Clara,76,81
S004,David,88,92

Load it:

import pandas as pd

students = pd.read_csv(
    "students.csv"
)

print(students)

Select only the required columns:

students = pd.read_csv(
    "students.csv",
    usecols=[
        "StudentID",
        "Name",
        "Math"
    ]
)

Use the student ID as the index:

students = pd.read_csv(
    "students.csv",
    index_col="StudentID"
)

37. Practical Example — Excel Workbook

Suppose academic_data.xlsx contains:

• Students sheet
• Attendance sheet
• Results sheet

Read the Results sheet:

results = pd.read_excel(
    "academic_data.xlsx",
    sheet_name="Results"
)

print(results)

Read all sheets:

workbook = pd.read_excel(
    "academic_data.xlsx",
    sheet_name=None
)

results = workbook["Results"]
attendance = workbook["Attendance"]

38. Practical Example — JSON API Data

Assume an application provides student data as a JSON string.

from io import StringIO
import pandas as pd

json_data = """
[
    {
        "id": "S001",
        "name": "Alice",
        "score": 85
    },
    {
        "id": "S002",
        "name": "Brian",
        "score": 91
    }
]
"""

students = pd.read_json(
    StringIO(json_data)
)

print(students)

The resulting DataFrame can then be processed using normal pandas operations.

39. Practical Example — SQL Database

import sqlite3
import pandas as pd

connection = sqlite3.connect(
    "school.db"
)

query = """
SELECT
    StudentID,
    Name,
    Score
FROM students
WHERE Score >= 80
"""

students = pd.read_sql(
    query,
    connection
)

print(students)

connection.close()

This workflow retrieves only the required records from the database and returns them as a DataFrame.

40. Reading Data from a URL

Some pandas readers can accept URLs directly when the remote resource is supported by the underlying I/O functionality.

url = "https://example.com/data.csv"

df = pd.read_csv(url)

print(df)

41. Working with Large CSV Files

Very large CSV files may not fit comfortably into memory when loaded all at once.

One approach is to process the file in chunks.

for chunk in pd.read_csv(
    "large_dataset.csv",
    chunksize=10000
):

    print(chunk.shape)

Each iteration processes a smaller DataFrame instead of loading the complete file into memory at once.

42. Common Data Ingestion Errors

43. Pandas Data Ingestion Interview Questions

pd.read_excel(
    "file.xlsx",
    sheet_name="Sheet1"
)

from io import StringIO

df = pd.read_json(
    StringIO(json_string)
)

44. Examination Questions

Multiple Choice Questions

Short Answer Questions

1. Define data ingestion.
2. Write the syntax for reading a CSV file using pandas.
3. What is the purpose of the sep parameter?
4. How can you read a specific Excel worksheet?
5. What is JSON and where is it commonly used?
6. How can JSON data be converted into a DataFrame?
7. How can SQL query results be loaded into a DataFrame?
8. Why is parameterized SQL preferable to string concatenation?

45. Practical Lab Exercise

46. Common Mistakes to Avoid

48. Key Takeaways

• Data ingestion brings external data into a DataFrame for analysis.
• pd.read_csv() is used for CSV and delimiter-separated tabular files.
• pd.read_excel() imports spreadsheet data.
• sheet_name controls which Excel worksheet is loaded.
• pd.read_json() can parse compatible JSON data.
• StringIO is a robust way to pass an explicit JSON string to read_json().
• pd.json_normalize() is useful for suitable nested JSON structures.
• pd.read_sql() can execute SQL queries and return their results as DataFrames.
• usecols, nrows, and chunksize can help control how much data is loaded.
• dtype, parse_dates, and encoding help control how source data is interpreted.
• SQL queries containing external values should use parameterized inputs rather than unsafe string concatenation.