How do i get an aggregate percentage on two concatenated codes denoted by a value in a pandas dataframe

Admin 1
Viewed 60

im new to python and trying to understand data manipulation

df
Alpha               AlphaComboCount
12-99                   8039
22-99                   1792
12-99,138-99            1776
12-45,138-45            1585
21-99                   1225
123-99                  1145
121-99                  1102
21-581                  1000
121-99,22-99             909
32-99                    814
21-141                   75
12-581,12-99             711
347-99                   685
2089-281                 685
123-49,121-29,22-79      626
121-99,123-99,22-99      4

As you can see above, there are two columns. Alpha being a string made out of concatenation of 2 codes seperated by '-'. My objective is to find the aggregate percentage of alphacombocount by the first code. For example:

where there is 21 subcode-

Alpha   AlphaComboCount  Percent
21-99   1225             53%
21-141    75             3.2%
21-581  1000            43.3%

The objective as you see above is to get a corresponding percentage. since the total aggregrate here is 2300 of the 21 subcode.

Where it gets more complicated is for combination codes:

   123-49,121-29,22-79       626  99%
    121-99,123-99,22-99      4   0.6%

As you see above, all the first sub codes are the same but rearranged. This is also a valid case to get percentage values. As long as the combination is the same of the first subcode before'-'. How can i go about this to get the percentage values for all alpha combinations? is there an algorithm for this?

pandas percentage algorithm python string
2 Answers

First, you want to separate the codes within a cell, then you can extract the first code and groupby:

# separate the codes
tmp = df.assign(FirstCode=df.Alpha.str.split(','))

# extract the first code
tmp['FirstCode'] = [tuple(sorted(set(x.split('-')[0] for x in cell)))
                        for cell in tmp.FirstCode]

# sum per each first codes with groupby
sum_per_code = tmp['AlphaComboCount'].groupby(tmp['FirstCode']).transform('sum')

# percentage is just a simple division
tmp['Percent'] = tmp['AlphaComboCount']/sum_per_code

# let's print the output:
print(tmp.sort_values('FirstCode'))

Output:

                  Alpha  AlphaComboCount       FirstCode   Percent
0                 12-99             8039           (12,)  0.918743
11         12-581,12-99              711           (12,)  0.081257
2          12-99,138-99             1776       (12, 138)  0.528414
3          12-45,138-45             1585       (12, 138)  0.471586
6                121-99             1102          (121,)  1.000000
14  123-49,121-29,22-79              626  (121, 123, 22)  0.993651
15  121-99,123-99,22-99                4  (121, 123, 22)  0.006349
8          121-99,22-99              909       (121, 22)  1.000000
5                123-99             1145          (123,)  1.000000
13             2089-281              685         (2089,)  1.000000
4                 21-99             1225           (21,)  0.532609
7                21-581             1000           (21,)  0.434783
10               21-141               75           (21,)  0.032609
1                 22-99             1792           (22,)  1.000000
9                 32-99              814           (32,)  1.000000
12               347-99              685          (347,)  1.000000
user_42384080

If you have a number of codes in Alpha column, in different order, then one of possible solutions is to extract one of them (e.g. minimal), then take the part before '-', save it in a new column and use in further processing:

df['Alpha_1'] = df.Alpha.str.split(',')\
    .apply(lambda lst: min(lst)).str.split('-', expand=True)[0]

The result is:

                  Alpha  AlphaComboCount Alpha_1
0                 12-99             8039      12
1                 22-99             1792      22
2          12-99,138-99             1776      12
3          12-45,138-45             1585      12
4                 21-99             1225      21
5                123-99             1145     123
6                121-99             1102     121
7                21-581             1000      21
8          121-99,22-99              909     121
9                 32-99              814      32
10               21-141               75      21
11         12-581,12-99              711      12
12               347-99              685     347
13             2089-281              685    2089
14  123-49,121-29,22-79              626     121
15  121-99,123-99,22-99                4     121

To compute percentage of AlphaComboCount in each group (with particular value of Alpha_1), define the following function:

def proc(grp):
    return (grp.AlphaComboCount / grp.AlphaComboCount.sum()
        * 100).apply('{0:.2f}%'.format)

Group df by Alpha_1 and apply this function, saving the result in Grp_pct column:

df['Grp_pct'] = df.groupby('Alpha_1').apply(proc).reset_index(level=0, drop=True)

To inspect the result easily, with rows from each group together, print df the following way:

print(df.sort_values('Alpha_1'))

getting:

                  Alpha  AlphaComboCount Alpha_1  Grp_pct
0                 12-99             8039      12   66.38%
2          12-99,138-99             1776      12   14.66%
3          12-45,138-45             1585      12   13.09%
11         12-581,12-99              711      12    5.87%
6                121-99             1102     121   41.73%
8          121-99,22-99              909     121   34.42%
14  123-49,121-29,22-79              626     121   23.70%
15  121-99,123-99,22-99                4     121    0.15%
5                123-99             1145     123  100.00%
13             2089-281              685    2089  100.00%
4                 21-99             1225      21   53.26%
7                21-581             1000      21   43.48%
10               21-141               75      21    3.26%
1                 22-99             1792      22  100.00%
9                 32-99              814      32  100.00%
12               347-99              685     347  100.00%

Now, e.g. compare the section concerning Alpha_1 == 21 with your expected result for subcode 21.

user_73884770
Related