0
\$\begingroup\$

I'm running into a large bottleneck in my program that takes hours to perform.

I have a Dataframe that is very large. I need to take the columns of the Dataframe and create new columns within same Dataframe. The new columns need to grouped by a specific date once grouped they are ranked. After they are ranked they are divided by the total number of values in that day (this number is stored in counts_date). This gives me a range of 0-1. The dataframe is a mulitindex with date as the level 0 and a unique id is level 1.

Is there a way to do this so I don't have to do it column by column and still create new columns?

df=pd.read_hdf('data.h5')

ranks2=list(df.columns.values)


for i in ranks2:
    df[i+'_rank']=df.groupby('date')[i].rank()

for i in ranks2:       
    df[i+'_rank']=df[i+'_rank']/df['counts_date']
\$\endgroup\$
1
\$\begingroup\$

I don't know if I fully understand the end goal, it's difficult to tell without some sample data, however there are certainly better ways to make this call.

I noticed the manipulations over each column could be simplified to a Pandas apply, so that's what I went for.

suffixed = [i + '_rank' for i in df.columns]

g = df.groupby('date')

df[suffixed] = df[df.columns].apply(lambda column: g[column.name].rank() / df['counts_date'])

There could be a way to precompute the group ranks and then concatenate those columns straight to the original, but I didn't attempt that.

Apply will almost always be faster than a for loop.

\$\endgroup\$