Is there a better way to split a pandas dataframe column based on some characters?

user_10138470 0
Viewed 159

How do I separate the dates in the Date column below. The separating characters are either Y,X or " "?

xx = {'Code': ["001", "002","003"], 'Date': ["202103151716Y202103151716","202103151716X202103151716","202103151716 202103151716"]}
df = pd.DataFrame(data=xx)
df
    Code     Date
0   001 202103151716Y202103151716
1   002 202103151716X202103151716
2   003 202103151716 202103151716

My solution below finds one of the characters i.e. Y or X at a time but not both of them at the same time. It replaces them with " ", then separates them. Is there any way I can write one statement that looks for either X, Y or space (" ") and separates them using the space? More like an OR statement in my solution below as compared to running the three statements below to replace Y, then replace X.

df['Date'] = df['Date'].str.replace('Y',' ') #Replace Y with space
df['Date'] = df['Date'].str.replace('X',' ')
df[['Date1', 'Date2']] = df['Date'].str.split(' ', 1, expand=True) # Splits the column into two other columns, Date1 and  Date2

Expected Output:

    Code    Date                     Date1           Date2
0   001 202103151716 202103151716   202103151716    202103151716
1   002 202103151716 202103151716   202103151716    202103151716
2   003 202103151716 202103151716   202103151716    202103151716
pandas dataframe python regex
1 Answers

You can split the column data directly with a \D+ pattern that matches one or more chars other than digits (since space, X or Y are non-digits):

import pandas as pd
xx = {'Code': ["001", "002","003"], 'Date': ["202103151716Y202103151716","202103151716X202103151716","202103151716 202103151716"]}
df = pd.DataFrame(data=xx)
df[['Date1', 'Date2']] = df['Date'].str.split(r'\D+', 1, expand=True)
df['Date1'] = pd.to_datetime(df['Date1'])
df['Date2'] = pd.to_datetime(df['Date2'])
df
# =>  Code                       Date               Date1               Date2
#   0  001  202103151716Y202103151716 2021-03-15 17:16:00 2021-03-15 17:16:00
#   1  002  202103151716X202103151716 2021-03-15 17:16:00 2021-03-15 17:16:00
#   2  003  202103151716 202103151716 2021-03-15 17:16:00 2021-03-15 17:16:00

So, there is no need replacing anything in the first place.

After separating with non-numerical chars, you may use pd.to_datetime() to cast the datetime numerical value to a datetime type.

Note that [ XY] is a character class that matches only a space, X or Y, but it seems the \D+ non-digit pattern should be safe with the data you showed.

user_38329700
Related