Rename columns of Pandas DataFrame using values located at fixed locations of each column

user_15381275 0
Viewed 1212

I am new to Python and am trying to import an Excel spreadsheet with multiple sheets and ~20 cols, 40 rows per sheet.

            Unnamed: 1 Unnamed: 2 Unnamed: 3  
 0          NaN        NaN        NaN  
 1          NaN        NaN        NaN  
 2          NaN        NaN        NaN  
 3          NaN        NaN        NaN  
 4          NaN        NaN        NaN  
 5   Population        Sex        NaN  
 6          NaN       Male     Female  
 7      Percent        NaN        NaN  
 8          NaN        NaN        NaN  
 9          NaN       48.5       51.5  
 10         NaN        NaN        NaN  
 11        12.2       18.4        6.5  
 12        36.1       41.5       31.1  
 13        51.6       40.1       62.5

I am trying to rename the columns with values that currently sit within each column. Throughout the dataframe the current column name and location of the new column names would always be as follows:

'Unnamed: 1' -> value in 5th row
'Unnamed: 2' -> value in 6th row
'Unnamed: 3' -> value in 6th row

However, while the positions are always the same the values are different, so I cannot just rename the columns

df = df['Sheet'].rename(columns={'Unnamed: 1':'Total population', 
                                 'Unnamed: 2':'Male', 
                                 'Unnamed: 3':'Female',
                                 })

I was hoping to use rename columns and iloc to say

'where col.name == 'Unnamed: 1', rename(column=df.iloc[5])'
'where col.name == 'Unnamed: 2', rename(column=df.iloc[6])'
'where col.name == 'Unnamed: 3', rename(column=df.iloc[6])'

Is it possible to do this?

pandas data-cleaning rename python
2 Answers

Specify the value at the given location instead of the string. Since you have multiple sheets, you need to iterate over the dictionary to apply the renaming to all sheets:

for sheet in df.keys():
    df[sheet] = df[sheet].rename(columns={'Unnamed: 1': df['Unnamed: 1'].iloc[5], 
                                          'Unnamed: 2': df['Unnamed: 2'].iloc[6], 
                                          'Unnamed: 3': df['Unnamed: 3'].iloc[6],
                                 })
user_63725980

What about this:

n = [5,6,6]
df.columns = df.values[n].diagonal()

Output

df
    Population  Male  Female
0          NaN   NaN     NaN
1          NaN   NaN     NaN
2          NaN   NaN     NaN
3          NaN   NaN     NaN
4          NaN   NaN     NaN
5   Population   Sex     NaN
6          NaN  Male  Female
7      Percent   NaN     NaN
8          NaN   NaN     NaN
9          NaN  48.5    51.5
10         NaN   NaN     NaN
11        12.2  18.4     6.5
12        36.1  41.5    31.1
13        51.6  40.1    62.5
user_137432330
Related