您可以按read_excel
键按原样加载Electron 表格,然后按post-process键将其加载:
raw = pd.read_excel("file.xlsx")
idx_names = ["Student Name", "Class", "Percent Attendance"]
left = raw.iloc[2:, :3].set_axis(idx_names, axis=1)
col_mux = pd.MultiIndex.from_arrays(raw.iloc[:2, 3:].ffill(axis=1).to_numpy())
right = raw.iloc[2:, 3:]
df = (pd.concat([left, right], axis=1)
.set_index(idx_names).set_axis(col_mux, axis=1))
发帖主题:Re:Kolibrios
# df.index.nlevels # 2
# df.columns.nlevels # 3
print(df)
19/9/2023 20/9/2023
AM PM P1 P2 P3 P4 P5 AM PM P1 P2 P3
Student Name Class Percent Attendance
Student A 7M 100% / \ / / / NaN / / \ / / /
Student B 12H 100% / \ NaN / N NaN NaN / \ / / /
Student C 8R 100% / \ / / / NaN NaN / \ / / /
[3 rows x 12 columns]