Clever Geek Handbook

Create JSON file from csv by grouping fields

I am trying to create a json file from a csv file. I want to also group certain fields in the csv file and group them together in the json file, the following code I have so far, but I don't understand how I can group them.

from csv import DictReader
import json
json_input_file="test.csv"
json_output_file="test.json"


# read csv for json conversion
def read_csv(file, json_file):
    csv_rows = []
    with open(json_input_file) as csvfile:
        _reader = csv.DictReader(csvfile)
        _title = _reader.fieldnames

        for _row in _reader:
            csv_rows.extend([{_title[i]:_row[_title[i]] for i in range(len(_title))}])
        write_json(csv_rows, json_file)
# write json file
def write_json(data, json_file):
    with open(json_file, "w") as F:
        F.write(json.dumps(data, sort_keys=False, indent=4, separators=(',', ': '),encoding="utf-8",ensure_ascii=False))
# exec the conversion
read_csv(json_input_file, json_output_file)

My csv file looks like this:

brand_x, x_type, x_color, brand_y, y_type,  y_color
x_code1, type1,  green,   y_code1, type200, orange
x_code1, type1,  red,     y_code1, type200, pink
x_code1, type1,  black,   y_code1, type200, yellow
x_code2, type20, blue,    y_code2, type201, blue
x_code2, type20, red,     y_code3, type202, black
x_code3, type1,  white,   y_code3, type202, black
x_code3, type1,  blue,    y_code3, type202, blue

I am trying to group a color that is part of a brand and type eg group all color that belongs to x_code1 brand_x and that is type 1 x_type, etc.

Below is the json output I'm looking for:

[
    {
        "brand_x": "x_code1",
        "brand_y": "y_code1",
        "x_type": "type1",
        "y_type":"type200",
        "x_type1_color": [
          {
            "x_color": "green"
          },
          {
            "x_color": "red"
          },
          {
            "x_color": "black"
          }
        ],
        "y_type200_color":[
            {
                "y_color":"orange"
            },
            {
                "y_color": "pink"
            },
            {
                "y_color": "yellow"
            }
        ]
      }
]
+3


source to share


2 answers


Pandas seems to be the right fit for this. Here is a rough solution

I haven't tried to match your result exactly because you seem to have some custom mappings, such as y_type200_color

which appears to be a combination of columns "y_type":"type200"

and y_color

. I also think this format is more accurate.

Edit made the solution a bit neater by extending the for loop



import pandas as pd
import tempfile
import csv
import os
import json

###############
#  CSV Setup  #
###############

tmp = tempfile.NamedTemporaryFile(delete=False)

raw_string =  """brand_x,x_type,x_color,brand_y,y_type,y_color
x_code1,type1,green,y_code1,type200,orange
x_code1,type1,red,y_code1,type200,pink
x_code1,type1,black,y_code1,type200,yellow
x_code2,type20,blue,y_code2,type201,blue
x_code2,type20,red,y_code3,type202,black
x_code3,type1,white,y_code3,type202,black
x_code3,type1,blue,y_code3,type202,blue"""

raw_data = [line.split(',') for line in raw_string.split()]
# Open the file for writing.
with open(tmp.name, 'w') as f:
    csv_writer = csv.writer(f)
    csv_writer.writerows(raw_data)
tmp.close()

##############
#  Solution  #
##############

# make a pandas data frame from csv
df = pd.read_csv(tmp.name)

# what columns will you use as index
index_columns = ["brand_x", "x_type"]
df = df.set_index(index_columns)

# select rows by index
df = df.loc[("x_code1", "type1")]

# reset index so that it will be included in our output
df = df.reset_index()

# messy line that matches columns to their values. The list(set(x) makes it so values are unique but also json serializable
output = dict()
for k, v in df.to_dict("list").items():
    # unique values only
    v = list(set(v))
    if len(v) <= 1:
        v = v[0]
    output[k] = v

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

##############
#  Clean up  #
##############
os.remove(tmp.name)

Output:

{
    "brand_x": "x_code1",
    "x_color": [
        "red",
        "green",
        "black"
    ],
    "brand_y": "y_code1",
    "x_type": "type1",
    "y_color": [
        "pink",
        "orange",
        "yellow"
    ],
    "y_type": "type200"
}
0


source


I implemented some Alter code, but made some basic changes:

import json
import io
import pandas as pd

csv = """brand_x,x_type,x_color,brand_y,y_type,y_color
x_code1,type1,green,y_code1,type200,orange
x_code1,type1,red,y_code1,type200,pink
x_code1,type1,black,y_code1,type200,yellow
x_code2,type20,blue,y_code2,type201,blue
x_code2,type20,red,y_code3,type202,black
x_code3,type1,white,y_code3,type202,black
x_code3,type1,blue,y_code3,type202,blue"""

df = pd.read_csv(io.StringIO(csv))

for item in list(df.groupby(by=[i for i in df.columns if not i.endswith("color")])):
    df_temp = item[1]
    # messy line that matches columns to their values. The list(set(x) makes it so values are unique but also json serializable
    a = {k : (list(set(v)) if len(set(v)) > 1 else list(set(v))[0]) for k, v in df_temp.to_dict("list").items()}
    print(json.dumps(a, indent=4))


prints:

{
    "y_type": "type200",
    "brand_y": "y_code1",
    "x_type": "type1",
    "y_color": [
        "pink",
        "orange",
        "yellow"
    ],
    "brand_x": "x_code1",
    "x_color": [
        "red",
        "green",
        "black"
    ]
}
{
    "y_type": "type201",
    "brand_y": "y_code2",
    "x_type": "type20",
    "y_color": "blue",
    "brand_x": "x_code2",
    "x_color": "blue"
}
{
    "y_type": "type202",
    "brand_y": "y_code3",
    "x_type": "type20",
    "y_color": "black",
    "brand_x": "x_code2",
    "x_color": "red"
}
{
    "y_type": "type202",
    "brand_y": "y_code3",
    "x_type": "type1",
    "y_color": [
        "black",
        "blue"
    ],
    "brand_x": "x_code3",
    "x_color": [
        "white",
        "blue"
    ]
}
0


source






More articles:


All Articles