My input is a string :
text = '''10 February 2023
abc
def
23 March 2023
ghi
jkl'''
I made the code below (using regex but I’m open to any other alternative) :
data = []
for m in re.finditer(r'(\d+ \w+ \w+)\n(.*)', text, flags=re.I|re.S):
data.append([m.group(1), m.group(2).splitlines()])
df = pd.DataFrame(data, columns=['date', 'letters']).explode('letters')
My code gives me this weird result :
date letters
0 10 February 2023 abc
0 10 February 2023 def
0 10 February 2023 23 March 2023
0 10 February 2023 ghi
0 10 February 2023 jkl
While I was expecting this one :
date letters
0 10 February 2023 abc
0 10 February 2023 def
1 23 March 2023 ghi
1 23 March 2023 jkl
How can I fix my code ? Also, do you have any alternatives to suggest ? I’d be very interested to learn from them.
>Solution :
One option without regex, taking advantage of date recognition by pd.to_datetime:
df = pd.DataFrame({'letters': text.splitlines()})
m = pd.to_datetime(df['letters'], errors='coerce').notna()
out = df.assign(date=df['letters'].where(m).ffill()
).loc[~m, ::-1].reset_index(drop=True)
Alternative syntax:
s = pd.Series(text.splitlines())
m = pd.to_datetime(s, errors='coerce').notna()
df = pd.DataFrame({'date': s.where(m).ffill(), 'letters': s}
)[~m].reset_index(drop=True)
Output:
date letters
0 10 February 2023 abc
1 10 February 2023 def
2 23 March 2023 ghi
3 23 March 2023 jkl