I am trying to make a python program that will calculate a result based on a formula, given factors and input data table. I am having troubles making it and I couldn't find a solution on my own nor a similar problem on SO. I hope someone can help!
Below I am showing my sample data in order to understand if I am doing something wrong.
My input data looks like this. I have a number of cars (N_cars) on a given length of the road (l) and their average speed (v).
input_columns = ['l', 'N_cars', 'v']
input_data = [[3.5, 1000, 100], [5.7, 500, 110],
[10, 367, 110], [11.1, 1800, 95],
[2.8, 960, 105], [4.7, 800, 120],
[10.4, 103, 111], [20.1, 1950, 115]]
input_df = pd.DataFrame(input_data, columns=input_columns)
input_df
l N_cars v
0 3.5 1000 100
1 5.7 500 110
2 10.0 367 110
3 11.1 1800 95
4 2.8 960 105
5 4.7 800 120
6 10.4 103 111
7 20.1 1950 115
I also know the factors needed for the formula for each category of cars, and I know the % of each category of total category. I also have different options for each category (3 options that I have here are just an example, there are many more options).
factors_columns = ['category', 'category %', 'option', 'a', 'b', 'c', 'd', 'e', 'f', 'g', 'h']
factors_data = [['A', 58, 'opt_1', 0.000011, 0.23521, 0.93847, 0.39458, 0.00817, 0.24566, 0.0010, 0],
['A', 58, 'opt_2', 0.000011, 0.23521, 0.93145, 0.39458, 0.00467, 0.24566, 0.0010, 0],
['A', 58, 'opt_3', 0.000011, 0.23521, 0.93145, 0.39458, 0.00467, 0.24566, 0.0010, 0],
['B', 22, 'opt_1', 0.002452, 0.48327, 0.83773, 0.92852, 0.00871, 0.29568, 0.0009, 0.02],
['B', 22, 'opt_2', 0.002899, 0.49327, 0.83773, 0.92852, 0.00871, 0.30468, 0.0009, 0.02],
['B', 22, 'opt_3', 0.002452, 0.48327, 0.83773, 0.92852, 0.00771, 0.29568, 0.0119, 0.01],
['C', 17, 'opt_1', 0.082583, 0.39493, 0.02462, 0.82714, 0.00918, 0.28572, 0.0012, 0],
['C', 17, 'opt_2', 0.072587, 0.35493, 0.02852, 0.82723, 0.00912, 0.29572, 0.0018, 0],
['C', 17, 'opt_3', 0.082583, 0.39493, 0.02852, 0.82714, 0.00962, 0.28572, 0.0012, 0.01],
['D', 3, 'opt_1', 0.018327, 0.32342, 0.82529, 0.92752, 0.00988, 0.21958, 0.0016, 0],
['D', 3, 'opt_2', 0.014427, 0.32342, 0.82729, 0.92752, 0.00968, 0.22558, 0.0026, 0],
['D', 3, 'opt_3', 0.018327, 0.32342, 0.82729, 0.94452, 0.00988, 0.21258, 0.0016, 0]]
factors_df = pd.DataFrame(factors_data, columns=factors_columns)
factors_df
category category % option a b c d e f g h
0 A 58 opt_1 0.000011 0.23521 0.93847 0.39458 0.00817 0.24566 0.0010 0.00
1 A 58 opt_2 0.000011 0.23521 0.93145 0.39458 0.00467 0.24566 0.0010 0.00
2 A 58 opt_3 0.000011 0.23521 0.93145 0.39458 0.00467 0.24566 0.0010 0.00
3 B 22 opt_1 0.002452 0.48327 0.83773 0.92852 0.00871 0.29568 0.0009 0.02
4 B 22 opt_2 0.002899 0.49327 0.83773 0.92852 0.00871 0.30468 0.0009 0.02
5 B 22 opt_3 0.002452 0.48327 0.83773 0.92852 0.00771 0.29568 0.0119 0.01
6 C 17 opt_1 0.082583 0.39493 0.02462 0.82714 0.00918 0.28572 0.0012 0.00
7 C 17 opt_2 0.072587 0.35493 0.02852 0.82723 0.00912 0.29572 0.0018 0.00
8 C 17 opt_3 0.082583 0.39493 0.02852 0.82714 0.00962 0.28572 0.0012 0.01
9 D 3 opt_1 0.018327 0.32342 0.82529 0.92752 0.00988 0.21958 0.0016 0.00
10 D 3 opt_2 0.014427 0.32342 0.82729 0.92752 0.00968 0.22558 0.0026 0.00
11 D 3 opt_3 0.018327 0.32342 0.82729 0.94452 0.00988 0.21258 0.0016 0.00
GOAL: For each option (opt_1, opt_2, opt_3), I have to calculate the result based on this formula (factors are taken from the factors table, but v is coming from the input table):
formula = ( (a*v*v) + (b*v) + c + (d/v) ) / ( (e*v*v) + (f*v) + g) * (1 - h)
result = l * N_cars * formula
BUT, I have to take into account the % of each category of cars. For each row of the input_df I have to make calculations 3 times, for each of the 3 options. For example, for the index 0 of input_df, I have 1000 cars, v=100 and l=3.5, the output should be something like this:
for opt_1:
result = 3.5 * 1000 * ( (58% of category A {formula for index 0 of factors_df}) +
(22% of category B {formula for index 3 of factors_df) +
(17% of category C {formula for index 6 of factors_df}) +
(3% of category D {formula for index 9 of factors_df}) )
for opt_2:
result = 3.5 * 1000 * ( (58% of category A {formula for index 1 of factors_df}) +
(22% of category B {formula for index 4 of factors_df) +
(17% of category C {formula for index 7 of factors_df}) +
(3% of category D {formula for index 10 of factors_df}) )
for opt_3:
result = 3.5 * 1000 * ( (58% of category A {formula for index 2 of factors_df}) +
(22% of category B {formula for index 5 of factors_df) +
(17% of category C {formula for index 8 of factors_df}) +
(3% of category D {formula for index 11 of factors_df}) )
So, as an output, for each of the rows in input_df, I should have 3 results, each for one of the 3 options.
I can do the calculation manually for each step, but what I am having troubles with is to make a loop that does it automatically for each input row and all 3 options and then passes to the next input row and so on until the last input row.
If anyone has an idea how to solve this easily, please do help!
from How to make a python program that calculates a result for each row of the input table?
No comments:
Post a Comment