How to split a specific column data into a nested json data structure using python?-CodePudding

I began working with a csv data which has the following data:

I want to create a json data structure which looks like this:

{
    "name": "Place",
    "Details": [
      {
        "detail": "I",
        "info": [
          "Iran",
          "Iraq"
        ]
      },
      {
        "detail": "J",
        "info": "Japan"
      }
    ]
  }

Below is my code but I m unable to split the second column as required:

import pandas as pd
path="/content/file.csv/"
data=pd.read_csv(path)
df=pd.DataFrame(data)
out=df.to_json(orient="records")
print(out)

CodePudding user response：

Use custom function with splitting values in Details column:

def f(x):
    out = []
    splitted = x.split(',')
    for x in splitted:
        a, b = x.split('-')
        c = b.split('/')
        if len(c) == 1:
            d = {'detail': a, 'info':c[0]}
        else:
            d = {'detail': a, 'info':c}
        out.append(d)
    return out

                   
df['Details'] = df['Details'].apply(f)
print (df)
       Name                                            Details Verfied
0  Alphabet  [{'detail': 'A', 'info': 'Apple'}, {'detail': ...     Yes
1     Place  [{'detail': 'I', 'info': ['Iran', 'Iraq']}, {'...     Yes

out=df[['Name','Details']].to_json(orient="records")
print(out)

[{"Name":"Alphabet","Details":[{"detail":"A","info":"Apple"},
                               {"detail":"B","info":"Ball"}]},
 {"Name":"Place","Details":[{"detail":"I","info":["Iran","Iraq"]},
                            {"detail":"J","info":"Japan"}]}]