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

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.

CodePudding user response：

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]