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.