python pandas convert dataframe to array of desired dicts

Question

[EDITED]

I have a dataframe in below way

ID      , EmailID    , First Name, Last Name, Gender, DOB
1       , 1@sof.com  , One First , One Last , M     , 11-13-1920
2       , 2@sof.com  , Two First , Two Last , M     , 11-13-1920
3       , 3@sof.com  , Thr First , Thr Last , M     , 11-13-1920
4       , 4@sof.com  , Fou First , Fou Last , M     , 11-13-1920
5       , 5@sof.com  , Fiv First , Fiv Last , M     , 11-13-1920
6       , 6@sof.com  , Six First , Six Last , M     , 11-13-1920

I want the desired out like below

[
   {"_id" : "1-1@sof.com", "_souce" : {"ID": 1, "EmailID" : "1@sof.com", "data" : "{'ID':'1', 'EmailID': '1@sof.com', 'First Name' : 'One First', 'Last Name' : 'One First', 'Gender': 'M', 'DOB': '11-13-1920'}"}},
   {"_id" : "2-2@sof.com", "_souce" : {"ID": 2, "EmailID" : "2@sof.com", "data" : "{'ID':'2', 'EmailID': '2@sof.com', 'First Name' : 'Two First', 'Last Name' : 'Two First', 'Gender': 'M', 'DOB': '11-13-1920'}"}},
   {"_id" : "3-3@sof.com", "_souce" : {"ID": 3, "EmailID" : "3@sof.com", "data" : "{'ID':'3', 'EmailID': '3@sof.com', 'First Name' : 'The First', 'Last Name' : 'The First', 'Gender': 'M', 'DOB': '11-13-1920'}"}},
   {"_id" : "4-4@sof.com", "_souce" : {"ID": 4, "EmailID" : "4@sof.com", "data" : "{'ID':'4', 'EmailID': '4@sof.com', 'First Name' : 'Fou First', 'Last Name' : 'Fou First', 'Gender': 'M', 'DOB': '11-13-1920'}"}},
   {"_id" : "5-5@sof.com", "_souce" : {"ID": 5, "EmailID" : "5@sof.com", "data" : "{'ID':'5', 'EmailID': '5@sof.com', 'First Name' : 'Fiv First', 'Last Name' : 'Fiv First', 'Gender': 'M', 'DOB': '11-13-1920'}"}},
   {"_id" : "6-6@sof.com", "_souce" : {"ID": 6, "EmailID" : "6@sof.com", "data" : "{'ID':'6', 'EmailID': '6@sof.com', 'First Name' : 'Six First', 'Last Name' : 'Six First', 'Gender': 'M', 'DOB': '11-13-1920'}"}}
]

How can I do it in a effective way? Should I loop and make another array out of it or through pandas its possible

Converted dicts should have

1. _id with combination of ID and EmailID
2. _source should have below info;
   1. data with all the all the info converted to json string
   2. have ID, EmailID in the same dict

Answer 1

Convert all rows to jsons to new column, then add _id column, last set columns with expected order to dictioanry by DataFrame.to_dict:

df['data'] = df.apply(lambda x: x.to_json(), axis=1)
df['_souce'] = df[['ID','EmailID','data']].apply(lambda x: x.to_dict(), axis=1)
df['_id'] =  df['ID'].astype(str)+ '-' + df['EmailID'].astype(str)
d = df[['_id','_souce']].to_dict(orient='records')

---

print (d)

[{
    '_id': '1-1@sof.com',
    '_souce': {
        'ID': 1,
        'EmailID': '1@sof.com',
        'data': '{"ID":1,"EmailID":"1@sof.com","First Name":"One First","Last Name":"One Last","Gender":"M","DOB":"11-13-1920"}'
    }
}, {
    '_id': '2-2@sof.com',
    '_souce': {
        'ID': 2,
        'EmailID': '2@sof.com',
        'data': '{"ID":2,"EmailID":"2@sof.com","First Name":"Two First","Last Name":"Two Last","Gender":"M","DOB":"11-13-1920"}'
    }
}, {
    '_id': '3-3@sof.com',
    '_souce': {
        'ID': 3,
        'EmailID': '3@sof.com',
        'data': '{"ID":3,"EmailID":"3@sof.com","First Name":"Thr First","Last Name":"Thr Last","Gender":"M","DOB":"11-13-1920"}'
    }
}, {
    '_id': '4-4@sof.com',
    '_souce': {
        'ID': 4,
        'EmailID': '4@sof.com',
        'data': '{"ID":4,"EmailID":"4@sof.com","First Name":"Fou First","Last Name":"Fou Last","Gender":"M","DOB":"11-13-1920"}'
    }
}, {
    '_id': '5-5@sof.com',
    '_souce': {
        'ID': 5,
        'EmailID': '5@sof.com',
        'data': '{"ID":5,"EmailID":"5@sof.com","First Name":"Fiv First","Last Name":"Fiv Last","Gender":"M","DOB":"11-13-1920"}'
    }
}, {
    '_id': '6-6@sof.com',
    '_souce': {
        'ID': 6,
        'EmailID': '6@sof.com',
        'data': '{"ID":6,"EmailID":"6@sof.com","First Name":"Six First","Last Name":"Six Last","Gender":"M","DOB":"11-13-1920"}'
    }
}]