Grouped subsets operations
Task:Calculate the daily inventory status of various goods in the specified time period.
Python
| 1 | import pandas as pd |
| 2 | import numpy as np |
| 3 | starttime = '2015-01-01' |
| 4 | endtime = '2015-12-31' |
| 5 | stock_data = pd.read_csv('E:\\txt\\stocklog.csv',sep='\t') |
| 6 | stock_data['DATE']=pd.to_datetime(stock_data['DATE']) |
| 7 | stock_data = stock_data[(stock_data['DATE']>=starttime)&(stock_data['DATE']<=endtime)] |
| 8 | stock_data['ENTER']=stock_data['QUANTITY'][stock_data['INDICATOR']!='ISSUE'] |
| 9 | stock_data['ISSUE']=stock_data['QUANTITY'][stock_data['INDICATOR']=='ISSUE'] |
| 10 | stock_g = stock_data[['STOCKID','DATE','ENTER','ISSUE']].groupby(by=['STOCKID','DATE'],as_index=False).sum() |
| 11 | stock_gr = stock_g.groupby(by='STOCKID',as_index = False) |
| 12 | date_df = pd.DataFrame(pd.date_range(starttime,endtime),columns=['DATE']) |
| 13 | stock_status_list = [] |
| 1415 | for index,group in stock_gr: |
| 16 | date_df['STOCKID']=group['STOCKID'].values[0] |
| 17 | stock_status = pd.merge(date_df,group,on=['STOCKID','DATE'],how='left') |
| 18 | stock_status = stock_status.sort_values(['STOCKID','DATE']) |
| 19 | stock_status['OPEN']=0 |
| 20 | stock_status['CLOSE']=0 |
| 21 | stock_status['TOTAL']=0 |
| 22 | stock_status = stock_status.fillna(0) |
| 23 | stock_value = stock_status[['STOCKID','DATE','OPEN','ENTER','TOTAL','ISSUE','CLOSE']].values |
| 24 | open = 0 |
| 25 | for value in stock_value: |
| 26 | value[2] = open |
| 27 | value[4] = value[2] + value[3] |
| 28 | value[6] = value[4] - value[5] |
| 29 | open = value[6] |
| 30 | stock = pd.DataFrame(stock_value,columns = ['STOCKID','DATE','OPEN','ENTER','TOTAL','ISSUE','CLOSE']) |
| 31 | stock_status_list.append(stock) |
| 32 | stock_status = pd.concat(stock_status_list,ignore_index=True) |
| print(stock_status) |
esProc
| A | B | |
| 1 | =file("E:\\txt\\stocklog.csv").import@t() | |
| 2 | =A1.select(DATE>=date("2015-01-01") && DATE<=date("2015-12-31")) | |
| 3 | =A2.groups(STOCKID,DATE; sum(if(INDICATOR=="ISSUE",QUANTITY,0)):ISSUE, sum(if(INDICATOR!="ISSUE",QUANTITY,0)):ENTER) |
|
| 4 | =periods(start,end) | |
| 5 | for A3.group(STOCKID) | =A5.align(A4,DATE) |
| 6 | >b=c=0 | |
| 7 | =B5.new(A5.STOCKID:STOCKID,A4(#):DATE,c:OPEN,ENTER, (b=c+ENTER):TOTAL,ISSUE, (c=b-ISSUE):CLOSE) |
|
| 8 | >B7.run(ENTER=ifn(ENTER,0),ISSUE=ifn(ISSUE,0)) | |
| 9 | =@|B7 |
esProc 9 lines of code complete the task of Python 32 lines of code, and when you read the code later on, esProc is easier to understand.
