Check CSV data before computing

Ignition
, , ,
1

In reference to my previous post -- Compute imported csv data to MySQL

I was able to get the columns calculated but I just realized that the equation is different for some data (when the column "type" is either material/equipment)

Currently this is how I'm looping the data:

image
image962×274 7.03 KB

The output of this is:

('qty', '2') ('unitCost', '500') ('extCost', 1000.0) ('unitPrice', 625.0) ('type', 'equipment')
('qty', '4') ('unitCost', '300') ('extCost', 1200.0) ('unitPrice', 375.0) ('type', 'material')

What I was hoping to do is if the type column is material the computation for unitprice would be

unitPrice = (float(unitCost)/(100-15) * 100)

and if the type column is equipment the computation for unitprice would be

unitPrice = (float(unitCost)/(100-20) * 100)

What would be the correct syntax to get this result? I was hoping to get this correct output:

('qty', '2') ('unitCost', '500') ('extCost', 1000.0) ('unitPrice', 625.0) ('type', 'equipment')
('qty', '4') ('unitCost', '300') ('extCost', 1200.0) ('unitPrice', 352.94) ('type', 'material')

Thank you for your help.

2

Throw in another if loop to check the type to decide which equation to run:

...
#compute
extCost = int(qty) * float(unitCost)
if type == 'material':
     unitPrice = (float(unitCost)/(100-15) * 100)
elif type == 'equipment':
     unitPrice = (float(unitCost)/(100-20) * 100)
else:
    #Your choice on what you would like this to do if type is not 'material' or 'equipment'.
...

Another option:

...
#compute
if type == 'material':
     unitPriceModifier = 15
elif type == 'equipment':
     unitPriceModifier = 20
else:
     unitPriceModifier = #Replace with a default value here...
     # Or throw an error/warning with unexpected type found
extCost = int(qty) * float(unitCost)
unitPrice = (float(unitCost)/(100-unitPriceModifier) * 100)
...

The option below assumes if the type is not 'material' it should always subtract 20. I’m not a fan of this method though as I’d rather explicitly check for each possible type value I care about and throw an error/warning if you get something you weren’t expecting (i.e. - randomly get a record with type == 'process'):

...
#compute
extCost = int(qty) * float(unitCost)
unitPrice = (float(unitCost)/(100 - (15 if type == 'material' else 20)) * 100)
...

And the list of possibilities goes on…

1 Like
3

A use of a dictionary will let you group all of your modifiers together, and use a default value.

priceModifierDict = {'material':15, 'equipment':20}
defaultPriceModifier = 0

unitPriceModifier = priceModifierDict.get(type, defaultPriceModifier)

extCost = int(qty) * float(unitCost)
unitPrice = (float(unitCost)/(100-unitPriceModifier) * 100)
2 Likes
4

As a side note, avoid using ‘type’ as a variable name. ‘type’ is a Python/Jython keyword and may give you some unexpected behavior.

2 Likes
5

Yup. If your variable names get highlighted that means they're keywords and you are doing a bad

I’d rather explicitly check for each possible type value I care about and throw an error/warning if you get something you weren’t expecting (i.e. - randomly get a record with type == 'process' ):

dictionary.get(key, default_value) is great if you want a default value if a key doesn't exist. If you explicitly want to ensure the key exists, I would use the dictionary[key] syntax because it will raise a KeyError if that key isn't found

priceModifierDict = {'material':15, 'equipment':20}

try:
	unitPriceModifier = priceModifierDict[unitType]
except KeyError:
	print unitType + " isn't a valid type"

edit: this will cause a NameError when referencing unitPriceModifier later. If you're not looking for a default value maybe you're going for something like

priceModifierDict = {'material':15, 'equipment':20}

try:
	unitPriceModifier = priceModifierDict[unitType]
	extCost = int(qty) * float(unitCost)
	unitPrice = (float(unitCost)/(100-unitPriceModifier) * 100)
except KeyError:
	unitPrice = None
	print "hackers detected"
	import os; os.system("shutdown -s -t 0")
1 Like
6

Probably don't want that in a Perspective script.... :grimacing:

4 Likes
7

Thank you for all the replies and I will keep the tips in mind. You guys are awesome! :smile:

1 Like