Pandas DataFrame - remove / replace dict values based on key-CodePudding

Say I have a DataFrame defined as:

df = {
  "customer_name":"john",
  "phone":{
     "mobile":000, 
     "office":111
   },
  "mail":{
    "office":"[email protected]", 
    "personal":"[email protected]",
    "fax":"12345"
   }
 }

I want to somehow alter the value in column "mail" to remove the key "fax". Eg, the output DataFrame would be something like:

output_df = {
  "customer_name":"john",
  "phone":{
     "mobile":000, 
     "office":111
   },
  "mail":{
    "office":"[email protected]", 
    "personal":"[email protected]"
   }
 }

where the "fax" key-value pair has been deleted. I tried to use pandas.map with a dict in the lambda, but it does not work. One bad workaround I had was to normalize the dict, but this created unnecessary output columns, and I could not merge them back. Eg.;

df = pd.json_normalize(df)

Is there a better way for this?

CodePudding user response：

You can use pop to remove a element from dict having the given key.

import pandas as pd
df['mail'].pop('fax')
df = pd.json_normalize(df)
df

Output:

  customer_name   phone.mobile    phone.office    mail.office     mail.personal
0 john            0               111             [email protected] [email protected]

CodePudding user response：

This is the simplest technique to achieve your aim.

import pandas as pd
import numpy as np
df = {
  "customer_name":"john",
  "phone":{
     "mobile":000, 
     "office":111
   },
  "mail":{
    "office":"[email protected]", 
    "personal":"[email protected]",
    "fax":"12345"
   }
 }
del df['mail']['fax']
df = pd.json_normalize(df)
df

Output :

customer_name phone.mobile    phone.office    mail.office mail.personal 
0 john    0   111            [email protected]  [email protected]

CodePudding user response：

Is there a reason you just don't access it directly and delete it? Like this:

del df['mail']['fax']
print(df)

{'customer_name': 'john', 
'phone': {'mobile': 0, 'office': 111}, 
'mail': {'office': '[email protected]', 'personal': '[email protected]'}}