I have 2 different Dataframe of unequal shape:
pack_df:
| pack_id | item_size | temperature |
|---|---|---|
| pck1 | M | 7.3 |
| pck2 | S | 10.0 |
| pck3 | L | 5.2 |
| pck4 | S | 15.3 |
| pck5 | M | 3.3 |
| pck6 | L | 9.3 |
| pck7 | L | 20.3 |
| pck8 | M | 8.1 |
| pck9 | M | 21.3 |
| pck10 | S | 9.7 |
temperature_range_df:
| min_temp | max_temp | S | M | L |
|---|---|---|---|---|
| -4 | 10 | 2 | 1 | 1 |
| 10.1 | 20 | 4 | 3 | 2 |
| 20.1 | 30 | 6 | 4 | 2 |
I need to match if pack_df['temperature'] with in range temperature_range_df['min_temp'], temperature_range_df['max_temp'] when a match is found i need to assign the packet counts ( temperature_range_df['S']/temperature_range_df['M']/temperature_range_df['L'] based on pack_df['item_size']. How can we achieve this without iterating over each rows and comparing them in our dataframe as the dataframe is likely to grow a lot over time.( not using dataframe.iterrows() )
My final Dataframe should look like
final_dataframe:
| pack_id | item_size | temperature | pack_count |
|---|---|---|---|
| pck1 | M | 7.3 | 1 |
| pck2 | S | 10.0 | 2 |
| pck3 | L | 5.2 | 1 |
| pck4 | S | 15.3 | 4 |
| pck5 | M | 3.3 | 1 |
| pck6 | L | 9.3 | 1 |
| pck7 | L | 20.3 | 2 |
| pck8 | M | 8.1 | 1 |
| pck9 | M | 21.3 | 4 |
| pck10 | S | 9.7 | 2 |
calculation- pck1 item size should be 'M' and temperature '7.3'. this falls under temp range '-4 to 10' based on item size 'M' we can assign only 1 packet.
Thanks for the help!