Combine multiple columns of text of multiple rows in pandas

user_16062635 0
Viewed 132

I want to combine multiple columns of text from multiple row groups. I know that the join joins into a single cell but it doesn't read by product. I want a single cell to have the information for that product in different brands and continents by the plan. For example: Within the laptop product what are the brands and continents in plan 1, in plan 2 , 3 and so on?

df['all'] = df[['product', 'brand', ...]].agg('-'.join, axis=1)

Here the dataframe:

product     brand     continent    plan
laptop         lg       n_am         P1
laptop    samsung       n_am         P1
laptop      apple         eu         P3
tv             lg         eu         P3
tv        samsung         eu         P2
tv          apple       n_am         P2
tv        samsung       n_am         P1
cellphone      lg         eu         P3
cellphone   apple       n_am         P2
cellphone   apple         eu         P1

Expected dataframe:

product     brand     continent    plan   all
laptop         lg       n_am         P1   product: laptop; plan: P1; brand: lg-samsung; continent: n_am
laptop    samsung       n_am         P1   product: laptop; plan: P1; brand: lg-samsung; continent: n_am
laptop      apple         eu         P3   product: laptop; plan: P3; brand: apple; continent: eu
tv             lg         eu         P3   product: tv;   plan: P3; brand: lg; continent: eu
tv        samsung         eu         P2   product: tv;   plan: P2; brand: samsung-apple; continent: n_am-eu
tv          apple       n_am         P2   product: tv;   plan: P2; brand: samsung-apple; continent: n_am-eu
tv        samsung       n_am         P1   product: tv;   plan: P1; brand: samsung; continent: n_am
cellphone      lg         eu         P3   product: cellphone; plan: P3; brand: lg; continent: eu
cellphone   apple       n_am         P2   product: cellphone; plan: P2; brand: apple; continent: n_am
cellphone   apple         eu         P1   product: cellphone; plan: P1; brand: apple; continent: eu
pandas dataframe python
2 Answers

This is the required code:

#Grouping by the product and plan and then aggregating unique values by placing a hyphen(-) in between. 
df_dict = df.groupby(['product','plan'])[['brand','continent']]\
            .agg(lambda x: "-".join(set(x))).reset_index()

#Merging the above dataframe with the original dataframe to get the values we need
final_df = pd.merge(df.drop(['brand','continent'],axis=1),\
                    df_dict, on=['product','plan'], how='left')

#Finally aggregating all columns by joining the various "column- value" pairs with "; "
final_df['all'] = final_df.apply(lambda x: '; '.join([(i+":"+j) for i, j in x.to_dict().items()]), axis=1)

The output is like this:

     product plan          brand continent                                                all
0     laptop   P1     samsung-lg      n_am  product:laptop; plan:P1; brand:samsung-lg; con...
1     laptop   P1     samsung-lg      n_am  product:laptop; plan:P1; brand:samsung-lg; con...
2     laptop   P3          apple        eu  product:laptop; plan:P3; brand:apple; continen...
3         tv   P3             lg        eu        product:tv; plan:P3; brand:lg; continent:eu
4         tv   P2  samsung-apple   eu-n_am  product:tv; plan:P2; brand:samsung-apple; cont...
5         tv   P2  samsung-apple   eu-n_am  product:tv; plan:P2; brand:samsung-apple; cont...
6         tv   P1        samsung      n_am  product:tv; plan:P1; brand:samsung; continent:...
7  cellphone   P3             lg        eu  product:cellphone; plan:P3; brand:lg; continen...
8  cellphone   P2          apple      n_am  product:cellphone; plan:P2; brand:apple; conti...
9  cellphone   P1          apple        eu  product:cellphone; plan:P1; brand:apple; conti...

PS: There are chances that some of the things that I did could've been done by an inbuilt pandas function. But this code gives you the functionality to tweak at any level.

user_82933090

Use groupby with sort=False and Series.unique. After that join back and use list comprehension to construct string out of dict and assign to all column

l1 = (df[['product','plan']].join(df.groupby(['product', 'plan'], sort=False)
                                    .transform(lambda x: '-'.join(x.unique()))).to_dict('records'))
df['all'] = [';'.join(k+':'+v for k, v in x.items()) for x in l1]

Out[528]:
     product    brand continent plan                                                       all
0     laptop       lg      n_am   P1    product:laptop;plan:P1;brand:lg-samsung;continent:n_am
1     laptop  samsung      n_am   P1    product:laptop;plan:P1;brand:lg-samsung;continent:n_am
2     laptop    apple        eu   P3           product:laptop;plan:P3;brand:apple;continent:eu
3         tv       lg        eu   P3                  product:tv;plan:P3;brand:lg;continent:eu
4         tv  samsung        eu   P2  product:tv;plan:P2;brand:samsung-apple;continent:eu-n_am
5         tv    apple      n_am   P2  product:tv;plan:P2;brand:samsung-apple;continent:eu-n_am
6         tv  samsung      n_am   P1           product:tv;plan:P1;brand:samsung;continent:n_am
7  cellphone       lg        eu   P3           product:cellphone;plan:P3;brand:lg;continent:eu
8  cellphone    apple      n_am   P2      product:cellphone;plan:P2;brand:apple;continent:n_am
9  cellphone    apple        eu   P1        product:cellphone;plan:P1;brand:apple;continent:eu
user_101892140
Related