import pandas as pd
Define file paths
original_file_path = ‘test.xlsx’
new_file_path = ‘new_file.xlsx’
Load the original Excel file
df = pd.read_excel(original_file_path, sheet_name=‘Sheet1’, header=3)
Required columns
required_columns = [‘DIDs (HEX)’, ‘Name’, ‘Byte’]
Check for missing columns
missing_columns = set(required_columns) - set(df.columns)
if missing_columns:
raise ValueError(f"Missing columns in the data: {missing_columns}")
Subset the dataframe to the required columns
df_subset = df[required_columns]
print(df_subset)
Save the subset to a new Excel file
df_subset.to_excel(new_file_path, index=False, sheet_name=‘mid’)
Load the newly created file and drop the first row
df1 = pd.read_excel(new_file_path, sheet_name=‘mid’)
df1 = df1.drop(index=0)
df1.to_excel(new_file_path, index=False, sheet_name=‘mid’)
Load the original file again with a different header to extract specific data
df2 = pd.read_excel(original_file_path, sheet_name=‘Sheet1’, header=4)
Select the required columns (22 and 2E)
selected_data = df2[[22, ‘2E’]]
print(selected_data)
Ensure the indices align before concatenation
df1.reset_index(drop=True, inplace=True)
selected_data.reset_index(drop=True, inplace=True)
Concatenate the dataframes along the columns
df_combined = pd.concat([df1, selected_data], axis=1)
df_combined.to_excel(new_file_path, index=False, sheet_name=‘mid’)
print(f"Data successfully combined and saved to {new_file_path}")