Split a pandas dataframe into rows based on an integer column

Question

Not a an ideal title but I wouldn't know how to describe it better.

I have a dataframe (df1) and want to split it on the column "chicken" so that:

each chicken that laid an egg becomes a distinct row
the chickens that didn't lay an egg are aggregated in a unique row.

The output I need is df2, example:

In farm "A", there are 5 chicken, of which 2 chicken laid an egg, so there are 2 rows with egg = "True" and weight = 1 each, and 1 row with egg = "False" and weight = 3 (the 3 chicken that didn't lay an egg).

The code I came up with is messy, can you guys think of a cleaner way of doing it? Thanks!!

#code to create df1:
df1 = pd.DataFrame({'farm':["A","B","C"],"chicken":[5,10,5],"eggs":[2,3,0]})
df1=df1[["farm","chicken","eggs"]]


#code to transform df1 to df2:
df2 = pd.DataFrame()
for i in df1.index:
    number_of_trues = df1.iloc[i]["eggs"]
    number_of_falses = df1.iloc[i]["chicken"] - number_of_trues
    col_farm = [df1.iloc[i]["farm"]]*(number_of_trues+1)
    col_egg = ["True"]*number_of_trues + ["False"]*1
    col_weight = [1]*number_of_trues + [number_of_falses]
    mini_df = pd.DataFrame({"farm":col_farm,"egg":col_egg,"weight":col_weight})
    df2=df2.append(mini_df)
df2 = df2[["farm","egg","weight"]]
df2

BENY · Accepted Answer · 2018-04-17 18:56:29Z

2

This is customize solution , by creating two different sub dataframe then concat it back to achieve the expected output.Key method : repeat

s=pd.DataFrame({'farm':df1.farm.repeat(df1.eggs),'egg':[True]*df1.eggs.sum(),'weight':[1]*df1.eggs.sum()})
t=pd.DataFrame({'farm':df1.farm,'egg':[False]*len(df1.farm),'weight':df1.chicken-df1.eggs})
pd.concat([t,s]).sort_values(['farm','egg'],ascending=[True,False])
Out[847]: 
     egg farm  weight
0   True    A       1
0   True    A       1
0  False    A       3
1   True    B       1
1   True    B       1
1   True    B       1
1  False    B       7
2  False    C       5

answered Apr 17, 2018 at 18:56

BENY

324k22 gold badges176 silver badges250 bronze badges

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

4 Comments

Julien Massardier Over a year ago

nice approach, tks @Wen !

BENY Over a year ago

@JulienMassardier yw : -) happy coding

BENY Over a year ago

@JulienMassardier btw, would you like consider accept this ?

Julien Massardier Over a year ago

sounds fair, tks again for looking into it :) @Wen

Collectives™ on Stack Overflow

Split a pandas dataframe into rows based on an integer column

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related