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
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
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.
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