Concat values from same column name in "one" dataframe

Question

I want to concat the values if they have same columns.

I've found some solutions that are from different dataframe, but not from one dataframe. Also, I tried to separate columns to single dataframe then concat, but it seems not working because the columns' name are shown differently. (For example, it shows "apple", "banana", "pizza", "apple.1", "banana.1"...)

Is there any solution to show like this? Thanks!

Providing text instead of images helps to get faster recommendations from the community — RF1991
– RF1991, Commented Mar 26, 2022 at 10:16
@RF1991 Hey, did you see the images? I've posted my desired result one the post — Megan
– Megan, Commented Mar 26, 2022 at 10:18

Corralien · Accepted Answer · 2022-03-28 15:53:27Z

2

You can use melt to flatten your dataframe then pivot to reshape it as its original shape:

df.columns = df.columns.str.rsplit('.').str[0]

out = df.melt().assign(index=lambda x: x.groupby('variable').cumcount()) \
        .pivot_table('value', 'index', 'variable', fill_value=0) \
        .rename_axis(index=None, columns=None)[df.columns.unique()]

print(out)

# Output
   apple  banana  pizza
0      1       4      4
1      2       3      7
2      3       2      3
3      5       0      1
4      8       0      5
5      9       0     34

edited Mar 28, 2022 at 15:53

answered Mar 26, 2022 at 10:24

Corralien

121k8 gold badges44 silver badges69 bronze badges

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

9 Comments

Megan Over a year ago

Hey, it sounds great! But I found that my column name would show apple.1 and banana.1, so apple and apple.1 cannot concat. Do you know how to prevent rename the columns automatically? Thanks a lot

Corralien Over a year ago

When you use read_csv and read_excel, column with same names are renamed (mangle_dupe_cols parameter). I will update my answer soon

Corralien Over a year ago

I think it should work now :) I prepend .melt() by rename(...)

Megan Over a year ago

Hey! I've one more question. Is there any method to make the column names not ordering by the alphabet instead of ordering them. I want them to show what appears first and next (EX: apple, pizza, banana is the original data ordering, but it shows apple, banana, pizza instead.)

Corralien Over a year ago

In your example the order is apple, banana, pizza ;-)

|

Collectives™ on Stack Overflow

Concat values from same column name in "one" dataframe

1 Answer 1

9 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

9 Comments

Your Answer

Sign up or log in

Post as a guest

Related