Combine string in a column based on groups in a pandas DataFrame

Question

I have a Pandas DataFrame, DF:

Column A	Column B	Column C
Apple	red	Texas
Apple	red	California
Banana	yellow	Indiana
Banana	yellow	Florida

I would like to get it in a dictionary in the form:

{ "Apple red" : ['Texas', 'California'], "Banana yellow" : ['Indiana', 'Florida'] }

where Key = concatenation of strings in column A and column B (and)

Value = all corresponding strings from column C (based on groupby) in a list.

I am not sure how to achieve this.

Key Note: It should also work if there are more than 3 columns to be grouped for dictionary's key

Andrej Kesely · Accepted Answer · 2022-10-25 23:05:22Z

3

Try:

x = dict(
    df.groupby(df["Column A"] + " " + df["Column B"])["Column C"].agg(list)
)

print(x)

Prints:

{'Apple red': ['Texas', 'California'], 'Banana yellow': ['Indiana', 'Florida']}

answered Oct 25, 2022 at 23:05

Andrej Kesely

196k15 gold badges60 silver badges105 bronze badges

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

2 Comments

Krishna Over a year ago

Sorry, I just made an last minute edit to the post. It should also work if there are multiple columns in the DF to be grouped for the dictionary's key.

Andrej Kesely Over a year ago

@Krishna If they are strings then just concatenate the columns together: df.groupby(df["Column A"] + " " + df["Column B"] + " " + df['Column X'] + ...etc)

sammywemmy · Accepted Answer · 2022-10-25 23:21:06Z

1

One option, which should be performant as well, is with a default dictionary:

from collections import defaultdict

out = defaultdict(list)

for a, b, c in zip(df['Column A'], df['Column B'], df['Column C']):
    key = a + " " + b
    out[key].append(c)

out
defaultdict(list,
            {'Apple red': ['Texas', 'California'],
             'Banana yellow': ['Indiana', 'Florida']})

answered Oct 25, 2022 at 23:21

sammywemmy

28.9k4 gold badges21 silver badges35 bronze badges

Collectives™ on Stack Overflow

Combine string in a column based on groups in a pandas DataFrame

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related