How to make an array of row values from same column in pandas?

Question

Here I have the following table having 3 columns S.No,Test and Key.I want to join those rows from column "Key" in form of array in which the values of column "Test" are equal like in table 2(resultant table). Table 1:

S.No    Test    Key
   1    AB     X1
   2    AB     X2
   3    12     X1
   4    34     X4
   5    AB     X3
   6    12     X5
   7    11     X2
   8    12     X6
   9    QW     X0

Table 2(resultant):

S.No    Test       Key
 1      AB        [X1,X2,X3]
 2      12        [X1,X5,X6]
 3      34        [X4]
 4      11        [X2]
 5      QW        [X0]

Can someone please help me in how to do this.Right now I just got the duplicated entries.Thanks.

df.groupby('Test',as_index=False).agg({'Key':list}) ?

sophocles
– sophocles

2021-12-15 18:05:38 +00:00
Commented Dec 15, 2021 at 18:05 — sophocles
– sophocles, Commented Dec 15, 2021 at 18:05

JayPeerachai · Accepted Answer · 2021-12-15 18:21:20Z

1

Create list of Key by using groupby the Test column

new_df = df.groupby('Test', sort=False)['Key'].apply(list).to_frame()
new_df.reset_index(inplace=True)
new_df.index = np.arange(1,len(new_df)+1)
new_df['S.No'] = new_df.index
new_df = new_df[df.columns]

Output:

  S.No    Test    Key
1 1   AB  [X1, X2, X3]
2 2   12  [X1, X5, X6]
3 3   34  [X4]
4 4   11  [X2]
5 5   QW  [X0]

answered Dec 15, 2021 at 18:21

JayPeerachai

3,8523 gold badges19 silver badges34 bronze badges

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

Collectives™ on Stack Overflow

How to make an array of row values from same column in pandas?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related