In [2]:
import collections
import csv
import itertools
import json
import pandas as pd
Join duplicate Part Numbers and write to file
In [3]:
pn_and_qty_with_duplicates = pd.read_csv('data/PN_and_QTY_with_duplicates.csv')
In [10]:
pn_and_qty_no_duplicates = dict([(key, 0) for key in pn_and_qty['PN']])
pns = pn_and_qty['PN']
qtys = pn_and_qty['QTY']
for pn, qty in itertools.izip(pns, qtys):
pn_and_qty_no_duplicates[pn] += qty
In [13]:
with open('data/PN_and_QTY.csv', 'wb') as f:
w = csv.DictWriter(f, ['PN', 'QTY'])
w.writeheader()
w.writerows([{'PN': key, 'QTY': val}
for key, val in pn_and_qty_no_duplicates.items()])
Import and prepare Part Numbers data
In [4]:
pn_and_qty = pd.read_csv(r'data/PN_and_QTY.csv')
Import and prepare data from 1C
In [5]:
data_from_1c = pd.read_csv(r'data/WOSS.csv')
data_from_1c.columns = ['Part no.'] + list(data_from_1c.columns[1:])
data_from_1c.columns
Out[5]:
In [54]:
data_from_1c = data_from_1c[[u'Part no.', u'Part description', u'PO no.', u'PO date', u'PO qty',
u'Customs file no.', u'Collected from port']]
data_from_1c[u'Collected from port'] = pd.to_datetime(data_from_1c[u'Collected from port'], dayfirst=True)
data_from_1c['PO qty'] = pd.to_numeric(data_from_1c[u'PO qty'], errors='coerce')
data_from_1c = data_from_1c.dropna(subset=[u'Collected from port', u'PO qty'])
data_from_1c.sort_values(by=[u'Collected from port'], ascending=False, inplace=True)
data_from_1c.columns
Out[54]:
Import and prepare data from Store
In [9]:
data_from_store = pd.read_csv(r'data/STORE_01012014-30062014.csv', low_memory=False)
data_from_store.columns
Out[9]:
In [10]:
data_from_store = data_from_store[[u'Part Number', u'Transaction Id', u'Last Updated Date', u'Quantity Txn Part',
u'Transaction Status Name']]
data_from_store = data_from_store[data_from_store[u'Transaction Status Name'] == 'Closed']
data_from_store[u'Last Updated Date'] = pd.to_datetime(data_from_store[u'Last Updated Date'])
data_from_store.sort_values(by=[u'Last Updated Date'], ascending=False, inplace=True)
data_from_store.columns
Out[10]:
In [ ]:
Process all Part Numbers and their quantities
In [13]:
pn_qty = {pn: {'QTY': qty} for pn, qty in itertools.izip(pn_and_qty['PN'], pn_and_qty['QTY'])}
In [64]:
pns = data_from_store[u'Part Number']
pn1c = data_from_1c[u'Part no.']
In [55]:
for pn, table in pn_qty.iteritems():
pn_qty[pn]['storeinfo'] = data_from_store[0:0]
pn_qty[pn]['1cstoreinfo'] = data_from_1c[0:0]
pn_qty[pn]['1cinfo'] = data_from_1c[0:0]
qty_left_store = qty_left_1c = table['QTY']
table_store = data_from_store[data_from_store[u'Part Number'] == pn]
table_1c = data_from_1c[data_from_1c[u'Part no.'] == pn]
while qty_left_store > 0 and not table_store.empty:
pn_qty[pn]['storeinfo'].append(table_store.iloc[0], ignore_index=True)
qty_left_1c_store = table_store.iloc[0].at[u'Quantity Txn Part']
qty_left_store -= qty_left_1c_store
store_time = table_store.iloc[0].at[u'Last Updated Date']
table_1c_store = table_1c[table_1c[u'Collected from port'] < store_time]
while qty_left_1c_store > 0 and not table_1c_store.empty:
pn_qty[pn]['1cstoreinfo'].append(table_1c_store.iloc[0], ignore_index=True)
qty_recorded_in_1c_and_store = table_1c_store.iloc[0].at[u'PO qty']
qty_left_1c -= qty_recorded_in_1c_and_store
qty_left_1c_store -= qty_recorded_in_1c_and_store
table_1c_store = table_1c_store[1:]
# mask = [row in pn_qty[pn]['1cstoreinfo']]
# table_1c = table_1c.where(table_1c not in pn_qty[pn]['1cstoreinfo'])
# # while qty_left_1c > 0 and ta
In [49]:
qty_recorded_in_1c_and_store
Out[49]:
In [46]:
# qty_left_1c_store = table_store[0:1]
qty_left_1c_store
table_store.iloc[0].at[u'Quantity Txn Part']
In [204]:
mask = [(row in pn_qty[pn]['1cstoreinfo']) for row in table_1c.iterrows()]
In [211]:
table_1c.drop(pn_qty[pn]['1cstoreinfo'])
In [193]:
pn_qty[pn]['1cstoreinfo'].append(table_1c.ix[0])
In [197]:
pn_qty[pn]['1cstoreinfo']
Out[197]:
In [44]:
pn = 'B012828'
table_store = data_from_store[data_from_store['Part Number'] == pn]
table_1c = data_from_1c[data_from_1c['PO no.'] == pn]
pn_qty[pn]['storeinfo'] = []
pn_qty[pn]['1cinfo'] = []
In [165]:
table_store = data_from_store.ix[pns == pn].reset_index(drop=True)
In [183]:
table_store.ix[5:]
Out[183]:
In [150]:
pn_qty[pn]['storeinfo'].append(table_store.ix[0])
In [151]:
qty_left_1c_store = table_store.get_value(0, u'Quantity Txn Part')
In [154]:
store_time
Out[154]:
In [181]:
pn_qty
Out[181]:
In [153]:
store_time = table_store.get_value(0, u'Last Updated Date')
In [155]:
table_1c[u'Collected from port'] < store_time
Out[155]:
In [158]:
table_1c = table_1c.where(table_1c[u'Collected from port'] < store_time)
In [163]:
table_1c.reset_index(drop=True)
Out[163]:
In [ ]:
In [ ]:
In [ ]:
In [37]:
counter = collections.Counter(data_from_1c[u'Part no.'])
In [38]:
pn_counter = {}
for pn in pn_qty.iterkeys():
pn_counter[pn] = counter[pn]
In [39]:
pn_counter
Out[39]:
In [113]:
pn_and_qty.columns
Out[113]:
In [117]:
pn_and_qty[[u'PN', u'QTY']]
Out[117]:
In [ ]:
In [16]:
In [37]:
In [ ]:
In [39]:
list(pn_and_qty.iteritems())
Out[39]:
In [178]:
table = []
for pn in pn_qty.iterkeys():
table.append(data_from_1c.where(data_from_1c['Part no.'] == pn))
In [180]:
table[0]
Out[180]:
In [172]:
type(table)
Out[172]:
In [ ]: