Update inventory stock csv file with python

Question

I have a stock inventory file in csv format like this:

sku nome    prezzo  qty codice 
1   uno       10    1   11111
2   due       10    1   22222
3   tre       10    1   33333
4   quattro   10    1   44444
5   cinque    10    1   55555
10  dieci     10    1   101010

The only column that can be used as a key is 'sku'. The file is update adding new sku,updating existings and deleting sku that are no more present in stock, like this:

sku nome    prezzo  qty codice 
  1  uno        20    2  11111
  2  due        20    2  22222
  3  tre        20    2  33333
  5  cinque     20    2  55555
 10  dieci      20    2  101010
 11  undici     20    2  111111

I'm new in python but with pandas module and 2 or 3 line of code I've make a outer merge :

import pandas as pd

    a = pd.read_csv("./old.csv")
    b = pd.read_csv("./new.csv")

    c = pd.merge(a, b, on = 'sku', how = 'outer', indicator = True)
    c.to_csv("./updated.csv", index=False)

The result is right:

sku nome_x  prezzo_x    qty_x   codice _x   nome_y  prezzo_y    qty_y   codice _y   _merge
1   uno 10  1   11111   uno 20  2   11111   both
2   due 10  1   22222   due 20  2   22222   both
3   tre 10  1   33333   tre 20  2   33333   both
4   quattro 10  1   44444                   left_only
5   cinque  10  1   55555   cinque  20  2   55555   both
10  dieci   10  1   101010  dieci   20  2   101010  both
11                  undici  20  2   111111  right_only

but isnt an useable csv... I hope that I can have this:

sku nome    prezzo  qty codice
  1  uno        20  2   11111
  2  due        20  2   22222
  3  tre        20  2   33333
  4  quattro     0  0   44444
  5  cinque     20  2   55555
  10 dieci      20  2   101010
  11 undici     20  2   111111

MaxU - stand with Ukraine · Accepted Answer · 2017-11-13 20:30:49Z

1

IIUC:

In [52]: r = b.set_index('sku') \
    ...:       .reindex(pd.Index(a['sku']).union(pd.Index(b['sku']))) \
    ...:       .combine_first(a.set_index('sku').assign(qty=0, prezzo=0)) \
    ...:       .reset_index()
    ...:

In [53]: r[['prezzo','qty','codice']] = r[['prezzo','qty','codice']].astype(int)

In [54]: r
Out[54]:
   sku     nome  prezzo  qty  codice
0    1      uno      20    2   11111
1    2      due      20    2   22222
2    3      tre      20    2   33333
3    4  quattro       0    0   44444
4    5   cinque      20    2   55555
5   10    dieci      20    2  101010
6   11   undici      20    2  111111

edited Nov 13, 2017 at 20:30

answered Nov 13, 2017 at 20:05

MaxU - stand with Ukraine

212k37 gold badges402 silver badges437 bronze badges

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

10 Comments

twindad Over a year ago

I haven't tried your code, but prezzo and qty aren't right. Only last record is

twindad Over a year ago

Thank you for interest! I had to read well your code to understand it,but seems very good writed. One thing: how to remove float to qty and codice? I'll read the sources as str?

twindad Over a year ago

And ( this is crucial) 4 is a out of stock item, so the price and qty must be set to 0 not with old information

twindad Over a year ago

r[['prezzo','qty','codice']] = r[['prezzo','qty','codice']].astype(int) give me KeyError: "['codice'] not in index" error.

cs95 Over a year ago

@twindad In case the error wasn't obvious enough for you, check your column names?

|

Collectives™ on Stack Overflow

Update inventory stock csv file with python

1 Answer 1

10 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

10 Comments

Your Answer

Sign up or log in

Post as a guest

Related