Add multiple csv in a single csv sheet in tabs using Pandas

Question

I have multiple csv files, lets say a.csv , b.csv . I want both a.csv and b.csv in a new c.csv . Both new csv should be in tabs inside as it is. I just want to add both csv in tabs . Is there any short way doing that in pandas. I have the below code with me but its not giving any output ,however its executing . Tried checking few posts in stackoverflow but somehow I did not got the wanted output. Please help me with some better solution.

folders=next(os.walk('.'))[1]
for host in folders:
    Path=os.path.join(os.getcwd(),host)                    
    all_files = glob.glob(os.path.join(Path, "*.csv"))     
    df_from_each_file = (pd.read_csv(f) for f in all_files)
    df   = pd.concat(df_from_each_file, ignore_index=True)
    df.to_csv('c.csv,' index=False)

Answer 1

A CSV file is really only ever a single sheet. An Excel xlsx file though does support sheets. Pandas can be made to write directly to an Excel file using an ExcelWriter. Something like the following should do what you need:

import pandas as pd
import xlsxwriter
import glob
import os

writer = pd.ExcelWriter('multi_sheet.xlsx', engine='xlsxwriter')
folders = next(os.walk('.'))[1]

for host in folders:
    Path = os.path.join(os.getcwd(), host)

    for f in glob.glob(os.path.join(Path, "*.csv")):
        print(f)
        df = pd.read_csv(f)
        df.to_excel(writer, sheet_name=os.path.basename(f)[:31])

writer.save()

For every CSV file that is found in your folders, a new Excel sheet will be created. The name of the sheet is the name of the CSV file, e.g. a.csv would have the sheetname a.

Note: Excel has a restriction of 31 characters for a sheet name.