convert pandas dataframe to json with specific format

user_9430160 0
Viewed 134

I am trying to convert the dataframe mentioned below to desired json

enter image description here

column_id,column_name,mandatory,column_data_type,column_data_length,_id,data_format,file_type,active_ind
1,PAT_ID,FALSE,VARCHAR,2500,5f2193c39448c44f0c1b65e0,TEXT,FACT,TRUE
2,PAT_NAME,FALSE,VARCHAR,2500,5f2193c39448c44f0c1b65e0,TEXT,FACT,TRUE
3,PAT_AGE,FALSE,VARCHAR,2500,5f2193c39448c44f0c1b65e0,TEXT,FACT,TRUE

Like the json mentioned below

{
    "_id": 5f2193c39448c44f0c1b65e0,
    "data_format": "TEXT",
    "file_type": "FACT",
    "columns": [
        {
            "column_id": 1,
            "column_name": "PAT_ID",
            "mandatory": "false",
            "column_data_type": "VARCHAR",
            "column_data_length": 2500
            
        },

        {
            "column_id": 2,
            "column_name": "PAT_NAME",
            "mandatory": "false",
            "column_data_type": "VARCHAR",
            "column_data_length": 2500
          
        }
    ],
    "active_ind": "true",
}

I tried to group by based on column name and column id with so many methods

  1. this will group column but not all the values cac= df.groupby('column_id').apply(lambda x: x.to_json(orient='records'))

  2. cac = df.to_json(orient='records')

I am not able to separate the id and columns.

Please help me on this

pandas python-3.x json
1 Answers

here is what I would do:

# Load data
df = pd.read_csv('data.csv')

# Create list of dict for columns column
col_set = ['column_id', 
           'column_name', 
           'mandatory', 
           'column_data_type', 
           'column_data_length']
df['columns'] = df[col_set].apply(lambda x: x.to_dict(), axis=1)
reorder = ['column_id', 
           'column_name', 
           'mandatory', 
           'column_data_type', 
           'column_data_length', 
           'columns', 
           'active_ind', 
           '_id', 
           'data_format', 
           'file_type']
df = df[reorder]

# Group by similar rows and join sub dicts
col_set_2 = ['_id', 'data_format', 'file_type', 'columns', 'active_ind']
col_set_3 = ['_id', 'data_format', 'file_type', 'active_ind']
df2 = df[col_set_2].groupby(col_set_3)['columns'].apply(lambda x: list(x)).reset_index()
df2 = df2[col_set_2]

# Dataframe to json
parsed = json.loads(df2.to_json(orient='records', indent=4))
result = json.dumps(parsed[0], indent=4)

print(result)

{
    "_id": "5f2193c39448c44f0c1b65e0",
    "data_format": "TEXT",
    "file_type": "FACT",
    "columns": [
        {
            "column_id": 1,
            "column_name": "PAT_ID",
            "mandatory": false,
            "column_data_type": "VARCHAR",
            "column_data_length": 2500
        },
        {
            "column_id": 2,
            "column_name": "PAT_NAME",
            "mandatory": false,
            "column_data_type": "VARCHAR",
            "column_data_length": 2500
        },
        {
            "column_id": 3,
            "column_name": "PAT_AGE",
            "mandatory": false,
            "column_data_type": "VARCHAR",
            "column_data_length": 2500
        }
    ],
    "active_ind": true
}
user_119050800
Related