CODEWITHSUNDEEP
pythonnumpypandas
user1893148
user1893148

Reputation: 2190

Pandas Data Frame: Add Column Conditionally On Past Dates and Values

I'm continuing to try to accomplish things in pandas which are easy in excel. Consider the df:

   |  ID  |  Value  |  Date
0  |  A   |  .21    |  2010-01-01
1  |  A   |  .31    |  2010-02-01
2  |  A   |  .44    |  2010-02-15
3  |  B   |  .23    |  2010-01-01
4  |  C   |  .21    |  2010-02-01
5  |  C   |  .91    |  2010-02-15

Thoughts on the best way to add a new column which checks to see (a) if the value is greater than .30 and (b) whether or not the ID has a record (row) with an earlier date that is also greater than .30?

I would ideally like to record 'Yes' in a new column when the value is greater than .3, and it's the earliest date at which that ID has a value greater than .30; record 'No' where the value is less than .3 and the ID has no earlier record greater than .3; and record 'Already' whenever the ID has an earlier record with a value > .3.

So the output looks something like:

   |  ID  |  Value  |  Date        | Result 
0  |  A   |  .21    |  2010-01-01  | No
1  |  A   |  .31    |  2010-02-01  | Yes
2  |  A   |  .24    |  2010-02-15  | Already
3  |  B   |  .23    |  2010-01-01  | No
4  |  C   |  .21    |  2010-02-01  | No
5  |  C   |  .91    |  2010-02-15  | Yes

Thanks kindly for any input.

Upvotes: 1

Views: 512

Answers (1)

Andy Hayden
Andy Hayden

Reputation: 375925

Here's one way, create a function which acts on each ID subDataFrame to return a Series of No, Yes and Already:

In [11]: def f(x, threshold=0.3):
             first = (x > threshold).values.argmax()
             if x.iloc[first] > threshold:
                 return pd.concat([pd.Series('No', x.index[:first]),
                                   pd.Series('Yes', [x.index[first]]),
                                   pd.Series('Already', x.index[first+1:])])
             else:
                 return pd.Series('No', x.index)

In [12]: df.groupby('ID')['Value'].apply(f)
Out[12]:
0         No
1        Yes
2    Already
3        Yes
4         No
5        Yes
dtype: object

In [13]: df['Result'] = df.groupby('ID')['Value'].apply(f)

In [14]: df
Out[14]:
  ID  Value        Date   Result
0  A   0.21  2010-01-01       No
1  A   0.31  2010-02-01      Yes
2  A   0.29  2010-02-15  Already
3  B   0.23  2010-01-01      Yes
4  C   0.21  2010-02-01       No
5  C   0.91  2010-02-15      Yes

Upvotes: 3

Related Questions