Conditional copy on id level

user_1783739 0
Viewed 28

I have a dataframe that looks like:

ID   VALUE        
1     Low         
1     High        
1     Medium      
1     Very_High   
2     Low         
2     Medium      
2     Medium

I want to go through each ID level and applying certain conditions, transform the column VALUE. The conditions are that:

i. If there is 'Very_High' in any of the rows of that ID, make all rows for that ID 'Very_High'

ii. Else If there is 'High' in any of the rows of that ID, make all rows for that ID 'High'

iii. Else If there is 'Medium' in any of the rows of that ID, make all rows for that ID 'Medium'

iv. Else make the rows in the ID to be 'Low'

Expected dataframe result:

ID   VALUE
1    Very_High
1    Very_High
1    Very_High
1    Very_High
2    Medium
2    Medium
2    Medium

This is what I tried but not working:

if (df_new.groupby('ID')['VALUE']=='Very_High').any():
    df_new = df_new.loc[df_new.groupby('ID')['VALUE'] == 'Very_High'].copy()
elif (df_new.groupby('ID')['VALUE'] =='High').any():
    df_new = df_new.loc[df_new.groupby('ID')['VALUE'] == 'High'].copy()
elif (df_new.groupby('ID')['VALUE']=='Medium').any():
    df_new = df_new.loc[df_new.groupby('ID')['VALUE'] == 'Medium'].copy()
else: 
    df_new = df_new.loc[df_new.groupby('ID')['VALUE'] == 'Low'].copy()
pandas python-3.x group-by
1 Answers

We can do Categorical, then merge it.

df.VALUE=pd.Categorical(df.VALUE, ['Low','Medium','High','Very_High'], ordered=True)
df=df.drop('VALUE', 1).merge(df.sort_values('VALUE').drop_duplicates('ID',keep='last'),how='left', on ='ID')
Out[244]: 
   ID      VALUE
0   1  Very_High
1   1  Very_High
2   1  Very_High
3   1  Very_High
4   2     Medium
5   2     Medium
6   2     Medium
user_79645270
Related