python - How to filter a list based on ascending values?

Question

I have the following 3 lists:

minimal_values = ['0,32', '0,35', '0,45']
maximal_values = ['0,78', '0,85', '0,72']

my_list = [
    ['Morocco', 'Meat', '190,00', '0,15'], 
    ['Morocco', 'Meat', '189,90', '0,32'], 
    ['Morocco', 'Meat', '189,38', '0,44'],
    ['Morocco', 'Meat', '188,94', '0,60'],
    ['Morocco', 'Meat', '188,49', '0,78'],
    ['Morocco', 'Meat', '187,99', '0,70'],
    ['Spain', 'Meat', '190,76', '0,10'], 
    ['Spain', 'Meat', '190,16', '0,20'], 
    ['Spain', 'Meat', '189,56', '0,35'],
    ['Spain', 'Meat', '189,01', '0,40'],
    ['Spain', 'Meat', '188,13', '0,75'],
    ['Spain', 'Meat', '187,95', '0,85'],
    ['Italy', 'Meat', '190,20', '0,11'],
    ['Italy', 'Meat', '190,10', '0,31'], 
    ['Italy', 'Meat', '189,32', '0,45'],
    ['Italy', 'Meat', '188,61', '0,67'],
    ['Italy', 'Meat', '188,01', '0,72'],
    ['Italy', 'Meat', '187,36', '0,55']]

I'm trying to filter my_list based if index [-1] is between the value in minimal_values and the value in maximal_values.These values are mpping the min and max by country. Im also doing a substraction inside the list. So for Morocco I only want the rows where index[-1] is between 0,32 and 0,78 etc. The problem is that after 0,78 the values drops to 0,70 which means that row also satifies the if statement.

Note:The values in my_list -1 are first asceding and then descending. I only want the rows in the ascending part, not in the descending part. Im not sure how to solve this problem.

This is my code:

price = 500

# Convert values to float.
minimal_values = [float(i.replace(',', '.')) for i in minimal_values]
maximal_values = [float(i.replace(',', '.')) for i in maximal_values]

# Collect all unique countries in a list.
countries = list(set(country[0] for country in my_list))

results = []
for l in my_list:
    i = countries.index(l[0])
    if minimal_values[i] <= float(l[-1].replace(',', '.')) <= maximal_values[i]:
        new_index_2 = price - float(l[-2].replace(',', '.'))
        l[-2] = new_index_2
        results.append(l)

print(results)

This is my current output:

[['Morocco', 'Meat', '189.90', '0,32'], 
['Morocco', 'Meat', 310.62, '0,44'], 
['Morocco', 'Meat', 311.06, '0,60'], 
['Morocco', 'Meat', 311.51, '0,78'], 
['Morocco', 'Meat', 312.01, '0,70'], 
['Spain', 'Meat', 310.44, '0,35'], 
['Spain', 'Meat', 310.99, '0,40'], 
['Spain', 'Meat', 311.87, '0,75'], 
['Spain', 'Meat', '312.05', '0,85'],
['Italy', 'Meat', 310.68, '0,45'], 
['Italy', 'Meat', 311.39, '0,67'], 
['Italy', 'Meat', 311.99, '0,72'], 
['Italy', 'Meat', 312.64, '0,55']]

This is my desired output:

 [['Morocco', 'Meat', '189.90', '0,32'], 
    ['Morocco', 'Meat', 310.62, '0,44'], 
    ['Morocco', 'Meat', 311.06, '0,60'], 
    ['Morocco', 'Meat', 311.51, '0,78'], 
    ['Spain', 'Meat', 310.44, '0,35'], 
    ['Spain', 'Meat', 310.99, '0,40'], 
    ['Spain', 'Meat', 311.87, '0,75'],
    ['Spain', 'Meat', '312.05', '0,85'], 
    ['Italy', 'Meat', 310.68, '0,45'], 
    ['Italy', 'Meat', 311.39, '0,67'], 
    ['Italy', 'Meat', 311.99, '0,72']]

*****Pandas related answers are also welcome.

Answer 1

pandas solution:

import pandas as pd
import numpy as np

# create input dataframe
my_list = [
    ['Morocco', 'Meat', '190,00', '0,15'], 
    ['Morocco', 'Meat', '189,90', '0,32'], 
    ['Morocco', 'Meat', '189,38', '0,44'],
    ['Morocco', 'Meat', '188,94', '0,60'],
    ['Morocco', 'Meat', '188,49', '0,78'],
    ['Morocco', 'Meat', '187,99', '0,70'],
    ['Spain', 'Meat', '190,76', '0,10'], 
    ['Spain', 'Meat', '190,16', '0,20'], 
    ['Spain', 'Meat', '189,56', '0,35'],
    ['Spain', 'Meat', '189,01', '0,40'],
    ['Spain', 'Meat', '188,13', '0,75'],
    ['Spain', 'Meat', '187,95', '0,85'],
    ['Italy', 'Meat', '190,20', '0,11'],
    ['Italy', 'Meat', '190,10', '0,31'], 
    ['Italy', 'Meat', '189,32', '0,45'],
    ['Italy', 'Meat', '188,61', '0,67'],
    ['Italy', 'Meat', '188,01', '0,72'],
    ['Italy', 'Meat', '187,36', '0,55']]

dfi = pd.DataFrame(my_list).applymap(lambda x: x.replace(',', '.'))
dfi[[2, 3]] = dfi[[2, 3]].astype(float)
print(dfi)

#         0     1       2     3
# 0   Morocco  Meat  190.00  0.15
# 1   Morocco  Meat  189.90  0.32
# 2   Morocco  Meat  189.38  0.44
# 3   Morocco  Meat  188.94  0.60
# 4   Morocco  Meat  188.49  0.78
# 5   Morocco  Meat  187.99  0.70
# 6     Spain  Meat  190.76  0.10
# 7     Spain  Meat  190.16  0.20
# 8     Spain  Meat  189.56  0.35
# 9     Spain  Meat  189.01  0.40
# 10    Spain  Meat  188.13  0.75
# 11    Spain  Meat  187.95  0.85
# 12    Italy  Meat  190.20  0.11
# 13    Italy  Meat  190.10  0.31
# 14    Italy  Meat  189.32  0.45
# 15    Italy  Meat  188.61  0.67
# 16    Italy  Meat  188.01  0.72
# 17    Italy  Meat  187.36  0.55

# create df_filter with contry and min_v, max_v
minimal_values = ['0,32', '0,35', '0,45']
maximal_values = ['0,78', '0,85', '0,72']
minimal_values = [float(i.replace(',', '.')) for i in minimal_values]
maximal_values = [float(i.replace(',', '.')) for i in maximal_values]

df_filter = pd.DataFrame(list(zip(dfi[0].unique().tolist(),
                                  minimal_values,
                                  maximal_values)))
df_filter.columns = [0, 'min_v', 'max_v']
print(df_filter)
#          0  min_v  max_v
# 0  Morocco   0.32   0.78
# 1    Spain   0.35   0.85
# 2    Italy   0.45   0.72

# merge dfi and fi_filter
dfm = pd.merge(dfi, df_filter, on=0, how='left')
print(dfm)

#          0     1       2     3  min_v  max_v
# 0   Morocco  Meat  190.00  0.15   0.32   0.78
# 1   Morocco  Meat  189.90  0.32   0.32   0.78
# 2   Morocco  Meat  189.38  0.44   0.32   0.78
# 3   Morocco  Meat  188.94  0.60   0.32   0.78
# 4   Morocco  Meat  188.49  0.78   0.32   0.78
# 5   Morocco  Meat  187.99  0.70   0.32   0.78
# 6     Spain  Meat  190.76  0.10   0.35   0.85
# 7     Spain  Meat  190.16  0.20   0.35   0.85
# 8     Spain  Meat  189.56  0.35   0.35   0.85
# 9     Spain  Meat  189.01  0.40   0.35   0.85
# 10    Spain  Meat  188.13  0.75   0.35   0.85
# 11    Spain  Meat  187.95  0.85   0.35   0.85
# 12    Italy  Meat  190.20  0.11   0.45   0.72
# 13    Italy  Meat  190.10  0.31   0.45   0.72
# 14    Italy  Meat  189.32  0.45   0.45   0.72
# 15    Italy  Meat  188.61  0.67   0.45   0.72
# 16    Italy  Meat  188.01  0.72   0.45   0.72
# 17    Italy  Meat  187.36  0.55   0.45   0.72

# filter min_v <= column 3 <= max_v
cond = dfm[3].ge(dfm.min_v) & dfm[3].le(dfm.max_v)
dfm = dfm[cond].copy()

# filter 3 that is not ascending
cond = dfm.groupby(0)[3].diff() < 0
dfo = dfm.loc[~cond, [0,1,2,3]].reset_index(drop=True)

# outut result
price = 500
dfo[2] = price - dfo[2]

print(dfo)

#           0     1       2     3
# 0   Morocco  Meat  310.10  0.32
# 1   Morocco  Meat  310.62  0.44
# 2   Morocco  Meat  311.06  0.60
# 3   Morocco  Meat  311.51  0.78
# 4     Spain  Meat  310.44  0.35
# 5     Spain  Meat  310.99  0.40
# 6     Spain  Meat  311.87  0.75
# 7     Spain  Meat  312.05  0.85
# 8     Italy  Meat  310.68  0.45
# 9     Italy  Meat  311.39  0.67
# 10    Italy  Meat  311.99  0.72