Summarize a column if values in another column are inferior (without for loop)

Question

The dataframe

I have dataframe with many items.

The items are identified by a code "Type" and by a weight.

The last column indicates the quantity.

|-|------|------|---------|
| | type |weight|quantity |
|-|------|------|---------|
|0|100010|   3  |  456    |
|1|100010|   1  |  159    |
|2|100010|   5  |  735    |
|3|100024|   3  |  153    |
|4|100024|   7  |  175    |
|5|100024|   1  |  759    |
|-|------|------|---------|

The compatibility rule

A given item "A" is "compatible" with others items if :

It is the same type
The weights of the other items is equal or less than the weight of the item "A"

The result expected

I want to add a column "compatible quantity" calculating for each row, how many items are compatible.

|-|------|------|---------|---------------------|
| | type |weight|quantity | compatible quantity |
|-|------|------|---------|---------------------|
|0|100010|   3  |  456    |        615          | 456 + 159
|1|100010|   1  |  159    |        159          | 159 only (the lightest items)
|2|100010|   5  |  735    |       1350          | 735 + 159 + 456 (the heaviest)   
|3|100024|   3  |  153    |        912          | 153 + 759
|4|100024|   7  |  175    |       1087          | ...
|5|100024|   1  |  759    |        759          | ...
|-|------|------|---------|---------------------|

I want to avoid to use a For loop ti get this result. (the dataframe is huge).

My code using a For loop

import pandas as pd 

df = pd.DataFrame([[100010, 3, 456],[100010, 1, 159],[100010, 5, 735], [100024, 3, 153], [100024, 7, 175], [100024, 1, 759]],columns = ["type", "weight", "quantity"])

print(df)

for inc in range(df["type"].count()):

    the_type = df["type"].iloc[inc]
    the_weight = df["weight"].iloc[inc]
    the_quantity = df["quantity"].iloc[inc]

    df.at[inc,"quantity_compatible"] = df.loc[(df["type"] == the_type) & (df["weight"] <= the_weight),"quantity"].sum()

print(df)

Some possible ideas

Can "apply" or "Transform" be helpful ?
Can it be done using loc inside a loc ?

score 2 · Accepted Answer · answered Nov 01 '19 at 02:06

First sort your values by weight and type, then do a groupby for cumsum, and finally do a merge on index:

df = pd.DataFrame([[100010, 3, 456],[100010, 1, 159],[100010, 5, 735], [100024, 3, 153], [100024, 7, 175], [100024, 1, 759]],columns = ["type", "weight", "quantity"])

new_df = df.merge(df.sort_values(["type","weight"])
                  .groupby("type")["quantity"]
                  .cumsum(),left_index=True, right_index=True)

print (new_df)

#
     type  weight  quantity_x  quantity_y
0  100010       3         456         615
1  100010       1         159         159
2  100010       5         735        1350
3  100024       3         153         912
4  100024       7         175        1087
5  100024       1         759         759

CypherX · Answer 2 · 2019-11-01T00:20:18.503

Solution

Try this.

import pandas as pd
from io import StringIO

s = """
    type    weight  quantity
0   100010  3   456
1   100010  1   159
2   100010  5   735
3   100024  3   153
4   100024  7   175
5   100024  1   759
"""

def process_dataframe(df, sort_values_by_init_index = True):
    df2 = df.groupby(by=['type','weight']).sum().reset_index()
    df3 = df.groupby(by=['type','weight']).sum().groupby(level=[0], as_index=False)['quantity_compatible'].cumsum().reset_index()
    df2['quantity_compatible'] = df3['quantity_compatible'].tolist()
    if sort_values_by_init_index:
        df2 = df2.sort_values('index')
    #print(df2)
    df2 = df2.reset_index().drop(columns=['index'])

    return df2

df = pd.read_csv(StringIO(s), sep='\t')
df.drop(columns='Unnamed: 0', inplace=True)
df['quantity_compatible'] = df['quantity'].copy()
df = df.reset_index()

# custom function
process_dataframe(df)

Output:

References

@floupinette Try now. Updated the solution to match the expected output. — CypherX, Nov 01 '19 at 00:21