Stack Exchange
Iterate and update pandas dataframe simultaneously
I have a dataframe containing Level, Product ID and Cost.
Here Level 1 indicates it is a main product and Level 2 indicates it is a sub-product and further increase in Level indicates multiple sub-products of a sub-product.
    Level    Product ID    Cost
0   1         111           12
1   1         112           15
.
.
.
25  1         294           32

I need to iterate on the above dataframe and search in database if any product with specific Product ID has a sub-product. For example, product with Product ID 112 can have 2 sub-products with Product ID 1121 and 1122. Then I need to add these 2 sub-products in my dataframe.
Note: Product ID can be any number or string. It need not be a multiple of its base product id.
Another condition here is that a sub-product can have further sub-products. For example, a sub-product 1122 can have 3 sub-products 11221, 11222, 11223. 
Also, if a product has sub-products then the cost of the product should be equal to sum of the cost of all it's sub-products. 
The final dataframe must look like this.
    Level    Product ID    Cost
0   1        111           12
1   1        112           15
2   2        1121          8
3   2        1122          7
4   3        11221         2
5   3        11222         3
6   3        11223         2
.
.
.
27  1        294           32

Can someone please help me in achieving this solution. Below is the code that I tried.
for i, _ in multi_bom_df.iterrows():
        if i == 0:
            multi_bom_df.at[i, 'Level'] = '1'
        else:
            multi_bom_df.at[i, 'Level'] = str(current_level)
            base_part_number = str(multi_bom_df.loc[i]['Name'])
            sub_assemblies = models.MultiLevel.objects.filter(base_part=base_part)
            if sub_assemblies.exists():
                current_level += 1
                for index, record in enumerate(sub_assemblies):
                    sub_index = i + (index + 1) / 10
                    multi_bom_df.at[sub_index, 'Level'] = current_level
                    multi_bom_df.at[sub_index, 'Product ID'] = record.sub_assembly_product_id
                    multi_bom_df.at[sub_index, 'Cost'] = record.cost
                multi_bom_df.index = multi_bom_df.index.astype(float)
                multi_bom_df = multi_bom_df.sort_index()```



Would you please provide a minimal and reproducible example of your code? stackoverflow.com/help/minimal-reproducible-example

There are to many unknowns here - format your question and add in the full logic. The underlying solution is simple - at each level increase apply a multiplcation *10 for level  2 and *100 for level 3 and then groupby with add & cumcount

@Datanovice Could you please elaborate your answer based on the code that I have tried.

Here are the directions for something like this.  Take the product ID and turn it into a string.  Put character 1 of the product ID into it's own column "pid1"  do the same for the 2nd character "pid2" and "pid3" for the 3rd, as well as "pid4" (N-1 columns)
df.groupby(['pid1','pid2','pid3','pid4']).agg({'cost':'sum'})

this will get you all the level 4 stuff (the sums of level 5 things)
df.groupby(['pid1','pid2','pid3']).agg({'cost':'sum'})

this will get you all the level 3 stuff.
Note: This is a terrible format for things it assumes you only have <1000 products with subassemblies or that a subassembly can't be used in other products.
