Stack Overflow Asked by KDynamic on January 1, 2022
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.
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.
Answered by Venkat J on January 1, 2022
Get help from others!
Recent Answers
Recent Questions
© 2024 TransWikia.com. All rights reserved. Sites we Love: PCI Database, UKBizDB, Menu Kuliner, Sharing RPP