Grouping dataframe using Python

Question

My dataframe:

  ordercode             quantity
PMC21-AA1U1FBWBJA           1
PMP23-GR1M1FB3CJ            1
PMC11-AA1U1FJWWJA           1
PMC11-AA1U1FBWWJA+I7        2
PMC11-AA1U1FJWWJA           3
PMC11-AA1L1FJWWJA           3

My desired output:

  Group    ordercode                quantity
    0       PMC21-AA1U1FBWBJA           1
            PMP23-GR1M1FB3CJ            1
             PMC11-AA1U1FJWWJA          1
            PMC11-AA1U1FBWWJA+I7        1
    1      PMC11-AA1U1FBWWJA+I7         1
            PMC11-AA1U1FJWWJA           3
    2      PMC11-AA1L1FJWWJA            3

So here my desired result is based on column['quantity']. Max value of quantity is 4.

In group0 & group1 the total values (1+1+1+1=4)(1+3=4) (i.e keeping the max vale of quantity as 4). In group2 we can see that no values to add so the group is formed by the left over (here it is 3). In group0 & group1 we can see that PMC11-AA1U1FBWWJA+I7's value splits.

I got little help from the forum and done the following coding:

df = pd.DataFrame(np.concatenate(df.apply(lambda x: [x[0]] * x[1], 1).as_matrix()), 
              columns=['ordercode'])
df['quantity'] = 1
df['group'] = sorted(range(0, len(df)/3, 1) * 4)[0:len(df)]
df.groupby(['group', 'ordercode']).sum()

but I am getting error.

Type error: 'float' object cannot be interpreted as an integer

If I use int in

df['group'] = sorted(range(0, int(len(df)/3), 1) * 4)[0:len(df)]

again I'm getting typeerror. Can anyone tell me why?

576i · Accepted Answer · 2018-09-25 08:23:35Z

2

Assuming you use Python3, type a double slash // to do integer division and convert the range to a list.

df['group'] = sorted(list(range(0, len(df) // 3, 1)) * 4)[0:len(df)]

For the second attempt, it looks like this.

df['group'] = sorted(list(range(0, int(len(df) / 3), 1)) * 4)[0:len(df)]

So the full code runs like this. I copied your example to the clipboard before running.

import pandas as pd
import numpy as np
df = pd.read_clipboard()
df = pd.DataFrame(np.concatenate(df.apply(lambda x: [x[0]] * x[1], 1).as_matrix()), 
              columns=['ordercode'])
df['quantity'] = 1
df['group'] = sorted(list(range(0, len(df) // 3, 1)) * 4)[0:len(df)]
df = df.groupby(['group', 'ordercode']).sum()
print(df)

The ".as_matrix()" command you used generates a warning, but works.

edited Sep 25, 2018 at 8:23

answered Sep 25, 2018 at 8:05

576i

8,45216 gold badges62 silver badges106 bronze badges

Sign up to request clarification or add additional context in comments.

12 Comments

i1100362 Over a year ago

Yes I'm using Python 3.I'm getting Typeerror: unsupported operand type(s) for *:'range' and 'int'

576i Over a year ago

I updated the reply. There was a bracket problem in the first line.

i1100362 Over a year ago

When I Use list function its giving me a warning.

Future warning: Method .as_Matrix will be removed in a future version. Use .values Instead.df = pd.DataFrame(np.concatenate(df.apply(lambda x: [x[0]] * x[1], 1).as_matrix()),                columns=['ordercode'])

And I didn't get any result

576i Over a year ago

I ran your example in jupyter notebooks and get the requested result. I will update the answer to include the running example

i1100362 Over a year ago

Hey I got the result of df['group'] = sorted(list(range(0, len(df) // 3, 1)) * 4)[0:len(df)] but It didn't group df.groupby(['group', 'ordercode']).sum()

|

Collectives™ on Stack Overflow

Grouping dataframe using Python

1 Answer 1

12 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

12 Comments

Your Answer

Sign up or log in

Post as a guest

Related