Create a new column based on calculations that change between rows?

user_11548079 0
Viewed 135

I would like to calculate a sum of variables for a given day. Each day contains a different calculation, but all the days use the variables consistently.

There is a df which specifies my variables and a df which specifies how calculations will change depending on the day.

How can I create a new column containing answers from these different equations? 

import pandas as pd
import numpy as np

conversion = [["a",5],["b",1],["c",10]]
conversion_table = pd.DataFrame(conversion,columns=['Variable','Cost'])

data1 = [[1,"3a+b"],[2,"c"],[3,"2c"]]
to_solve = pd.DataFrame(data1,columns=['Day','Q1'])

desired = [[1,16],[2,10],[3,20]]
desired_table=pd.DataFrame(desired,columns=['Day','Q1 solved'])

I have separated my variables and equations based on row. Can I loop though these equations to find non-numerics and re-assign them? 

#separate out equations and values 

for var in conversion_table["Variable"]:
    cost=(conversion_table.loc[conversion_table['Variable'] == var, 'Cost']).mean()

for row in to_solve["Q1"]:
    equation=row
pandas numpy python
2 Answers

A simple suggestion, perhaps you need to rewrite a part of your code. Not sure if your want something like this:

a = 5
b = 1
c = 10

# Rewrite the equation that is readable by Python
# e.g. replace 3a+b by 3*a+b
data1 = [[1,"3*a+b"],
         [2,"c"],
         [3,"2*c"]]

desired_table = pd.DataFrame(data1,
                        columns=['Day','Q1'])
desired_table['Q1 solved'] = desired_table['Q1'].apply(lambda x: eval(x))
desired_table

Output:

   Day     Q1  Q1 solved
0    1  3*a+b         16
1    2      c         10
2    3    2*c         20
user_121253950

If it's possible to have the equations changed to equations with * then you could do this.

Get the mapping from the

mapping = dict(zip(conversion_table['Variable'], conversion_table['Cost'])

the eval the function and replace variables with numeric from the mapping

desired_table['Q1 solved'] = to_solve['Q1'].map(lambda x: eval(''.join([str(mapping[i]) if i.isalpha() else str(i) for i in x])))

0    16
1    10
2    20
user_60319950
Related