how to calculate percentage and format table

user_19320246 0
Viewed 39

I have a table looks like:

c_id  soap_spend  towel_spend  year_spend cluster
c1     1           2              3          1
c2     2           4              6          2
c3     1           2              3          2
c4     3           2              5          1

I want to return two tables.

table 1:

                     cluster_1          cluster_2
    % soap_spend     a = (1+3)/(3+5)   c= (2+1)/(6+3) -- soap_spend.sum/year_spend.sum 
    % towel_spend    b = (2+2)/(3+5)   d= (2+4)/(6+3) -- towel_spend.sum/year_spend.sum

table 2:

    use results from table 1
                            cluster_1          cluster_2
        % soap_spend     a/mean(soap_spend)   c/mean(towel_spend)
        % towel_spend    b/mean(soap_spend)  d/mean(towel_spend)

my code:

    cols = ['soap_spend', 'towel_spend']
    df.groupby('cluster').apply(df[col].sum()/df['year_spend'].sum()

any suggestions on hwo to fix the code?

pandas python
1 Answers

You don't need to group by cluster, it's enough to sum with loc operations like this:

numerator = df['soap_spend'].loc[df['cluster'] == val].sum()
denominator = df['year_spend'].loc[df['cluster'] == val].sum()

The full code would be like this:

for val in df.cluster.unique():
     soap_numerator = df['soap_spend'].loc[df['cluster'] == val].sum()
     denominator = df['year_spend'].loc[df['cluster'] == val].sum()

     towel_numerator = df['towel_spend'].loc[df['cluster'] == val].sum()

     soap_spend = soap_numerator / denominator
     towel_spend = towel_numerator / denominator
     col = [soap_spend,towel_spend]
     df_results.insert(int(val),'cluster_{}'.format(int(val)),col)
  
df_results = df_results[['cluster_1','cluster_2']]

For the table 2 you should apply that:

table_2 = table_1.copy()
for row in range(table_2.shape[0]):
  for col in table_2.columns:
    table_2[col].iloc[row] = table_2[col][row] / table_2.iloc[row].mean()
user_107493170
Related