Split values in CSV that look like JSON-CodePudding

So I have a CSV file with a column called content. However, the contents in column look like it is based on JSON, and, therefore, house more columns. I would like to split these contents into multiple columns or extract the final part of it after "value". See picture below to see an example of the file. Any ideas how to get this? I would prefer using Python. I don't have any experience with JSON.

CodePudding user response：

Using pandas you could do in a simpler way.
EDIT updated to handle the single quotes:

import pandas as pd
import json

data = pd.read_csv('test.csv', delimiter="\n")["content"]
res = [json.loads(row.replace("'", '"')) for row in data]
result = pd.DataFrame(res)

result.head()

my csv:

result:

CodePudding user response：

This script will create a new csv file with the 'value' added to the csv as an additional column

(make sure that the input_csv and output_csv are different filenames)

import csv
import json

input_csv = "data.csv"
output_csv = "data_updated.csv"

values = []
with open(input_csv) as f_in:
    dr = csv.DictReader(f_in)
    for row in dr:
        value = json.loads(row["content"].replace("'", '"'))["value"]
        values.append(value)


with open(input_csv) as f_in:
    with open(output_csv, "w ") as f_out:
        w = csv.writer(f_out, lineterminator="\n")
        r = csv.reader(f_in)

        all = []
        row = next(r)
        row.append("value")
        all.append(row)

        i = 0
        for row in r:
            row.append(values[i])
            all.append(row)
            i  = 1

        w.writerows(all)