Logic to replace column value in mysql python

Question

+--- -----+------------+-------------+
|master_id| product_id |products     |
+---------+------------+-------------+
| 0       | 123       | home        |
| 12      | 234       | office      |
| 15      | 456       | home        |
| 0       | 678       | office      |
| 25      | 890       | home        |
| 0       | 145       | office      |
+---+-----------------+--------------+

I have this table(only a part of rows are displayed,more than 1000 rows), I want to write a logic , such that, whenever the

master_id = 0 , master_id = product_id

I moved It into pandas dataframe. now how do I write this logic

Mureinik · Accepted Answer · 2016-10-28 11:37:58Z

1

You don't need pandas for this, you can do it directly via the database.

If you just want to display the records differently:

SELECT CASE master_id WHEN 0 THEN product_id ELSE master_id END AS master_id,
       product_id,
       products
FROM   mytable

If you actually want to update the database, it's even easier:

UPDATE mytable
SET    master_id = product_id
WHERE  master_id = 0

edited Oct 28, 2016 at 11:37

answered Oct 27, 2016 at 11:02

Mureinik

316k54 gold badges399 silver badges405 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Shubham R Over a year ago

can I use select distinct case(...)

Shubham R Over a year ago

can I use select distinct ? I want to find the count of products my query. SELECT DISTINCT CASE master_id WHEN 0 THEN product_id ELSE master_id END AS master_id, count(distinct(product_id) as total_products from mytable group by master_id

Mureinik Over a year ago

You should group by the same case statement, not the master_id. Using distinct in such a query is possible, but redundant, as the group by takes care of uniqueness.

Gordon Linoff · Accepted Answer · 2016-10-27 11:01:37Z

1

Is this what you want?

select (case when master_id = 0 then product_id else master_id end) as master_id,
       product_id, products
from t;

answered Oct 27, 2016 at 11:01

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

3 Comments

Shubham R Over a year ago

can I use select distinct case(...)

Shubham R Over a year ago

can I use select distinct ? I want to find the count of products my query. select distinct(case when master_id = 0 then product_id else master_id end) as master_id, count(distinct(product_id) as total_products from mytable group by master_id

Gordon Linoff Over a year ago

@SRingne . . . If you have another question, you should ask it as a question and not in a comment. But yes, the distinct in select distinct applies to all expressions in the select. You need to use group by to get a count. If you don't understand that, you should spend some time learning SQL.

PdevG · Accepted Answer · 2016-10-27 11:48:07Z

1

If you want to use a pandas dataframe you can use the numpy.where function

import numpy as np
import pandas as pd

# initialise dataframe
df = pd.DataFrame([[0, 12, 15, 0, 25],
                  [123, 234, 456, 678, 890],
                  ['home', 'office', 'home', 'office', 'home']]).T
df.columns = ['master_id', 'product_id', 'products']

# Now do fix master_id
df.master_id = np.where(df.master_id == 0, df.product_id, df.master_id)

# New df:
    master_id   product_id  products
0   123          123          home
1   12           234          office
2   15           456          home
3   678          678          office
4   25           890          home

answered Oct 27, 2016 at 11:48

PdevG

3,6972 gold badges18 silver badges34 bronze badges

Collectives™ on Stack Overflow

Logic to replace column value in mysql python

3 Answers 3

3 Comments

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related