Bottom Page

Thread Rating:
  • 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
 How replace Unnamed header column with previous
#1
Hi,
I have excel file with merged columns as below:

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:
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:
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:

df.rename(columns={'*Unnamed: 1':'nan'}, inplace=True)
but I want to replace if header starts with: Unnamed"
Quote
#2
Assuming you want to replace all columns containing the string 'Unnamed' with the string 'nan', this will do:
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.
df.columns = df.columns.map(lambda col: col if not 'Unnamed' in col else 'nan')
Quote
#3
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
Quote
#4
The following function should do the trick. This won't win any beauty prize however as I just quickly hacked this together.
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)
Quote

Top Page

Possibly Related Threads...
Thread Author Replies Views Last Post
  How to modify df column SriMekala 1 119 Aug-14-2019, 11:06 PM
Last Post: boring_accountant
  Trying to replace for loops with numpy vspoloni 8 343 Aug-04-2019, 07:09 AM
Last Post: paul18fr
  Add column to numpy matrix Gigux 1 123 Aug-02-2019, 12:05 AM
Last Post: scidam
  How to delete column if entire column values are "nan" Sri 4 428 Apr-13-2019, 12:16 PM
Last Post: Sri
Star Pandas referring to result of a previous row slavok400 1 550 Sep-27-2018, 03:03 AM
Last Post: slavok400
  Text to column pandas ms5573 0 493 Aug-25-2018, 08:18 PM
Last Post: ms5573
  cannot replace with np.nan ankurk017 2 860 Jul-02-2018, 11:40 AM
Last Post: volcano63
  Output column names Scott 2 668 Jun-25-2018, 12:47 PM
Last Post: volcano63
  How to extract previous and current rows if their value is equal SriRajesh 5 838 Jun-18-2018, 08:09 PM
Last Post: volcano63
  how often does a word occur in this column? Jack_Sparrow 6 1,090 Jun-10-2018, 08:52 PM
Last Post: volcano63

Forum Jump:


Users browsing this thread: 1 Guest(s)