1

I am completely new to Python and struggling to sort out many issues faced while writing code. I have generated a series of results from the CSV file after converting it into an excel file. Each file has produced a series of data. For instance, CSV file no.1 gives the output U1, V1, W1, X1, and file no 2 gives U2, V2, W2, X2, and so on. I want to export these files into an excel file as a number of rows. I made a dataframe to incorporate U, V, W, X & exported it to excel using 'to_Excel'. Unfortunately, I am getting only results from one file. You can see it from my attached screenshotenter image description here, which shows the excel file and the real output. Can anyone help to export all the results into excel in several rows (not on different sheets), rather than just the analysis from one CSV file? Also, I need to add one more piece of information to the final excel sheet, which is the excel file name.

I am seeking suggestions to solve the above issue. FOLLOWING IS MY SECTION OF PYTHON CODE RELATED TO THE EXPORT OF RESULTS TO AN EXCEL FILE

for excel_file in glob(r"C:\Users\sujith0327\pythonProject2\0 Nano Attempts*.xlsx"):

df_excel = pd.read_excel(excel_file)


max_depth_no = df_excel['Depth(nm)'].idxmax()


loading_part = df_excel.iloc[0:max_depth_no]
unloading_part = df_excel.iloc[max_depth_no:-1]


y = np.array(loading_part['Load (µN)'])
area_load = trapz(y, dx=0.01)


y = np.array(unloading_part['Load (µN)'])
area_unload = trapz(y, dx=0.01)


y = np.array(df_excel['Load (µN)'])
area = trapz(y, dx=0.01)


reversible_area = area_load - area_unload
irreversible_area = area_unload
total_area = reversible_area + irreversible_area

reversible_share = (reversible_area / total_area) * 100
irriversible_share = (irreversible_area / total_area) * 100



max_depth = df_excel['Depth(nm)'][max_depth_no]
remnant_depth = df_excel['Depth(nm)'].iat[-1]
max_load = df_excel['Load (µN)'][max_depth_no]
depth_recoverability = (1 - (remnant_depth / max_depth)) * 100

print(max_depth, remnant_depth, max_load, depth_recoverability, reversible_share, irriversible_share)

data = {'Max Load ((µN)': [max_load], 'Max Depth (nm)': [max_depth], 'Remnant Depth(nm)': [remnant_depth],
        'Depth Recoverability (%)': [depth_recoverability], 'Reversible Energy (%)': [reversible_share],
        'Irriversible Energy (%)': [irriversible_share]}
frame = pd.DataFrame(data)
print(data)

frame.to_excel("00 ALL RESULTS.xlsx")

Sujith Kumar S
  • 11
  • 2

1 Answers1

0

when saving to excel use this code

writer = pd.ExcelWriter(path, engine = 'xlsxwriter')
df1.to_excel(writer, sheet_name = 'x1')
df2.to_excel(writer, sheet_name = 'x2')
writer.save()
writer.close()

check this answer: https://stackoverflow.com/a/42375263/12009526

rektifyer
  • 51
  • 4
  • Thanks for the suggestion. I am not looking to add sheets on excel sheets. Instead, I need numbers of ROWS, each shows results from individual CSV files. – Sujith Kumar S Aug 23 '22 at 14:34