How to handle same column headers with different values

Question

I have the following data:

S.No   Department  stock stock  stock
1       Medicine        34  38  58
2       Pharma          23  39  71
3       ortho           76  12  81

The source file I am getting has the repeated values of column headers as "stock", it actually should be "Stock1", "Stock2" and "Stock3". I do not want to do it manually but programmatically.

I tried:

df.rename(columns  = {df.columns[1]: 'Stock1'})
df.rename(columns  = {df.columns[2]: 'Stock2'})
df.rename(columns  = {df.columns[3]: 'Stock3'})

But this does not work.

Please help.

Answer 1

Several points of failure.

1. The pandas.DataFrame.rename method returns a copy. You need to reassign it back to the name df
2. You should be doing it with one dictionary
3. That wouldn't work because you have three columns named the same thing

I have to assume some things about the general form of your problem. First, I'll split your dataframe up into columns with 'stock' and columns without

df_stock = df.filter(regex='^stock$')
df_other = df.drop('stock', axis=1)

df_stock.columns += list(map(str, range(1, df_stock.shape[1] + 1)))

df_new = df_other.join(df_stock)

Answer 2

Have you tried df.columns=['Stock1', 'Stock2', 'Stock3']?