将一列拆分为两列，留下空格

Question

我在pandas中有一个这样格式的数据帧

(df)
School ID      Column 1 
School 1       AD6000         
School 2       3000TO4000      
School 3       5000TO6000      
School 4       AC2000         
School 5       BB3300        
School 6       9000TO9900      
....

我要做的就是将第1列中包含单词“to”作为分隔符的行拆分为两个新列，同时保留原始列。结果就是这样

(df)
School ID      Column 1          Column 2     Column 3
School 1       AD6000            NaN          NaN
School 2       3000TO4000        3000         4000
School 3       5000TO6000        5000         6000
School 4       AC2000            NaN          NaN
School 5       BB3300            NaN          NaN
School 6       9000TO9900        9000         9900
....

这是我认为有效的代码，但结果是在第2列和第3列中留下空格，而不是将to左右的数字拆分为各自的列

df[['Column 2','Column 3']] = df['Column 1'].str.extract(r'(\d+)TO(\d+)')

谢谢你的帮助

Answer 1

这是因为右侧是一个具有不同列名（0，1）的数据帧，Pandas在该数据帧中找不到Column 2或Column 3

您可以传递基础numpy数组而不是数据帧：

df[['Column 2','Column 3']] = df['Column 1'].str.extract(r'(\d+)TO(\d+)').values

输出：

  School ID    Column 1 Column 2 Column 3
0  School 1      AD6000      NaN      NaN
1  School 2  3000TO4000     3000     4000
2  School 3  5000TO6000     5000     6000
3  School 4      AC2000      NaN      NaN
4  School 5      BB3300      NaN      NaN
5  School 6  9000TO9900     9000     9900

Answer 2

使用

new = df["Column 1"].str.split("TO", n = 1, expand = True)

并为结果列指定新名称

df["Col1"]= new[0] 
df["Col2"]= new[1]