Blog

CSV (Comma-Separated Values) is a widely used format for storing tabular data. JSON (JavaScript Object Notation) is another popular format for structuring data in a more flexible and hierarchical way. In some cases, you may need to convert CSV data into JSON format for easier manipulation and compatibility with various applications and services. In this article, we’ll explore how to convert a CSV file containing values for each date into a JSON format using Python.

Understanding the CSV Data for convert csv to json

Before we dive into the code for converting CSV to JSON, let’s briefly understand the structure of the provided CSV data

31 Jan 2023,,,,,
Type,Time,Number,Duration,Quantity,Cost
Talk,12:32,61407228499,00:00:14,N/A,$0.00
Talk,11:50,61425380005,00:41:17,N/A,$0.00
Talk,08:28,61414464256,00:20:22,N/A,$0.00
30 Jan 2023,,,,,
Type,Time,Number,Duration,Quantity,Cost
Text,12:24,61431655867,N/A,N/A,$0.00
Talk,11:15,61431655867,00:00:21,N/A,$0.00
Talk,09:00,131318,00:11:53,N/A,$0.00
28 Jan 2023,,,,,
Type,Time,Number,Duration,Quantity,Cost
Talk,12:45,61414464256,00:00:03,N/A,$0.00
27 Jan 2023,,,,,

The CSV data is organized with date headers, followed by rows containing information about various types, times, numbers, durations, quantities, and costs. Our goal is to convert this structured data into a JSON format where each date serves as a key, and the corresponding data is stored as values in a dictionary.

Python Code for Convert CSV to JSON

Here’s a Python code snippet to convert CSV data into a JSON format:

import csv
import json

# Provided CSV data as a string
csv_data = """
31 Jan 2023,,,,,
Type,Time,Number,Duration,Quantity,Cost
Talk,12:32,61407228499,00:00:14,N/A,$0.00
Talk,11:50,61425380005,00:41:17,N/A,$0.00
Talk,08:28,61414464256,00:20:22,N/A,$0.00
30 Jan 2023,,,,,
Type,Time,Number,Duration,Quantity,Cost
Text,12:24,61431655867,N/A,N/A,$0.00
Talk,11:15,61431655867,00:00:21,N/A,$0.00
Talk,09:00,131318,00:11:53,N/A,$0.00
28 Jan 2023,,,,,
Type,Time,Number,Duration,Quantity,Cost
Talk,12:45,61414464256,00:00:03,N/A,$0.00
27 Jan 2023,,,,,
"""

# Split CSV data into lines
csv_lines = csv_data.strip().split('\n')

# Initialize CSV reader
csv_reader = csv.reader(csv_lines)

# Initialize a dictionary to store the result
result = {}
current_date = None

# Loop through CSV rows
for row in csv_reader:
    # Remove empty values from the row
    while ("" in row):
        row.remove("")

    # Check the length of the row to determine its content
    if len(row) == 1:
        current_date = row[0]
        result[current_date] = []
    elif len(row) == 6:
        if current_date is not None:
            if row[0] == "Type":
                pass
            else:
                data = {
                    "Type": row[0],
                    "Time": row[1],
                    "Number": row[2],
                    "Duration": row[3],
                    "Quantity": row[4],
                    "Cost": row[5]
                }
                result[current_date].append(data)

# Initialize a dictionary for the final result
final_result = {}

# Organize data into the desired dictionary format
for date, data_list in result.items():
    final_result[date] = {f"Entry {i + 1}": data for i, data in enumerate(data_list)}


print(json.dumps(final_result, indent=4))

How the Code Works to convert csv

1. We start by importing the csv and json libraries in Python, as we’ll be using them to handle CSV data and JSON conversion.
2. The provided CSV data is stored as a multi-line string in the csv_data variable.
3. We split the CSV data into lines using csv_lines = csv_data.strip().split('\n').
4. A CSV reader is initialized using csv.reader(csv_lines) to parse the CSV data row by row.
5. We iterate through the rows while removing any empty values.
6. Depending on the length of each row, we determine whether it represents a date header or data rows. Date headers are used as keys in the result dictionary, and data rows are converted into dictionaries and added as values associated with their respective dates.
7. After parsing the CSV data, we organize the result dictionary into the desired JSON format, where each date becomes a key, and the data is stored under entries labeled as “Entry 1,” “Entry 2,” and so on.
8. Finally, we use json.dumps(final_result, indent=4) to convert the final_result dictionary into a nicely formatted JSON string and print it.

Conclusion

Converting CSV data into JSON format can be a useful process for various data manipulation tasks. In this article, we’ve walked through a Python code example that demonstrates how to convert CSV data containing values for each date into a structured JSON format. This code can be adapted and expanded to handle larger CSV datasets and different data structures as needed.

where question is asked

stackoverflow