This page includes an interactive code editor. Try modifying and running the examples!

Reading Data with Pandas

Key Concept: Pandas provides powerful functions to read data from various sources including files, databases, web APIs, and more.

Introduction to Data Reading

One of Pandas' greatest strengths is its ability to read data from numerous sources. Whether you're working with local files, databases, or web APIs, Pandas provides intuitive functions to load data into DataFrames.

Supported Formats

CSV & TSV files
Excel spreadsheets
JSON files
SQL databases
HTML tables
Parquet files
HDF5 files

Common Sources

Local files
Web URLs
APIs
Databases
Clipboard
Cloud storage

Basic File Reading

The most common way to read data is from files. Pandas provides dedicated functions for different file formats.

Basic File Reading Examples

Common Reading Functions:

Function	Description	Common Parameters
`pd.read_csv()`	Read CSV files	`filepath, sep, header, index_col`
`pd.read_excel()`	Read Excel files	`io, sheet_name, header`
`pd.read_json()`	Read JSON files	`path, orient, lines`
`pd.read_sql()`	Read SQL databases	`sql, con, index_col`

Advanced Reading Options

Pandas offers numerous parameters to handle different data formats and structures.

Advanced Reading Parameters

Key Parameters for CSV:

sep - Delimiter (default: ',')
header - Row to use as column names
index_col - Column to use as row index
usecols - Columns to read
dtype - Data types for columns
parse_dates - Parse dates automatically

Memory Optimization:

chunksize - Read in chunks
nrows - Number of rows to read
low_memory - Process in chunks
memory_map - Use memory mapping

Reading from Web and APIs

Pandas can directly read data from URLs and web APIs, making it easy to work with live data sources.

Web and API Data Reading

import pandas as pd
import requests
from io import StringIO

print("=== Reading from Web APIs ===")

# Example: Reading from a public API
try:
    # COVID-19 data example (public API)
    url = "https://api.covid19api.com/summary"
    response = requests.get(url)
    data = response.json()
    
    # Convert to DataFrame
    countries_df = pd.DataFrame(data['Countries'])
    print("COVID-19 Data (first 5 countries):")
    print(countries_df[['Country', 'TotalConfirmed', 'TotalDeaths', 'TotalRecovered']].head())
    
except Exception as e:
    print("API request failed. Using sample data instead.")
    # Fallback sample data
    sample_data = {
        'Country': ['USA', 'India', 'Brazil', 'France', 'UK'],
        'TotalConfirmed': [1000000, 800000, 600000, 400000, 300000],
        'TotalDeaths': [20000, 15000, 10000, 8000, 5000],
        'TotalRecovered': [900000, 700000, 500000, 350000, 250000]
    }
    countries_df = pd.DataFrame(sample_data)
    print("Sample country data:")
    print(countries_df)

print("\n=== Reading from URLs ===")
# Reading CSV directly from URL
try:
    # Example: Titanic dataset from GitHub
    url = "https://raw.githubusercontent.com/datasciencedojo/datasets/master/titanic.csv"
    titanic_df = pd.read_csv(url)
    print("Titanic dataset shape:", titanic_df.shape)
    print("\nFirst 3 rows:")
    print(titanic_df.head(3))
except:
    print("URL reading failed. Check internet connection.")

Note: Reading from web sources requires an internet connection and may be subject to API rate limits or authentication requirements.

Reading from Databases

Pandas integrates seamlessly with SQL databases using SQLAlchemy or database-specific connectors.

Database Reading Examples

import pandas as pd
import sqlite3
from io import StringIO

print("=== Reading from Databases ===")

# Create a sample in-memory SQLite database for demonstration
conn = sqlite3.connect(':memory:')
cursor = conn.cursor()

# Create sample table
cursor.execute('''
    CREATE TABLE employees (
        id INTEGER PRIMARY KEY,
        name TEXT,
        department TEXT,
        salary INTEGER,
        hire_date TEXT
    )
''')

# Insert sample data
sample_employees = [
    (1, 'Alice', 'Engineering', 75000, '2020-01-15'),
    (2, 'Bob', 'Marketing', 65000, '2019-03-20'),
    (3, 'Charlie', 'Engineering', 80000, '2018-07-10'),
    (4, 'Diana', 'Sales', 60000, '2021-02-28'),
    (5, 'Eve', 'HR', 55000, '2020-11-05')
]

cursor.executemany('INSERT INTO employees VALUES (?, ?, ?, ?, ?)', sample_employees)
conn.commit()

# Read data using pandas
print("Reading from SQL database:")
df_sql = pd.read_sql_query('SELECT * FROM employees', conn)
print(df_sql)

print("\n=== Reading with SQL queries ===")
# Complex query example
query = '''
    SELECT department, AVG(salary) as avg_salary, COUNT(*) as employee_count
    FROM employees 
    GROUP BY department 
    ORDER BY avg_salary DESC
'''
dept_stats = pd.read_sql_query(query, conn)
print("Department statistics:")
print(dept_stats)

# Clean up
conn.close()

print("\n=== Reading from other sources ===")
# Clipboard example (commented out as it requires GUI)
# df_clipboard = pd.read_clipboard()
# print("Data from clipboard:", df_clipboard.shape)

Database Connection Examples:

# SQLite
import sqlite3
conn = sqlite3.connect('database.db')

# PostgreSQL
import psycopg2
conn = psycopg2.connect("dbname=test user=postgres")

# MySQL
import mysql.connector
conn = mysql.connector.connect(user='user', database='test')

# Using SQLAlchemy
from sqlalchemy import create_engine
engine = create_engine('sqlite:///database.db')

Common Issues and Solutions

Data reading can encounter various issues. Here's how to handle common problems.

Troubleshooting Data Reading

import pandas as pd
from io import StringIO

print("=== Handling Common Reading Issues ===")

# Sample problematic CSV
problematic_csv = '''Name,Age,City,Salary,Notes
Alice,25,New York,50000,"Joined in 2020"
Bob,30,London,60000,"Senior developer"
Charlie,35,Tokyo,70000,"Manages team"
Diana,28,Paris,55000,"New hire"
Eve,32,"Sydney, Australia",65000,"Works remotely"'''

print("Problematic CSV data:")
print(problematic_csv)

print("\n=== Solution 1: Handling quotes and delimiters ===")
df_fixed = pd.read_csv(
    StringIO(problematic_csv),
    quotechar='"',
    escapechar='\\'
)
print("Fixed reading:")
print(df_fixed)

print("\n=== Solution 2: Handling missing values ===")
# CSV with missing values
csv_with_missing = '''Name,Age,City,Salary
Alice,25,New York,50000
Bob,,London,60000
Charlie,35,,70000
Diana,28,Paris,
Eve,32,Sydney,65000'''

df_missing = pd.read_csv(
    StringIO(csv_with_missing),
    na_values=['', 'NULL', 'N/A']  # Specify additional NA values
)
print("Data with missing values handled:")
print(df_missing)
print("\nMissing values summary:")
print(df_missing.isnull().sum())

print("\n=== Solution 3: Specifying data types ===")
df_dtypes = pd.read_csv(
    StringIO(csv_with_missing),
    dtype={'Name': 'string', 'Age': 'float64', 'Salary': 'float64'}
)
print("Data with specified dtypes:")
print(df_dtypes.dtypes)

print("\n=== Solution 4: Handling encoding issues ===")
# For encoding issues, use encoding parameter
# df = pd.read_csv('file.csv', encoding='utf-8')
# df = pd.read_csv('file.csv', encoding='latin-1')
# df = pd.read_csv('file.csv', encoding='ISO-8859-1')

Common Problems

Encoding issues
Missing values
Incorrect data types
Large file memory usage
Malformed files

Solutions

Specify encoding parameter
Use na_values parameter
Set dtype parameter
Use chunksize for large files
Use error_bad_lines=False

Best Practices

Memory Management

Use dtype to optimize memory
Read only needed columns with usecols
Use chunksize for large files
Consider data types (int8 vs int64)

Error Handling

Always check file existence
Handle encoding issues proactively
Validate data after reading
Use try-except blocks for external sources

Performance Tips

Use low_memory=False for consistent dtypes
Prefer CSV over Excel for large datasets
Use Parquet for better performance
Cache frequently used data

Quick Reference

Most Commonly Used Reading Functions:

# CSV Files
df = pd.read_csv('file.csv', index_col=0, parse_dates=['date_column'])

# Excel Files  
df = pd.read_excel('file.xlsx', sheet_name='Sheet1', usecols='A:D')

# JSON Files
df = pd.read_json('file.json', orient='records')

# SQL Database
df = pd.read_sql_query('SELECT * FROM table', connection)

# From URL
df = pd.read_csv('https://example.com/data.csv')

# With specific data types
dtype_dict = {'column1': 'category', 'column2': 'float32'}
df = pd.read_csv('file.csv', dtype=dtype_dict)

Next: In the following sections, we'll learn how to manipulate and analyze the data we've read into Pandas DataFrames.

← Data Structures Data Selection →

Pandas Tutorial