Excel数据处理

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}")