CODEWITHSUNDEEP
pythonpandasdatetimedataframe
Jennifer Drake
Jennifer Drake

Reputation: 119

How do I check date time is in order with missing date times in data frame?

I have a pandas data frame with 4 columns containing some zero int values and some date times. I want to create a new column with a True/False value to tell me if the date times are in chronological order even though some are 0.

example df

P1.   P2.   P3.   P4. 
0.    2011.  0.   2015
2015. 0.    0.    2013

I then want to make a new column that just has a T/F depending on if it is in chronological order e.g. in the case above row 1 is true and row 2 is false.

I have considered a loop with if and else but was wondering if there is a cleaner way. FYI the dates are in the full format 2014-11-31 00:00:00.

Thanks in advance.

Upvotes: 3

Views: 755

Answers (2)

MaxU - stand with Ukraine
MaxU - stand with Ukraine

Reputation: 210842

Source DF:

In [250]: x
Out[250]:
      P1.     P2.  P3.   P4.
0     0.0  2011.0  0.0  2015
1  2015.0     0.0  0.0  2013

Solution:

In [251]: x['new'] = x[x!=0].apply(lambda x: x.dropna().is_monotonic, axis=1)

Result:

In [252]: x
Out[252]:
      P1.     P2.  P3.   P4.    new
0     0.0  2011.0  0.0  2015   True
1  2015.0     0.0  0.0  2013  False

Upvotes: 2

akuiper
akuiper

Reputation: 214957

Here is a method following the logic:

  • Use apply method to loop through rows axis = 1;
  • For each row, remove zeros, calculate the difference and compare with zeros;
  • If none of the difference is smaller than zero, then you can claim that it is in chronological order (ascending);

Plus, if the row contains zero or one valid date (non zero), this logic gives true:

df.apply(lambda r: ~(r[r != 0].diff() < 0).any(), 1)

#0     True
#1    False
#dtype: bool

Upvotes: 2

Related Questions