This is more of a curiosity question. I recently got involved with using Pandas and thought it would've been really useful when I was in university.
We got given a lookup table.
,Torque(Nm),Torque(Nm),Torque(Nm),Torque(Nm),Torque(Nm),Torque(Nm),Torque(Nm),Torque(Nm),Torque(Nm),Torque(Nm),Torque(Nm),Torque(Nm)
Speed,15.0,30.0,45.0,60.0,75.0,90.0,105.0,120.0,135.0,150.0,165.0,180.0
4000,0.6789,0.8672,1.0555,1.2438,1.4321,1.6204,1.8087,1.997,2.1852,2.3735,2.5618,2.7501
3500,0.529,0.6938,0.8585,1.0233,1.188,1.3528,1.5175,1.6823,1.847,2.0118,2.1766,2.2589
3250,0.4701,0.6231,0.7761,0.929,1.082,1.235,1.388,1.541,1.694,1.847,1.9999,2.0382
3000,0.42,0.5612,0.7024,0.8436,0.9849,1.1261,1.2673,1.4085,1.5497,1.691,1.8322,1.8322
2750,0.3773,0.5068,0.6362,0.7657,0.8951,1.0246,1.154,1.2835,1.4129,1.5424,1.6395,1.6395
2500,0.3407,0.4584,0.5761,0.6937,0.8114,0.9291,1.0468,1.1645,1.2822,1.3998,1.4587,1.4587
2250,0.3076,0.4135,0.5194,0.6253,0.7312,0.8371,0.943,1.049,1.1549,1.2608,1.2873,1.2873
2000,0.2759,0.37,0.4642,0.5583,0.6525,0.7466,0.8408,0.9349,1.0291,1.1232,1.1232,1.1232
1750,0.2451,0.3274,0.4098,0.4922,0.5746,0.657,0.7393,0.8217,0.9041,0.9659,0.9659,0.9659
1500,0.2145,0.2851,0.3557,0.4263,0.4969,0.5675,0.6381,0.7087,0.7793,0.8146,0.8146,0.8146
1250,0.1834,0.2423,0.3011,0.3599,0.4188,0.4776,0.5365,0.5953,0.6541,0.6689,0.6689,0.6689
1000,0.1513,0.1984,0.2455,0.2925,0.3396,0.3867,0.4338,0.4808,0.5279,0.5279,0.5279,0.5279
500,0.1513,0.1984,0.2455,0.2925,0.3396,0.3867,0.4338,0.4808,0.5279,0.5279,0.5279,0.5279
50,0.1513,0.1984,0.2455,0.2925,0.3396,0.3867,0.4338,0.4808,0.5279,0.5279,0.5279,0.5279
0,0.5693,0.5693,0.5693,0.5693,0.5693,0.5693,0.5693,0.5693,0.5693,0.5693,0.5693,0.5693
Then we got given something like this
Time(s),Speed(RPM),Torque(Nm)
0,0,0
0.5,0,0
1,0,0
1.5,0,0
2,0,0
2.5,0,0
3,0,0
3.5,0,0
4,0,0
4.5,0,0
5,0,0
5.5,0,0
6,0,0
6.5,0,0
7,0,0
7.5,0,0
8,0,0
8.5,0,0
9,0,0
9.5,0,0
10,0,0
10.5,0,0
11,0,0
11.5,125.3297809,73.08357584
12,250.6595619,73.0969915
12.5,375.9893428,73.11935093
13,501.3191237,73.15065413
13.5,626.6489047,73.1909011
14,751.9786856,73.24009184
14.5,877.3084665,73.29822635
15,1002.638247,73.36530464
15.5,1002.638247,7.611470158
16,1002.638247,7.611470158
16.5,1002.638247,7.611470158
17,1002.638247,7.611470158
17.5,1002.638247,7.611470158
18,1002.638247,7.611470158
18.5,1002.638247,7.611470158
19,1002.638247,7.611470158
19.5,1002.638247,7.611470158
20,1002.638247,7.611470158
20.5,1002.638247,7.611470158
21,1002.638247,7.611470158
21.5,1002.638247,7.611470158
22,1002.638247,7.611470158
22.5,1002.638247,7.611470158
23,1002.638247,7.611470158
23.5,902.3744227,-45.04597555
24,802.110598,-45.09462967
24.5,701.8467732,-45.13755977
25,601.5829485,-45.17476586
25.5,501.3191237,-45.20624793
26,401.055299,-45.232006
26.5,300.7914742,-45.25204004
27,200.5276495,-45.26635008
27.5,100.2638247,-45.2749361
Using the lookup table we will look at the vehicle data and find the fuel consumption of the vehicle. I ended up manually looking through the look-up table and finding the fuel consumption (imagine the desperation). I was interested if using pandas its possible to automate looking through the 2-D look-up table and go through the data bases and find the fuel consumption without using the obvious if/else statement.
The closest answer that was somewhat helpful was Pandas table lookup. Maybe I didn't understand the theory but I can't figure it out
Initial Idea and some Code
I thought I could use pd.melt to format the lookup table and then using Pandas table lookup answer to match data with the lookup table.
So far the code looks like this (new to Pandas btw)
import pandas as pd
import numpy as np
df = pd.read_csv('vehicle_data.csv')
#Fuel consumption file
fcf = pd.read_csv('fuel_consumption.csv')
# Converting 2-D Look-up table to a two column and result format
fcf = pd.melt(fcf, id_vars=['Speed'], value_vars=['15.0','30.0','45.0','60.0','75.0','90.0','105.0','120.0','135.0','150.0','165.0','180.0'])
# Converting str to number
fcf['variable'] = pd.to_numeric(fcf['variable'])
fcf = fcf.rename({'variable':'TorLook'}, axis=1)
fcf = fcf.rename({'value':'Fuel Consumption(g/s)'}, axis=1)
lower_boundary_limit = fcf.loc[0, 'Speed']
upper_boundary_limit = fcf.loc[3, 'Speed']
match = (fcf['Speed'] <= lower_boundary_limit) & (fcf['Speed'] > upper_boundary_limit)
#Creating empty column
fcf['Result'] = ''
result_to_enter = fcf.loc[0, 'Fuel Consumption(g/s)']
fcf['Result'] = fcf['Result'].apply(lambda score: fcf['Speed'][(fcf['Speed'] <= lower_boundary_limit) & (fcf['Speed'] > upper_boundary_limit)].values[0])
# fcf.to_csv('test.csv')
print(fcf)
Output
Speed TorLook Fuel Consumption(g/s) Result
0 4000 15.0 0.6789 4000
1 3500 15.0 0.5290 4000
2 3250 15.0 0.4701 4000
3 3000 15.0 0.4200 4000
4 2750 15.0 0.3773 4000
.. ... ... ... ...
175 1250 180.0 0.6689 4000
176 1000 180.0 0.5279 4000
177 500 180.0 0.5279 4000
178 50 180.0 0.5279 4000
179 0 180.0 0.5693 4000