Applying calculations to filtered values in Pandas DataFrame

Question

I am new to Pandas.

Consider this my DataFrame:

df

Search              Impressions     Clicks      Transactions    ContainsBest       ContainsFree         Country
Best phone          10              5           1               True               False                UK
Best free phone     15              4           2               True               True                 UK
free phone          20              3           4               False              True                 UK
good phone          13              1           5               False              False                US
just a free phone   12              3           4               False              True                 US

I have columns ContainsBest and ContainsFree. I want to sum all Impressions, Clicks and Transactions where ContainsBest is True, then I want to sum Impressions, Clicks and Transactions where ContainsFree is True and do the same per unique value in column Country. So the new DataFrame would look like this:

output_df

Country             Impressions     Clicks      Transactions
UK                  45              12          7
ContainsBest        25              9           3
ContainsFree        35              7           6

US                  25              4           9
ContainsBest        0               0           0
ContainsFree        12              3           4

For this I would understand I would need to use something like the following:

uk_toal_impressions = df['Impressions'].sum().where(df['Country']=='UK')

uk_best_impressions = df['Impressions'].sum().where(df['Country']=='UK' & df['ContainsBest'])

uk_free_impressions = df['Impressions'].sum().where(df['Country']=='UK' & df['ContainsFree'])

Then I would apply the same logic for Clicks and Transactions and redo the same code for Country US.

The second thing I am trying to achieve is to add column TopCategories per Country and Impressions, Clicks and Transactions, so that my final_output_df would look like this:

final_output_df

Country             Impressions     Clicks      Transactions        TopCategoriesForImpressions     TopCategoriesForClicks          TopCategoriesForTransactions     
UK                  45              12          7                   ContainsFree                    ContainsBest                    ContainsFree
ContainsBest        25              9           3                   ContainsBest                    ContainsFree                    ContainsBest
ContainsFree        35              7           6

US                  25              4           9                   ContainsFree                    ContainsFree                    ContainsFree
ContainsBest        0               0           0
ContainsFree        12              3           4

The column TopCategoriesForxx logic is a simple sort of ContainsBest and ContainsFree rows under Country column. So that the TopCategoriesForImpressions for UK country is

1. ContainsFree
2. ContainsBest

While the TopCategoriesForClicks for UK country is:

1. ContainsBest
2. ContainsFree

I understand I would need to use something like this:

TopCategoriesForImpressions = output_df['Impressions'].sort_values(by='Impressions', ascending=False).where(output_df['Country']=='UK')

I just find difficult to put everything to look as in my last final_output_df. Also, I would assume I do not need output_df to be created, just wanted to add it for better understanding of what my steps are to achieve the final_output_df.

So my questions are:

1. How to apply calculations based on one and multiple conditions? See row ContainsBest and ContainsFree
2. How can I sort column values based on a condition? See column TopCategoriesForImpressions
3. In reality, I have 70 countries and 20 columns Containsxxx, is there a way to achieve this without adding conditions for 70 countries and 20 Containsxxx columns?

Thank you very much for your suggestions.

Answer 1

First part of solution should be:

#removed unnecessary column Search and added ContainAll column filled Trues
df1 = df.drop('Search', 1).assign(ContainAll = True)

#columns for tests
cols1 = ['Impressions','Clicks','Transactions']
cols2 = ['ContainsBest','ContainsFree','ContainAll']

print (df1[cols2].dtypes)
ContainsBest    bool
ContainsFree    bool
ContainAll      bool
dtype: object

print (df1[cols1].dtypes)
Impressions     int64
Clicks          int64
Transactions    int64
dtype: object

---

print (df1.melt(['Country'] + cols1, var_name='Type', value_name='mask'))
   Country  Impressions  Clicks  Transactions          Type   mask
0       UK           10       5             1  ContainsBest   True
1       UK           15       4             2  ContainsBest   True
2       UK           20       3             4  ContainsBest  False
3       US           13       1             5  ContainsBest  False
4       US           12       3             4  ContainsBest  False
5       UK           10       5             1  ContainsFree  False
6       UK           15       4             2  ContainsFree   True
7       UK           20       3             4  ContainsFree   True
8       US           13       1             5  ContainsFree  False
9       US           12       3             4  ContainsFree   True
10      UK           10       5             1    ContainAll   True
11      UK           15       4             2    ContainAll   True
12      UK           20       3             4    ContainAll   True
13      US           13       1             5    ContainAll   True
14      US           12       3             4    ContainAll   True

print (df1.melt(['Country'] + cols1, var_name='Type', value_name='mask').query('mask'))
   Country  Impressions  Clicks  Transactions          Type  mask
0       UK           10       5             1  ContainsBest  True
1       UK           15       4             2  ContainsBest  True
6       UK           15       4             2  ContainsFree  True
7       UK           20       3             4  ContainsFree  True
9       US           12       3             4  ContainsFree  True
10      UK           10       5             1    ContainAll  True
11      UK           15       4             2    ContainAll  True
12      UK           20       3             4    ContainAll  True
13      US           13       1             5    ContainAll  True
14      US           12       3             4    ContainAll  True

---

#all possible combinations of Country and boolean columns
mux = pd.MultiIndex.from_product([df['Country'].unique(), cols2], 
                                  names=['Country','Type'])

#reshape by melt for all boolean column to one mask column
#filter Trues by loc and aggregate sum
#add 0 rows by reindex
df1 = (df1.melt(['Country'] + cols1, var_name='Type', value_name='mask')
          .query('mask')
          .drop('mask', axis=1)
          .groupby(['Country','Type'])
          .sum()
          .reindex(mux, fill_value=0)
          .reset_index())
print (df1)
  Country          Type  Impressions  Clicks  Transactions
0      UK  ContainsBest           25       9             3
1      UK  ContainsFree           35       7             6
2      UK    ContainAll           45      12             7
3      US  ContainsBest            0       0             0
4      US  ContainsFree           12       3             4
5      US    ContainAll           25       4             9

For second is possible filter rows for check sortings with numpy.argsort with descending order per groups:

def f(x):
    i = x.index.to_numpy()
    a = i[(-x.to_numpy()).argsort(axis=0)]
    return pd.DataFrame(a, columns=x.columns)


df2 = (df1[df1['Type'].isin(['ContainsBest','ContainsFree']) &
          ~df1[cols1].eq(0).all(1)]
           .set_index('Type')
           .groupby('Country')[cols1]
           .apply(f)
           .add_prefix('TopCategoriesFor')
           .rename_axis(['Country','Type'])
           .rename({0:'ContainsBest', 1:'ContainsFree'})
)
print (df2)
                     TopCategoriesForImpressions TopCategoriesForClicks  \
Country Type                                                              
UK      ContainsBest                ContainsFree           ContainsBest   
        ContainsFree                ContainsBest           ContainsFree   
US      ContainsBest                ContainsFree           ContainsFree   

                     TopCategoriesForTransactions  
Country Type                                       
UK      ContainsBest                 ContainsFree  
        ContainsFree                 ContainsBest  
US      ContainsBest                 ContainsFree  

---

df3 = df1.join(df2, on=['Country','Type'])
print (df3)
  Country          Type  Impressions  Clicks  Transactions  \
0      UK  ContainsBest           25       9             3   
1      UK  ContainsFree           35       7             6   
2      UK    ContainAll           45      12             7   
3      US  ContainsBest            0       0             0   
4      US  ContainsFree           12       3             4   
5      US    ContainAll           25       4             9   

  TopCategoriesForImpressions TopCategoriesForClicks  \
0                ContainsFree           ContainsBest   
1                ContainsBest           ContainsFree   
2                         NaN                    NaN   
3                ContainsFree           ContainsFree   
4                         NaN                    NaN   
5                         NaN                    NaN   

  TopCategoriesForTransactions  
0                 ContainsFree  
1                 ContainsBest  
2                          NaN  
3                 ContainsFree  
4                          NaN  
5                          NaN