I Have a df which i the want to convert into dict in a way that id is the key and then gets a list of dictionaries as value:
d = {'id': [1,1,1,1,2,2,3,3,3,4,4,4,4],
'label':['A','A','B','G','A','BB','C','C','A','BB','B','AA','AA']
,'amount':[2,-12,12,-12,5,-5,2,3,5,3,3,10,-10]}
df = pd.DataFrame(d)
d = defaultdict(lambda: defaultdict(list))
#only append the negative amounts
for index,row in df.iterrows():
if row["amount"] < 0:
d[row["id"]][row["amount"]].append(
{ "id": row["id"],
"label": row["label"]})
print(d)
Out: defaultdict(<function __main__.<lambda>()>,
{1: defaultdict(list,
{-12: [{'id': 1, 'description': 'A'},
{'id': 1, 'description': 'G'}]}),
2: defaultdict(list, {-5: [{'id': 2, 'description': 'BB'}]}),
4: defaultdict(list, {-10: [{'id': 4, 'description': 'AA'}]})})
d2 = defaultdict(lambda: defaultdict(list))
#only append the positive amounts
for index,row in df.iterrows():
account_id = row["id"]
amount = row["amount"]
if amount > 0:
d2[account_id][amount].append( { "id": row["id"],
"label": row["label"]})
print(d2)
Out: defaultdict(<function __main__.<lambda>()>,
{1: defaultdict(list,
{2: [{'id': 1, 'description': 'A'}],
12: [{'id': 1, 'description': 'B'}]}),
2: defaultdict(list, {5: [{'id': 2, 'description': 'A'}]}),
3: defaultdict(list,
{2: [{'id': 3, 'description': 'C'}],
3: [{'id': 3, 'description': 'C'}],
5: [{'id': 3, 'description': 'A'}]}),
4: defaultdict(list,
{3: [{'id': 4, 'description': 'BB'},
{'id': 4, 'description': 'B'}],
10: [{'id': 4, 'description': 'AA'}]})})
How can i compare the two dictioanries in a way that i get the records that contain matching positive and negative numbers for the same user so that my dict will look like this below? I only want to use dicts and not pandas operations.d
defaultdict(<function __main__.<lambda>()>,
{1: defaultdict(list,
{-12: [{'id': 1, 'description': 'A'},
{'id': 1, 'description': 'G'}],
12: [{'id': 1, 'description': 'B'}]},
2: defaultdic (list, {-5: [{'id': 2, 'description': 'BB'},
5: {'id': 2, 'description': 'A'}]}),
4: defaultdict(list, {-10: [{'id': 4, 'description': 'AA'}],
10: [{'id': 4, 'description': 'AA'}]}))
CodePudding user response:
So you basically want to filter the amounts that exist both as a positive and a negative integer per id? I suggest filtering this in pandas prior to converting it to a dict. You can groupby id
and then filter the groups by comparing which amounts also exist in the same negative Series
:
new_df = df.groupby('id').apply(lambda g: g[g['amount'].isin(g['amount']*-1)]).reset_index(drop=True)
Output:
id | label | amount | |
---|---|---|---|
0 | 1 | A | -12 |
1 | 1 | B | 12 |
2 | 1 | G | -12 |
3 | 2 | A | 5 |
4 | 2 | BB | -5 |
5 | 4 | AA | 10 |
6 | 4 | AA | -10 |
You can then export the entire df as you please:
d = defaultdict(lambda: defaultdict(list))
for index,row in new_df.iterrows():
d[row["id"]][row["amount"]].append(
{ "id": row["id"],
"label": row["label"]})
In case you just want to compare the dicts you can iterate one, compare if the negative of the values exist in the other dict, then write both values to a new dict:
new_dict = {}
for item in d:
for i in d[item]:
if i*-1 in [i for item in d2 for i in d2[item]]:
new_dict[item] = {i:d[item][i], -i:d2[item][-i]}
Result:
{1: {-12: [{'id': 1, 'label': 'A'}, {'id': 1, 'label': 'G'}],
12: [{'id': 1, 'label': 'B'}]},
2: {-5: [{'id': 2, 'label': 'BB'}], 5: [{'id': 2, 'label': 'A'}]},
4: {-10: [{'id': 4, 'label': 'AA'}], 10: [{'id': 4, 'label': 'AA'}]}}