I have a table of data in my csv. I want to reduce the data_type in my csv to 27 types according to the text file. After the filter, I want the Excel to contain 27 pages with each data type on each page. Now I want to make a summary page with the first column containing the names of the data types and the second column containing the total amount of rows of each data type on their respective pages. For the last step, I want my summary page to be arranged as the first page. My codes seem to work fine until the last step whereas the summary page cannot be placed to the first page. import pandas as pd # Load the CSV file into a DataFrame df = pd.read_csv('my_file.csv') # Read the data types from a text file with open('data_types.txt', 'r') as file: data_types = file.read().splitlines() # Filter the DataFrame to include only the specified data types filtered_df = df[df['a'].isin(data_types)] # Create a separate sheet for each type of data writer = pd.ExcelWriter('filtered_data.xlsx', engine='xlsxwriter') summary_data = [] for data_type in data_types: # Filter the DataFrame for the current data type type_filtered_df = filtered_df[filtered_df['a'] == data_type] # Write the filtered data to a new sheet in the Excel file type_filtered_df.to_excel(writer, sheet_name=f'Type_{data_type}', index=False) # Append the summary data summary_data.append([data_type, len(type_filtered_df)]) # Create the summary DataFrame summary_df = pd.DataFrame(summary_data, columns=['Data Type', 'Total Rows']) # Add a header row for both columns in the summary DataFrame summary_df_header = pd.DataFrame([['Header']], columns=['Data Type']) summary_df = pd.concat([summary_df_header, summary_df]) # Save the DataFrame to the 'Summary' sheet summary_df.to_excel(writer, sheet_name='Summary', index=False) # Access the workbook and summary sheet workbook = writer.book summary_sheet = writer.sheets['Summary'] # Merge the cells for the header row summary_sheet.merge_range('A1:B1', 'Header') # Reorder the sheets so that the Summary sheet is the first sheet sheets = workbook.sheetnames summary_sheet_index = sheets.index('Summary') sheets.insert(0, sheets.pop(summary_sheet_index)) for i, sheet_name in enumerate(sheets): workbook.set_sheet_order(sheet_name, i) # Save and close the Excel file writer.save() Continue reading...
