Write CSV content to Excel produces empty sheets

Question

Writing DataFrame to excel file leaves sheets with zero data.

I am creating a Robotics "Scouting application". It receives multiple .csv files throughout the coarse of two days. The csv files will be named with a four digit number plus a hyphen and then a match number. For example "2073-18.csv". Multiple files for each team will arrive. I need one sheet for each team with the content of each csv file on the same sheet for that team. Creating the sheets works, writing the data to these sheets doesn't.

import os
import glob
import csv
from xlsxwriter.workbook import Workbook
import pandas as pd
import numpy as np
#from sqlalchemy import create_engine
from openpyxl import load_workbook

os.chdir ("/EagleScout")
path = '.'
extension = 'csv'
engine = 'xlsxwriter'

files_in_dir = [ f for f in glob.glob('*.csv')]

workbook = Workbook('Tournament.xlsx')

with pd.ExcelWriter('Tournament.xlsx') as writer:
    for csvfile in files_in_dir:
        df = pd.read_csv(csvfile)
        fName, fExt = (os.path.splitext(csvfile))
        sName = fName.split('-')
        worksheet = workbook.get_worksheet_by_name(sName [0])

        if worksheet is None:
            worksheet = workbook.add_worksheet(sName [0]) #workseet with csv file name

        df.to_excel(writer, sheet_name = (sName[0]))

    writer.save()

workbook.close()

What I need is one workbook with one sheet for each team, up to 70 teams. Each sheet will have multiple rows, one for each csv file that arrived for that team. The question is, how do I get Pandas, or other libraries, to write the content of each csv file to it's appropriate sheet in the workbook?

Answer 1

OK, with the input from @ivan_pozdeev, I finally got past my issues. Remember, my original desire was to generate a script that could be run on a regular basis and generate a spreadsheet with multiple worksheets. Each worksheet would contain all the data from the .csv files for every match that had played, and grouped by the team number. I have also added a single spreadsheet that contains the raw data. Here is what I came up with:

import os
import glob
import csv
import xlsxwriter
from xlsxwriter.workbook import Workbook
import pandas as pd
import numpy as np
#from sqlalchemy import create_engine
#import openpyxl
#from openpyxl import load_workbook

os.chdir ("/EagleScout")
path = '.'
extension = 'csv'


# Remove the combined .csv file from previous runs
#This will provide clean date without corruption from earlier runs
if os.path.exists('./Spreadsheets/combined.csv'): 
    os.remove ('./Spreadsheets/combined.csv')

#Remove previous Excel spreadsheet
if os.path.exists('./Spreadsheets/Tournament.xlsx'): 
    os.remove ('./Spreadsheets/Tournament.xlsx')


#Remove sorted combined csv
#Remove previous Excel spreadsheet
if os.path.exists('./Spreadsheets/Combined.xlsx'): 
    os.remove ('./Spreadsheets/Combined.xlsx')


#Read in and merge all .CSV file names
files_in_dir = [ f for f in glob.glob('*.csv')] 


#Create a single combined .csv file with all data
#from all matches completed so far.
d1 = pd.read_csv('Header.txt')
d1.to_csv('./Spreadsheets/combined.csv', header = True, index = False)

for filenames in files_in_dir: 
    df = pd.read_csv(filenames)
    fName, fExt = (os.path.splitext(filenames))
    sName = fName.split('-')
    N=(sName[1])
    df.insert(0,N,N,True)
    df.to_csv('./Spreadsheets/combined.csv', index_label = (sName[0]), mode = 'a')


#Combine all csv files into one master Raw Excel Data file
#and add column headers as labels
with pd.ExcelWriter('./Spreadsheets/Combined.xlsx') as writer:
    dt = pd.read_csv('./Spreadsheets/combined.csv')
    dt.to_excel(writer, sheet_name = 'All data')

    writer.save()



#Parse through all .CSV files and append content to appropriate team worksheet.
with pd.ExcelWriter('./Spreadsheets/Tournament.xlsx') as writer:

    df2 = pd.read_excel('./Spreadsheets/Combined.xlsx')
    group = df2.groupby('Team')
    for Team, Team_df in group:

        Team_df.to_excel(writer, sheet_name = str(Team))


    writer.save()

I am certain there is a cleaner way to do this code, I'm still new at this, but for now it does what I expect.