Python Pandas - IO Tools

Pandas - Input/Output Tools (Reading and Writing Data)

Pandas provides a rich set of functions for reading data from various file formats into Pandas objects (primarily DataFrames) and for writing data back out to files. These I/O (Input/Output) tools are crucial for loading real-world data and saving results.

The most common functions for reading text-based tabular data (often called flat files) are pd.read_csv() and pd.read_table().

pd.read_csv(): Designed for reading Comma-Separated Values (CSV) files. This is arguably the most frequently used I/O function.

pd.read_table(): A more general function for reading delimited files, defaulting to tab (\t) as the separator. Often, pd.read_csv() with the sep parameter specified is used instead.

The basic signature for read_csv includes many useful parameters:

pd.read_csv(filepath_or_buffer,
            sep=',',            # Delimiter to use (default is comma)
            delimiter=None,     # Alias for sep
            header='infer',     # Row number(s) to use as column names (default: 0 if names not provided)
            names=None,         # List of column names to use
            index_col=None,     # Column(s) to use as row index labels
            usecols=None,       # Subset of columns to read
            dtype=None,         # Specify data type for columns (e.g., {'col': str})
            engine=None,        # Parser engine ('c' or 'python')
            skiprows=None,      # Number of lines to skip at the start
            nrows=None,         # Number of rows to read from the file
            parse_dates=False,  # Specify columns to parse as dates
            ...                 # Many other options available
           )

Let’s assume we have a CSV file named data.csv with the following content:

S.No,Name,Age,City,Salary
1,Tom,28,Toronto,20000
2,Lee,32,HongKong,3000
3,Steven,43,Bay Area,8300
4,Ram,38,Hyderabad,3900

(You would need to save this text into a file named data.csv in the same directory as your script/notebook to run the examples.)

Basic Reading with read_csv

Reading a standard CSV file is straightforward:

import pandas as pd

try:
    # Read the CSV file into a DataFrame
    df = pd.read_csv("data.csv")
    print("DataFrame read from data.csv:")
    print(df)
except FileNotFoundError:
    print("Error: data.csv not found. Please create the file with the sample data.")

Output (if data.csv exists):

DataFrame read from data.csv:
   S.No    Name  Age       City  Salary
0     1     Tom   28    Toronto   20000
1     2     Lee   32   HongKong    3000
2     3  Steven   43   Bay Area    8300
3     4     Ram   38  Hyderabad    3900

Pandas automatically inferred the header from the first row and used the default comma separator.

Specifying the Index Column (index_col)

If one of the columns in the CSV should serve as the DataFrame index, use the index_col parameter.

import pandas as pd

try:
    # Use the 'S.No' column as the index
    df_indexed = pd.read_csv("data.csv", index_col='S.No')
    print("DataFrame with 'S.No' as index:")
    print(df_indexed)
except FileNotFoundError:
    print("Error: data.csv not found.")

Output:

DataFrame with 'S.No' as index:
         Name  Age       City  Salary
S.No
1         Tom   28    Toronto   20000
2         Lee   32   HongKong    3000
3      Steven   43   Bay Area    8300
4         Ram   38  Hyderabad    3900

Specifying Data Types (dtype)

Pandas tries to infer data types, but sometimes you need to explicitly specify them, especially for columns that might be misinterpreted (e.g., IDs read as numbers instead of strings). Use the dtype parameter with a dictionary mapping column names to types.

import pandas as pd
import numpy as np # Needed for np.float64

try:
    # Explicitly set 'Salary' to float and 'Age' to string
    df_typed = pd.read_csv("data.csv", dtype={'Salary': np.float64, 'Age': str})
    print("DataFrame with specified dtypes:")
    print(df_typed)
    print("\nData types:")
    print(df_typed.dtypes)
except FileNotFoundError:
    print("Error: data.csv not found.")

Output:

DataFrame with specified dtypes:
   S.No    Name Age       City   Salary
0     1     Tom  28    Toronto  20000.0
1     2     Lee  32   HongKong   3000.0
2     3  Steven  43   Bay Area   8300.0
3     4     Ram  38  Hyderabad   3900.0

Data types:
S.No        int64
Name       object
Age        object  # Note: 'object' often indicates string type
City       object
Salary    float64
dtype: object

Notice Salary is now float64 and Age is object (string) as specified.

Custom Column Names and Handling Headers (names, header)

If the file lacks a header row, or you want to provide your own column names, use the names parameter. If the file does have a header you want to override, you also need to specify header=0 to indicate the first row should be treated as data (or skipped if skiprows is used).

import pandas as pd

try:
    # Provide custom names AND specify that row 0 is the original header to replace
    custom_names = ['ID', 'EmployeeName', 'AgeYrs', 'Location', 'Pay']
    df_named = pd.read_csv("data.csv", names=custom_names, header=0)
    print("DataFrame with custom names (original header replaced):")
    print(df_named)
except FileNotFoundError:
    print("Error: data.csv not found.")

Output:

DataFrame with custom names (original header replaced):
   ID EmployeeName  AgeYrs   Location   Pay
0   1          Tom      28    Toronto 20000
1   2          Lee      32   HongKong  3000
2   3       Steven      43   Bay Area  8300
3   4          Ram      38  Hyderabad  3900

If the file had no header, you would just use names=custom_names and omit header=0 (or set header=None). If the header was on a different row (e.g., row 3), you’d use header=2 (0-based index).

Skipping Rows (skiprows)

Use skiprows to skip a specific number of rows from the beginning of the file, or provide a list of row indices (0-based) to skip.

import pandas as pd

try:
    # Skip the first two rows (header and first data row)
    # Note: This will likely cause the next row (Lee's data) to become the header
    df_skipped = pd.read_csv("data.csv", skiprows=2)
    print("DataFrame after skipping first 2 rows:")
    print(df_skipped)
except FileNotFoundError:
    print("Error: data.csv not found.")

Output:

DataFrame after skipping first 2 rows:
   2     Lee  32  HongKong  3000
0  3  Steven  43  Bay Area  8300
1  4     Ram  38 Hyderabad  3900

To skip rows and still use the original header or provide names, combine skiprows with header or names appropriately. For example, to skip the first 2 rows and use custom names without treating row 2 as a header: pd.read_csv("data.csv", skiprows=2, header=None, names=custom_names)

Writing DataFrames to Files (.to_csv(), etc.)

Just as you read data in, you can write DataFrames out to various formats. The .to_csv() method is the counterpart to read_csv.

import pandas as pd

# Assuming df_indexed exists from a previous step
df_indexed = pd.DataFrame({'Name': ['Tom', 'Lee'], 'Age': [28, 32]}, index=[1, 2])
df_indexed.index.name = 'S.No'

try:
    # Write df_indexed to a new CSV file
    # index=True (default) writes the index
    # index=False omits the index
    df_indexed.to_csv("output.csv", index=True)
    print("\nDataFrame successfully written to output.csv")

    # Read it back to verify
    df_read_back = pd.read_csv("output.csv", index_col='S.No')
    print("\nReading back output.csv:")
    print(df_read_back)

except Exception as e:
    print(f"An error occurred during writing/reading: {e}")

Key parameters for to_csv() include sep, header (boolean or list of strings), index (boolean), mode ('w' for write, 'a' for append), encoding, etc.

Other Common Formats

Pandas supports many other formats:

• Excel: pd.read_excel(), df.to_excel() (requires openpyxl or xlrd library).
• JSON: pd.read_json(), df.to_json().
• HTML: pd.read_html() (reads tables from web pages), df.to_html().
• SQL: pd.read_sql(), df.to_sql() (requires SQLAlchemy and a DB driver).
• Parquet: pd.read_parquet(), df.to_parquet() (requires pyarrow or fastparquet). Efficient columnar format, good for large datasets.
• Feather: pd.read_feather(), df.to_feather() (requires pyarrow). Fast, lightweight binary format.

The choice of format depends on the data source, storage requirements, performance needs, and interoperability with other systems. CSV remains highly common for text-based exchange, while formats like Parquet are preferred for performance with larger datasets.