Dunder Mifflin assignment

You are getting 3 files from Dunder Mifflin (cut down to a very small subset) Your mission should you choose to accept it is to generate an output table in the following format (with an example – and actually correct – entry) SCPRODUCTSOLDTRANSFERRED_INON_HAND 28011710160 The total result should have 18 entries use that and the sample entry as a check. SC is the ‘Service Center’ (= branch) PRODUCT is the product ID SOLD is the number of units of this product sold in this SC in the month of May! Transferred_in is the number of units of this SKU that were transferred into this SC also in the month of May! And the QOH column is the amount of inventory we have on hand in that SC for that product. Documentation of the source files and the columns is below. A couple of hints: – cleanup will be required (look closely) – you can convert DateTime objects to months using the df.DATE.dt.month function – you will have to generate the individual columns separately and then join them together afterwards. – When you use the .merge function you can merge on multiple columns by usingon=[‘col1’’col2’] – To derive the individual columns you will need to use ‘group by’. You can group by multiple columns using .groupby([‘A’’B’]).agg({QTY : sum}) – Once you use groupby that grouping columns will become an index.You can convert it back to a column using reset_index(inplace=True). – To pick dates please use the INVOICE_ORDER_DATE column not the ORDER_DATE one Example for the last part: s1=sales.groupby(’Customer’).agg({‘QTY’ : ‘sum}) s1.reset_index(inplace=True) – For the final output use LEFT joins starting with the sales then the transfers finally the inventory Deliverable is a notebook file with all your steps and the output included. Pandas or R I don’t care. But it needs to work Data files: Sales test – a file with the sales transactions. Most fields should be obvious SHIP_VIA is the mode of shipping (e.g. UP for UPS) – not important. SHIP_SC is the Service Center from which it ships SKU master test – a file with the SKU master data. Important non-obvious fields are SC – Service Center (=branch) QOH – quantity on hand – actual current inventory EOQ – the economic re quantity DATE – the date this data was pulled you can ignore most of the rest but try to guess what they might be Transfers test – a file with the transfers of products between branches. The fields are RUN_DATE – date when this report was run TO – SC from which the item was shipped FR – SC to which the item was shipped rest is obvious

BE VERY CAREFUL WHEN YOU LOOK AT THE CSV FILES WITH EXCEL – DO NOT HIT ‘SAVE’ OR EXCEL WILL MESS THEM UP AND YOU HAVE TO RE-DOWNLOAD THEM

Requirements: N/A