CODEWITHSUNDEEP
pythonpandas
scrollex
scrollex

Reputation: 2723

Renaming pandas data frame columns using a for loop

I'm not sure if this is a dumb way to go about things, but I've got several data frames, all of which have identical columns. I need to rename the columns within each to reflect the names of each data frame (I'll be performing an outer merge of all of these afterwards).

Let's say the data frames are called df1, df2 and df3, and each contains the columns name, date, and count.

I'd like to rename each of the columns in df1 into name_df1, date_df1, and count_df1.

I've written a function to rename the columns, thus: 

df_list=[df1, df2, df3]

def rename_cols():
    col_name="name"+suffix
    col_count="count"+suffix
    col_date="date"+suffix

for x in df_list:
    if x['name'].tail(1).item() == df1['name'].tail(1).item():
        suffix="_"+"df1"
        rename_cols()
        continue
    elif x['name'].tail(1).item() == df2['name'].tail(1).item():
        suffix="_"+"df2"
        rename_cols()
        continue
    else:
        suffix="_"+"df3"
        rename_cols()

    col_names=[col_name,col_date,col_count]
    x.columns=col_names

Unfortunately, I get the following error: KeyError: 'name'

I'm really struggling to figure out why that's going on. The columns for df1, the first data frame in the df_list, gets renamed. Everything else stays the same... Am I messing up basic syntax (probably), or is there a fundamental misunderstanding that I've got of how things should work?

From what I can ascertain, the first data frame in the list is being iterated through more than once — but why would that be the case?

Upvotes: 11

Views: 59920

Answers (5)

W Kenny
W Kenny

Reputation: 2089

A more simple way

Get total length from cursor.description Then convert it into list Apply the list directly into DF

num_fields = len(cursor.description)
field_names = [ i[0] for i in cursor.description ]
df.columns = field_names

Upvotes: 0

Hisham Sajid
Hisham Sajid

Reputation: 301

My preferred rather simple way of doing this, especially when you want to apply some logic to all column names is:

for col in df.columns:
    df.rename(columns={col:col.upper().replace(" ","_")},inplace=True)

Upvotes: 12

majr
majr

Reputation: 373

For completeness, since nobody has mentioned df.rename, see Andy Hayden's answer here:

Renaming columns in pandas

df.rename can take a function as an argument, so in this case:

df_dict = {'df1':df1,'df2':df2,'df3':df3}
for name,df in df_dict.items():
    df.rename(lambda x: x+'_'+name, inplace=True)

Upvotes: 3

maxymoo
maxymoo

Reputation: 36555

I'll suppose that you have your stored in a dictionary as this is the idiomatic way of storing a series of named objects in Python. The idiomatic pandas way of changing your column names is to use a vectorised string operation on df.columns:

df_dict = {"df1":df1, "df2":df2, "df3":df3}
for name, df in df_dict.items():
   df.columns = df.columns + "_" + name

Another option to consider is adding the suffixes automatically during the merge. When you call merge you can specify the suffixes that will be appended to duplicate column names with the suffixes parameter. If you just want to append the names of the dataframes, you can call it like this. :

from itertools import reduce
df_merged = reduce(lambda x,y: ("df_merged", 
                               x[1].merge(y[1], left_index=True, right_index=True, 
                                         suffixes = ("","_"+y[0]))),
                   df_dict.items())[1]

Upvotes: 4

mgc
mgc

Reputation: 5443

I guess you can achieve this with something simplier, like that :

df_list=[df1, df2, df3]
for i, df in enumerate(df_list, 1):
    df.columns = [col_name+'_df{}'.format(i) for col_name in df.columns]

If your DataFrames have prettier names you can try: 

df_names=('Home', 'Work', 'Park')
for df_name in df_names:
    df = globals()[df_name]
    df.columns = [col_name+'_{}'.format(df_name) for col_name in df.columns]

Or you can fetch the name of each variable by looking up into globals() (or locals()) : 

df_list = [Home, Work, Park]
for df in df_list:
    name = [k for k, v in globals().items() if id(v) == id(df) and k[0] != '_'][0]
    df.columns = [col_name+'_{}'.format(name) for col_name in df.columns]

Upvotes: 13

Related Questions