CODEWITHSUNDEEP
pythonpython-3.xpandas
RustyShackleford
RustyShackleford

Reputation: 3677

How to merge duplicate columns with `.1` into single column without the `.1`?

I have a df like this:

col1  col1.
1     nan
2     nan

How can I merge these columns into one without the .1? 

col1
1
2

I can do this if the column names are the same:

df = df.groupby(level=0,axis=1).first()

If I try to do string replace, column names get characters removed, not sure why:

df.columns = df.columns.str.replace('.1','')

Upvotes: 1

Views: 43

Answers (1)

user3483203
user3483203

Reputation: 51165

Use a regular expression to normalize your columns:

df.columns = df.columns.str.extract(r'(\w+)\.?', expand=False)

Use groupby + first:

df.groupby(level=0, axis=1).first()


   col1
0   1.0
1   2.0

You can also groupby the regex result:

df.groupby(df.columns.str.extract(r'(\w+)\.?', expand=False), axis=1).first()

Upvotes: 2

Related Questions