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!

Answer 1

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