CODEWITHSUNDEEP
pythonpandaspandas-groupby
Nishith Neygandhi
Nishith Neygandhi

Reputation: 33

Append several rows as a single row python based on Group by

everyone, I'm new to python I have a dataset like 

ROLLNO     G1     G2     G3    G4
1          12     14     14    20
1          10     12     11    12
2          14     02     16    18
2          18     16     12    14

I want to merge them as

ROLLNO    G1    G2     G3    G4   G5     G6     G7    G8
1         12    14     14    20   10     12     11    12
2         14    02     16    18   18     16     12    14

This is the code I have written but it is giving me the wrong result.

print (CseStudents_ds.groupby('ROLLNO').apply(' '.join).reset_index())

I have searched a lot and thought of using group by but unable to figure out what should be done next. Any help

Upvotes: 0

Views: 90

Answers (2)

U13-Forward
U13-Forward

Reputation: 71610

Try this formatting columns and groupby and new data frame and concat and drop:

v = df.groupby('ROLLNO').apply(lambda x: x.values.tolist())

df = pd.DataFrame(v.tolist(), index=v.index)\
       .rename(columns=lambda x: x + 1)\
       .add_prefix('G')\
       .reset_index()
df1 = pd.DataFrame(df['G1'].values.tolist())
df2 = pd.DataFrame(df['G2'].values.tolist())

df = pd.concat([df['ROLLNO'], df1, df2], axis=1)
df.columns=df.columns.astype(str)
df=df.drop(['0','6'],1)
df.columns = [df.columns[0]] + [f'G{i+1}' for i in range(len(df.columns)-1)]
print(df)

Upvotes: 2

Naga kiran
Naga kiran

Reputation: 4607

You can group the dataframe by ROLLNO and flatten the rows assign it to dataframe

Dataframe

    ROLLNO  G1  G2  G3  G4
0   1   12  14  14  20
1   1   10  12  11  12
2   2   14  2   16  18
3   2   18  16  12  14


df.set_index(['ROLLNO'],inplace=True)
df = df.groupby(df.index).apply(lambda x: x.values.flatten())
df1 = pd.DataFrame(df.values.tolist(),columns=['G{}'.format(i) for i in range(1,len(df.columns)*2-1)])
df1['ROLLNO'] = df.index

Out:

    G1  G2  G3  G4  G5  G6  G7  G8  ROLLNO
0   12  14  14  20  10  12  11  12  1
1   14  2   16  18  18  16  12  14  2

Upvotes: 3

Related Questions