How can I insert a column of a dataframe in pandas as a list of a cell into another dataframe?

user_5901629 0
Viewed 50

I have several of dataframes (df, tmp_df and sub_df) and I want to enter a column of tmp_df into a cell of sub_df as a list. My code and dataframes are shown as below. But the loop part is not working correctly:

import pandas as pd
df = pd.read_csv('myfile.csv')
tmp_df = pd.DataFrame()
sub_df = pd.DataFrame()
tmp_df = df[df['Type'] == True]
for c in tmp_df['Category']:
    sub_df['Data'] , sub_df ['Category'], sub_df['Type'] = [list(set(tmp_df['Data']))],
    tmp_df['Category'], tmp_df['Type']

df:

Data Category Type
30275 A True
35881 C False
28129 C True
30274 D False
30351 D True
35886 A True
39900 C True
35887 A False
35883 A True
35856 D True
35986 C False
30350 D False
28129 C True
31571 C True

tmp_df:

Data Category Type
30275 A True
28129 C True
30351 D True
35886 A True
39900 C True
35883 A True
35856 D True
28129 C True
31571 C True

What should I do if I want the following result?

sub_df:

Data Category Type
[30275,35886,35883] A True
[28129,39900,28129,31571] C True
[30351,35856] D True
pandas dataframe list python
1 Answers

you can select the rows withquery, then groupby+agg:

(df.query('Type') # or 'Type == "True"' if strings
   .groupby('Category', as_index=False)
   .agg({'Data': list, 'Type': 'first'})
)

output:

  Category                          Data  Type
0        A         [30275, 35886, 35883]  True
1        C  [28129, 39900, 28129, 31571]  True
2        D                [30351, 35856]  True
user_163434640
Related