Explode pandas column of dictionary with list of tuples as value

user_5434659 0
Viewed 589

I have the following dataframe where col2 is a dictionary with a list of tuples as values. The keys are consistantly 'added' and 'deleted' in the whole dataframe.

Input df

col1 col2
value1 {'added': [(59, 'dep1_v2'), (60, 'dep2_v2')], 'deleted': [(59, 'dep1_v1'), (60, 'dep2_v1')]}
value 2 {'added': [(61, 'dep3_v2')], 'deleted': [(61, 'dep3_v1')]}

Here's a copy-pasteable example dataframe:

jsons = ["{'added': [(59, 'dep1_v2'), (60, 'dep2_v2')], 'deleted': [(59, 'dep1_v1'), (60, 'dep2_v1')]}",
         "{'added': [(61, 'dep3_v2')], 'deleted': [(61, 'dep3_v1')]}"]

df = pd.DataFrame({"col1": ["value1", "value2"], "col2": jsons})

edit

col2 directly comes from the diff_parsed field of pydriller output

I want to "explode" col2 so that I obtain the following result:

Desired output

col1 number added deleted
value1 59 dep1_v2 dep1_v1
value1 60 dep2_v2 dep2_v1
value2 61 dep3_v2 dep3_v1

So far, I tried the following:

df = df.join(pd.json_normalize(df.col2))
df.drop(columns=['col2'], inplace=True)

The above code is simplified. I first manipulate the column to convert to proper json. It was in an attempt to first explode on 'added' and 'deleted' and then try to play around with the format to obtain what I want...but the list of tuples is not preserved and I obtain the following:

col1 added deleted
value1 59, dep1_v2, 60, dep2_v2 59, dep1_v1, 60, dep2_v1
value2 61, dep3_v1 61, dep3_v2

Thanks

pandas dataframe python
3 Answers

Well this certainly isn't elegant, but here's a potential solution that is at least easier to understand and reason about:

def explode_records(df):
    new_records = []
    def map_dict_to_row(value, col2_dict):
        temp = {}
        for number, added in col2_dict["added"]:
            temp[number] = {"value": value, "number": number, "added": added}
        for number, deleted in col2_dict["deleted"]:
            if number in temp:
                temp[number] = {**temp[number], "deleted": deleted}
            else:
                temp[number] = {"value": value, "deleted": deleted}
        new_records.extend(list(temp.values()))

    df.apply(lambda row: map_dict_to_row(row.col1, row.col2), axis=1)  # assumes col2 is a dict
    return pd.DataFrame(new_records)

Usage:

In [4]: explode_records(df)
Out[4]:
     value  number    added  deleted
0   value1      59  dep1_v2  dep1_v1
1   value1      60  dep2_v2  dep2_v1
2  value 2      61  dep3_v2  dep3_v1

Note that I got value 2 from your original data. I'm assuming it's just a typo, and not that you also need value x -> valuex functionality.

I wasn't able to get the other solution working, so I wasn't able to compare its performance vs mine.

user_62987120

Get data for added and deleted (values in col2, have been converted to dicts):

added, deleted = [df.col2.str[head].explode()
                    .apply(pd.Series).set_axis(['number', head], axis = 1) 
                  for head in ('added', 'deleted')]

Get rid of duplicate number column:

 added = added['added']

Trim df:

df = df['col1']

Concatenate:

pd.concat([df, added, deleted], axis = 1)

      col1    added  number  deleted
0   value1  dep1_v2      59  dep1_v1
0   value1  dep2_v2      60  dep2_v1
1  value 2  dep3_v2      61  dep3_v1

@ddejohn's solution should be more performant, as you are dealing with native python structures, before dumping them into pandas

user_71757130

Here's a solution. It's a little long, but it works:

tmp = pd.concat([df, pd.json_normalize(df['col2'])], axis=1).drop('col2', axis=1).explode(['added', 'deleted'])
new_df = pd.concat([tmp.drop(['added', 'deleted'], axis=1).reset_index(drop=True), pd.DataFrame(tmp['added'].tolist()).merge(pd.DataFrame(tmp['deleted'].tolist()), on=0).set_axis(['number', 'added', 'deleted'], axis=1)], axis=1)

Output:

>>> new_df
     col1  number    added  deleted
0  value1      59  dep1_v2  dep1_v1
1  value1      60  dep2_v2  dep2_v1
2  value2      61  dep3_v2  dep3_v1
Admin1
Related