2

How do I fill the Date column so that when it detects a date it adds that date to the below rows, until it sees a new date starts adding that date?

Reproducible example:

Input:


                Date                                           Headline
0   Mar-20-21 04:03AM  Apple CEO Cook, executives on tentative list o...
1             03:43AM  Apple CEO Cook, execs on tentative list of wit...
2   Mar-19-21 10:19PM  Dow Jones Futures: Why This Market Rally Is So...
3             06:13PM  Zuckerberg: Apples Privacy Move Could Spur Mor...
4             05:45PM  Apple (AAPL) Dips More Than Broader Markets: W...
5             04:17PM  Facebook Stock Jumps As Zuckerberg Changes Tun...
6             04:03PM  Best Dow Jones Stocks To Buy And Watch In Marc...
7             01:02PM  The Nasdaq's on the Rise Friday, and These 2 S...

Desired Output:


                 Date                                           Headline
0   Mar-20-21 04:03AM  Apple CEO Cook, executives on tentative list o...
1   Mar-20-21 03:43AM  Apple CEO Cook, execs on tentative list of wit...
2   Mar-19-21 10:19PM  Dow Jones Futures: Why This Market Rally Is So...
3   Mar-19-21 06:13PM  Zuckerberg: Apples Privacy Move Could Spur Mor...
4   Mar-19-21 05:45PM  Apple (AAPL) Dips More Than Broader Markets: W...
5   Mar-19-21 04:17PM  Facebook Stock Jumps As Zuckerberg Changes Tun...
6   Mar-19-21 04:03PM  Best Dow Jones Stocks To Buy And Watch In Marc...
7   Mar-19-21 01:02PM  The Nasdaq's on the Rise Friday, and These 2 S...

Attempt:

df['Time'] = [x[-7:] for x in df['Date']]
df['Date'] = [x[:-7] for x in df['Date']]
# Some code that fills the date
# Then convert to datetime

1 Answer 1

1

Before you use ffill() you need to split the two columns to get the correct time, and only fill in the Date part. You will need to replace spaces with np.nan to use ffill(). Then put the columns back together and wrap that operation in pd.to_datetime to get the correct dtype.

Lastly you can drop the time column.

# Imports
import numpy as np
import pandas as pd

# Split the column
df[['Date','Time']] = df['Date'].str.split(' ',expand=True)

# Replace space with nan and use ffill()
df['Date'] = df['Date'].replace(r'^\s*$', np.nan, regex=True).ffill()

# Put the columns back and convert to datetime
df['Date'] =  pd.to_datetime(df['Date'] + ' ' + df['Time'])

# Drop the time column
del(df['Time'])

Will get you back:

df
                 Date                                           Headline
0 2021-03-20 04:03:00  Apple CEO Cook, executives on tentative list o...
1 2021-03-20 03:43:00  Apple CEO Cook, execs on tentative list of wit...
2 2021-03-19 22:19:00  Dow Jones Futures: Why This Market Rally Is So...
3 2021-03-19 18:13:00  Zuckerberg: Apples Privacy Move Could Spur Mor...
4 2021-03-19 17:45:00  Apple (AAPL) Dips More Than Broader Markets: W...
5 2021-03-19 16:17:00  Facebook Stock Jumps As Zuckerberg Changes Tun...
6 2021-03-19 16:03:00  Best Dow Jones Stocks To Buy And Watch In Marc...
7 2021-03-19 13:02:00  The Nasdaq's on the Rise Friday, and These 2 S...

EDIT If you want your 'Date' to show exactly as you have it in your desired outcome, i.e. this format 'Mar-20-21', don't wrap it in pd.to_datetime() and keep it as an object:

df['Date'] =  df['Date'] + ' ' + df['Time']

df
                Date                                           Headline
0  Mar-20-21 04:03AM  Apple CEO Cook, executives on tentative list o...
1  Mar-20-21 03:43AM  Apple CEO Cook, execs on tentative list of wit...
2  Mar-19-21 10:19PM  Dow Jones Futures: Why This Market Rally Is So...
3  Mar-19-21 06:13PM  Zuckerberg: Apples Privacy Move Could Spur Mor...
4  Mar-19-21 05:45PM  Apple (AAPL) Dips More Than Broader Markets: W...
5  Mar-19-21 04:17PM  Facebook Stock Jumps As Zuckerberg Changes Tun...
6  Mar-19-21 04:03PM  Best Dow Jones Stocks To Buy And Watch In Marc...
7  Mar-19-21 01:02PM  The Nasdaq's on the Rise Friday, and These 2 S...
Sign up to request clarification or add additional context in comments.

1 Comment

the solution works without this line: df[['Date','Time']] = df['Date'].str.split(' ',expand=True). It gives me NaT with that line

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.