Append Dictionary Elements into an Empty Pandas Dataframe Column

Question

I have data in a pandas dataframe which looks like this:

queryName   Market  tags    categoryDetails
dummy_query (dummy_market)  dummy_market    dummy_tag   [{'name': 'relevant_data', 'parentName': 'relevant_scrape', 'parentId': '289245228', 'id': '2892695401'}, {'name': 'relevant_data', 'parentName': 'relevant_scrape', 'parentId': '289245228', 'id': '21892718'}, {'name': 'dummy_data', 'parentName': 'Location', 'parentId': '21221517840', 'id': '229565351'}]
dummy_query (dummy_market)  dummy_market    dummy_tag   [{'name': 'relevant_data', 'parentName': 'relevant_scrape', 'parentId': '289245228', 'id': '2892659'}, {'name': 'relevant_data', 'parentName': 'relevant_scrape', 'parentId': '289245228', 'id': '2892667'}, {'name': 'irrelevant_data', 'parentName': 'irrelevant_scrape', 'parentId': '2662610', 'id': '268415777'}, {'name': 'dummy_data', 'parentName': 'Location', 'parentId': '21221517840', 'id': '2565351'}]
dummy_query (dummy_market)  dummy_market    dummy_tag   [{'name': 'relevant_data', 'parentName': 'relevant_scrape', 'parentId': '289245228', 'id': '2892695401'}, {'name': 'irrelevant_data', 'parentName': 'irrelevant_scrape', 'parentId': '2662610', 'id': '268415777'}, {'name': 'dummy_data', 'parentName': 'Location', 'parentId': '21221517840', 'id': '229565351'}, {'name': 'Consideration', 'parentName': 'irrelevant_scrape', 'parentId': '2203873', 'id': '2203874'}]
dummy_query (dummy_market)  dummy_market    dummy_tag   [{'name': 'relevant_data', 'parentName': 'relevant_scrape', 'parentId': '289245228', 'id': '2892695401'}, {'name': 'irrelevant_data', 'parentName': 'irrelevant_scrape', 'parentId': '2662610', 'id': '268415777'}, {'name': 'dummy_data', 'parentName': 'Location', 'parentId': '21221517840', 'id': '229565351'}]
dummy_query (dummy_market)  dummy_market    dummy_tag   [{'name': 'relevant_data', 'parentName': 'relevant_scrape', 'parentId': '289245228', 'id': '21892718'}, {'name': 'irrelevant_data', 'parentName': 'irrelevant_scrape', 'parentId': '2662610', 'id': '268415777'}, {'name': 'dummy_data', 'parentName': 'Location', 'parentId': '21221517840', 'id': '229565351'}]
dummy_query (dummy_market)  dummy_market    dummy_tag   [{'name': 'relevant_data', 'parentName': 'relevant_scrape', 'parentId': '289245228', 'id': '2892659'}, {'name': 'dummy_data', 'parentName': 'Location', 'parentId': '21221517840', 'id': '229565351'}, {'name': 'dummy_data', 'parentName': 'irrelevant_scrape', 'parentId': '2203873', 'id': '2203880'}]

I need my dataframe to have an additional, fifth column which will contain all the name keys with the elements called "relevant_data" of each row. These datapoints are selected based off of the parentName. If parentName = 'relevant_scrape', select "name."

How should I go about doing this? Here is my code so far.

import pandas as pd
import json
from pandas import DataFrame, read_csv

df = pd.read_csv('dataset.csv', sep = '\t')
for row in df.categoryDetails:
    if isinstance(row, str):
        list_dicts = json.loads(row.replace("'", "\""))
        for each_dict in list_dicts:
            if each_dict["parentName"] == "relevant_scrape":
                df['fifth_column'] = each_dict["name"]

df.to_csv('output.txt', sep = '\t')

(Note: my original data is a bit messy and couldn't be rendered as JSON until I replaced its quotation marks with double quotation marks. Hence the json.loads call.)

This produces for me a dataframe with a fifth column, but it inserts the same exact "name" element in each row. Any and all help is appreciated, thank you.

OmerBA · Accepted Answer · 2016-07-07 20:59:26Z

1

You are using df['fifth_column'] = each_dict["name"], which sets all values in the 'fifth_column' column to the same value each iteration, since pandas' operations are column wise by default.

Maybe you should try the following snippet:

def extract_details(row):
    # your parsing logic.
    if isinstance(row, str):
        list_dicts = json.loads(row.replace("'", "\""))
        all_relevant_data = []
        for each_dict in list_dicts:
            if each_dict["parentName"] == "relevant_scrape":
                all_relevant_data.append(each_dict["name"])
        return ','.join(all_relevant_data)

and then you could do:

df['fifth_column'] = df.categoryDetails.apply(extract_details)

edited Jul 7, 2016 at 20:59

answered Jul 7, 2016 at 19:56

OmerBA

8429 silver badges14 bronze badges

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

8 Comments

dataelephant Over a year ago

I got: "TypeError: 'str' object does not support item assignment" when I replaced column with row.

dataelephant Over a year ago

This executed, but all the data in that column (fifth column) is being duplicated from the first piece of "relevant_data." Any ideas?

OmerBA Over a year ago

This is what the code you posted does. all the first dicts have name=relevant_data in the data.

OmerBA Over a year ago

No problem :). if you want all the 'relevant_data' fields you might want to keep a list (and append relevant data from each dict to it) and then return the list in the inner function. i'll edit the answer again

OmerBA Over a year ago

take a look at stackoverflow.com/questions/29058147/…

|

Collectives™ on Stack Overflow

Append Dictionary Elements into an Empty Pandas Dataframe Column

1 Answer 1

8 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

8 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related