How to convert this nested array JSON response column to DataFrame in pandas Python

Question

I have a pandas data frame which has a json response column

I have read the data using this code:

data = pd.read_csv(f"""bureau_response_1.csv""",sep=";")

Later i use eval function to evaluate using this code:

data['account_Segments']=data['account_Segments'].apply(lambda x:eval(x))

its throwing an error while using json_normalize

code1 : data = pd.json_normalize(data['account_Segments'])

Error from running above code1:

Data looks like this after evaluate:

I need this json data in flattened column values.

After reading csv using, data1 = pd.read_csv("bureau_response.csv",sep=",") note that it has two columns, APPLICATION__ID & account_Segments i want the APPLICATION_ID column as index after flattening account_Segments

so after data1.head(1).to_dict() i get, this output

I have removed double quotes and cleaned data so first two rows are given below. note that index column has APPLICATION_ID

when I call your function s = (data2.applymap(type) == list).all() this condition is coming false so the data is not flattened

{'LAI-100518437': "[{'cashLimit': '3,000', 'accountType': 'Credit Card', 'creditLimit': '30,000', 'amountOverdue': '1,331', 'currentBalance': '4,336', 'paymentHistory1': '093    063    033    003    003    000    003    003    003    003    003    000    000    003    000    003    003    003    ', 'paymentHistory2': '003    000    000    003    000    000    000    000    000    000    000    000    000    000    000    000    000    000    ', 'paymentFrequency': 'Monthly', 'dateofLastPayment': '07/07/2017', 'ownershipIndicator': 'Individual', 'dateOpened_Disbursed': '27/08/2013', 'paymentHistoryEndDate': '01/12/2014', 'paymentHistoryStartDate': '01/11/2017', 'dateReportedandCertified': '03/11/2017', 'reportingMemberShortName': 'NOT DISCLOSED', 'highCredit_SanctionedAmount': '34,051'}, {'emiAmount': '11,288', 'accountType': 'Personal Loan', 'amountOverdue': '31,728', 'currentBalance': '3,92,459', 'rateOfInterest': '12.00', 'paymentHistory1': '089    029    STD    STD    STD    STD    000    000    000    000    000    000    000    000    000    000    000    000    ', 'paymentHistory2': '000    000    000    ', 'repaymentTenure': '60', 'paymentFrequency': 'Monthly', 'dateofLastPayment': '12/04/2017', 'ownershipIndicator': 'Individual', 'dateOpened_Disbursed': '12/01/2016', 'paymentHistoryEndDate': '01/02/2016', 'paymentHistoryStartDate': '01/10/2017', 'dateReportedandCertified': '31/10/2017', 'reportingMemberShortName': 'NOT DISCLOSED', 'highCredit_SanctionedAmount': '5,00,000'}, {'dateClosed': '11/07/2017', 'accountType': 'Business Loan – General', 'accountNumber': 'LK0000010410', 'currentBalance': '0', 'paymentHistory1': '000    000    000    000    ', 'paymentFrequency': 'Monthly', 'ownershipIndicator': 'Individual', 'dateOpened_Disbursed': '10/04/2017', 'paymentHistoryEndDate': '01/04/2017', 'paymentHistoryStartDate': '01/07/2017', 'dateReportedandCertified': '31/07/2017', 'reportingMemberShortName': 'AADRILTD', 'highCredit_SanctionedAmount': '1,00,000'}, {'accountType': 'Auto Loan (Personal)', 'currentBalance': '10,65,245', 'paymentHistory1': '000    000    000    000    000    000    000    000    000    000    000    000    000    000    ', 'dateofLastPayment': '12/09/2017', 'ownershipIndicator': 'Guarantor', 'dateOpened_Disbursed': '25/08/2016', 'paymentHistoryEndDate': '01/08/2016', 'paymentHistoryStartDate': '01/09/2017', 'dateReportedandCertified': '30/09/2017', 'reportingMemberShortName': 'NOT DISCLOSED', 'highCredit_SanctionedAmount': '14,00,000'}, {'accountType': 'Auto Loan (Personal)', 'currentBalance': '3,74,330', 'paymentHistory1': '000    000    000    000    000    000    000    000    000    000    000    000    000    000    000    000    000    000    ', 'paymentHistory2': '000    ', 'dateofLastPayment': '12/09/2017', 'ownershipIndicator': 'Joint', 'dateOpened_Disbursed': '21/03/2016', 'paymentHistoryEndDate': '01/03/2016', 'paymentHistoryStartDate': '01/09/2017', 'dateReportedandCertified': '30/09/2017', 'reportingMemberShortName': 'NOT DISCLOSED', 'highCredit_SanctionedAmount': '7,00,000'}, {'accountType': 'Credit Card', 'creditLimit': '1,25,000', 'currentBalance': '71,670', 'paymentHistory1': '000    005    000    000    000    000    000    000    000    000    000    000    000    000    000    000    000    000    ', 'paymentHistory2': 'XXX    000    XXX    000    000    000    000    ', 'paymentFrequency': 'Monthly', 'dateofLastPayment': '02/10/2017', 'ownershipIndicator': 'Individual', 'actualPaymentAmount': '6,884', 'dateOpened_Disbursed': '30/10/2015', 'paymentHistoryEndDate': '01/10/2015', 'paymentHistoryStartDate': '01/10/2017', 'dateReportedandCertified': '31/10/2017', 'reportingMemberShortName': 'NOT DISCLOSED', 'highCredit_SanctionedAmount': '1,14,344'}, {'accountType': 'Credit Card', 'currentBalance': '11,036', 'paymentHistory1': '000    000    000    000    000    000    000    000    000    000    000    000    000    000    000    000    000    000    ', 'paymentHistory2': '000    000    000    000    000    000    000    000    000    000    000    000    000    000    000    000    000    000    ', 'dateofLastPayment': '02/10/2017', 'ownershipIndicator': 'Individual', 'dateOpened_Disbursed': '26/10/2014', 'paymentHistoryEndDate': '01/11/2014', 'paymentHistoryStartDate': '01/10/2017', 'dateReportedandCertified': '13/10/2017', 'reportingMemberShortName': 'NOT DISCLOSED', 'highCredit_SanctionedAmount': '26,102'}, {'dateClosed': '03/11/2016', 'accountType': 'Auto Loan (Personal)', 'currentBalance': '0', 'paymentHistory1': '000    000    000    000    000    000    000    000    000    000    000    000    000    000    000    000    000    000    ', 'paymentHistory2': '000    000    000    000    000    XXX    000    000    000    000    000    000    ', 'dateofLastPayment': '28/10/2016', 'ownershipIndicator': 'Guarantor', 'dateOpened_Disbursed': '25/06/2014', 'paymentHistoryEndDate': '01/06/2014', 'paymentHistoryStartDate': '01/11/2016', 'dateReportedandCertified': '30/11/2016', 'reportingMemberShortName': 'NOT DISCLOSED', 'highCredit_SanctionedAmount': '10,27,000'}]",
 'LAI-100826051': "[{'dateClosed': '02/01/2018', 'accountType': 'Business Loan – General', 'accountNumber': 'LK0000013293', 'currentBalance': '0', 'paymentHistory1': '000    STD    STD    STD    000    000    000    ', 'paymentFrequency': 'Monthly', 'ownershipIndicator': 'Individual', 'dateOpened_Disbursed': '01/07/2017', 'paymentHistoryEndDate': '01/07/2017', 'paymentHistoryStartDate': '01/01/2018', 'dateReportedandCertified': '31/01/2018', 'reportingMemberShortName': 'AADRILTD', 'highCredit_SanctionedAmount': '50,00,000'}, {'dateClosed': '04/10/2017', 'accountType': 'Business Loan – General', 'accountNumber': 'LK0000013294', 'currentBalance': '0', 'paymentHistory1': 'STD    000    000    000    ', 'paymentFrequency': 'Monthly', 'ownershipIndicator': 'Individual', 'dateOpened_Disbursed': '01/07/2017', 'paymentHistoryEndDate': '01/07/2017', 'paymentHistoryStartDate': '01/10/2017', 'dateReportedandCertified': '31/10/2017', 'reportingMemberShortName': 'AADRILTD', 'highCredit_SanctionedAmount': '50,00,000'}, {'dateClosed': '27/09/2017', 'accountType': 'Business Loan – General', 'accountNumber': 'LK0000009268', 'currentBalance': '0', 'paymentHistory1': '000    XXX    XXX    000    000    000    000    ', 'paymentFrequency': 'Monthly', 'dateofLastPayment': '27/09/2017', 'ownershipIndicator': 'Individual', 'dateOpened_Disbursed': '08/03/2017', 'paymentHistoryEndDate': '01/03/2017', 'paymentHistoryStartDate': '01/09/2017', 'dateReportedandCertified': '30/09/2017', 'reportingMemberShortName': 'AADRILTD', 'highCredit_SanctionedAmount': '30,00,000'}, {'accountType': 'Credit Card', 'currentBalance': '-170429', 'paymentHistory1': '000    000    000    000    000    000    000    000    000    000    000    000    000    000    000    000    000    000    ', 'paymentHistory2': '000    000    000    000    ', 'dateofLastPayment': '29/06/2017', 'ownershipIndicator': 'Individual', 'dateOpened_Disbursed': '17/05/2016', 'paymentHistoryEndDate': '01/05/2016', 'paymentHistoryStartDate': '01/02/2018', 'dateReportedandCertified': '28/02/2018', 'reportingMemberShortName': 'NOT DISCLOSED', 'highCredit_SanctionedAmount': '28,750'}, {'dateClosed': '27/04/2016', 'accountType': 'Credit Card', 'currentBalance': '-14', 'paymentHistory1': '000    000    000    000    000    000    000    000    000    000    000    000    000    000    000    000    000    000    ', 'paymentHistory2': '000    000    000    000    000    000    000    000    000    000    ', 'dateofLastPayment': '27/04/2016', 'ownershipIndicator': 'Authorised User', 'dateOpened_Disbursed': '23/01/2014', 'paymentHistoryEndDate': '01/01/2014', 'paymentHistoryStartDate': '01/04/2016', 'dateReportedandCertified': '30/04/2016', 'reportingMemberShortName': 'NOT DISCLOSED', 'highCredit_SanctionedAmount': '1,16,999'}, {'dateClosed': '27/04/2016', 'accountType': 'Credit Card', 'currentBalance': '-14', 'paymentHistory1': '000    000    000    000    000    000    000    000    000    000    000    000    000    000    000    000    000    000    ', 'paymentHistory2': '000    000    000    000    000    000    000    000    000    000    000    000    000    000    000    000    000    000    ', 'dateofLastPayment': '27/04/2016', 'ownershipIndicator': 'Individual', 'dateOpened_Disbursed': '07/09/2012', 'paymentHistoryEndDate': '01/05/2013', 'paymentHistoryStartDate': '01/04/2016', 'dateReportedandCertified': '30/04/2016', 'reportingMemberShortName': 'NOT DISCLOSED', 'highCredit_SanctionedAmount': '1,16,999'}]"}

I have used these codes to clean by data & eventually created a new DF using your codes & I am getting following format below as output:

dict1 = data1['account_Segments'].to_dict() dict_str = str(dict1).replace('"','') import ast new_dict = ast.literal_eval(dict_str)

df1 = pd.DataFrame.from_dict(new_dict, orient='index').reset_index()

df2 = flatten_nested_json_df(df1) df2 = df2.drop(['level_0'], axis=1) df2

I would like to have all the json flattened with application_id as row index

Do, not paste images. Paste the content of this image (i.e. the actual dict) in the question editor. To do this nicely, paste it between two ``````. See (https://stackoverflow.com/editing-help#code). This enables anyone to reproduces your code instead of having to invent own data. — Serge de Gosson de Varennes, Aug 05 '21 at 11:27
There is unfortunately thing that are wrong with your data to begin with. For one, the data that you shared is full of double quotes everywhere. Try to clean that up. Reach out again if I can assist. — Serge de Gosson de Varennes, Aug 05 '21 at 13:19
@Serge updated also i want to convert each row in column account_Segments to list() — Ak Suhail, Aug 05 '21 at 14:46
Proposed a new solution. However, you need to check the quality of your data. Good excercice for learning pandas. — Serge de Gosson de Varennes, Aug 05 '21 at 15:41

Serge de Gosson de Varennes · Answer 1 · 2021-08-05T15:40:36.967

You can try this function:

def flatten_nested_json_df(df):
    df = df.reset_index()
    s = (df.applymap(type) == list).all()
    list_columns = s[s].index.tolist()
    
    s = (df.applymap(type) == dict).all()
    dict_columns = s[s].index.tolist()

    
    while len(list_columns) > 0 or len(dict_columns) > 0:
        new_columns = []

        for col in dict_columns:
            horiz_exploded = pd.json_normalize(df[col]).add_prefix(f'{col}.')
            horiz_exploded.index = df.index
            df = pd.concat([df, horiz_exploded], axis=1).drop(columns=[col])
            new_columns.extend(horiz_exploded.columns) # inplace

        for col in list_columns:
            print(f"exploding: {col}")
            df = df.drop(columns=[col]).join(df[col].explode().to_frame())
            new_columns.append(col)

        s = (df[new_columns].applymap(type) == list).all()
        list_columns = s[s].index.tolist()

        s = (df[new_columns].applymap(type) == dict).all()
        dict_columns = s[s].index.tolist()
    return df

So, in your case:

flatten_nested_json_df(data)

BUT: You need to do something about How you read your data. It should look like this:

data = {'LAI-100518437': [{'cashLimit': '3,000', 'accountType': 'Credit Card', 'creditLimit': '30,000', 'amountOverdue': '1,331', 'currentBalance': '4,336', 'paymentHistory1': '093    063    033    003    003    000    003    003    003    003    003    000    000    003    000    003    003    003    ', 'paymentHistory2': '003    000    000    003    000    000    000    000    000    000    000    000    000    000    000    000    000    000    ', 'paymentFrequency': 'Monthly', 'dateofLastPayment': '07/07/2017', 'ownershipIndicator': 'Individual', 'dateOpened_Disbursed': '27/08/2013', 'paymentHistoryEndDate': '01/12/2014', 'paymentHistoryStartDate': '01/11/2017', 'dateReportedandCertified': '03/11/2017', 'reportingMemberShortName': 'NOT DISCLOSED', 'highCredit_SanctionedAmount': '34,051'}, {'emiAmount': '11,288', 'accountType': 'Personal Loan', 'amountOverdue': '31,728', 'currentBalance': '3,92,459', 'rateOfInterest': '12.00', 'paymentHistory1': '089    029    STD    STD    STD    STD    000    000    000    000    000    000    000    000    000    000    000    000    ', 'paymentHistory2': '000    000    000    ', 'repaymentTenure': '60', 'paymentFrequency': 'Monthly', 'dateofLastPayment': '12/04/2017', 'ownershipIndicator': 'Individual', 'dateOpened_Disbursed': '12/01/2016', 'paymentHistoryEndDate': '01/02/2016', 'paymentHistoryStartDate': '01/10/2017', 'dateReportedandCertified': '31/10/2017', 'reportingMemberShortName': 'NOT DISCLOSED', 'highCredit_SanctionedAmount': '5,00,000'}, {'dateClosed': '11/07/2017', 'accountType': 'Business Loan – General', 'accountNumber': 'LK0000010410', 'currentBalance': '0', 'paymentHistory1': '000    000    000    000    ', 'paymentFrequency': 'Monthly', 'ownershipIndicator': 'Individual', 'dateOpened_Disbursed': '10/04/2017', 'paymentHistoryEndDate': '01/04/2017', 'paymentHistoryStartDate': '01/07/2017', 'dateReportedandCertified': '31/07/2017', 'reportingMemberShortName': 'AADRILTD', 'highCredit_SanctionedAmount': '1,00,000'}, {'accountType': 'Auto Loan (Personal)', 'currentBalance': '10,65,245', 'paymentHistory1': '000    000    000    000    000    000    000    000    000    000    000    000    000    000    ', 'dateofLastPayment': '12/09/2017', 'ownershipIndicator': 'Guarantor', 'dateOpened_Disbursed': '25/08/2016', 'paymentHistoryEndDate': '01/08/2016', 'paymentHistoryStartDate': '01/09/2017', 'dateReportedandCertified': '30/09/2017', 'reportingMemberShortName': 'NOT DISCLOSED', 'highCredit_SanctionedAmount': '14,00,000'}, {'accountType': 'Auto Loan (Personal)', 'currentBalance': '3,74,330', 'paymentHistory1': '000    000    000    000    000    000    000    000    000    000    000    000    000    000    000    000    000    000    ', 'paymentHistory2': '000    ', 'dateofLastPayment': '12/09/2017', 'ownershipIndicator': 'Joint', 'dateOpened_Disbursed': '21/03/2016', 'paymentHistoryEndDate': '01/03/2016', 'paymentHistoryStartDate': '01/09/2017', 'dateReportedandCertified': '30/09/2017', 'reportingMemberShortName': 'NOT DISCLOSED', 'highCredit_SanctionedAmount': '7,00,000'}, {'accountType': 'Credit Card', 'creditLimit': '1,25,000', 'currentBalance': '71,670', 'paymentHistory1': '000    005    000    000    000    000    000    000    000    000    000    000    000    000    000    000    000    000    ', 'paymentHistory2': 'XXX    000    XXX    000    000    000    000    ', 'paymentFrequency': 'Monthly', 'dateofLastPayment': '02/10/2017', 'ownershipIndicator': 'Individual', 'actualPaymentAmount': '6,884', 'dateOpened_Disbursed': '30/10/2015', 'paymentHistoryEndDate': '01/10/2015', 'paymentHistoryStartDate': '01/10/2017', 'dateReportedandCertified': '31/10/2017', 'reportingMemberShortName': 'NOT DISCLOSED', 'highCredit_SanctionedAmount': '1,14,344'}, {'accountType': 'Credit Card', 'currentBalance': '11,036', 'paymentHistory1': '000    000    000    000    000    000    000    000    000    000    000    000    000    000    000    000    000    000    ', 'paymentHistory2': '000    000    000    000    000    000    000    000    000    000    000    000    000    000    000    000    000    000    ', 'dateofLastPayment': '02/10/2017', 'ownershipIndicator': 'Individual', 'dateOpened_Disbursed': '26/10/2014', 'paymentHistoryEndDate': '01/11/2014', 'paymentHistoryStartDate': '01/10/2017', 'dateReportedandCertified': '13/10/2017', 'reportingMemberShortName': 'NOT DISCLOSED', 'highCredit_SanctionedAmount': '26,102'}, {'dateClosed': '03/11/2016', 'accountType': 'Auto Loan (Personal)', 'currentBalance': '0', 'paymentHistory1': '000    000    000    000    000    000    000    000    000    000    000    000    000    000    000    000    000    000    ', 'paymentHistory2': '000    000    000    000    000    XXX    000    000    000    000    000    000    ', 'dateofLastPayment': '28/10/2016', 'ownershipIndicator': 'Guarantor', 'dateOpened_Disbursed': '25/06/2014', 'paymentHistoryEndDate': '01/06/2014', 'paymentHistoryStartDate': '01/11/2016', 'dateReportedandCertified': '30/11/2016', 'reportingMemberShortName': 'NOT DISCLOSED', 'highCredit_SanctionedAmount': '10,27,000'}],
 'LAI-100826051': [{'dateClosed': '02/01/2018', 'accountType': 'Business Loan – General', 'accountNumber': 'LK0000013293', 'currentBalance': '0', 'paymentHistory1': '000    STD    STD    STD    000    000    000    ', 'paymentFrequency': 'Monthly', 'ownershipIndicator': 'Individual', 'dateOpened_Disbursed': '01/07/2017', 'paymentHistoryEndDate': '01/07/2017', 'paymentHistoryStartDate': '01/01/2018', 'dateReportedandCertified': '31/01/2018', 'reportingMemberShortName': 'AADRILTD', 'highCredit_SanctionedAmount': '50,00,000'}, {'dateClosed': '04/10/2017', 'accountType': 'Business Loan – General', 'accountNumber': 'LK0000013294', 'currentBalance': '0', 'paymentHistory1': 'STD    000    000    000    ', 'paymentFrequency': 'Monthly', 'ownershipIndicator': 'Individual', 'dateOpened_Disbursed': '01/07/2017', 'paymentHistoryEndDate': '01/07/2017', 'paymentHistoryStartDate': '01/10/2017', 'dateReportedandCertified': '31/10/2017', 'reportingMemberShortName': 'AADRILTD', 'highCredit_SanctionedAmount': '50,00,000'}, {'dateClosed': '27/09/2017', 'accountType': 'Business Loan – General', 'accountNumber': 'LK0000009268', 'currentBalance': '0', 'paymentHistory1': '000    XXX    XXX    000    000    000    000    ', 'paymentFrequency': 'Monthly', 'dateofLastPayment': '27/09/2017', 'ownershipIndicator': 'Individual', 'dateOpened_Disbursed': '08/03/2017', 'paymentHistoryEndDate': '01/03/2017', 'paymentHistoryStartDate': '01/09/2017', 'dateReportedandCertified': '30/09/2017', 'reportingMemberShortName': 'AADRILTD', 'highCredit_SanctionedAmount': '30,00,000'}, {'accountType': 'Credit Card', 'currentBalance': '-170429', 'paymentHistory1': '000    000    000    000    000    000    000    000    000    000    000    000    000    000    000    000    000    000    ', 'paymentHistory2': '000    000    000    000    ', 'dateofLastPayment': '29/06/2017', 'ownershipIndicator': 'Individual', 'dateOpened_Disbursed': '17/05/2016', 'paymentHistoryEndDate': '01/05/2016', 'paymentHistoryStartDate': '01/02/2018', 'dateReportedandCertified': '28/02/2018', 'reportingMemberShortName': 'NOT DISCLOSED', 'highCredit_SanctionedAmount': '28,750'}, {'dateClosed': '27/04/2016', 'accountType': 'Credit Card', 'currentBalance': '-14', 'paymentHistory1': '000    000    000    000    000    000    000    000    000    000    000    000    000    000    000    000    000    000    ', 'paymentHistory2': '000    000    000    000    000    000    000    000    000    000    ', 'dateofLastPayment': '27/04/2016', 'ownershipIndicator': 'Authorised User', 'dateOpened_Disbursed': '23/01/2014', 'paymentHistoryEndDate': '01/01/2014', 'paymentHistoryStartDate': '01/04/2016', 'dateReportedandCertified': '30/04/2016', 'reportingMemberShortName': 'NOT DISCLOSED', 'highCredit_SanctionedAmount': '1,16,999'}, {'dateClosed': '27/04/2016', 'accountType': 'Credit Card', 'currentBalance': '-14', 'paymentHistory1': '000    000    000    000    000    000    000    000    000    000    000    000    000    000    000    000    000    000    ', 'paymentHistory2': '000    000    000    000    000    000    000    000    000    000    000    000    000    000    000    000    000    000    ', 'dateofLastPayment': '27/04/2016', 'ownershipIndicator': 'Individual', 'dateOpened_Disbursed': '07/09/2012', 'paymentHistoryEndDate': '01/05/2013', 'paymentHistoryStartDate': '01/04/2016', 'dateReportedandCertified': '30/04/2016', 'reportingMemberShortName': 'NOT DISCLOSED', 'highCredit_SanctionedAmount': '1,16,999'}]}

That is, the "" should not be there around the lists of json.

So, doint the following

df1 = pd.DataFrame.from_dict(data, orient='index').reset_index()
df2 = flatten_nested_json_df(df1)
df2 = df2.drop(['level_0'], axis=1)
df2

returns:

  index                                                  6  \
0  LAI-100518437  {'accountType': 'Credit Card', 'currentBalance...   
1  LAI-100826051                                               None   

                                                   7 0.cashLimit  \
0  {'dateClosed': '03/11/2016', 'accountType': 'A...       3,000   
1                                               None         NaN   

             0.accountType 0.creditLimit 0.amountOverdue 0.currentBalance  \
0              Credit Card        30,000           1,331            4,336   
1  Business Loan – General           NaN             NaN                0   

                                   0.paymentHistory1  \
0  093    063    033    003    003    000    003 ...   
1  000    STD    STD    STD    000    000    000       

                                   0.paymentHistory2  ... 5.dateofLastPayment  \
0  003    000    000    003    000    000    000 ...  ...          02/10/2017   
1                                                NaN  ...          27/04/2016   

  5.ownershipIndicator 5.actualPaymentAmount 5.dateOpened_Disbursed  \
0           Individual                 6,884             30/10/2015   
1           Individual                   NaN             07/09/2012   

  5.paymentHistoryEndDate 5.paymentHistoryStartDate  \
0              01/10/2015                01/10/2017   
1              01/05/2013                01/04/2016   

  5.dateReportedandCertified 5.reportingMemberShortName  \
0                 31/10/2017              NOT DISCLOSED   
1                 30/04/2016              NOT DISCLOSED   

  5.highCredit_SanctionedAmount 5.dateClosed  
0                      1,14,344          NaN  
1                      1,16,999   27/04/2016  

[2 rows x 95 columns]

Up to you to rename the columns (you can for instance drop prefixes like 0. and so on).

Note that my solution does not flatten what isn't common to both lists. I suppose you would need to handle them separately. However, check the quality of the data you read to begin with.

what if I load this using df using comma separated then will this function work ? — Ak Suhail, Aug 05 '21 at 07:27
@AkSuhail What do you mean? This function is independent of whether you use ```pd.read_csv(file, sep=";")``` or ```pd.read_csv(file, sep=",")```. Use it after you've done either the one or the other. — Serge de Gosson de Varennes, Aug 05 '21 at 07:31
not exactly. suppose I read a csv using pd.read_csv(file,sep= ",") and the file has two columns first column is unique_id & 2nd is json response, now first i will evaluate using my code : data['account_Segments']=data['account_Segments'].apply(lambda x:eval(str(x))) next step is to call your function using flatten_nested_json_df(data) in this case my data is not flattened. it doesn't explode as well. output is same as the column data['account Segments'] Your function works only when I pd.read_csv(file,sep=";") assuming df has only 1 column i.e df['accountSegments'] — Ak Suhail, Aug 05 '21 at 08:44
its because of the first condition in your function i.e s = (data2.applymap(type) == list).all() is coming False — Ak Suhail, Aug 05 '21 at 09:20
@AkSuhail Could you possibly do the following thing: After ```data = pd.read_csv(f"""bureau_response_1.csv""",sep=";")``` or ```data = pd.read_csv(f"""bureau_response_1.csv""",sep=",")```, do ```data.to_dict()``` and paste what you get into your question. I'll find you a way to solve this. — Serge de Gosson de Varennes, Aug 05 '21 at 09:24
its done i have updated the question, your function works for single column df & went i use sep=";" but not when i have two columns i want the APPLICATION_ID to be index on flattened data and i am reading CSV with sep = "," — Ak Suhail, Aug 05 '21 at 10:04

How to convert this nested array JSON response column to DataFrame in pandas Python

1 Answers1