Convert JSON to CSV

Introduction

In this project, you will learn how to convert JSON data to CSV format using Python. This is a common task in data science and development, as JSON is often used for API responses, while CSV is a popular format for storing tabular data.

👀 Preview

$ head result.csv
"IP","Status","Time","HttpReferer","HttpUserAgent","Request","HttpXForwardedFor","BodyBytesSent","RemoteUser","RequestLength"
"72.55.30.187","202","[2016-02-23 16:25:10]","http://www.google.cn/search?q=hive","Mozilla/4.0 (compatible; MSIE 7.0; Windows NT 5.1; Trident/4.0; .NET CLR 2.0.50727)","GET /index.html HTTP/1.1","-","-","-","0"
"55.222.156.202","200","[2016-02-23 16:25:10]","-","Mozilla/4.0 (compatible; MSIE6.0; Windows NT 5.0; .NET CLR 1.1.4322)","GET /login.php HTTP/1.1","-","-","-","0"
"190.215.55.29","201","[2016-02-23 16:25:10]","-","Mozilla/4.0 (compatible; MSIE6.0; Windows NT 5.0; .NET CLR 1.1.4322)","GET /view.php HTTP/1.1","-","-","-","0"
"63.132.98.30","200","[2016-02-23 16:25:10]","-","Mozilla/5.0 (compatible; MSIE 10.0; Windows NT 6.2; Trident/6.0)","GET /list.php HTTP/1.1","-","-","-","0"
"214.124.190.132","201","[2016-02-23 16:25:10]","-","Mozilla/5.0 (iPhone; CPU iPhone OS 7_0_3 like Mac OS X) AppleWebKit/537.51.1 (KHTML, like Gecko) Version/7.0 Mobile/11B511 Safari/9537.53","GET /login.php HTTP/1.1","-","-","-","0"
"98.215.187.30","202","[2016-02-23 16:25:10]","-","Mozilla/5.0 (compatible; MSIE 10.0; Windows NT 6.2; Trident/6.0)","GET /upload.php HTTP/1.1","-","-","-","0"
"143.55.168.187","201","[2016-02-23 16:25:10]","-","Mozilla/5.0 (compatible; MSIE 10.0; Windows NT 6.2; Trident/6.0)","GET /login.php HTTP/1.1","-","-","-","0"
"98.190.201.29","200","[2016-02-23 16:25:10]","-","Mozilla/5.0 (Linux; Android 4.2.1; Galaxy Nexus Build/JOP40D) AppleWebKit/535.19 (KHTML, like Gecko) Chrome/18.0.1025.166 Mobile Safari/535.19","GET /view.php HTTP/1.1","-","-","-","0"
"10.168.55.143","301","[2016-02-23 16:25:10]","http://cn.bing.com/search?q=spark mlib","Mozilla/5.0 (compatible; MSIE 10.0; Windows NT 6.2; Trident/6.0)","GET /admin/login.php HTTP/1.1","-","-","-","0"

🎯 Tasks

In this project, you will learn:

• How to read and understand JSON data
• How to convert JSON data to a CSV file
• How to write the CSV file with the correct column names and formatting

🏆 Achievements

After completing this project, you will be able to:

• Efficiently convert JSON data obtained from APIs to a structured CSV format
• Understand the process of parsing JSON data and writing it to a CSV file
• Apply these skills to a variety of data processing and analysis tasks

Skills Graph

Read and Understand the JSON Data

In this step, you will learn how to read and understand the JSON data from the web_access.json file.

1. Open the web_access.json file located in the ~/project directory using a text editor.
2. Examine the contents of the file. You should see an array of objects, where each object represents a web access log entry. Each entry has several properties, such as "IP", "Status", "Time", "HttpReferer", "HttpUserAgent", "Request", "HttpXForwardedFor", "BodyBytesSent", "RemoteUser", and "RequestLength".
3. Familiarize yourself with the structure and content of the JSON data. This information will be useful in the next step when you convert the data to CSV format.

Convert JSON to CSV

In this step, you will learn how to convert the JSON data from the web_access.json file to a CSV file.

1. Create a new Python file named convert.py in the ~/project directory.
2. In the convert.py file, import the necessary libraries:

import csv
import json

3. Read the JSON data from the web_access.json file:

with open("web_access.json", "r") as json_file:
    data = json.load(json_file)

4. Define the order of the columns in the CSV file:

fieldnames = [
    "IP",
    "Status",
    "Time",
    "HttpReferer",
    "HttpUserAgent",
    "Request",
    "HttpXForwardedFor",
    "BodyBytesSent",
    "RemoteUser",
    "RequestLength",
]

5. Open a new CSV file named result.csv in the ~/project directory and create a csv.DictWriter object:

with open("result.csv", "w", newline="") as csvfile:
    writer = csv.DictWriter(csvfile, fieldnames=fieldnames, quoting=csv.QUOTE_ALL)

6. Write the header row to the CSV file:

writer.writeheader()

7. Write the data rows to the CSV file:

for row in data:
    writer.writerow(row)

Your complete convert.py file should look like this:

import csv
import json

## Read JSON data from the file
with open("web_access.json", "r") as json_file:
    data = json.load(json_file)

## Define the order of columns
fieldnames = [
    "IP",
    "Status",
    "Time",
    "HttpReferer",
    "HttpUserAgent",
    "Request",
    "HttpXForwardedFor",
    "BodyBytesSent",
    "RemoteUser",
    "RequestLength",
]

## Writing to CSV file
with open("result.csv", "w", newline="") as csvfile:
    writer = csv.DictWriter(csvfile, fieldnames=fieldnames, quoting=csv.QUOTE_ALL)

    ## Writing header
    writer.writeheader()

    ## Writing data rows
    for row in data:
        writer.writerow(row)

Run the Python Script

In this step, you will run the convert.py script to generate the result.csv file.

1. Open a terminal and navigate to the ~/project directory.
2. Run the convert.py script using the following command:

python convert.py

3. After the script completes, you should see a new file named result.csv in the ~/project directory.
4. You can view the first 10 lines of the result.csv file using the following command:

head result.csv

This should output the header row and the first 9 data rows of the CSV file.

"IP","Status","Time","HttpReferer","HttpUserAgent","Request","HttpXForwardedFor","BodyBytesSent","RemoteUser","RequestLength"
"72.55.30.187","202","[2016-02-23 16:25:10]","http://www.google.cn/search?q=hive","Mozilla/4.0 (compatible; MSIE 7.0; Windows NT 5.1; Trident/4.0; .NET CLR 2.0.50727)","GET /index.html HTTP/1.1","-","-","-","0"
"55.222.156.202","200","[2016-02-23 16:25:10]","-","Mozilla/4.0 (compatible; MSIE6.0; Windows NT 5.0; .NET CLR 1.1.4322)","GET /login.php HTTP/1.1","-","-","-","0"
"190.215.55.29","201","[2016-02-23 16:25:10]","-","Mozilla/4.0 (compatible; MSIE6.0; Windows NT 5.0; .NET CLR 1.1.4322)","GET /view.php HTTP/1.1","-","-","-","0"
"63.132.98.30","200","[2016-02-23 16:25:10]","-","Mozilla/5.0 (compatible; MSIE 10.0; Windows NT 6.2; Trident/6.0)","GET /list.php HTTP/1.1","-","-","-","0"
"214.124.190.132","201","[2016-02-23 16:25:10]","-","Mozilla/5.0 (iPhone; CPU iPhone OS 7_0_3 like Mac OS X) AppleWebKit/537.51.1 (KHTML, like Gecko) Version/7.0 Mobile/11B511 Safari/9537.53","GET /login.php HTTP/1.1","-","-","-","0"
"98.215.187.30","202","[2016-02-23 16:25:10]","-","Mozilla/5.0 (compatible; MSIE 10.0; Windows NT 6.2; Trident/6.0)","GET /upload.php HTTP/1.1","-","-","-","0"
"143.55.168.187","201","[2016-02-23 16:25:10]","-","Mozilla/5.0 (compatible; MSIE 10.0; Windows NT 6.2; Trident/6.0)","GET /login.php HTTP/1.1","-","-","-","0"
"98.190.201.29","200","[2016-02-23 16:25:10]","-","Mozilla/5.0 (Linux; Android 4.2.1; Galaxy Nexus Build/JOP40D) AppleWebKit/535.19 (KHTML, like Gecko) Chrome/18.0.1025.166 Mobile Safari/535.19","GET /view.php HTTP/1.1","-","-","-","0"
"10.168.55.143","301","[2016-02-23 16:25:10]","http://cn.bing.com/search?q=spark mlib","Mozilla/5.0 (compatible; MSIE 10.0; Windows NT 6.2; Trident/6.0)","GET /admin/login.php HTTP/1.1","-","-","-","0"

Congratulations! You have successfully converted the JSON data from the web_access.json file to a CSV file named result.csv.

Summary

Congratulations! You have completed this project. You can practice more labs in LabEx to improve your skills.