Pandas: Not getting the desired output when using a nested for loop to fill an empty column

user_14391780 0
Viewed 38

I've written the following code using a nested for loop for filling an empty column of a dataframe with values based on comparison with table of categories in another dataframe.

When I run the script, I noticed that the category column in the data DataFrame is filled with a single value which is not correct.

I'd like to use the nested for loop to fill the category column for every item in data['item]. I noticed when printing variables in the nested for loop, that the category column is always been filled with the current "j" value from the second loop. So, in every loop, the entire category column is re-written (examples of the actual and desired outputs are shown below)

I'd greatly appreciate any feedback for solving this and also welcome recommendations on how to write this code in a better/more efficient way (i.e the actual dataset I work with is significantly larger that this example subset).

import pandas as pd
import numpy as np

data=pd.ExcelFile(path)

# creating separate dataframes from each tab in the excel file
categories=pd.read_excel(data, 'test')
data=pd.read_excel(data, 'data')
display(categories, data)

# catefories
Item    Category    low_value   high_value
Car A   0.0 1.9
Car B   2.0 3.0
House   A   3.0 5.0
House   B   5.5 6.1
House   C   6.2 8.0
House   D   9.0 11.0

# data
Item    value   category
Car 1.5 NaN
Car 1.0 NaN
Car 2.0 NaN
Car 3.0 NaN
Car 2.5 NaN
Car 1.1 NaN
House   8.0 NaN
House   5.0 NaN
House   3.0 NaN
House   11  NaN
House   7.3 NaN
House   4.4 NaN
House   6.1 NaN


    # creating list for items, to be used in the for loop
    item_list=data['Item'].unique()
    
    
   for i in range(len(item_list)):
    item_categories=categories.loc[categories['Item']==item_list[i]]
    item=data[data['Item']==item_list[i]]
    cat_list=item_categories['Category'].unique()

    for j in range(len(cat_list)):
        low=item_categories.loc[item_categories['Category']==cat_list[j], 'low_value'].values[0]
        high=item_categories.loc[item_categories['Category']==cat_list[j], 'high_value'].values[0]
        if [(item['value']>=low) & (item['value']<=high)]:
            data['category']= category_list[j]
        else:
            j+=1
        

    display(data)

Output

   Item    value    category
0   Car     1.5        D
1   Car     1.0        D
2   Car     2.0        D
3   Car     3.0        D
4   Car     2.5        D
5   Car     1.1        D
6   House   8.0        D
7   House   5.0        D
8   House   3.0        D
9   House   1.1        D
10  House   7.3        D
11  House   2.4        D
12  House   6.1        D

Desired Result

enter image description here

pandas for-loop nested loops
2 Answers

Let's try merge_asof:

(pd.merge_asof(data.assign(idx=data.index)
                   .sort_values('value'),
              categories.sort_values('low_value'), 
              left_on='value',
              right_on='low_value', by='Item')
   .set_index('idx')
   .sort_index()
   [['Item','value','Category']]
)

Output (notice the Category column):

      Item  value Category
idx                       
0      Car    1.5        A
1      Car    1.0        A
2      Car    2.0        B
3      Car    3.0        B
4      Car    2.5        B
5      Car    1.1        A
6    House    8.0        C
7    House    5.0        A
8    House    3.0        A
9    House   11.0        D
10   House    7.3        C
11   House    4.4        A
12   House    6.1        B
user_42384080

You can use df.merge and Series.between:

In [2287]: x = data.merge(categories, on='Item')

In [2292]: res = x[x.value.between(x.low_value, x.high_value)][['Item', 'value', 'Category']]

In [2293]: res
Out[2293]: 
     Item  value Category
0     Car    1.5        A
2     Car    1.0        A
5     Car    2.0        B
7     Car    3.0        B
9     Car    2.5        B
10    Car    1.1        A
14  House    8.0        C
16  House    5.0        A
20  House    3.0        A
27  House   11.0        D
30  House    7.3        C
32  House    4.4        A
37  House    6.1        B
user_58208140
Related