Proportional/Percentage values

user_11631851 0
Viewed 53

I have this data frame:

o   d   r   kz  p
1   3   1   5   NaN
1   3   2   0   NaN
1   10  1   7   NaN
1   10  3   1   NaN
1   10  2   2   NaN

I would like to fill up the 'p' column by the proportions of 'kz' values for each pair of 'o' and 'd'. The result should look like: 

o   d   r   kz  p
1   3   1   5   100%
1   3   2   0   0%
1   10  1   7   70%
1   10  3   1   10%
1   10  2   2   20%

I am thinking of looping through the data frame and assign a list of lists of kz values and then regressively fill up the p column.

Is there any elegant way to do it e.g. with groupby or Pivot table?

pandas-groupby python-3.x python
2 Answers

You can do it in several steps:

  • Compute the sum per group with groupby (doc) and agg (doc).
  • Merge these values with you current dataframe with merge (doc).
  • Compute the ratio

Here the code:

# Import modules
import pandas as pd
import numpy as np

# Data
df = pd.DataFrame(
    [[1,   3,  1,   5,   np.NaN],
     [1,  3,  2,   0,   np.NaN],
     [1,  10,  1,   7,   np.NaN],
     [1,  10,  3,  1,   np.NaN],
     [1,  10,  2,   2,   np.NaN]],
    columns=["o", "d", "r", "kz", "p"])
print(df)
#    o   d  r  kz   p
# 0  1   3  1   5 NaN
# 1  1   3  2   0 NaN
# 2  1  10  1   7 NaN
# 3  1  10  3   1 NaN
# 4  1  10  2   2 NaN

# Compute the sum per group
sum_ = df.groupby(['o', 'd']).agg({'kz': 'sum'})
sum_.reset_index(inplace=True)
print(sum_)
#    o   d  kz
# 0  1   3   5
# 1  1  10  10

# Merge these values with the current dataframe
df = df.merge(sum_, on=['o', 'd'], how="outer", suffixes=('', '_sum'))
print(df)
#    o   d  r  kz   p  kz_sum
# 0  1   3  1   5 NaN       5
# 1  1   3  2   0 NaN       5
# 2  1  10  1   7 NaN      10
# 3  1  10  3   1 NaN      10
# 4  1  10  2   2 NaN      10

# Compute teh ratio
df.p = df.kz / df.kz_sum * 100
print(df)
#    o   d  r  kz      p  kz_sum
# 0  1   3  1   5  100.0       5
# 1  1   3  2   0    0.0       5
# 2  1  10  1   7   70.0      10
# 3  1  10  3   1   10.0      10
# 4  1  10  2   2   20.0      10
user_100418230

First sum() 'kz' column group by 'o' and 'd' and store it in the 'tmp'. Merge those two data frames. Then calculate the percentage value 'p' using the original value of 'kz' and sum value of 'kz'. Drop sum value of 'kz' and rename the original column name to 'kz'.

import pandas as pd
d = {'o' : pd.Series([1,1,1,1,1]),
      'd' : pd.Series([3,3,10,10,10]),
      'r' : pd.Series([1,2,1,3,2]),
      'kz' : pd.Series([5,0,7,1,2]),
      'p' : pd.Series(None)}

# creates Dataframe.
df = pd.DataFrame(d)

tmp=df.groupby(['o','d'])["kz"].sum()
merge_tmp=pd.merge(df, tmp, on=['o','d'], how='inner',suffixes=('_org','_tmp'))
merge_tmp['p'] = ((merge_tmp['kz_org']/merge_tmp['kz_tmp'])*100)

merge_tmp = merge_tmp.drop('kz_tmp', axis='columns')
merge_tmp = merge_tmp.rename({'kz_org': 'kz'}, axis='columns')
print(merge_tmp)
user_40500150
Related