How to convert from a non traditional CSV-style content (file with JSON data but its extension is .CSV) to a traditional CSV-style with Python?

Question

CSV (even though once opened it does not contain a traditional CSV format I think it contains JSON data)[![1], when I try to open it as a TXT file I got this as shown in the first screenshot. When I open it in Excel sheet I got this as shown in second screenshot

As you may see in a better view (TXT file) I want to convert this whole file to something like this (third screenshot)

I want to do it with a python script that can do this as fast as possiblefrom the old folder that contain 3 million of files to a new folder with 3 million of a new converted CSV files as the format I showed in the third screenshot containing the date and the values of the parameter. If anyone wants one CSV file to try on it, I am still new to this StachExchange community and I do not know how to upload it or even if it is possible I can send it via an e-mail.

Answer 1

There's no general algorithm for converting JSON to CSV as JSON is in general not tabular. Thus to convert JSON to CSV, there's an assumption that the JSON contains some sort of table data. Thus to process JSON into CSV one needs to:

1. Determine the structure of the data in the JSON. If this varies from file to file, one would need to figure out how to determine this on a file by file basis.
2. Load the JSON object and extract the relevant data based on the structure determined in step 1.) and collect it in a tabular format.
3. Write the extracted data as a table to CSV.

For the example JSON data (an example I parsed from the image is included below as minimal input string), it looks like you're only interested in the field called "properties". You can use pandas.read_json or Python's built in json parser. I'm not sure which would be most efficient, but I would expect pandas as it delegates a lot to the non-Python backend while using Python's json relies on looping over the data in Python.

First, here's a minimal version of the data structure

data_string = '{"type": "Feature", "geometry": {"type":"point", "coordinates": [1]}, "properties": {"123":5, "456":7}}'

In general this depends on how "flat" vs "nested" the data is. If deeply nested, the second approach probably would be more flexible, but will have to be adjusted for the specific data structure.

from io import StringIO
import pandas as pd
# filepath = 'json.csv' 
df = pd.read_json(StringIO(data_string)) # For minimal example
 # df = pd.read_json(filepath)
# Add below any additional fields you want to ignore
drop_rows = df.index.str.contains('type') | df.index.str.contains('geometry') | df.index.str.contains('coordinates') 
df = df[~drop_rows]
df = df[['properties']]
df.to_csv('converted.csv') # See the documentation to specify the exact output

https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.DataFrame.to_csv.html

The following will also work, but it may not be the fastest. I

from io import StringIO # Unnecessary for reading from files directly.
import json
import numpy as np
# Self contained example data
# data_string = '{"type": "Feature", "geometry": {"type":"point", "coordinates": [1]}, "properties": {"123":5, "456":7}}'
# data = json.load(StringIO(data_string)))

# To read a file first open it
filepath = 'json.csv'
with open(filepath) as f:
    data = json.load(f)

# properties = data["properties"] # For the self contained example
properties = data["properties"]['parameter']['ALLSKY_SFC_SW_DWN'] # for the example CSV
data_array = np.empty((len(properties),2))
for idx, (key, value) in enumerate(properties.items()):
    data_array[idx] = [int(key), float(value)]

output_csv = 'converted.csv'
np.savetxt(output_csv, data_array, delimiter=',')

https://docs.python.org/3/library/json.html