CODEWITHSUNDEEP
pythondataframedatetime
user6308605
user6308605

Reputation: 731

extract substring and convert to datetime python

I have a dataframe, df:

data = [{0: 18, 1: '(Responses) 17th Nov 20'},
        {0: 304, 1: '(Responses) 17th Nov 20'},
        {0: 1177, 1: '(Responses) 17th Nov 20'},
        {0: 899, 1: '(Responses) 17th Nov 20'}]

df = pd.DataFrame(data)

0                1                                          
18    (Responses) 17th Nov 20
304   (Responses) 17th Nov 20
1177  (Responses) 17th Nov 20
899   (Responses) 17th Nov 20

Is there any efficient way to extract out 17th Nov 2020 and make it to a new column[2] as 17-11-2020 as date?

It can also be 1st or 2nd or 3rd for other date.

Expected output:

0                1                 2                         
18    (Responses) 17th Nov 20   17-11-2020
304   (Responses) 17th Nov 20   17-11-2020
1177  (Responses) 17th Nov 20   17-11-2020
899   (Responses) 17th Nov 20   17-11-2020

Upvotes: 0

Views: 433

Answers (2)

U13-Forward
U13-Forward

Reputation: 71580

Try using str.split and pd.to_datetime:

df[2] = pd.to_datetime(df[1].str.replace('\(Responses\) ', ''))
print(df)

Output:

      0                        1          2
0    18  (Responses) 17th Nov 20 2020-11-17
1   304  (Responses) 17th Nov 20 2020-11-17
2  1177  (Responses) 17th Nov 20 2020-11-17
3   899  (Responses) 17th Nov 20 2020-11-17

Upvotes: 1

ljuk
ljuk

Reputation: 784

Just split your string with "(responses)" as your keyword, and then get the second element after split:

  df['new_column'] = df['1'].str.split("(responses)").str[1]

Upvotes: 0

Related Questions