Reading Data: CSV, Excel, JSON, SQL

Loading CSV Files with read_csv

The CSV format is the ubiquitous standard for flat-file data exchange, and read_csv is the workhorse function in your library. When you invoke this function, the engine scans the file to infer column data types based on the content of the first few rows. This process is dynamic, meaning the library assigns types like integer, float, or object based on detected patterns. Understanding this is crucial because incorrect type inference can lead to massive memory overhead; for example, storing strings as objects consumes significantly more space than categorical types. By controlling parameters such as 'dtype', 'sep', and 'usecols', you manually influence the parsing logic to prioritize memory efficiency and speed. Properly defining your column types during the ingestion phase prevents expensive downcasting operations later in your workflow, ensuring that your data structure is optimized for high-performance vectorized operations immediately upon loading.

import pandas as pd

# Loading a dataset with specific types to optimize memory
# usecols allows us to load only the necessary data into memory
df = pd.read_csv('sales_data.csv', 
                 sep=',', 
                 dtype={'id': 'int32', 'revenue': 'float32'},
                 usecols=['id', 'date', 'revenue']) 
print(df.head())

Reading Excel Worksheets

Reading Excel files requires the underlying library to interpret complex spreadsheet metadata such as formatting, merged cells, and multiple sheets. Unlike plain text formats, Excel files carry structural overhead; therefore, read_excel allows you to specify a 'sheet_name' to target specific data segments. A critical aspect of Excel ingestion is that the library must convert rich cell data into a uniform DataFrame structure. If your file contains headers spread across rows or complex formatting, you must utilize the 'header' and 'skiprows' arguments to normalize the input. Because Excel operations are inherently slower than text-based formats, you should be intentional about which ranges you load. Being aware of the internal translation process helps you anticipate common issues like empty row propagation or data type coercion failures when cells contain mixed-type entries, allowing for cleaner initial data structures.

import pandas as pd

# Reading a specific sheet and skipping unnecessary header rows
df = pd.read_excel('financial_report.xlsx', 
                   sheet_name='Q3_Results', 
                   header=1, 
                   skiprows=[2]) # Skip the second row containing comments
print(df.info())

Parsing Semi-Structured JSON

JSON is a semi-structured format that often maps poorly to a flat two-dimensional DataFrame, making read_json a versatile but nuanced tool. Because JSON data can be nested (e.g., objects within objects), the reader often requires an 'orient' parameter to tell it how to map the hierarchy to rows and columns. When you load JSON, the parser recursively traverses the structure, potentially flattening it depending on your configuration. Understanding the 'orient' parameter is vital because choosing the wrong configuration can result in malformed columns or transposed data. If your data is heavily nested, simple reading may not suffice; you might need to use json_normalize to flatten deeply buried attributes. Recognizing that JSON lacks the strict column-row discipline of a CSV file helps you anticipate when you will need to perform post-load transformation to achieve a usable analytical shape.

import pandas as pd

# Reading JSON data where records are organized as a list of dictionaries
# 'records' orientation aligns the JSON structure with the DataFrame format
df = pd.read_json('user_activity.json', orient='records')
print(df.describe())

Querying SQL Databases

Connecting to a database requires a bridge, typically an engine created via a connection string, which the library uses to execute queries and stream results directly into memory. The read_sql function is distinct because it offloads the heavy lifting of data selection and filtering to the database engine itself. This is highly efficient compared to loading an entire table, as you can use SQL syntax to perform aggregations or joins before the data ever hits your local environment. By carefully crafting your SQL SELECT statements, you minimize data transfer and memory footprint. Understanding that this function returns a DataFrame built from a SQL result set is fundamental; you must ensure your query returns a consistent set of columns to maintain predictable types. Always close connections after the data is retrieved to ensure efficient resource management within your environment.

from sqlalchemy import create_engine
import pandas as pd

# Establishing a database connection and executing a selective query
engine = create_engine('sqlite:///company_db.sqlite')
df = pd.read_sql('SELECT id, region, amount FROM transactions WHERE amount > 100', 
                 con=engine)
print(df.shape)

Handling Large Datasets with Chunking

When dealing with files that exceed your available RAM, standard reading functions will cause memory overflow errors. The solution is 'chunking', where you process data in iterative slices rather than loading the entire file at once. By setting the 'chunksize' parameter, the read function returns an iterator of DataFrames. This approach fundamentally changes how you write your code: instead of performing operations on the full dataset, you loop through chunks, applying transformations and aggregations to each subset individually. This allows you to process gigabytes of data on a standard laptop. The reasoning here is simple: you trade off execution time for memory stability. Being adept at chunking is the hallmark of a senior-level practitioner who understands the physical constraints of the local machine and the mechanics of data streaming.

import pandas as pd

# Processing a massive file in chunks of 10,000 rows to save memory
chunk_iterator = pd.read_csv('massive_log_file.csv', chunksize=10000)

for chunk in chunk_iterator:
    # Perform analysis or filtering on each chunk
    print(f'Processing chunk with {len(chunk)} rows')
    # Optionally write the result to a new file incrementally

Interview questions

All Pandas interview questions →

Check yourself

Take the full Pandas quiz →