Extract multiple date/time values from text field into new variable columns-CodePudding

I have dataframe - see below. This is just a snippet of the full dateframe, there are more text and date/times in each respective rows/IDS. As you can see the text before and after each date/time is random.

ID        RESULT
1         Patients Discharged Home :   12/07/2022 11:19 Bob Melciv   Appt 12/07/2022 12:19 Medicaid...
2         Stawword Geraldio -    12/17/2022 11:00 Bob Melciv   Appt 12/10/2022 12:09 Risk Factors...

I would like to pull all date/times where the format is MM/DD/YYYY HH:MM from the RESULT column and make each of those respective date/times into their own column.

ID    DATE_TIME_1              DATE_TIME_2        DATE_TIME_3 .....
1     12/07/2022 11:19         12/07/2022 12:19
2     12/17/2022 11:00         12/10/2022 12:09

CodePudding user response：

How about:

\d{2}/\d{2}/\d{4} \d{2}:\d{2}

Of course this doesn't cover nonsensical dates such as 55/55/1023, but it should get you 99% of the way there.

CodePudding user response：

From @David542's regex, you can use str.extractall:

pattern = r'(\d{2}/\d{2}/\d{4} \d{2}:\d{2})'
out = pd.concat([df['ID'],
                 df['RESULT'].str.extractall(pattern).squeeze()
                             .unstack().rename(columns=lambda x: f'DATE_TIME_{x 1}')
                             .rename_axis(columns=None)], axis=1)
print(out)

# Output
   ID       DATE_TIME_1       DATE_TIME_2
0   1  12/07/2022 11:19  12/07/2022 12:19
1   2  12/17/2022 11:00  12/10/2022 12:09

A slightly modified version to convert extracted date/time to pd.DatetimeIndex:

pattern = r'(\d{2}/\d{2}/\d{4} \d{2}:\d{2})'
out = pd.concat([df['ID'],
                 df['RESULT'].str.extractall(pattern).squeeze().apply(pd.to_datetime)
                             .unstack().rename(columns=lambda x: f'DATE_TIME_{x 1}')
                             .rename_axis(columns=None)], axis=1)
print(out)

# Output
   ID         DATE_TIME_1         DATE_TIME_2
0   1 2022-12-07 11:19:00 2022-12-07 12:19:00
1   2 2022-12-17 11:00:00 2022-12-10 12:09:00