Working with CSV and Excel Files

After completing this topic, you will be able to:

Read and write CSV and Excel files using pandas.
Solve common issues encountered in real-world scenarios, such as encoding, sheet selection, and column specification.

What is CSV?

CSV (Comma-Separated Values) is a text file where values are separated by commas. When opened in a text editor, it looks like this:

text

Name,Age,City
Kim Hoon,30,Seoul
Lee Soo,25,Busan
Park Jin,35,Daejeon

It is the most common data exchange format. It can be read by any programming language and any operating system. Excel, Google Sheets, and databases all support CSV export.

Reading CSV Files

python

import pandas as pd

# Basic reading
df = pd.read_csv("data.csv")

# Specifying encoding - preventing Korean character errors
df = pd.read_csv("data.csv", encoding="cp949")
# Korean CSV files saved from Windows Excel use cp949 (or euc-kr)

# UTF-8 BOM
df = pd.read_csv("data.csv", encoding="utf-8-sig")

When reading a Korean CSV file and encountering a UnicodeDecodeError, it's an encoding issue. Try the following in order: utf-8 → utf-8-sig → cp949 → euc-kr.

Reading CSV Files - Advanced Options

python

# Reading only specific columns - saving memory
df = pd.read_csv("data.csv", usecols=["Name", "Age"])

# When the delimiter is not a comma
df = pd.read_csv("data.tsv", sep="\t")     # Tab-delimited
df = pd.read_csv("data.txt", sep="|")      # Pipe-delimited

# File with no header
df = pd.read_csv("data.csv", header=None,
                 names=["col1", "col2", "col3"])

# Specifying rows to skip
df = pd.read_csv("data.csv", skiprows=2)    # Skips the first 2 rows

# Large file - reading in chunks
for chunk in pd.read_csv("big.csv", chunksize=10000):
    process(chunk)  # Processes 10,000 rows at a time

chunksize prevents memory errors in large files (hundreds of MB or more). Instead of loading the entire file into memory at once, it processes it in chunks.

Reading Excel Files

python

# Basic reading (first sheet)
df = pd.read_excel("report.xlsx")

# Specific sheet
df = pd.read_excel("report.xlsx", sheet_name="Sales")

# Reading multiple sheets at once
sheets = pd.read_excel("report.xlsx", sheet_name=None)
# {'Sheet1': DataFrame, 'Sales': DataFrame, 'Costs': DataFrame}

for name, data in sheets.items():
    print(f"{name}: {len(data)} rows")

# Specifying cell range
df = pd.read_excel("report.xlsx",
                   usecols="B:E",    # Only columns B to E
                   skiprows=3,        # Skips the first 3 rows
                   nrows=100)         # Only 100 rows

Reading Excel files requires the openpyxl package (pip install openpyxl).

Saving Files

python

# Saving to CSV
df.to_csv("output.csv", index=False, encoding="utf-8-sig")
# index=False: Excludes the row number column
# utf-8-sig: Prevents Korean characters from being garbled when opened in Excel

# Saving to Excel
df.to_excel("output.xlsx", index=False, sheet_name="Results")

# Saving to multiple sheets
with pd.ExcelWriter("output.xlsx") as writer:
    df_sales.to_excel(writer, sheet_name="Sales", index=False)
    df_cost.to_excel(writer, sheet_name="Costs", index=False)

When saving to CSV, using encoding="utf-8-sig" will prevent Korean characters from being garbled when opened in Windows Excel. Saving with utf-8 often causes garbled characters in Excel.

Real-World Workflow - Merging CSV Files

Merging multiple monthly CSV files into one is a very common task.

python

import glob

# Find all CSV files in the folder
files = glob.glob("monthly_data/*.csv")
print(f"{len(files)} files found")

# Read and merge
dfs = []
for f in sorted(files):
    temp = pd.read_csv(f, encoding="utf-8-sig")
    temp["source_file"] = f  # Record the original file name
    dfs.append(temp)

df_all = pd.concat(dfs, ignore_index=True)
print(f"Total {len(df_all)} rows")

# Save the result
df_all.to_csv("merged_all.csv", index=False, encoding="utf-8-sig")

If you don't include ignore_index=True, the indexes of each file will be preserved, resulting in an index like 0, 1, 2, ..., 0, 1, 2, ...

Key Takeaways

CSV and Excel are the entry and exit points of data analysis. You often receive data from external sources in CSV/Excel format, and you often deliver results in CSV/Excel format. If you are confident in encoding (utf-8-sig vs cp949), sheet selection, and column specification, you can solve 90% of "file won't open" problems.

BioPlayground

Working with CSV and Excel Files

Working with CSV and Excel Files

After completing this topic, you will be able to:

What is CSV?

Reading CSV Files

Reading CSV Files - Advanced Options

Reading Excel Files

Saving Files

Real-World Workflow - Merging CSV Files

Key Takeaways

💬 Questions & Comments