Pandas split DataFrame by Column Function with Grouping

Question

I have a DataFrame with the columns below:

         A      B     C    D
ID                          
276      4    7.0    30    0
76       2    3.0    15    0
203      7    9.5    15   11
197      7    9.5    11    0
261      8    8.5     9   11
119      2    8.0    15   12
547      7    2.5    10    0
385     13   10.0    30    0
202      6    9.5    11    0
286      4    9.0    30    0
459      4    9.5     7    0
36       2    3.0    15    0
442      5    9.0     7    0
554      6    8.0     6    0
309      2    7.5    30    0
334      5    7.0    15    0
238      2    8.5    18    0
6        2    6.0    30    0
237      2    7.5    24    0
509      3    7.0    30    0
319      3    6.0    18    0
179      6    9.0    18    0
328      4    8.0    18    0

I'm attempting to split this DataFrame into multiples by column 'A' where the sum of the values is less than or equal to w with no defined limit on the number of splits or IDs

(i.e. df['A'].sum() <= w)

And if possible, group by the largest values of column 'B' first.

The resulting DataFrames should look like this:

    A   B   C   D
ID
385 13  10  30  0
203 7   9.5 15  11

    A   B   C   D
ID
197 7   9.5 11  0
202 6   9.5 11  0
459 4   9.5 7   0

    A   B   C   D
ID
286 4   9   30  0
442 5   9   7   0
179 6   9   18  0

    A   B   C   D
ID
261 8   8.5 9   11
238 2   8.5 18  0
119 2   8   15  12
554 6   8   6   0

    A   B   C   D
ID
328 4   8   18  0
309 2   7.5 30  0
237 2   7.5 24  0
276 4   7   30  0
334 5   7   15  0
509 3   7   30  0

    A   B   C   D
ID
6   2   6   30  0
319 3   6   18  0
76  2   3   15  0
36  2   3   15  0
547 7   2.5 10  0

What is the simplest method to do this?

So to clarify you want to create multiple new DataFrames where the sum of A is less than w and the sum of B is roughly equal across each new Frame? — Duncan Cam-Stei
– Duncan Cam-Stei, Commented Jun 22, 2020 at 16:09
I'm attempting to chunk the dataframe where the sum of 'A' is less than 'w' and have those chunks be grouped by minimizing the difference in column 'B'. — CCullinan
– CCullinan, Commented Jun 22, 2020 at 16:13

Paul Menzies · Accepted Answer · 2020-06-22 21:42:38Z

I couldn't think of a nice pandas way of doing it so I have just iterated over the dataframe:-

import pandas as pd
from io import StringIO
w = 20
data = StringIO('''
ID       A      B     C    D                   
276      4    7.0    30    0
76       2    3.0    15    0
203      7    9.5    15   11
197      7    9.5    11    0
261      8    8.5     9   11
119      2    8.0    15   12
547      7    2.5    10    0
385     13   10.0    30    0
202      6    9.5    11    0
286      4    9.0    30    0
459      4    9.5     7    0
36       2    3.0    15    0
442      5    9.0     7    0
554      6    8.0     6    0
309      2    7.5    30    0
334      5    7.0    15    0
238      2    8.5    18    0
6        2    6.0    30    0
237      2    7.5    24    0
509      3    7.0    30    0
319      3    6.0    18    0
179      6    9.0    18    0
328      4    8.0    18    0
''')
df = pd.read_csv(data, delimiter=r"\s+")
df.set_index('ID', inplace=True)
df.sort_values(by=['B'], ascending=[False], inplace=True)

dfs = []
new_df = df.iloc[0:0,:].copy()
total = 0

for n, (index, row) in enumerate(df.iterrows()):
    total += row['A']
    if total > w or n+1 >= len(df):
        dfs.append(new_df)
        new_df = df.iloc[0:0, :].copy()
        total = row['A']


    new_df = new_df.append(row)


for group in dfs:
    print(group, end='\n\n')

Output:-

        A     B     C     D
ID                         
385  13.0  10.0  30.0   0.0
203   7.0   9.5  15.0  11.0

       A    B     C    D
ID                      
197  7.0  9.5  11.0  0.0
202  6.0  9.5  11.0  0.0
459  4.0  9.5   7.0  0.0

       A    B     C    D
ID                      
179  6.0  9.0  18.0  0.0
286  4.0  9.0  30.0  0.0
442  5.0  9.0   7.0  0.0
238  2.0  8.5  18.0  0.0

       A    B     C     D
ID                       
261  8.0  8.5   9.0  11.0
328  4.0  8.0  18.0   0.0
119  2.0  8.0  15.0  12.0
554  6.0  8.0   6.0   0.0

       A    B     C    D
ID                      
309  2.0  7.5  30.0  0.0
237  2.0  7.5  24.0  0.0
509  3.0  7.0  30.0  0.0
276  4.0  7.0  30.0  0.0
334  5.0  7.0  15.0  0.0
6    2.0  6.0  30.0  0.0

       A    B     C    D
ID                      
319  3.0  6.0  18.0  0.0
76   2.0  3.0  15.0  0.0
36   2.0  3.0  15.0  0.0

Collectives™ on Stack Overflow

Pandas split DataFrame by Column Function with Grouping

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related