List creation from dataframe using for loop

Question

having a dataframe as below:

data={'column1':[1,1,1,1,1,1,1,1,2,2,2,2],'person':['A','A','A','A','B','B','B','B','C','C','C','C'],'location1':['GOA','BANGLORE','GOA','BANGLORE','BANGLORE','DELHI','BANGLORE','DELHII','KOCHI','DELHI','DELHI','KOCHI'],'location2':['BANGLORE','GOA','GOA','BANGLORE','DELHI','DELHI','BANGLORE','BANGLORE','DELHI','KOCHI','DELHI','KOCHI'],'time':[20,40,0,0,34,0,0,23,21,56,0,0]}

df = pd.DataFrame(data)

need to create a list of list

for i in set(df.column1):
    for j in set(df.person):
            col=['location1','location2','time']
            print(df[col].values.tolist())
        

But couldn't do the rest. Can anyone help me to create list of list?

Answer 1

You can start off by grouping the dataframe based on the columns 'column1', person and location1. After that, you use apply to make a list of the results in every group.

grouped_df = df.groupby(['column1','person','location1'])['time'].apply(lambda x:list(x))

column1  person  location1
1        A       BANGLORE     [40, 0]
                 GOA          [20, 0]
         B       BANGLORE     [34, 0]
                 DELHI        [0, 23]
2        C       DELHI        [56, 0]
                 KOCHI        [21, 0]
Name: time, dtype: object

Finally, you perform the same function again.

result = grouped_df.groupby(['column1','person']).apply(lambda x:list(x))

column1  person
1        A         [[40, 0], [20, 0]]
         B         [[34, 0], [0, 23]]
2        C         [[56, 0], [21, 0]]
Name: time, dtype: object

Update

To obtain a dataframe of your desired output, just use reset_index

result.reset_index()

   column1 person                time
0        1      A  [[40, 0], [20, 0]]
1        1      B  [[34, 0], [0, 23]]
2        2      C  [[56, 0], [21, 0]]