Pandas DataFrame - remove / replace dict values based on key

user_11644523 0
Viewed 119

Say I have a DataFrame defined as:

df = {
  "customer_name":"john",
  "phone":{
     "mobile":000, 
     "office":111
   },
  "mail":{
    "office":"john@office.com", 
    "personal":"john@home.com",
    "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":"john@office.com", 
    "personal":"john@home.com"
   }
 }

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?

pandas python
3 Answers

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             john@office.com john@home.com
user_121355180

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': 'john@office.com', 'personal': 'john@home.com'}}
user_155213920

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":"john@office.com", 
    "personal":"john@home.com",
    "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            john@office.com  john@home.com
user_165458940
Related