Joining duplicate columns in pandas dataframe

Question

I have a dataframe that has all unique columns, however, I have to rename the columns with their field alias after creation. Some field aliases are duplicates, in this case, those columns need to be joined together to one column. It's worth noting that in either case, the values could be strings or integers as well as contain special characters.

  Type Type Project ID
0  AS3        112    1
1  AS4        131    2
2       AS5   115    3
3  AS6        191    4
4       AS7   100    5

When I use df.groupby(df.columns, axis=1) I end up with an empty dataframe. But I'm assuming that's because groupby returns a dataframegroupby which isn't an actual dataframe? Is there an easy way to do this so my output is:

  Type Project ID
0  AS3   112    1
1  AS4   131    2
2  AS5   115    3
3  AS6   191    4
4  AS7   100    5

BENY · Accepted Answer · 2019-03-21 17:00:12Z

3

Using groupby with first ,also make sure your blank is np.nan , if not , replace it

#df=df.replace({'':'np.nan}) or df=df.mask(df=='')
df.groupby(level=0,axis=1).first()

edited Mar 21, 2019 at 17:00

answered Mar 21, 2019 at 16:58

BENY

324k22 gold badges176 silver badges250 bronze badges

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

4 Comments

AGH_TORN Over a year ago

This doesn't appear to be working for me. I should have also noted that the dataframe is a result of a pd.concat(dfs) where dfs is a list of dataframes. Should I reassign the variable before performing this?

BENY Over a year ago

@AGH_TORN you should assign it back df=df.groupby(level=0,axis=1).first()

AGH_TORN Over a year ago

coming back to this, is there any way to maintain column order with this? Seems to be rearranging my columns at random.

BENY Over a year ago

@AGH_TORN (df.groupby(level=0,axis=1).first()).reindex(columns=df.columns.unique())

Vaishali · Accepted Answer · 2019-03-21 17:01:00Z

1

If the blank is empty string, sum will work

df.groupby(df.columns, axis = 1).sum()

    ID  Project Type
0   1   112     AS3
1   2   131     AS4
2   3   115     AS5
3   4   191     AS6
4   5   100     AS7

answered Mar 21, 2019 at 17:01

Vaishali

38.5k5 gold badges62 silver badges88 bronze badges

Collectives™ on Stack Overflow

Joining duplicate columns in pandas dataframe

2 Answers 2

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related