Pandas GroupBy Function with expanded columns

user_17817433 0
Viewed 60

I have the following table:

Brand Product
0 Nike Shoes
1 Nike Socks
2 Adidas Shoes
3 Adidas Shoes
4 Adidas Socks
5 Flight Shorts

I want to use the Pandas GroupBy function to produce the following table (totals for rows and columns) to find the number of occurrences that a specific Brand-Product Pair have.

Shoes Socks Shorts Total
Nike 1 1 0 2
Adidas 2 1 0 3
Flight 0 0 1 1
Total 3 2 1 6

And then want to convert the cells in terms of Percentages:

  • The cell % comes from diving the cell value by the column total (e.g., {Shoes, Adidas} = 2/3 = 67% or {Total, Adidas} = 3/6 = 50%)
Shoes Socks Shorts Total
Nike 50% 50% 0% 33%
Adidas 67% 50% 0% 50%
Flight 0% 0% 100% 17%
Total 100% 100% 100% 100%

Also, on a final note, is there a way to multiply all cell numbers by an adjustment factor (e.g., 0.75)

pandas pivot-table group-by python
1 Answers

Try pd.crosstab:

out = pd.crosstab(df["Brand"], df["Product"])
out["Total"] = out.sum(axis=1)
out.index.name, out.columns.name = None, None
print(out)

Prints:

        Shoes  Shorts  Socks  Total
Adidas      2       0      1      3
Flight      0       1      0      1
Nike        1       0      1      2

Edit: To get percentages, you can do afterwards:

out.iloc[:, :-1] = (
    out.iloc[:, :-1]
    .div(out["Total"], axis=0)
    .mul(100)
    .round(0)
    .astype(int)
    .astype(str)
    + "%"
)

out["Total"] = (
    out["Total"]
    .div(out["Total"].sum())
    .mul(100)
    .round(0)
    .astype(int)
    .astype(str)
    + "%"
)

Prints:

       Shoes Shorts Socks Total
Adidas   67%     0%   33%   50%
Flight    0%   100%    0%   17%
Nike     50%     0%   50%   33%
user_100359850
Related