parse json field from csv file

[lebossejames]

Hello,

I want to get from csv file a column contain string json, so i use theses command:

fichier_complet=pd.read_csv("outputfile.csv", sep = ',',dtype='unicode',usecols = ['body'],keep_default_na=False)

print(fichier_complet.values[0])
json_string = json.dumps(fichier_complet.values[0])

The print display string with [' and '] at the beginning and end of value of cell csv file.

I want to get a json object which i can get a value from a different key.

How can i do that please ?
Thank you.

[buran]

Please, provide sample of the input file
Also, don't post images of code/data/errors, etc. Post as text with proper BBCode tags

[lebossejames]

Please, provide sample of the input file
Also, don't post images of code/data/errors, etc. Post as text with proper BBCode tags

Yes this input file

I want get "idAffaire" value then "code" value from prestation object

[Pedroski55]

I don't know how to use pandas very well.

Hard to know what starts where and what ends where in the json.

I thinks this gets what you want.

Look carefully at the output of

for item in json_data.items():
    print(json.dumps(item, indent = 4, sort_keys=True))

Sub keys are further right than leading keys. Look for lists as well!

import json
import csv

myfile = '/home/pedro/myPython/json/csv/outputfile.csv'
with open(myfile) as csv_file:
    csv_reader = csv.reader(csv_file, delimiter=',')
    data = []
    for row in csv_reader:
        data.append(row)
for d in data:
    print(d, len(d)

# get the last element of data[1] this is the json
json_data = json.loads(data[1][16])

for key in json_data.keys():
    print(json.dumps(key, indent = 4, sort_keys=True))
for item in json_data.items():
    print(json.dumps(item, indent = 4, sort_keys=True))

print(json_data["hits"]['hits'][0]['_source']['idAffaire'])
print(json_data["hits"]['hits'][0]['_source']['demande']['prestation']['code'])

[snippsat]

This is a JSON response from an API request.
The request is named "RFE" in the collection "Web_test" and it's a GET request to the URL ......
So should not be saved as .csv but .json then keep the correct data structure.
If you do not have control how to get data from the API request and just have the .csv file then have to parse as shown by Pedroski55.
Or like this this.

import csv
import json

csv_file_path = 'data.csv'
json_output = []
with open(csv_file_path, mode='r', encoding='utf-8') as csv_file:
    csv_reader = csv.DictReader(csv_file)
    for row in csv_reader:
        if row['body']:
            row['body'] = json.loads(row['body'])
        json_output.append(row)
json_data = json.dumps(json_output, indent=2)
print(json_data)

with open('output.json', 'w', encoding='utf-8') as json_file:
    json_file.write(json_data)

Output:
[
  {
    "iteration": "1",
    "collectionName": "Web_test",
    "requestName": "RFE",
    "method": "GET",
    "url": "https://indexa.com/_search",
    "status": "OK",
    "code": "200",
    "responseTime": "509",
    "responseSize": "1270",
    "executed": "",
    "failed": "",
    "skipped": "",
    "totalAssertions": "0",
    "executedCount": "0",
    "failedCount": "0",
    "skippedCount": "0",
    "body": {
      "took": 152,
      "timed_out": false,
      "_shards": {
        "total": 3,
        "successful": 3,
        "skipped": 0,
        "failed": 0
      },
      "hits": {
        "total": {
          "value": 1,
          "relation": "eq"
        },
        "max_score": 15.476334,
        "hits": [
          {
            "_index": "dossiers-bui1_v7",
            "_type": "_doc",
            "_id": "A07",
            "_score": 15.476334,
            "_source": {
              "idAffaire": "A07",
              "idContrat": "F003",
              "donneesPoint": {
                "codePostal": "27700",
                "commune": {
                  "libelle": "HARQUENCY"
                },
                "departement": "27"
              },
              "statut": "COURS",
              "demande": {
                "prestation": {
                  "code": "F8",
                  "valeur": "Rac"
                },
                "dateCreation": "2023-05-14T22:00:00.000+00:00",
                "client": {
                  "categorieClient": "RES",
                  "personnePhysique": {
                    "nom": "MOUSS"
                  },
                  "personneMorale": {}
                },
                "dateModification": "2023-09-25T22:00:00.000+00:00",
                "dateEffet": "2023-05-21T22:00:00.000+00:00",
                "motifCloture": {},
                "initiateur": {
                  "login": "[email protected]",
                  "acteurAppartenance": {
                    "codeAcm": "ACM_7",
                    "libelle": "B"
                  }
                },
                "affaireEtat": {
                  "libelle": "Demande re\u00e7ue",
                  "code": "DMRI"
                },
                "affaireEtatExterne": {
                  "libelle": "Demande transmise",
                  "code": "DMTR"
                },
                "affaireAvecRelance": "false",
                "interventionEnCours": false
              },
              "segment": "C",
              "acteurMarche": {
                "codeAcm": "ACM_",
                "libelle": "BCM "
              },
              "frnSortant": {},
              "applicationSource": "AD",
              "recevabilite": {
                "prestations": {
                  "prestation": {
                    "option": {}
                  }
                }
              }
            }
          }
        ]
      }
    }
  }
]