Pandas DataFrame - remove / replace dict values based on key

Question

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?

Answer 1

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]