Distinguish repeating column names by adding an integer using pandas

user_5942100 0
Viewed 72

I have some columns that have the same names. I would like to add a 1 to the repeating column names

Data

Date        Type    hi  hello   stat    hi  hello   
1/1/2022    a       0   0       1       1   0

Desired

Date        Type    hi  hello   stat    hi1     hello1  
1/1/2022    a       0   0       1       1       0

Doing

mask = df['col2'].duplicated(keep=False)

I believe I can utilize mask, but not sure how to efficiently achieve this without calling out the actual column. I would like to call the full dataset and allow the algorithm to update the dupe.

Any suggestion is appreciated

pandas dataframe numpy python
3 Answers

Use the built-in parser method _maybe_dedup_names():

df.columns = pd.io.parsers.base_parser.ParserBase({'usecols': None})._maybe_dedup_names(df.columns)

#        Date  Type  hi  hello  stat  hi.1  hello.1
# 0  1/1/2022     a   0      0     1     1        0

This is what pandas uses to deduplicate column headers from read_csv().

Note that it scales to any number of duplicate names:

cols = ['hi'] * 3 + ['hello'] * 5
pd.io.parsers.base_parser.ParserBase({'usecols': None})._maybe_dedup_names(cols)

# ['hi', 'hi.1', 'hi.2', 'hello', 'hello.1', 'hello.2', 'hello.3', 'hello.4']

In pandas < 1.3:

df.columns = pd.io.parsers.ParserBase({})._maybe_dedup_names(df.columns)
user_131383640

You need to apply the duplicated operation to the column names. And then map the duplication information to a string, which you can then add to the original column names.

df.columns = df.columns+[{False:'',True:'1'}[x] for x in df.columns.duplicated()]
user_113393860

We can do

s = df.columns.to_series().groupby(df.columns).cumcount().replace({0:''}).astype(str).radd('.')
df.columns = (df.columns + s).str.strip('.')
df
Out[153]: 
       Date Type  hi  hello  stat  hi.1  hello.1
0  1/1/2022    a   0      0     1     1        0
user_79645270
Related