CODEWITHSUNDEEP
pythonpandasdataframe
Kartik Mehra
Kartik Mehra

Reputation: 107

Pandas save duplicate excel sheets with ExcelWriter

I am trying to save multiple dataframe as a separate sheet in an excel notebook. For this, my code looks like this

output_file = "normalized_desertrose.xlsx"
writer = pd.ExcelWriter(output_file, engine='xlsxwriter')

sheet_names = ['Stem', 'Caudex', 'Buds8', 'Buds10', 'Buds12', 'Budsgrowth8', 
           'Budsgrowth10', 'Budsgrowth12', 'Leaves8', 'Leaves10', 'Leaves12', 
           'Leafarea', 'Flowerbud day1', 'Flowerbud day2', 'Bloom', 'Total Flower']

for sheet in sheet_names:
    df = pd.read_excel('desertrose_ed.xlsx',shee_name=sheet)
    print("Sheet is {}".format(sheet))
    print("--------------------------")
    
    x = df.values
    cols = df.columns
    min_max_scaler = preprocessing.MinMaxScaler()
    x_scaled = min_max_scaler.fit_transform(x)
    df = pd.DataFrame(x_scaled,columns=cols)
    df.to_excel(writer,sheet_name=sheet)
    for col in cols:
        print("Column is {}".format(col))
        mean = df[col].mean()
        std = np.std(df[col])
        if mean == 0:
            variation = 0.0
        else:
            variation = (std*1.0)/mean
        standard_error = std/math.sqrt(len(df))
        print("Mean {}\nStandard deviation {}\nCofficient of variation {}\nStandard error {}\n".format(mean,std,variation,standard_error))    

writer.save()

I see different sheets in my output excel notebook, but they all have same data.

Can anyone let me know where I am committing the mistake ? I think I am missing something, just not sure where.

Upvotes: 0

Views: 589

Answers (1)

Celius Stingher
Celius Stingher

Reputation: 18367

Instead of reading the excel file multiple times, I suggest you read it once and access the values in the dictionary:

dfs = pd.read_excel('desertrose_ed.xlsx',sheet_name=None)
for sheet in dfs.keys():
    print("Sheet is {}".format(sheet))
    print("--------------------------")
  
    x = dfs[sheet].values
    cols = dfs[sheet].columns
    min_max_scaler = preprocessing.MinMaxScaler()
    x_scaled = min_max_scaler.fit_transform(x)
    df = pd.DataFrame(x_scaled,columns=cols)
    df.to_excel(writer,sheet_name=sheet)
    for col in cols:
        print("Column is {}".format(col))
        mean = df[col].mean()
        std = np.std(df[col])
        if mean == 0:
            variation = 0.0
        else:
            variation = (std*1.0)/mean
        standard_error = std/math.sqrt(len(df))
        print("Mean {}\nStandard deviation {}\nCofficient of variation {}\nStandard error {}\n".format(mean,std,variation,standard_error))    

writer.save()

Upvotes: 1

Related Questions