Pandas。删除单元格并将其他单元格左移的好方法？

Question

在dataframe中，我需要删除一些单元格，并将行中的其他单元格左移：

df=pd.DataFrame({'X0':['anytext','anytext','anytext','anytext','anytext'],
                 'X1':['12:40','boss','engen','15:44','16:01'],
                 'X2':['anytext','12:44','14:06','anytext','anytext'],
                 'X3':['anytext','anytext','anytext','anytext','anytext']})

 df
  
        X0     X1       X2       X3
0  anytext  12:40  anytext  anytext
1  anytext   boss    12:44  anytext
2  anytext  engen    14:06  anytext
3  anytext  15:44  anytext  anytext
4  anytext  16:01  anytext  anytext

我想删除“boss”和“engen”，并将第行中的其他单元格左移：

        X0     X1       X2       X3
0  anytext  12:40  anytext  anytext
1  anytext  12:44  anytext      NaN
2  anytext  14:06  anytext      NaN
3  anytext  15:44  anytext  anytext
4  anytext  16:01  anytext  anytext

Answer 1

您需要选择用于移位的行，例如，这里测试X1中的前2个值是否为str[:2]的数值，以及^{}是否为~的反转掩码，因此仅对非数值使用^{}：

m = ~df['X1'].str[:2].str.isnumeric()

mask，Thankyou@Manakin的另一个想法是以HH:MM格式测试日期时间：

m = pd.to_datetime(df['X1'],format='%H:%M',errors='coerce').isna()

另外，如果要测试长度为2的:数字2：

m = ~df['X1'].str.contains('^\d{2}:\d{2}$')

---

df[m] = df[m].shift(-1, axis=1)
print(df)
      X1       X2       X3
0  12:40  anytext  anytext
1  12:44  anytext      NaN
2  14:06  anytext      NaN
3  15:44  anytext  anytext
4  16:01  anytext  anytext

如果需要修改X1一个想法之后的所有列：

df=pd.DataFrame({'X0':['anytext','anytext','anytext','anytext','anytext'],
                 'X1':['12:40','boss','engen','15:44','16:01'],
                 'X2':['anytext','12:44','14:06','anytext','anytext'],
                 'X3':['anytext','anytext','anytext','anytext','anytext']}) 

m = ~df['X1'].str.contains('^\d{2}:\d{2}$')
df.loc[m, 'X1':] =df.loc[m, 'X1':].shift(-1, axis=1)
print(df)
       X0     X1       X2       X3
0  anytext  12:40  anytext  anytext
1  anytext  12:44  anytext      NaN
2  anytext  14:06  anytext      NaN
3  anytext  15:44  anytext  anytext
4  anytext  16:01  anytext  anytext

另一个使用convertX0转换为索引：

df = df.set_index('X0')
m = ~df['X1'].str.contains('^\d{2}:\d{2}$')
df[m] = df[m].shift(-1, axis=1)
df = df.reset_index()
print(df)
        X0     X1       X2       X3
0  anytext  12:40  anytext  anytext
1  anytext  12:44  anytext      NaN
2  anytext  14:06  anytext      NaN
3  anytext  15:44  anytext  anytext
4  anytext  16:01  anytext  anytext