1
\$\begingroup\$

I am reading dataframe and writing exccel, I cannot use direct df.to_excel method as I am writing data in a very particular way.

Looping on dataframe is taking much time, could anything in below snippet be optimized.

When dataframe is having 9000 rows , loop is taking 90 seconds on local machine, please help.

def write_score_data(self,worksheet,year):
         row=3
         col=0

         df = self.data_df[self.data_df['YEAR']==year].sort('TYPE')

    player_list = list(df.PLAYER.unique())
    player_list.sort()

    for player in player_list:
        df_player=df[df['PLAYER']==player]
        worksheet.write(row,col,player)
        col+=1
        worksheet.write(row, col, df_player['PLAYER_NAME'].iloc[0])
        col+=1
        for sa_pair in self.strike_avg:
            df_pair = df_player[ (df_player['STRIKERATE']==sa_pair[1]) & (df_player['AVERAGE']==sa_pair[0])]
            for score_col in ['PREMATCH','MATCH','POSTMATCH']:
                cell_value = df_pair[score_col]
                if cell_value.empty:
                    cell_value = ' '
                else:
                    cell_value = cell_value.iloc[0]
                worksheet.write(row,col,cell_value)
                col +=1
        row=row+1
        col=0

My Sample dataframe is having structure as -

data_df
    PLAYER             PLAYER_NAME  SCORE  AVERAGE       MATCH    POSTMATCH  PREMATCH          STRIKERATE          TYPE    YEAR 
0   329                 Virat Kohli   70      62.89       100      80.0000      60               123.90             ODI     2008
\$\endgroup\$
  • \$\begingroup\$ I would need more data if I were to try and optimize this. But at the very least replace the outer loop with a groupby. \$\endgroup\$ – Stephen Rauch Nov 15 '17 at 1:29

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.