Mark last date record in Pandas/Dask dataframes

user_1362485 0
Viewed 96

In the Dask dataset below I have a list of ids (for example 1 and 2) and dates (the last column).

What I need is to add a new column to the dataframe that will have 1 if the date is the last one for that id. For example, for id 2 the last date is 2021-05-01. If it's not the last, the column should be None or 0.

The entire set is ordered by id and date, in case that's necessary.

I used to do this with SQL, having in the where clause where NOT EXISTS date > (select max(date)) ...

Is this possible with Pandas and/or Dask?

  pdf = pd.DataFrame({
        'id': [1, 1, 1, 2, 2],
        'balance': [150, 140, 130, 280, 260],
        'date' : ['2021-03-01', '2021-04-01', '2021-05-01', '2021-01-01', '2021-02-01']
    })
    print(pdf)

    id  balance        date
0   1      150  2021-03-01
1   1      140  2021-04-01
2   1      130  2021-05-01
3   2      280  2021-04-01
4   2      260  2021-05-01

pdf['date2'] = pd.to_datetime(pdf['date'])
ddf = dd.from_pandas(pdf, npartitions=1) 
ddf.compute()

id  balance    date      date2
0   1   150 2021-03-01  2021-03-01
1   1   140 2021-04-01  2021-04-01
2   1   130 2021-05-01  2021-05-01
3   2   280 2021-04-01  2021-04-01
4   2   260 2021-05-01  2021-05-01

For example the end result would be

id  balance    date      date2      last_date_flag
0   1   150 2021-03-01  2021-03-01    0
1   1   140 2021-04-01  2021-04-01    0
2   1   130 2021-05-01  2021-05-01    1
3   2   280 2021-04-01  2021-04-01    0
4   2   260 2021-05-01  2021-05-01    1
dask-distributed dask pandas dataframe python
2 Answers

This is the approach I would use for pandas, not sure about Dask.

pdf['last_date_flag'] = pdf.groupby('id')['date'].transform(lambda x: x == x.max()).astype(int)

Gives


id  balance date    date2   last_date_flag
0   1   150 2021-03-01  2021-03-01  0
1   1   140 2021-04-01  2021-04-01  0
2   1   130 2021-05-01  2021-05-01  1
3   2   280 2021-01-01  2021-01-01  0
4   2   260 2021-02-01  2021-02-01  1

If you are concerned with scalability this performs faster on large datasets:


pdf['last_date_flag'] = (pdf['date'] == pdf['id'].map(pdf.groupby('id')['date'].max().to_dict())).astype(int)
user_95418480 
pdf['date'] = pd.to_datetime(pdf['date'])
pdf['last_date_flag']=(pdf.groupby('id')['date'].transform(lambda x:x.max()).eq(pdf['date'])).astype(int)


    id  balance       date  last_date_flag
0   1      150 2021-03-01               0
1   1      140 2021-04-01               0
2   1      130 2021-05-01               1
3   2      280 2021-04-01               0
4   2      260 2021-05-01               1
user_89869750
Related