0

I am trying to read information from the following Excel workbook: Capacitor Workbook and overwrite the columns in a second workbook: Second Excel Workbook with the gathered data. The reason for this is that the second workbook lists capacitors using a unique device ID (I have blacked these values out) which I have to manually lookup in another program in order to find a capacitor ID that I can then search for settings by as our capacitor database only lists capacitors by their CAP ID and not by their device ID.

The data I need to extract is the Controller Name in column 4, and the value associated with the respective ON state and OFF state. For a Current controlled capacitor the setting code to determine on and off state are simply named "ON" and "OFF", while for a Temperature controlled capacitor the setting code is indicated by "TEMP ON" or "TEMP OFF". How would I go about having a prompt for an ID come up, input the id, and having the script "read" through the excel workbook for the values associated with that capacitor ID?

I would then like this extracted data to overwrite the data in another workbook with the column names "MODULE1CAPSWITCHCLOSEVALUE" which indicates the ON state, "MODULE1CAPSWITCHTRIPVALUE" which indicates the OFF state, and "PRIMARYCONTROLMODE" which will be either "TEMP" for temperature controlled or "VOLTS" for Current controlled as indicated in sheet/workbook one.

Do I have to create a new dataframe for the second workbook in order to overwrite the data? I am not sure how to go about this.

Any help is appreciated. Thank you.

What I was expecting to happen would be to input the cap ID, then have this auto lookup the associated settings with this CAP ID and populate the second workbook with these settings (overwriting the blank settings now there). I am not sure how to overwrite the data in the second sheet.

import pandas as pd

# Prompt for the Capacitor ID
id_number = input("Enter the CAP ID number: ")

# Load the source Excel workbook into a DataFrame
source_df = pd.read_excel(r'C:\Users\x9006\Documents\SWCAPS.xlsx')

# Create a new DataFrame for the new workbook
new_df = pd.DataFrame(data, columns=['CAP ID', 'Controller Name', 'Setting Code', 'Controller Name', 'Value'])

# Iterate through rows in the source DataFrame
for index, row in source_df.iterrows():
    if row['ID'] == id_number:
        setting_type = row['CONTROLLER_NAME']
        value = row['VALUE_NUM']
        
        # Process setting type
        if setting_type == "CURRENT":
            new_data.append([id_number, "VOLTS", value])
        elif setting_type == "TEMPERATURE":
            new_data.append([id_number, "TEMP", value])
        
        # Process ON/OFF values
        if row['ON'] == "ON":
            new_data.append([id_number, "ON", row['VALUE_NUM']])
        elif row['OFF'] == "OFF":
            new_data.append([id_number, "OFF", row['VALUE_NUM']])
        elif row['TEMP ON'] == "TEMP ON":
            new_data.append([id_number, "TEMP ON", row['VALUE_NUM']])
        elif row['TEMP OFF'] == "TEMP OFF":
            new_data.append([id_number, "TEMP OFF", row['VALUE_NUM']])

# Create a new DataFrame from the processed data
new_df = pd.DataFrame(new_data, columns=["ID", "Setting Type", "Value"])

# Save the new DataFrame to second Excel workbook
new_df.to_excel('Synergi Settings.xlsx', index=False)

print("Data has been processed and saved in 'Synergi Settings.xlsx'")`
Shy
  • 3
  • 3
  • Refrain from using a link for your data. Your question needs a minimal reproducible example consisting of sample input, expected output, actual output, and only the relevant code necessary to reproduce the problem. See [How to make good reproducible pandas examples](https://stackoverflow.com/questions/20109391/how-to-make-good-reproducible-pandas-examples) for best practices related to Pandas questions. – itprorh66 Aug 18 '23 at 21:37

1 Answers1

0

As already suggested in the comments, you should try to create an easily reproducable example going forward. Most of the information provided is redundant to the core question: How to adjust values of an existing Excel File in place.

The answer is quite simple: Use an ExcelWriter in mode 'append' and instruction to 'replace' the sheet if if already exists: pandas ExcelWriter

import pandas as pd
import os

# Just gets the directory path of the executed python script
BASE_PATH = os.path.split(os.path.abspath(__file__))[0]
EXCEL_PATH = os.path.join(BASE_PATH, 'my-excel.xlsx')
# Replace with EXCEL_PATH = 'your-excel-path'
SHEET_NAME = 'existing_sheet'

# This data will overwrite the data of the existing sheet
new_df = pd.DataFrame(dict(a=[1, 2, 3], b=[4, 5, 6]))

with pd.ExcelWriter(EXCEL_PATH, 'openpyxl', mode='a',
                    if_sheet_exists='replace') as writer:
    new_df.to_excel(writer, sheet_name=SHEET_NAME)

Your question: 'Do I have to create a new dataframe for the second workbook in order to overwrite the data?'

  • Actually no, you do not have to do it as per example above. You could also use a library like openpyxl to manipulate the data of the Excel Object itself, without having it in a separate DataFrame.

However, pandas will make your manipulations and processing much easier.

xArbisRox
  • 91
  • 5