Iterate through each category in column and add values from another column as a separate df

Question

I would like to add for each category within the Customer_Acquisition_Channel column all values in Days_To_Acquisition column to a separate df.

All Customer_ID vales are unique in dataset below

DF

Customer_ID Customer_Acquisition_Channel  Days_To_Acquisition
323         Organic                       2
583         Organic                       5
838         Organic                       2
193         Website                       7
241         Website                       7
642         Website                       1

Desired Output: Days_To_Acq_Organic_Df

Index Days_To_Acquisition
0     2
1     5
2     2

Days_To_Acq_Website_Df

Index Days_To_Acquisition
0     7
1     7
2     1

This is what I have tried so far, but I would like to use a for loop instead going through each column manually

sub_1 = df.loc[df['Customer_Acquisition_Channel'] == 'Organic']
Days_To_Acq_Organic_Df=sub_1[['Days_To_Acquisition']]

sub_2 = df.loc[df['Customer_Acquisition_Channel'] == 'Website']
Days_To_Acq_Website_Df=sub_2[['Days_To_Acquisition']]

have a look on: https://stackoverflow.com/questions/23691133/split-pandas-dataframe-based-on-groupby and https://stackoverflow.com/questions/14734533/how-to-access-pandas-groupby-dataframe-by-key — Anurag Dabas, May 23 '21 at 18:04
I'm not sure what you are trying to do with the result, but I think `pd.groupby` can help and combined with `agg` or `apply` with or without a `lambda` function or even with a list comprehension can help you get more out of the desired results. — Kay, May 23 '21 at 18:05
`df_dict = {f'Days_To_Acquisition_{g}_df':k.drop('Customer_Acquisition_Channel', 1) for g,k in df.groupby('Customer_Acquisition_Channel')}` ?? — Nk03, May 23 '21 at 18:06

pakpe · Accepted Answer · 2021-05-23T23:12:58.453

You can iterate through unique values of the channel column and create new dataframes, change the column names, and append them to a list:

dataframes = []
for channel in df.Customer_Acquisition_Channel.unique():
    new_df = df[df['Customer_Acquisition_Channel'] == channel][['Customer_ID','Days_To_Acquisition']]
    new_df.columns = ['Customer_ID',f'Days_To_Acquisition_{channel}_df']
    dataframes.append(new_df)

OUTPUT:

for df in dataframes:
    print(df,'\n__________')

   Customer_ID  Days_To_Acquisition_Organic_df
0          323                               2
1          583                               5
2          838                               2 
__________
   Customer_ID  Days_To_Acquisition_Website_df
3          193                               7
4          241                               7
5          642                               1 
__________

Alternatively, you can store the dataframes to a dictionary so you can name them and call them individually:

dataframes = {}
for channel in df.Customer_Acquisition_Channel.unique():
    new_df = df[df['Customer_Acquisition_Channel'] == channel][['Customer_ID','Days_To_Acquisition']]
    new_df.columns = ['Customer_ID',f'Days_To_Acquisition_{channel}']
    dataframes[f'Days_To_Acquisition_{channel}_df'] = new_df

OUTPUT:

print(dataframes['Days_To_Acquisition_Organic_df'])

   Customer_ID  Days_To_Acquisition_Organic
0          323                            2
1          583                            5
2          838                            2

How can I print only of the df's - lets say the first one? I get the following error when printing for example 'Days_To_Acquisition_Organic_df' --> error: name not defined — user12625679, May 23 '21 at 22:48

Iterate through each category in column and add values from another column as a separate df

1 Answers1