Grouping a 3 column dataframe by values of a particular column to create list of dictionaries

user_10912728 0
Viewed 48

I am very new to Python Pandas and have a question on using Pandas Dataframe groupby and filters.

Here's my sample dataframe:

data = {
    'A': ['A1', 'A2', 'A3', 'A4', 'A5', 'A6'],
    'B': ['B1', 'B2', 'B3', 'B3', 'B4', 'B5'],
    'C': ['C1', 'C1', 'C2', 'C2', 'C3', 'C3']
}
df = pd.DataFrame(data)

I want to group by values of Column 'C' and filter groups that have a different set of values under column 'B' to create the below list of dictionaries:

[{"C1": [{"A1":"B1"}, {"A2": "B2"}]}, {"C3": [{"A5":"B4"}, {"A6":"B5"}]}]

Can someone help with how can I do this using Pandas? Thanks!

pandas-groupby pandas dataframe python
3 Answers

Let's define a function dictify which groups the dataframe on column C and yields the dictionary in the specified format for the groups that have different set of values in column B:

def dictify():
    for k, g in df.groupby('C'):
        if g['B'].nunique() > 1:
            yield {k: [{x: y} for x, y in zip(g['A'], g['B'])]}

list(dictify())

Result:

[{'C1': [{'A1': 'B1'}, {'A2': 'B2'}]}, {'C3': [{'A5': 'B4'}, {'A6': 'B5'}]}]
user_128331660 
data = {
    'A': ['A1', 'A2', 'A3', 'A4', 'A5', 'A6'],
    'B': ['B1', 'B2', 'B3', 'B3', 'B4', 'B5'],
    'C': ['C1', 'C1', 'C2', 'C2', 'C3', 'C3']
}
df = pd.DataFrame(data)

df = df.drop_duplicates(subset=['B','C'], keep=False)
[df.groupby("C").apply(lambda x: [dict([i]) for i in x[['A','B']].values]).to_dict()]

Output

[{'C1': [{'A1': 'B1'}, {'A2': 'B2'}], 'C3': [{'A5': 'B4'}, {'A6': 'B5'}]}]
user_47183500

To generate the result, run:

result = [ {key: [ {row.A : row.B} for row in grp.itertuples() ]}
    for key, grp in df.groupby('C') if grp.B.unique().size > 1 ]
user_73884770
Related