CODEWITHSUNDEEP
pythonpandasdataframe
Aman Agarwal
Aman Agarwal

Reputation: 11

insert data frame in excel file sheet but df.to_excel is deleting other sheets of excel file

def write_sample_data(l):
   pp = os.path.join('sample_data.xlsx')
        for i in l:
            ll = []
            df = pd.read_excel('sample_data.xlsx'),sheet_name=i)
            columns = list(df.columns)
            for j in range(0,len(columns)):
                if '&' not in columns[j]:
                    ll.append('XXXX')
                else:
                    ll.append('[XXXX.1,XXXX.1]')

        df.loc[len(df)] = ll
        df.to_excel(pp, sheet_name=i, index=False)

Once i execute above code then it writes the required df in first sheet but deletes the other sheet. Pls help me on this.

Upvotes: 0

Views: 44

Answers (1)

tianlinhe
tianlinhe

Reputation: 989

First, you should avoid naming input and output with the same name to avoid confusion.

Given that the rest of your codes work properly, pd.ExcelWriter should be a good tool to write on multiple excel sheets. Remember to create a sheet name i inside your for loop:

def write_sample_data(l):
    pp = os.path.join('sample_data_out.xlsx')
    writer = pd.ExcelWriter(pp,engine ='xlsxwriter') 
    for i in l:
        ll = []
        df = pd.read_excel('sample_data.xlsx',sheet_name=i)
        columns = list(df.columns)
        for j in range(0,len(columns)):
            if '&' not in columns[j]:
                ll.append('XXXX')
            else:
                ll.append('[XXXX.1,XXXX.1]')

        df.loc[len(df)] = ll
        df.to_excel(writer, sheet_name=i, index=False)
    writer.save()

I am not sure if it works though, as you did not allude what l actually is. I guess it is a list of strings corresponding to sheet name in your input xlsx?

Upvotes: 1

Related Questions