Delete Part Of A Row In Pandas / Shift Up Part Of A Row ? Align Column Headings

So I have a data frame where the headings I want do not currently line up: In [1]: df = pd.read_excel('example.xlsx') print (df.head(10)) Out [1]:

Solution 1:

IIUC you can use:

#create df from multiindex in columns
df1 = pd.DataFrame([x for x in df.columns.values])
print df1
           010             Unique Identifier
1                Number of fund
2  Portfolio                B24
3      Asset                B65
4    Country                B35
5   Quantity                B44

#if len of string < 4, give value from column 0 to column 1
df1.loc[df1.iloc[:,1].str.len() < 4, 1] = df1.iloc[:,0]
print df1
           010             Unique Identifier
1                Number of fund
2  Portfolio          Portfolio
3      Asset              Asset
4    Country            Country
5   Quantity           Quantity

#set columns by first columns of df1
df.columns = df1.iloc[:,1]

print df
0  Unique Identifier  Number of fund Portfolio   Asset         Country  \
04562   General  Type A  UNITED KINGDOM   
11233   General  Type B              US   
27892   General  Type C  UNITED KINGDOM   
348524   General  Type C  UNITED KINGDOM   
46541   General  Type A          FRANCE   
59875   General  Type B  UNITED KINGDOM   
63211   General  Type B         GERMANY   
79513   General  Type A  UNITED KINGDOM   
83574   General  Type C  UNITED KINGDOM   

0  Quantity  
011224344352617283

EDIT by comments:

print df.columns
Index([u'Portfolio', u'Asset', u'Country', u'Quantity'], dtype='object')

#set first row by columns names
df.iloc[0,:] = df.columns

#reset_index
df = df.reset_index()
#set columns from first row
df.columns = df.iloc[0,:]
df.columns.name= None#remove first rowprint df.iloc[1:,:]
  Unique Identifier Number of fund Portfolio   Asset         Country Quantity
14562   General  Type A  UNITED KINGDOM        121233   General  Type B              US        237892   General  Type C  UNITED KINGDOM        4448524   General  Type C  UNITED KINGDOM        456541   General  Type A          FRANCE        369875   General  Type B  UNITED KINGDOM        273211   General  Type B         GERMANY        189513   General  Type A  UNITED KINGDOM        293574   General  Type C  UNITED KINGDOM        3