How to convert a dataframe to nested json

Question

I have this DataFrame:

df = pd.DataFrame({'Survey': "001_220816080015", 'BCD': "001_220816080015.bcd", 'Sections': "4700A1/305, 4700A1/312"})

All the dataframe fields are ASCII strings and is the output from a SQL query (pd.read_sql_query) so the line to create the dataframe above may not be quite right.

And I wish the final JSON output to be in the form

[{
  "Survey": "001_220816080015",
  "BCD": "001_220816080015.bcd",
  "Sections": [
    "4700A1/305", 
    "4700A1/312"
}]

I realize that may not be 'normal' JSON but that is the format expected by a program over which I have no control.

The nearest I have achieved so far is

[{
      "Survey": "001_220816080015",
      "BCD": "001_220816080015.bcd",
      "Sections": "4700A1/305, 4700A1/312"
    }]

Problem might be the structure of the dataframe but how to reformat it to produce the requirement is not clear to me.

The JSON line is:

df.to_json(orient='records', indent=2)

Answer 1

Isn't the only thing you need to do to parse the Sections into a list?

import pandas as pd

df= pd.DataFrame({'Survey': "001_220816080015", 'BCD': "001_220816080015.bcd", 'Sections': "4700A1/305, 4700A1/312"}, index=[0])

df['Sections'] = df['Sections'].str.split(', ')
print(df.to_json(orient='records', indent=2))

[
  {
    "Survey":"001_220816080015",
    "BCD":"001_220816080015.bcd",
    "Sections":[
      "4700A1\/305",
      "4700A1\/312"
    ]
  }
]

Answer 2

The DataFrame won't help you here, since it's just giving back the input parameter you gave it.

You should just split the specific column you need into an array:

input_data = {'Survey': "001_220816080015", 'BCD': "001_220816080015.bcd", 'Sections': "4700A1/305, 4700A1/312"}

input_data['Sections'] = input_data['Sections'].split(', ')

nested_json = [input_data]