如果只是复制数据，这是一个快速示例.
The Example sheet has two columns; 'Name' and 'Address' where;
Name includes "First" and "Last" name separated by space
Address includes "Street" "City" "State" and "Zip" separated by comma
示例代码读取源Excel表，将两列拆分成组成部分，并将结果写回目标表
import pandas as pd
### Read data from source sheet
df = pd.read_excel('source.xlsx', sheet_name='Sheet1')
### Split the necessary columns on the delimiters
df[['First', 'Last']] = df['Name'].str.split(' ', n=1, expand=True) # Delimiter is space
df[['Street', 'City', 'State', 'Zip']] = df['Address'].str.split(', ', n=3, expand=True) # Delimiter is comma
### Drop the now unnecessary columns
df = df.drop(['Name', 'Address'], axis=1)
### Reorder columns, probably not needed in this case but ensures the columns are in correct order.
df = df[['First', 'Last', 'Street', 'City', 'State', 'Zip']]
### Write to the destination sheet, start row 2 (startrow 1), drop index and headers
with pd.ExcelWriter('dest.xlsx', mode='a', engine='openpyxl', if_sheet_exists='overlay') as writer:
df.to_excel(writer, sheet_name="Sheet1", startrow=1, index=False, header=False)
结果数据帧
First Last Street City State Zip
0 Mavis West 421 E DRACHMAN TUCSON AZ 85705-7598
1 John Spurs 100 MAIN ST SEATTLE WA 98104
2 Jack East 105 KROME AVE MIAMI FL 33185 3700
In this example the destination Sheet contains the headers already on row 1 (note this is row 0 for to_excel). For this since writing to an existing sheet which already contains data (Headers) we use 'mode=a' (append) which requires Openpyxl as the engine.
Also to_excel write excludes the Headers from the dataframe. However if preferred the destination sheet could be empty and the headers written along with the column data.
