How to use shift in pandas based on ranking column

user_9025131 0
Viewed 113

I have data like this.

My Data looks like this.

I want to get Previous Datetime based on rank. When i use pandas shift(1) function I get Previous Datetime as '2019/10/15 00:00:00' instead of '2019/10/11 08:31:00' on 9th row and same way it happens for other rank groups. I want same previous time when rank is same. Below are required results.

  +------+---------------------+-----------------------+------+
| Rank |      DateTime       | Elapsed Time Previous | Name |
+------+---------------------+-----------------------+------+
|    1 | 2019/09/23 08:45:00 |                       |      |
|    2 | 2019/09/27 10:14:00 | 2019/09/23 08:45:00   |      |
|    3 | 2019/10/01 09:28:00 | 2019/09/27 10:14:00   |      |
|    4 | 2019/10/04 14:25:00 | 2019/10/01 09:28:00   |      |
|    5 | 2019/10/08 10:46:00 | 2019/10/04 14:25:00   |      |
|    6 | 2019/10/11 08:25:00 | 2019/10/08 10:46:00   |      |
|    7 | 2019/10/11 08:31:00 | 2019/10/11 08:25:00   |      |
|    8 | 2019/10/15 00:00:00 | 2019/10/11 08:31:00   |      |
|    8 | 2019/10/15 00:00:00 | 2019/10/11 08:31:00   |      |
|    1 | 2019/09/06 00:00:00 |                       |      |
|    2 | 2019/09/10 00:00:00 | 2019/09/06 00:00:00   |      |
|    3 | 2019/09/13 00:00:00 | 2019/09/10 00:00:00   |      |
|    4 | 2019/09/17 00:00:00 | 2019/09/13 00:00:00   |      |
|    5 | 2019/09/20 10:00:00 | 2019/09/17 00:00:00   |      |
|    6 | 2019/09/24 00:00:00 | 2019/09/20 10:00:00   |      |
|    7 | 2019/09/27 10:53:00 | 2019/09/24 00:00:00   |      |
|    8 | 2019/10/01 10:21:00 | 2019/09/27 10:53:00   |      |
|    9 | 2019/10/04 09:59:00 | 2019/10/01 10:21:00   |      |
|   10 | 2019/10/08 09:58:00 | 2019/10/04 09:59:00   |      |
|   11 | 2019/10/11 10:41:00 | 2019/10/08 09:58:00   |      |
|    1 | 2019/09/23 09:00:00 |                       |      |
|    2 | 2019/09/27 11:03:00 | 2019/09/23 09:00:00   |      |
|    3 | 2019/10/01 10:14:00 | 2019/09/27 11:03:00   |      |
|    4 | 2019/10/04 09:46:00 | 2019/10/01 10:14:00   |      |
|    5 | 2019/10/08 10:04:00 | 2019/10/04 09:46:00   |      |
|    6 | 2019/10/11 10:33:00 | 2019/10/08 10:04:00   |      |
|    7 | 2019/10/15 00:00:00 | 2019/10/11 10:33:00   |      |
|    7 | 2019/10/15 00:00:00 | 2019/10/11 10:33:00   |      |
+------+---------------------+-----------------------+------+
pandas datetime python
1 Answers

Use DataFrame.drop_duplicates with Series.shift of Series after convert Rank to index, so last is possible use Series.map:

df['DateTime'] = pd.to_datetime(df['DateTime'])

s = df.drop_duplicates('Rank').set_index('Rank')['DateTime'].shift()

df['Previous Datetime'] = df['Rank'].map(s)
print (df)
             DateTime   Previous Datetime  Rank
0 2019-09-06 00:00:00                 NaT     1
1 2019-09-10 00:00:00 2019-09-06 00:00:00     2
2 2019-09-13 00:00:00 2019-09-10 00:00:00     3
3 2019-09-17 00:00:00 2019-09-13 00:00:00     4
4 2019-09-20 10:00:00 2019-09-17 00:00:00     5
5 2019-09-24 00:00:00 2019-09-20 10:00:00     6
6 2019-09-27 10:21:00 2019-09-24 00:00:00     7
7 2019-10-01 00:00:00 2019-09-27 10:21:00     8
8 2019-10-01 00:00:00 2019-09-27 10:21:00     8

EDIT:

df = df.drop('Elapsed Time Previous', axsi=1)

df['DateTime'] = pd.to_datetime(df['DateTime'])

# df['Elapsed Time Previous'] = 
s = (df.drop_duplicates(['Rank','Name', 'ID'])
       .set_index(['Name', 'ID', 'Rank'])['DateTime']
       .unstack()
       .shift(axis=1)
       .stack()
       .rename('Elapsed Time Previous'))

df = df.join(s, on=['Name','ID','Rank'])
user_29010020
Related