Reading CSV and Excel Files and Merging Data with pandas in Python

The article uses real stock‑listing data to teach how to read common data files in Python with the pandas library.

Reading CSV data

CSV files are comma‑separated and can be loaded with

import pandas as pd
nasdaq = pd.read_csv('nasdaq-listings.csv')
print(nasdaq.head(10))

. The df.head(), df.info() methods display the first rows and a summary of the DataFrame.

To handle missing values and parse dates, the call can be refined as

nasdaq = pd.read_csv('nasdaq-listings.csv', na_values='NAN', parse_dates=['Last Update'])
print(nasdaq.head())
nasdaq.info()

, which shows proper dtypes such as datetime64[ns] for the "Last Update" column.

Reading Excel data

Excel files can be read with

nyse = pd.read_excel('listings.xlsx', sheet_name='nyse', na_values='n/a')
print(nyse.head())
nyse.info()

. The pd.ExcelFile() object provides access to all sheet names:

xls = pd.ExcelFile('listings.xlsx')
exchanges = xls.sheet_names
print(exchanges)  # ['amex', 'nasdaq', 'nyse']

.

All sheets can be loaded at once:

listings = pd.read_excel(xls, sheet_name=exchanges, na_values='n/a')
listings['nasdaq'].info()

.

Merging multiple tables

Because the sheets share the same column structure, they can be stacked with

# read two sheets
nyse = pd.read_excel('listings.xlsx', sheet_name='nyse', na_values='n/a')
nasdaq = pd.read_excel('listings.xlsx', sheet_name='nasdaq', na_values='n/a')
# add source label
nyse['Exchange'] = 'NYSE'
nasdaq['Exchange'] = 'NASDAQ'
# concatenate
combined_listings = pd.concat([nyse, nasdaq])
combined_listings.info()

. For all sheets, a loop can collect each DataFrame into a list and then concatenate:

listings = []
for exchange in exchanges:
    df = pd.read_excel(xls, sheet_name=exchange, na_values='n/a')
    df['Exchange'] = exchange
    listings.append(df)
listing_data = pd.concat(listings)
listing_data.info()

.

The final merged DataFrame contains 8 columns, including an "Exchange" column that identifies the original sheet for each row.