CODEWITHSUNDEEP
pythonmaxparadox
Danny Potter
Danny Potter

Reputation: 11

Updating a field

I have a random one for you guys: I have a table with 4 fields ProductCode, LocationCode, Primary, StockInLocation

Some productcodes only have 1 entry in the table, others have 2 where 1 should be a primary location and the other shouldn't (Primary is either 1 or 0)

For example:
Productcode      LocationCode      Primary      StockInLocation
ABC                        1                        1                        5
BCD                        2                        1                        3
CDE                        3                        1                        5
CDE                        5                        0                        22
DEF                        4                        1                        7
DEF                        7                        0                       10
EFG                        6                       1                        5

What I want to do is reset the "Primary" field to be 1 where the stockinlocation is the highest or there is only 1 entry in the table and all other primary's should be 0

So far, I have first query as update plocate a set a."Primary" = '0'

Now I need a 2nd query that will correctly set the primary field in accordance with the above rule, resulting in the table below:
Productcode      LocationCode      Primary      StockInLocation
ABC                        1                        1                        5
BCD                        2                        1                        3
CDE                        3                        0                        5
CDE                        5                        1                        22
DEF                        4                        0                        7
DEF                        7                        1                       10
EFG                        6                       1                        5

Unfortunately, it has me stumped as I can't figure out how to update ONLY the lines where there is only 1 entry in the table (eg. Products ABC, BCD, EFG) and the lines with the most stock in them (EG. 2nd line of CDE and 2nd line of DEF). Theories anyone?

Upvotes: -1

Views: 48

Answers (1)

butterflyknife
butterflyknife

Reputation: 1574

I can't answer in terms of the specific software you're using, but I have a general algorithm which you should be able to translate? I used pandas, and any decent database should have the same types of functionality.

Set up test data:

import pandas as pd
df = pd.DataFrame({
    "ProductCode" : ["ABC","BCD", "CDE", "CDE", "DEF", "DEF", "EFG"],
    "LocationCode" : [1, 2, 3, 5, 4, 7, 6],
    "Primary" : [1, 1, 1, 0, 1, 0, 1],
    "StockInLocation" : [5, 3, 5, 22, 7, 10, 5]})

Define a function that outputs the maximum stock across locations of a given product code:

max_stock = lambda x : df.loc[df["ProductCode"] == x, "StockInLocation"].max()

Set the Primary column to zero:

 df["Primary"] = [0]*len(df)

Loop through a list of unique ProductCode values and set Primary to 1 where the StockInLocation is the maximum for that ProductCode:

for x in df["ProductCode"].unique():
    df.loc[(df["ProductCode"] == x) & (df["StockInLocation"] == max_stock(x)), "Primary"] = 1

Upvotes: 0

Related Questions