Working with CSV, Excel, and JSON Files in Python

Welcome back, learners! Today, we are diving into one of the most practical parts of Data Science – working with different types of data files. Specifically, we’re going to explore CSV, Excel, and JSON files. These are some of the most common formats used for storing data, and mastering them is essential for any aspiring data scientist.

By the end of this guide, you will know how to handle these file types in Python, allowing you to move data effortlessly between your code and external files. Let’s get started!

Table of Contents

1. Introduction to Data File Types
2. Working with CSV Files
3. Handling Excel Files
4. Dealing with JSON Files
5. Mini Project: Manipulating Different File Types
6. Quiz Time

1. Introduction to Data File Types

Data can be stored in many formats, but three of the most common are:

• CSV (Comma-Separated Values): A simple text format where data is separated by commas, easy to use and read.
• Excel Files (XLSX): Spreadsheet files often used for business and analytical purposes.
• JSON (JavaScript Object Notation): A lightweight format for data exchange, commonly used for APIs.

Each of these formats has its advantages, and Python offers powerful tools to interact with all three.

2. Working with CSV Files

CSV files are one of the simplest and most widely used formats for storing tabular data. Each line in a CSV file represents a data record, and commas separate the values.

Importing CSV Files Using Pandas

Pandas is a powerful library in Python that makes working with CSV files super easy. Let’s see how to use it to read and write CSV files.

import pandas as pd

# Reading a CSV file
data = pd.read_csv('data.csv')
print(data.head())

# Writing to a CSV file
data.to_csv('output.csv', index=False)

• pd.read_csv() reads a CSV file and creates a DataFrame.
• to_csv() allows you to write the DataFrame back to a CSV file.

CSV files can also be read without Pandas, using the built-in csv module:

import csv

# Reading CSV using the csv module
with open('data.csv', mode='r') as file:
    reader = csv.reader(file)
    for row in reader:
        print(row)

This approach is lighter than Pandas but lacks the convenience of DataFrames.

3. Handling Excel Files

Excel files are used widely in industries because they allow for more sophisticated formatting and can include multiple sheets. Python’s openpyxl and pandas libraries are great tools for working with Excel files.

Reading and Writing Excel Files with Pandas

# Reading an Excel file
excel_data = pd.read_excel('data.xlsx', sheet_name='Sheet1')
print(excel_data.head())

# Writing to an Excel file
excel_data.to_excel('output.xlsx', index=False)

• pd.read_excel() lets you read data from an Excel sheet.
• to_excel() writes data to an Excel file, and you can specify the sheet name.

Working with Multiple Sheets

Excel files can have multiple sheets, and Pandas allows you to access them easily:

# Reading multiple sheets
all_sheets = pd.read_excel('data.xlsx', sheet_name=None)
for sheet, data in all_sheets.items():
    print(f"Sheet name: {sheet}")
    print(data.head())

This approach gives you a dictionary, where each key is a sheet name and each value is a DataFrame.

4. Dealing with JSON Files

JSON files are used extensively for web data and APIs because they allow for easy data exchange. They represent data as key-value pairs, similar to Python dictionaries.

Reading and Writing JSON Files

Python makes handling JSON files straightforward with its built-in json module.

import json

# Reading a JSON file
with open('data.json', 'r') as file:
    data = json.load(file)
    print(data)

# Writing to a JSON file
with open('output.json', 'w') as file:
    json.dump(data, file, indent=4)

• json.load() reads the JSON data into a Python dictionary.
• json.dump() writes a Python dictionary into a JSON file.

Using Pandas with JSON

Pandas also supports reading and writing JSON files directly:

# Reading a JSON file with Pandas
data = pd.read_json('data.json')
print(data.head())

# Writing to a JSON file
data.to_json('output.json', orient='records')

This approach is useful when you need to work with JSON data in tabular form.

5. Mini Project: Manipulating Different File Types

Let’s put our knowledge into practice. Suppose we have data in different formats (CSV, Excel, JSON), and we want to combine them into one analysis.

Project Steps:

1. Load data from CSV, Excel, and JSON.
2. Merge the data into a single DataFrame.
3. Save the combined data into a new Excel file.

Code Example:

# Load CSV, Excel, and JSON data
csv_data = pd.read_csv('data.csv')
excel_data = pd.read_excel('data.xlsx', sheet_name='Sheet1')
json_data = pd.read_json('data.json')

# Merge the datasets
combined_data = pd.concat([csv_data, excel_data, json_data], ignore_index=True)

# Save to a new Excel file
combined_data.to_excel('combined_output.xlsx', index=False)
print("Data successfully combined and saved!")

This project will give you practice in working with different file types and consolidating data for analysis.

6. Quiz Time

Let’s test your understanding of working with CSV, Excel, and JSON files:

1. Which method is used to read a CSV file using Pandas?

• a) pd.read_csv()
• b) pd.read_excel()
• c) json.load()

1. What is the advantage of using JSON for data storage?

• a) It’s easy to read and write.
• b) It supports multiple sheets.
• c) It requires no library.

1. Which Python library can be used to work with Excel files?

• a) openpyxl
• b) Pandas
• c) Both a and b

Answers: 1-a, 2-a, 3-c

Next Steps

Now that you have learned to work with CSV, Excel, and JSON files, it’s time to start applying these skills in your projects. Data in real-life scenarios often comes in various formats, and knowing how to handle each type is essential.

In the next article, we will explore data cleaning techniques, which will teach you how to handle missing values and prepare data for analysis. Stay tuned, and keep practicing!