-1

Can anyone help me to create a pandas pivot table to get the below ouput

please find the data frame

enter image description here

enter image description here

Deepweber
  • 11
  • 4
  • 2
    Check out [`df.pivot_table`](https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.pivot_table.html) – Ch3steR Nov 14 '20 at 11:01
  • Does this answer your question? [How to pivot a dataframe?](https://stackoverflow.com/questions/47152691/how-to-pivot-a-dataframe) – StupidWolf Nov 14 '20 at 11:19
  • If you want percentages, it's maybe better to use pd.crosstab() with argument normalize='index' or normalize='columns' – Sander van den Oord Nov 14 '20 at 11:36
  • [Please do not upload images of code/errors when asking a question](https://meta.stackoverflow.com/q/285551). Please provide sample data in a [reproducible way](https://stackoverflow.com/questions/20109391). Please describe the logic to produce the expected output. – Bill Huang Nov 14 '20 at 14:35

1 Answers1

1

Use the following code:

result = pd.crosstab(index=df['Product line'], columns=df.City,
    values=df['Stocked Unit'], aggfunc=np.sum, normalize='columns')\
    .applymap(lambda x: f'{100 * x:.2f}%').rename_axis(
    columns={'City': None}, index={'Product line': 'Row Labels'})

Important points:

  • normalize='columns' - Sum of values in each column is to be 1 (100%).
  • applymap(...) - Convert fractions to percents (with 2 decimal digits).
  • rename_axis(...) - Clear the name of column index and rename the row index.

To see the effect of each of the above points, run the above code without further points.

Edit

To get percentage of Consumed Unit to Stocked Unit you have to do it stepwise:

  1. For each combination (group) of City and Product line compute the ratio (sum of Consumed Unit / sum of Stocked Unit):

    wrk = df.groupby(['City', 'Product line']).apply(lambda grp:
    grp['Consumed Unit'].sum() / grp['Stocked Unit'].sum())\
    .rename('Ratio').reset_index()

  2. Compute the result as a crosstab from the above DataFrame:

    result = pd.crosstab(index=wrk['Product line'], columns=wrk.City, values=wrk.Ratio,
    aggfunc=np.sum, normalize='columns').applymap(lambda x: f'{100 * x:.2f}%')\
    .rename_axis(columns={'City': None}, index={'Product line': 'Row Labels'})\
    .reset_index()

For your source data (from your recent comment) the result is:

               Row Labels Mandalay Naypyitaw  Yangon
0  Electronic accessories   30.00%    59.89%  28.65%
1     Fashion accessories   30.00%     0.00%   0.00%
2      Food and beverages   40.00%     0.00%   0.00%
3       Health and beauty    0.00%     0.00%  25.55%
4      Home and lifestyle    0.00%    40.11%  22.90%
5       Sports and travel    0.00%     0.00%  22.90%
Valdi_Bo
  • 30,023
  • 4
  • 23
  • 41
  • i need the % calculations between consumed unit /stocked unit – Deepweber Nov 24 '20 at 07:42
  • It is not clear what you want. Replace the percentage concerning *Stocked Unit* (which I calculated) with the ratio of *Consumed Unit* to *Stocked Unit*? Or any other way? Another hint is that this is rather material for another question. And provide **text** representation of your data. We have no time ro rewrite your data from a picture. – Valdi_Bo Nov 24 '20 at 07:57
  • City Product line Consumed Unit Stocked Unit Yangon Health and beauty 74.69 522.83 Naypyitaw Electronic accessories 15.28 76.4 Yangon Home and lifestyle 46.33 324.31 Yangon Health and beauty 58.22 465.76 Yangon Sports and travel 86.31 604.17 Naypyitaw Electronic accessories 85.39 597.73 Yangon Electronic accessories 68.84 413.04 Naypyitaw Home and lifestyle 73.56 735.6 Yangon Health and beauty 36.26 72.52 Mandalay Food and beverages 54.84 164.52 Mandalay Fashion accessories 14.48 57.92 Mandalay Electronic accessories 25.51 102.04 Yangon Electronic accessories 46.95 234.75 Yango – Deepweber Nov 24 '20 at 08:42