2
$\begingroup$

I have a pandas data frame that contains a partially corrupted data field as below. It has numbers (which are not a date) or nans. The real data frame has an incredibly large number of rows as well. I want to take the non-date values in this and assigning them to the date closest to it row-wise. For example, if the date field in row 3 is a nan or a junk value (a number or a string), I want the date in row 3 to be equal to the date in row 2 or row 4. Is there a way to do this that doesn't involve iterating over the entire data frame in a for loop?

inputArr = [['A', Timestamp('2021-06-01 00:00:00'), 9],
 ['A', Timestamp('2021-06-01 00:00:00'), 60],
 ['A', Timestamp('2021-06-01 00:00:00'), 39],
 ['A', 3, 51],
 ['A', Timestamp('2021-06-01 00:00:00'), 99],
 ['B', Timestamp('2021-06-01 00:00:00'), 21],
 ['B', Timestamp('2021-06-01 00:00:00'), 93],
 ['B', Timestamp('2021-06-01 00:00:00'), 42],
 ['B', 'xpwh1i3992aisan', 87],
 ['B', Timestamp('2021-06-01 00:00:00'), 33],
 ['C', nan, 72],
 ['C', Timestamp('2021-06-01 00:00:00'), 90],
 ['C', Timestamp('2021-06-01 00:00:00'), 42],
 ['C', 3, 87],
 ['C', 'items 44', 30],
 ['D', Timestamp('2021-06-01 00:00:00'), 75],
 ['D', Timestamp('2021-06-01 00:00:00'), 87],
 ['D', Timestamp('2021-06-01 00:00:00'), 78],
 ['D', 3, 75],
 ['D', Timestamp('2021-06-01 00:00:00'), 60],
 ['E', Timestamp('2021-06-01 00:00:00'), 0],
 ['E', nan, 69],
 ['E', Timestamp('2021-06-01 00:00:00'), 21],
 ['E', 3, 30],
 ['E', Timestamp('2021-06-01 00:00:00'), 69]]

trialPD = pd.DataFrame(inputArr, columns = ["Name", "date_purchase", "num_items"])
$\endgroup$
1
  • 4
    $\begingroup$ Should be easily achievable. You may need to make all Non-Timestamp values to NaN, and do a Timestamp imputation in Pandas. $\endgroup$ Jun 30, 2021 at 7:23

2 Answers 2

2
$\begingroup$

Try:

import datetime

trialPD["date_purchase"].apply(lambda x: x if isinstance(x,datetime.datetime) else np.nan).fillna(method = "ffill")
$\endgroup$
2
$\begingroup$

Also this is a simple one:

trialPD['date_purchase'] = pd.to_datetime(trialPD['date_purchase'], format='%Y-%m-%d %H:%M:%S', errors='coerce').ffill()
$\endgroup$

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.