从长度不均匀的文本字符串中拆分日期时间值

由 psed发布于 2020-05-09 05:56:32

我需要将数据和时间字符串从具有不规则定界符行的数据框中的列中分离出来，即有些带有三个逗号，有些带有四个逗号。

我正在使用Python3，pandas

例：

df['sample field'].head(2)

退货

4457-I need, this, date, Nov 11 2013 12:00AM ,
2359-I need this, date, Apr 11 2013 12:00AM ,

I am trying to figure out how to strip the date and time values: 'Nov 11 2013 12:00AM', and 'Apr 11 2013 12:00AM' off the back of these two records in a column into a new column given the second row has fewer commas. Using:

df['sample field'].str.split(",", expand=True)

将数据扩展到不同的列，并交错显示日期。我在一列中需要日期和时间（甚至只是日期）信息，以便可以在进一步分析中使用日期值。

bby

2020-05-09 05:56:32

数据

df=pd.DataFrame({'Text':['4457-I need, this, date, Nov 11 2013 12:00AM ,','2359-I need this, date, Apr 11 2013 12:00AM ,']})
df

将df.extract与正则表达式一起使用

df['Date']= df.Text.str.extract('([A-Za-z]+\s+\d+\s+\d+\s+\d+:[0-9A-Z]+(?=\s+\,+))')
df

df.Date=pd.to_datetime(df.Date).dt.strftime('%b %d %Y %H:%M%p')

fillum

2020-05-09 05:56:32

IIUC you need str.extract with a regular expression.

Regex Demo Here

print(df)

                                              0
0  4457-I need, this, date, Nov 11 2013 12:00AM
1  2359-I need this, date, Apr 11 2013 12:00AM 

df['date'] = df[0].str.extract('(\w{3}\s\d.*\d{4}\s\d{2}:\d{2}\w{2})')

df['date']  = pd.to_datetime(df['date'] ,format='%b %d %Y %H:%M%p')

print(df)

                                              0                date
0  4457-I need, this, date, Nov 11 2013 12:00AM 2013-11-11 12:00:00
1  2359-I need this, date, Apr 11 2013 12:00AM  2013-04-11 12:00:00

psed

这家伙很懒，什么都没留下

积分
0
话题
0
评论
3233
注册排名
1525