There seems to be an issue with the 'Securities_CUSIP' columnin that its not populating the columns values in the printed results but it is being read in correctly
selected_columns = ['Securities_CUSIP', 'SecurityTransactions_PriceBloomberg', 'SecurityTransactions_PriceIDC', 'SecurityTransactions_PriceReuter', 'SecurityTransactions_PriceMarkit', 'SecurityTransactions_PriceSP']
updated_sheets = {}
for sheet_name, df in all_sheets.items():
if 'Securities_CUSIP' in df.columns:
df_selected = df[selected_columns].copy()
df_selected['Securities_CUSIP'] = df_selected['Securities_CUSIP'].astype(str).fillna("")
# df_selected.dropna(subset = ['Securities_CUSIP'], inplace = True)
numeric_cols = [col for col in selected_columns if col != 'Securities_CUSIP']
if numeric_cols:
df_selected[numeric_cols] = df_selected[numeric_cols].apply(pd.to_numeric, errors = 'coerce')
df_selected = df_selected.apply(pd.to_numeric, errors = 'coerce')
df_selected['Max Value'] = df_selected.max(axis = 1, skipna = True)
df_no_zeros = df_selected.replace(0, np.nan)
df_selected['Min Value'] = df_no_zeros.min(axis = 1, skipna = True)
df_selected['Max Range'] = df_selected['Max Value'] - df_selected['Min Value']
updated_sheets[sheet_name] = df_selected
else:
print(f"Skipping {sheet_name}: Selected columns not found.")
print("\n" + "-"*50 + "\n")
with pd.ExcelWriter(output_file, engine = 'xlsxwriter') as writer:
for sheet_name, df in updated_sheets.items():
df.to_excel(writer, sheet_name = sheet_name, index = False)
print(f"Updated Excel file saved as: {output_file}")
I confirmed the values are in the dataframes when reading in the excel file, i believe it has something to do with dropna or something with some of the columns having numeric values and the "Securities_CUSIP' are not numeric