Fetch and append player statistics to an Excel spreadsheet

Question

I am a new programmer in python, and I need a bit of help to structure my code.

game_schedule = (
 ('Daily Game Schedule', 'daily_game_schedule'), 
 ('Full Game Schedule', 'full_game_schedule'),
)
team_standings = (
 ('Overall Team Standings', 'overall_team_standings'),
 ('Playoff Team Standings', 'playoff_team_standings'), 
)
worksheets = (
 ('Game Schedule', game_schedule),
 ('Cumulative Player Stats', 'cumulative_player_stats'),
 ('Player Injuries', 'player_injuries'),
 ('Team Standings', team_standings),
)
def create_and_update_worksheets():
 """
 Add 'Player statistics' if the worksheet is not in file_name. 
 Otherwise, it will update the worksheet itself.
 """
 os.chdir(os.path.dirname(os.path.abspath(__file__)))
 writer = pd.ExcelWriter(file_name, engine='openpyxl')
 for key, value in worksheets:
 start_row = 0
 if isinstance(value, tuple):
 for subkey, subvalue in value:
 response = send_request('2017-2018-regular', subvalue).content
 df1 = pd.DataFrame(['Title'])
 df2 = pd.read_csv(io.StringIO(response.decode('utf-8')))
 df1.to_excel(writer, key, startrow=start_row, header=None, \
 index=False)
 df2.to_excel(writer, key, startrow=start_row+2, index=False)
 start_row += len(df2) + 4
 else:
 response = send_request('2017-2018-regular', value).content
 df1 = pd.DataFrame(['Title'])
 df2 = pd.read_csv(io.StringIO(response.decode('utf-8')))
 df1.to_excel(writer, key, startrow=start_row, header=None, \
 index=False)
 df2.to_excel(writer, key, startrow=start_row+2, index=False)
 for sheet in writer.sheets.values():
 resize_columns(sheet)
 writer.save()
 writer.close()
create_and_update_worksheets()

I think there is repetitive code in the for loop for key, value in worksheets:. How can I change the structure so that it is not repetitive?

Answer 1

As we can see these piece of code is repeated:

response = send_request('2017-2018-regular', value).content
df1 = pd.DataFrame(['Title'])
df2 = pd.read_csv(io.StringIO(response.decode('utf-8')))
df1.to_excel(writer, key, startrow=start_row, header=None, \
 index=False)
df2.to_excel(writer, key, startrow=start_row+2, index=False)

you can define a function for it, it works like this:

def create_and_update_worksheets():
 def less_repetitive(value, start_row):
 response = send_request('2017-2018-regular', value).content
 df1 = pd.DataFrame(['Title'])
 df2 = pd.read_csv(io.StringIO(response.decode('utf-8')))
 df1.to_excel(writer, key, startrow=start_row, header=None, \
 index=False)
 df2.to_excel(writer, key, startrow=start_row+2, index=False)
 ...
 for key, value in worksheets:
 ...
 if isinstance(value, tuple):
 for subkey, subvalue in value:
 less_repetitive(subvalue, start_row)
 start_row += len(df2) + 4
 else:
 less_repetitive(subvalue, start_row)
 ...

Another solution is change the value in worksheets to be all tuple, but I am not sure if it works. As you didn't use the subkey, in such situation those value is not tuple now, can be changed to (('', value))

and as you don't need use subkey just replace it with _ code works like this:

def create_and_update_worksheets():
 ...
 for key, value in worksheets:
 ...
 if not isinstance(value, tuple):
 value = (('',value))
 for _, subvalue in value:
 response = send_request('2017-2018-regular', subvalue).content
 df1 = pd.DataFrame(['Title'])
 df2 = pd.read_csv(io.StringIO(response.decode('utf-8')))
 df1.to_excel(writer, key, startrow=start_row, header=None, \
 index=False)
 df2.to_excel(writer, key, startrow=start_row+2, index=False)
 start_row += len(df2) + 4
 ...

• J.Doe: I will show what is inside worksheets.
• Aries_is_there: or you can do things like this: if not isinstance(value, tuple): value = (('', value)) and if you don't need use subkey, just replace it with _
• J.Doe: Could you modify your answer in considering that comment? I think I will accept your answer.