How to add a column value to another column's dictionary using python , pandas

Question

I have a column "data" which has json object as values. I would like to add a key-value pair inside nested json

source = {'my_dict':[{'_id': 'SE-DATA-BB3A'},{'_id': 'SE-DATA-BB3E'},{'_id': 'SE-DATA-BB3F'}],  'data': [ {'bb3a_bmls':[{'name': 'WAG 01', 'id': '105F', 'state': 'available', 'nodes': 3,'volumes-': [{'state': 'available', 'id': '330172', 'name': 'q_-4144d4e'}, {'state': 'available', 'id': '275192', 'name': 'p_3089d821ae', }]}]}
, {'bb3b_bmls':[{'name': 'FEC 01', 'id': '382E', 'state': 'available', 'nodes': 4,'volumes': [{'state': 'unavailable', 'id': '830172', 'name': 'w_-4144d4e'}, {'state': 'unavailable', 'id': '223192', 'name': 'g_3089d821ae', }]}]}
, {'bb3c_bmls':[{'name': 'ASD 01', 'id': '303F', 'state': 'available', 'nodes': 6,'volumes': [{'state': 'unavailable', 'id': '930172', 'name': 'e_-4144d4e'}, {'state': 'unavailable', 'id': '245192', 'name': 'h_3089d821ae', }]}]}
] }

input_df = pd.DataFrame(source)

input_df looks like below:

Now I need to add the "my_dict" column values as a 1st element inside the nested json values of "data" column

My Target dataframe should look like below ( I have highlighted the changes in bold)

I tired using dict.update() but it doesn't seem to help. I'm stuck here and not getting any idea how to take this forward. Appreciate your help.

Mark Wang · Accepted Answer · 2019-09-01 19:40:08Z

1

I don't see any benefit putting it as a dataframe, if you keep the original dictionary, then the following loop will do,

my_dict=[{'_id': 'SE-DATA-BB3A'},{'_id': 'SE-DATA-BB3E'},{'_id': 'SE-DATA-BB3F'}]


data = [ {'bb3a_bmls':[{'name': 'WAG 01', 'id': '105F', 'state': 'available', 'nodes': 3,'volumes-': [{'state': 'available', 'id': '330172', 'name': 'q_-4144d4e'}, {'state': 'available', 'id': '275192', 'name': 'p_3089d821ae', }]}]}
, {'bb3b_bmls':[{'name': 'FEC 01', 'id': '382E', 'state': 'available', 'nodes': 4,'volumes': [{'state': 'unavailable', 'id': '830172', 'name': 'w_-4144d4e'}, {'state': 'unavailable', 'id': '223192', 'name': 'g_3089d821ae', }]}]}
, {'bb3c_bmls':[{'name': 'ASD 01', 'id': '303F', 'state': 'available', 'nodes': 6,'volumes': [{'state': 'unavailable', 'id': '930172', 'name': 'e_-4144d4e'}, {'state': 'unavailable', 'id': '245192', 'name': 'h_3089d821ae', }]}]}
] 


for idx, val in enumerate(data):
    val[list(val.keys())[0]][0].update(my_dict[idx])

answered Sep 1, 2019 at 19:40

Mark Wang

2,7579 silver badges18 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Tad Over a year ago

Thanks Mark for the reply. It works for this sample data. But, If I'm applying it on large data I'm getting an error - "IndexError: list index out of range". Also, one of the data values is {'bb3b_pasf': []} . Would it be because of that?

Mark Wang Over a year ago

@Tad That's probably the reason, add a condition if check if it is []

Tad Over a year ago

could you please help me with the condition?

Djaballah Mohammed DJEDID · Accepted Answer · 2019-09-01 21:35:36Z

1

def get_val(row):
  my_dict_val = row.loc['my_dict']
  dict_key = list(row['data'].keys())[0]
  if not list(row['data'].values())[0]:
    return row['data']
  data_dict = list(row['data'].values())[0][0]
  data_dict.update(my_dict_val)
  res = dict()
  res[dict_key] = []
  res[dict_key].append(data_dict)
  return res

input_df['data'] = input_df.apply(get_val, axis=1)

edited Sep 1, 2019 at 21:35

answered Sep 1, 2019 at 19:35

Djaballah Mohammed DJEDID

5991 gold badge4 silver badges13 bronze badges

4 Comments

Tad Over a year ago

Thanks for the reply. Actually I need the values within the list[ ] inside the dictionary

Tad Over a year ago

Thanks Djaballah! It works for this sample data. But, If I'm applying it on large data I'm getting an error - "IndexError: list index out of range". because, one of the data values is {'bb3b_pasf': []} . I'm figuring to add a condition to check if the value of data dictionary is empty , so that the function can be applied on dictionaries with values

Djaballah Mohammed DJEDID Over a year ago

I made a check, you could try it

Tad Over a year ago

Now I'm getting this error :( File "<ipython-input-42-154e1b5c8b19>", line 13, in get_val data_dict = list(row['data'].values())[0][0] KeyError: (0, 'occurred at index 3')

Lovleen Kaur · Accepted Answer · 2019-09-03 12:52:33Z

0

The solution is as follows:

def update_data(row):
    data_dict = row['data']
    for key in data_dict:
        data_dict.update(row.loc['my_dict'])
    return data_dict
df['data'] = df.apply(update_data,axis=1)

edited Sep 3, 2019 at 12:52

answered Sep 1, 2019 at 21:02

Lovleen Kaur

3422 silver badges9 bronze badges

Collectives™ on Stack Overflow

How to add a column value to another column's dictionary using python , pandas

3 Answers 3

3 Comments

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related