How can I normalize a dict inside a CSV?

user_3158245 0
Viewed 118

I have a CSV file which contains a dict value.

Test.CSV

id,name,contact,Location
1,Julie,"[{""name"":""Jenny Brown"",""relation"":""mother"",""number"":2113131313},{""name"":""Jorge"",""relation"":""brother"",""number"":121313131}]",US
2,Jim,"[{""name"":""Sana"",""relation"":""sister"",""number"":83279131}]",UK

I want to normalize this CSV. Expected output:

id , name, contact_name,contact_realation,contact_number,location
1,Julie,Jenny Brown,mother,2113131313,US
1,Julie,Jorge,brother,121313131,US
2,Jim,Sana,sister,83279131,UK

I have loaded the data using CSV reader but I am not able to normalize the contact value. How can I do that?

csvfile = csv.reader(open(filename, encoding="utf8"))

So far I tried this:

df=pd.read_csv(filename, converters={'contact':json.loads}, header=0)
contact_df = pd.io.json.json_normalize(df['contact'])

but getting the below error:

AttributeError: 'list' object has no attribute 'values'
pandas python-2.7 python-3.x python
2 Answers

The function pd.io.json.json_normalize is used to work with JSON objects directly. But it seems you want to work with pd.Series directly. A nice trick is to map the value to a Series or Dataframe. Then you can use concat to create the contact dataframe, with mutliple index. Finally, you merger it back to the original dataframe.

df=pd.read_csv(filename, converters={'contact':json.loads}, header=0)
df.index.name = 'row_id'
concat_df = df.merge(
    pd.concat(df["contact"].apply(pd.DataFrame).tolist(), keys=df.index),
    left_index=True, right_index=True
).drop("contact",1)

Output:

          id name_x Location       name_y relation      number     role
row_id                                                                 
0      0   1  Julie       US  Jenny Brown   mother  2113131313      NaN
       1   1  Julie       US        Jorge      NaN   121313131  brother
1      0   2    Jim       UK         Sana   sister    83279131      NaN
user_30517150

In case you would like to do this with only the csv module, you can use something like this:

import csv
from io import StringIO
import json

data = """id,name,contact,location
1,Julie,"[{""name"":""Jenny Brown"",""relation"":""mother"",""number"":2113131313},{""name"":""Jorge"",""relation"":""brother"",""number"":121313131}]",US
2,Jim,"[{""name"":""Sana"",""relation"":""sister"",""number"":83279131}]",UK
3,Alice,,UK"""

reader = csv.DictReader(StringIO(data))
with open("processed.csv", 'w') as f:
    writer = csv.DictWriter(f, fieldnames=["id","name","contact_name","contact_relation","contact_number","location"])
    writer.writeheader()
    for row in reader:
        new_row = {k:row[k] for k in row if k != "contact"}
        if row["contact"]:
            contacts = json.loads(row["contact"])
            for contact in contacts:
                for key in contact:
                    new_row["contact_" + key] = contact[key]
                writer.writerow(new_row)
        else:
            writer.writerow(new_row)

Which results in:

$cat processed.csv
id,name,contact_name,contact_relation,contact_number,location
1,Julie,Jenny Brown,mother,2113131313,US
1,Julie,Jorge,brother,121313131,US
2,Jim,Sana,sister,83279131,UK
3,Alice,,,,UK

Edit: Updated code to account for entries with no contact info.

user_1894180
Related