Write modes in Python Pandas

 Different Write modes in Pandas

1. Write Mode (mode='w'): Creates a new file or overwrites an existing file.
2. Append Mode (mode='a'): Opens an existing file to append new data.
3. Error (if_sheet_exists='error'): Raises an error if the sheet exists.
4. New (if_sheet_exists='new'): Creates a new sheet if the sheet exists.
5. Replace (if_sheet_exists='replace'): Replaces the existing sheet with new data.

# Write the DataFrame to an Excel file in append mode and replace the sheet if it exists
with pd.ExcelWriter('file.xlsx', mode='a', if_sheet_exists='replace') as writer:
    df.to_excel(writer, sheet_name='Sheet1', index=False)

# Example 
import os
import pandas as pd
from openpyxl import load_workbook

# Define the folder containing the Excel files
folder_path = 'path_to_your_folder'

# Define the sheet names and columns to copy (using column indices)
sheet_name_source = 'Inspection Sheet'  # Source sheet name
sheet_name_target = 'Processed Data'  # Target sheet name
columns_to_copy_indices = [2, 3]  # Replace with your column indices (0-based)

# Iterate through each file in the folder
for filename in os.listdir(folder_path):
    if filename.endswith('.xlsx'):
        file_path = os.path.join(folder_path, filename)
        
        # Read the source sheet into a DataFrame
        df_source = pd.read_excel(file_path, sheet_name=sheet_name_source)

        # Select specific columns to copy using column indices
        df_selected_columns = df_source.iloc[:, columns_to_copy_indices]

        # Read the target sheet into a DataFrame
        df_target = pd.read_excel(file_path, sheet_name=sheet_name_target)

        # Concatenate the target DataFrame with the selected columns DataFrame along the columns (axis=1)
        df_merged = pd.concat([df_target, df_selected_columns], axis=1)

        # Write the merged DataFrame back to the target sheet
        with pd.ExcelWriter(file_path, engine='openpyxl', mode='a', if_sheet_exists='replace') as writer:
            df_merged.to_excel(writer, sheet_name=sheet_name_target, index=False)

        print(f"Processed file: {filename}")
        print(f"Merged columns {columns_to_copy_indices} from '{sheet_name_source}' into '{sheet_name_target}' at the last column")

print("Processing complete for all files.")