How replace Unnamed header column with previous

[SriMekala]

Hi,
I have excel file with merged columns as below:

1 2 3 4

Category    Group           Sub_valid               Name    Item    Value    Color   Status Group1      VGT     Power   0        VGT     Power             BHA     Voltage 1        BHA     Voltage

Group is merged for Name & Item column, Sub_valid is a merged column for Value, Color, Status.
When I read using:

1 2	import pandas as pd df = pd.read_excel('D:PythonCodes\mergedinput.xlsx',sheetname='Sheet1')

The column above Item is "Unnamed0" & Color column & Status column are "Unnamed1", Unnamed2",

But I want:

1 2 3 4

Category    Group  Group    Sub_valid  Sub_valid  Sub_valid             Name    Item    Value      Color      Status Group1      VGT     Power   0          VGT        Power             BHA     Voltage 1          BHA        Voltage

I use below code but it needs to do multiple time:

1	df.rename(columns={'*Unnamed: 1':'nan'}, inplace=True)

but I want to replace if header starts with: Unnamed"

[boring_accountant]

Assuming you want to replace all columns containing the string 'Unnamed' with the string 'nan', this will do:

1 2 3

print(df) df.columns = [col if not 'Unnamed' in col else 'nan' for col in df.columns] print(df)

Output:
  First  Unnamed: 1  Unnamed: 2
0     A           1           3
1     B           2           4

  First  nan  nan
0     A    1    3
1     B    2    4

As an alternative, the following yields equal results. I'm not sure whichever I prefer but both are possible.

1	df.columns = df.columns.map(lambda col: col if not 'Unnamed' in col else 'nan')

[SriMekala]

1 2 3 4

Category    Group  Group    Sub_valid  Sub_valid  Sub_valid             Name    Item    Value      Color      Status Group1      VGT     Power   0          VGT        Power             BHA     Voltage 1          BHA        Voltage

But I want to "Unnamed column should replace with previous not "Unnamed column" like, above

[boring_accountant]

The following function should do the trick. This won't win any beauty prize however as I just quickly hacked this together.

1 2 3 4 5 6 7 8 9 10 11 12 13 14

def repeat_cols(columns):     last_non_unnamed = '' if 'unnamed' in columns[0].lower() else columns[0]     new_columns = []     new_column = ''     for column in columns:         if 'unnamed' in column.lower():             new_column = last_non_unnamed         else:             new_column = column             last_non_unnamed = new_column         new_columns.append(new_column)     return new_columns   df.columns = repeat_cols(df.columns)