Python Pandas : remove max in categories of dataframe from string comment in another dataframe

user_13091251 0
Viewed 48

I have a dataset with thousands of rows which has a few outliers in the 'value' columns.

df_test = pd.DataFrame({
                 'product': ['Egg', 'Egg', 'Egg', 'Small Egg','Small Egg','Small Egg','Small Egg', 'Wheat','Wheat','Wheat','Wheat','Wheat','Rice','Rice','Rice','Garlic','Garlic','Garlic','Garlic','Garlic','Tomato','Tomato','Tomato', 'Ananas'], 
                 'value': ['13','5','3','28','5','4','5','28','28','28','1','1.5','7','4','4.3','140','143','149','320','5','400','10','15', '8']                  
                      })

I know which data is incorrect from comments available in another dataset, this one is basically the list of products (unique) with a comment on the maximum value to remove:

df_test_comment = pd.DataFrame({
                     'product': ['Egg', 'Small Egg', 'Wheat', 'Rice', 'Garlic','Tomato', 'Ananas'], 
                     'What to remove': ['1st max','1st and 2nd max','1st, 2nd, and 3rd max', '1st max', '1st, 2nd, 3rd and 4th max','1st and 2nd max', 'NaN']                  
                    })

Because I have only a limited number of different comments ('1st max', '1st and 2nd max', '1st, 2nd, and 3rd max', '1st, 2nd, 3rd and 4th max'), I was thinking of using a for loop to delete in df_test the max value of a product if the comment in df_test_comment is '1st max' ; max value and second max value when '1st and 2nd max' etc.

The ideal output with the sample example would be this:

df_result = pd.DataFrame({
                     'product': ['Egg','Egg','Small Egg','Small Egg','Wheat','Wheat','Rice','Rice','Garlic','Tomato', 'Ananas'], 
                     'Value': ['5','3','4','5','1','1.5','4','4.3','5','10','8']                  
                    })

Any idea how to tackle this cleaning?

pandas python
1 Answers

We need multiple steps, 1st find the number , then explode it , 2nd from df_test create the addtional rank key with cumcount: Notice this assume the datatframe already sort by value

#1st part
df_test['value']=pd.to_numeric(df_test['value'])
df_test=df_test.sort_values('value',ascending=False)
df_test_comment['number']=df_test_comment['What to remove'].str.findall('\d+')
df_test_comment=df_test_comment.explode('number')
#2nd part
m1=df_test['product']+(df_test.groupby('product').cumcount()+1).astype(str)
m2=df_test_comment['product']+df_test_comment['number']
dftest=df_test[~m1.isin(m2)].sort_index()


dftest
      product  value
1         Egg    5.0
2         Egg    3.0
4   Small Egg    5.0
5   Small Egg    4.0
10      Wheat    1.0
11      Wheat    1.5
13       Rice    4.0
14       Rice    4.3
19     Garlic    5.0
21     Tomato   10.0
23     Ananas    8.0
user_79645270
Related