Pandas - Iterate through lists / dictionaries for calculations

user_8670757 0
Viewed 77

I am new to coding & I am looking for a pythonic way to implement the following code. Here is a sample dataframe with code:

np.random.seed(1111)
df2 = pd.DataFrame({
'Product':np.random.choice( ['Prod 1','Prod 2','Prod 3', 'Prod 4','Prod 5','Prod 6','Box 1','Box 2','Box 3'], 10000),
'Transaction_Type': np.random.choice(['Produced','Transferred','Scrapped','Sold'], 10000),
'Quantity':np.random.randint(1,100, size=(10000)),
'Date':np.random.choice( pd.date_range('1/1/2017','12/31/2018',  
                      freq='D'), 10000)})
idx = pd.IndexSlice

In the data set, each 'Box' ('Box 1', 'Box 2', etc.) is a raw material that corresponds to multiple products. For example, 'Box 1' is used for 'Prod 1' & 'Prod 2', 'Box 2' is used for 'Prod 3' & 'Prod 4', & 'Box 3' is used for 'Prod 5' & 'Prod 6'.

The data set I'm working with is much larger, but I have these data sets stored as lists, for example I have 'Box 1' = ['Prod 1', 'Prod 2', 'Prod 3']. If need be, I could store as a dictionary with a tuple like Box1 = {'Box 1':('Prod 1','Prod 2') - whatever is best.

For each grouping, I'm looking to calculate the total number of boxes used which is the sum of 'Produced' + 'Scrapped' inventory. To get this value, I'm currently doing a manual filter on a groupby of each product & filtering manually. You can see I'm manually writing a list of the products as a the second assign statement.

For example, to calculate how much of 'Box 1' to relieve from inventory, each month, you would sum the values of 'Box 1' that was produced & scrapped. Then, you would calculate the values of 'Prod 1' through 'Prod 3' (since they use 'Box 1') that were produced & scrapped & add them all together to get a total 'Box 1' used & scrapped for each time frame. Here's an example of what I'm currently doing:

box1 = ['Box 1','Prod 1','Prod 2']
df2[df2['Transaction_Type'].isin(['Produced','Scrapped'])].groupby([pd.Grouper(key='Date',freq='A' ),'Product','Transaction_Type']).agg({'Quantity':'sum'})\
    .unstack()\
    .loc[idx[:,box1],idx[:]]\
    .assign(Box_1 = lambda x: 'Box 1')\
    .assign(List_of_Products = lambda x: 'Box 1, Prod 1, Prod 2')\
    .reset_index()\
    .set_index(['Box_1','List_of_Products','Date','Product'])\
    .groupby(level=[0,1,2]).sum()\

I'd then have to do the same clunky manual same exercise for 'Box 2', etc.

Is there a more pythonic way? I would like to complete this analysis each month going forward. The actual data is much more complex with roughly 20 different 'Boxes' that have a varying number of products associated with each. I'm not sure if I should be looking to create a function or use a dictionary vs. lists, but would appreciate any help along the way. As a last request, I'd love to have the flexibility to write each of these 'Box_1' to a different excel worksheet.

Thanks in advance!

pandas-groupby pandas python
3 Answers

I think I would filter my source dataframe down to just want I need to query first off then do you grouping and aggregrations:

df2.query('Transaction_Type in ["Produced","Scrapped"] and Product in ["Box 1","Prod 1","Prod 2"]')\
   .groupby([pd.Grouper(key='Date',freq='A'),'Product','Transaction_Type'])['Quantity'].sum()\
   .unstack().reset_index(level=1).groupby(level=0).agg({'Product':lambda x: ', '.join(x),'Produced':'sum','Scrapped':'sum'})

Output:

                          Product  Produced  Scrapped
Date                                                 
2017-12-31  Box 1, Prod 1, Prod 2     20450     19152
2018-12-31  Box 1, Prod 1, Prod 2     19404     16964
user_63615310

Not sure how you want the result at the end, but as each Prod uses only one Box, then you can replace the Prod by its Box and do the groupby like you do. Let's suppose you have a dictionary such as:

box_dict = {'Box 1': ('Prod 1', 'Prod 2'), 
            'Box 2': ('Prod 3', 'Prod 4'), 
            'Box 3': ('Prod 5', 'Prod 6')}

then you want to reverse it to get the prod as the key and the box as the value:

dict_prod = { prod:box for box, l_prod in box_dict.items() for prod in l_prod}

Now you can use replace:

print (df2[df2['Transaction_Type'].isin(['Produced','Scrapped'])]
          .replace({'Product':dict_prod}) #here to change the prod to the box used
          .groupby([pd.Grouper(key='Date',freq='A' ),'Product','Transaction_Type'])['Quantity']
          .sum().unstack())
                    Quantity         
Transaction_Type   Produced Scrapped
Date       Product                  
2017-12-31 Box 1      20450    19152
           Box 2      20848    21145
           Box 3      22475    21518
2018-12-31 Box 1      19404    16964
           Box 2      21655    20753
           Box 3      21343    21576
user_92747320

I do not understand why such a long expression is needed. It seems you only care about the total number of rows satisfying the condition, if I am not totally wrong.

d = {'Box 1': ('Box 1', 'Prod 1', 'Prod 2')}
d_type = {'Box 1': ('Produced', 'Scrapped')}
selected = df2[df2['Product'].isin(d['Box 1']) & df2['Transaction_Type'].isin(d_type['Box 1'])]
print(len(selected))

For your excel exporting needs, something as below would work.

writer = pd.ExcelWriter("test.xlsx")
selected.to_excel(writer, 'Sheet1')
writer.save()
user_79348010
Related