import pandas as pd
from pandasql import *
import pandas as pd
import sqlite3

# Create a SQL connection to our SQLite database
con = sqlite3.connect("orders.db")

cur = con.cursor()
# pysqldf = lambda q: sqldf(q, globals())
# d.read_csv("bindat.csv", parse_dates=[["date", "time"]],
# date_parser=lambda x: pd.to_datetime(x, format="%Y%m%d %H%M"),
# index_col="date_time")

order_df = pd.read_csv('orders.csv', usecols=['date', 'quantity', 'SKU'], parse_dates=['date'])
po_df = pd.read_csv('pos.csv', usecols=['SKU', 'stock_quantity', 'moq', 'lead_time'])

order_df.to_sql('orders', con=con, if_exists='append')


@@ -4,43 +4,16 @@
 ort 
-pandas as pd%0Afrom pandasql import *
+datetime
 %0Aimp
@@ -49,179 +49,222 @@
 e3%0A%0A
-# Create a SQL connection to our SQLite database%0Acon = sqlite3.connect(%22orders.db%22)%0A%0Acur = con.cursor()%0A# pysqldf = lambda q: sqldf(q, globals())%0A# d.read_csv(%22bindat.csv%22
+con = sqlite3.connect(':memory:')%0Acon.text_factory = str%0Acur = con.cursor()%0A%0A%0Adef fix_sku(row):%0A    return str(row%5B'SKU'%5D).replace(' ', '')%0A%0A%0Aorder_df = pd.read_csv('orders.csv', usecols=%5B'date', 'quantity', 'SKU'%5D
 , pa
@@ -278,353 +278,1715 @@
 es=%5B
-%5B%22
+'
 date
-%22, %22time%22%5D%5D,%0A# date_parser=lambda x: pd.to_datetime(x, format=%22%25Y%25m%25d %25H%25M%22),%0A# index_col=%22date_time%22)%0A%0Aorder_df = pd.read_csv('orders.csv', usecols=%5B'date', 'quantity', 'SKU'%5D, parse_dates=%5B'date'%5D)%0Apo_df = pd.read_csv('pos.csv', usecols=%5B'SKU', 'stock_quantity', 'moq', 'lead_time'%5D)%0A%0Aorder_df.to_sql('orders', con=con, if_exists='append')
+'%5D)%0Apo_df = pd.read_csv('pos.csv', usecols=%5B'SKU', 'stock_quantity', 'moq', 'lead_time'%5D)%0A%0Aorder_df%5B'SKU'%5D = order_df.apply(fix_sku, axis=1)%0Apo_df%5B'SKU'%5D = po_df.apply(fix_sku, axis=1)%0A%0Aorder_df.to_sql('orders', con=con, if_exists='replace')%0Apo_df.to_sql('pos', con=con, if_exists='replace')%0A%0Aq = %22%22%22%0Aselect%0A    order_data.SKU,%0A    julianday(max_date) - julianday(min_date) as total_days,%0A    qty_sum,%0A    pos_data.moq,%0A    pos_data.lead_time,%0A    pos_data.stock_qty as on_hand_inventory %0A    from (%0A        select %0A            SKU,%0A            max(date) as max_date,%0A            min(date) as min_date,%0A            sum(quantity) as qty_sum%0A        from orders%0A        group by SKU%0A        ) as order_data%0A    left outer join (%0A        select %0A            SKU,%0A            max(moq) as moq,%0A            max(lead_time) as lead_time,%0A            sum(stock_quantity) as stock_qty%0A        from pos%0A        group by SKU%0A    )pos_data on pos_data.SKU = order_data.SKU%0A%22%22%22%0Aresults = pd.read_sql(q, con=con)%0A%0A%0Adef days_inv(row):%0A    days = row%5B'total_days'%5D/row%5B'qty_sum'%5D%0A    return int(days)%0A%0A%0Adef reorder_days(row):%0A    days = row%5B'days_inv'%5D - row%5B'lead_time'%5D%0A    return days%0A%0A%0Adef reorder_date(row):%0A    now = datetime.datetime.now().date()%0A    try:%0A        end_date = now + datetime.timedelta(days=row%5B'reorder_days'%5D)%0A    except ValueError:%0A        end_date = now%0A    return end_date%0A%0Aresults%5B'days_inv'%5D = results.apply(days_inv, axis=1)%0Aresults%5B'reorder_days'%5D = results.apply(reorder_days, axis=1)%0Aresults%5B'reorder_day'%5D = results.apply(reorder_date, axis=1)%0A%0Adel results%5B'total_days'%5D%0Adel results%5B'qty_sum'%5D%0Adel results%5B'reorder_days'%5D%0A%0Aresults.to_csv('reorder_schedule.csv', index=False)%0Aprint results
 %0A%0A

version 1