0

Need help in writing this SQL update statement in python using the pandas library.

UPDATE example_table
SET NEW_COLUMN = EXISITNG_COLUMN *2
WHERE SOME_OTHER_EXISTING_COLUMN IN ('Value1','Value2','Value3','Value4')

Just to clarify, I want to create a NEW_COLUMN which a multiplication of values in the existing_column belonging to example_table SQLtable/dataframe. The values of NEW_COLUMN don't update if the condition in the where clause is not met. This is important because I would want to multiply with a different number for a another set of values in where condition. Also, it important to know that I am checking the condition on SOME_OTHER_EXISTING_COLUMN in the same SQLtable/dataframe.

I tried the following in python, but somehow it did not work:

value_list = ['Value1','Value2','Value3','Value4']
example_table["NEW_COLUMN"] = pd.DataFrame.where( cond= example_table['SOME_OTHER_EXISTING_COLUMN'].isin(value_list), self= example_table['EXISITNG_COLUMN']*2)

Side note: EXISITNG_COLUMN is float, SOME_OTHER_EXISTING_COLUMN is string/object and NEW_COLUMN should be a float datatype.

KDynamic
  • 1
  • 2
  • Welcome to SO! Try below syntax df['NEW_COLUMN']= df.apply(lambda x: x['EXISITNG_COLUMN ']*2 if x['SOME_OTHER_EXISTING_COLUMN '] == 'Value1' else 0, axis=1) – Venkat J Jul 24 '20 at 18:49
  • This is a fine code. However, if I try using .isin() , which is what I was asking in the question, then it throws AttributeError. Update to the question: else condition would require to keep the existing value if the new_column is formed. How will the else condition change in that case? – KDynamic Jul 24 '20 at 20:21

1 Answers1

0
record = {
    'Age': [21, 19, 20, 18, 17, 21],
    'Stream': ['Math', 'Commerce', 'Science', 'Math', 'Math', 'Science']}

# create a dataframe
dataframe = pd.DataFrame(record, columns=['Age', 'Stream'])

value_list = ['Math','Commerce','Value3','Value4']
dataframe["NEW_COLUMN"] = 2*dataframe['Age'].where(dataframe['Stream'].isin(value_list), 3*dataframe['Age'])

print(dataframe)

When your column is in your declared list, Age will be multiplied by 2, if not in list, it is multiplied by 3.

Venkat J
  • 305
  • 1
  • 4
  • 12