Updating grouped data based on date

user_18766270 0
Viewed 61

I have a dataframe that looks like the following:

df = pd.DataFrame({'ID': ['1', '1', '1', '2', '2', '2'], 
                   'ID2': ['25', '29', '56', '562', '92', '170'],
                   'Origin': ['2005', '2010', '2020', '1995', '1999', '2007'],
                   'Status' : ['Done', 'Unfinished', 'Done', 'Done', 'Done', 'Unfinished']
                  })
df


+----+-----+--------+------------+
| ID | ID2 | Origin |   Status   |
+----+-----+--------+------------+
|  1 |  25 |   2005 | Done       |
|  1 |  29 |   2010 | Unfinished |
|  1 |  56 |   2020 | Done       |
|  2 | 562 |   1995 | Done       |
|  2 |  92 |   1999 | Done       |
|  2 | 170 |   2007 | Unfinished |
+----+-----+--------+------------+

I am trying to separate into groups based on ID and update the Status column from Unfinished to Done if there is another row in the same group with a newer Done value. Essentially, if the Unfinished row is the most recent within the group, then leave the same. If Unfinished is not the most recent, change to Done.

Sample output below:


+----+-----+--------+------------+----------------+
| ID | ID2 | Origin |   Status   | Updated_Status |
+----+-----+--------+------------+----------------+
|  1 |  25 |   2005 | Done       |                |
|  1 |  29 |   2010 | Unfinished | Done           |
|  1 |  56 |   2020 | Done       |                |
|  2 | 562 |   1995 | Done       |                |
|  2 |  92 |   1999 | Done       |                |
|  2 | 170 |   2007 | Unfinished |                |
+----+-----+--------+------------+----------------+

After I finish df.groupby('ID') I know I have to try some variation of df.loc[(df['Status'] == 'Unfinished') & (df['Origin'] > today.year)] but I can't think of the proper logic or syntax to properly adjust this dataframe.

pandas dataframe numpy python
4 Answers

You can use:

# is the value "Done"?
m = df['Status'].eq('Done')
# keep only the True, backfill them per group
# this leaves the terminal "Unfinished" as NaN, which we fill as False
m2 = m.where(m).groupby(df['ID']).bfill().fillna(False)

# we could also use a reverse cummax
# m2 = m[::-1].groupby(df['ID']).cummax()

# boolean indexing
df.loc[m2&~m, 'Updated_Status'] = 'Done'

Output:

  ID  ID2 Origin      Status Updated_Status
0  1   25   2005        Done            NaN
1  1   29   2010  Unfinished           Done
2  1   56   2020        Done            NaN
3  2  562   1995        Done            NaN
4  2   92   1999        Done            NaN
5  2  170   2007  Unfinished            NaN

alternative format

Use df['Updated_Status'] = np.where(m2&~m, 'Done', '') as last line:

  ID  ID2 Origin      Status Updated_Status
0  1   25   2005        Done               
1  1   29   2010  Unfinished           Done
2  1   56   2020        Done               
3  2  562   1995        Done               
4  2   92   1999        Done               
5  2  170   2007  Unfinished
user_163434640

Updated_Status = df.groupby('ID').last()['Status'].replace('Unfinished','')

Following is the output upon printing Updated_Status

enter image description here

Must add this to the dataframe,,

user_187822240

I think you can test if shifted values per groups by DataFrameGroupBy.shift are Done and also if same row has Unfinished - both conditions are chain by & for bitwise AND, last set values in numpy.where:

df = df.sort_values(['ID','Origin'])
mask = df.groupby('ID')['Status'].shift(-1).eq('Done') & df['Status'].eq('Unfinished')
df['Updated_Status'] = np.where(mask, 'Done', '')
print (df)
  ID  ID2 Origin      Status Updated_Status
0  1   25   2005        Done               
1  1   29   2010  Unfinished           Done
2  1   56   2020        Done               
3  2  562   1995        Done               
4  2   92   1999        Done               
5  2  170   2007  Unfinished

Alternative solution if possible test if last value is Done per group is use GroupBy.transform:

df = df.sort_values(['ID','Origin'])
mask = (df.groupby(['ID'])['Status'].transform('last').eq('Done') & 
        df['Status'].eq('Unfinished'))
df['Updated_Status'] = np.where(mask, 'Done', '')
user_29010020

It is as simple as

mask = (df.groupby('ID')['Status'].transform('last') == 'Done') & (df.Status == 'Unfinished')
df['Updated_Status'] = np.where(mask, 'Done', np.nan)

The last row in each group corresponds to the latest entry. Use that to transform for each Status. Then, use the condition for updating the status.

user_51200490
Related