How to add a column with minimum as values to a pivot table?

Question

I need to get the min values(rank) for each row and create a new column for it, in a specific location in a data frame.

values_from_api_call = [
    {
        "rank": 5,
        "keyword": "brillenreiniger ultraschallbad",
        "volume": 566,
        "asin": "B08LCB95V8",
        "parent_asin": "B08LCD86RD",
    },
    {
        "rank": 20,
        "keyword": "brillenreiniger ultraschallbad",
        "volume": 566,
        "asin": "B08LCB95V8",
        "parent_asin": "B08LCD86RD",
    },
    {
        "rank": 4,
        "keyword": "classic ultraschallreiniger",
        "volume": None,
        "asin": "B073WWXTRF",
        "parent_asin": "B073WW53SR",
    },
    {
        "rank": 23,
        "keyword": "classic ultraschallreiniger",
        "volume": None,
        "asin": "B073WWXTRF",
        "parent_asin": "B073WW53SR",
    },
    {
        "rank": 7,
        "keyword": "classic ultraschallreiniger",
        "volume": None,
        "asin": "B09K7F2YN6",
        "parent_asin": "B09K7BW5KH",
    },
    {
        "rank": 17,
        "keyword": "classic ultraschallreiniger",
        "volume": None,
        "asin": "B09K7F2YN6",
        "parent_asin": "B09K7BW5KH",
    },
    {
        "rank": 1,
        "keyword": "classic ultraschallreiniger",
        "volume": None,
        "asin": "B08L8GKY47",
        "parent_asin": "B08LCD86RD",
    },
    {
        "rank": 13,
        "keyword": "classic ultraschallreiniger",
        "volume": None,
        "asin": "B08L8GKY47",
        "parent_asin": "B08LCD86RD",
    },
    {
        "rank": 11,
        "keyword": "flüssigkeit für ultraschallreiniger",
        "volume": 500,
        "asin": "B073WWXTRF",
        "parent_asin": "B073WW53SR",
    },
    {
        "rank": 24,
        "keyword": "flüssigkeit für ultraschallreiniger",
        "volume": 500,
        "asin": "B073WWXTRF",
        "parent_asin": "B073WW53SR",
    },
    {
        "rank": 7,
        "keyword": "flüssigkeit für ultraschallreiniger",
        "volume": 500,
        "asin": "B09K7F2YN6",
        "parent_asin": "B09K7BW5KH",
    },
    {
        "rank": 18,
        "keyword": "flüssigkeit für ultraschallreiniger",
        "volume": 500,
        "asin": "B09K7F2YN6",
        "parent_asin": "B09K7BW5KH",
    },
    {
        "rank": 1,
        "keyword": "flüssigkeit für ultraschallreiniger",
        "volume": 500,
        "asin": "B08L8GKY47",
        "parent_asin": "B08LCD86RD",
    },
    {
        "rank": 14,
        "keyword": "flüssigkeit für ultraschallreiniger",
        "volume": 500,
        "asin": "B08L8GKY47",
        "parent_asin": "B08LCD86RD",
    },
    {
        "rank": 1,
        "keyword": "konzentrat für ultraschallreiniger",
        "volume": None,
        "asin": "B073WWXTRF",
        "parent_asin": "B073WW53SR",
    },
    {
        "rank": 15,
        "keyword": "konzentrat für ultraschallreiniger",
        "volume": None,
        "asin": "B073WWXTRF",
        "parent_asin": "B073WW53SR",
    },
    {
        "rank": 3,
        "keyword": "konzentrat für ultraschallreiniger",
        "volume": None,
        "asin": "B09K7F2YN6",
        "parent_asin": "B09K7BW5KH",
    },
    {
        "rank": 16,
        "keyword": "konzentrat für ultraschallreiniger",
        "volume": None,
        "asin": "B09K7F2YN6",
        "parent_asin": "B09K7BW5KH",
    },
    {
        "rank": 2,
        "keyword": "konzentrat für ultraschallreiniger",
        "volume": None,
        "asin": "B08LCB95V8",
        "parent_asin": "B08LCD86RD",
    },
    {
        "rank": 14,
        "keyword": "konzentrat für ultraschallreiniger",
        "volume": None,
        "asin": "B08L8GKY47",
        "parent_asin": "B08LCD86RD",
    },
    {
        "rank": 1,
        "keyword": "reiniger ultraschallgerät",
        "volume": None,
        "asin": "B073WWXTRF",
        "parent_asin": "B073WW53SR",
    },
    {
        "rank": 14,
        "keyword": "reiniger ultraschallgerät",
        "volume": None,
        "asin": "B073WWXTRF",
        "parent_asin": "B073WW53SR",
    },
    {
        "rank": 5,
        "keyword": "reiniger ultraschallgerät",
        "volume": None,
        "asin": "B09K7F2YN6",
        "parent_asin": "B09K7BW5KH",
    },
    {
        "rank": 13,
        "keyword": "reiniger ultraschallgerät",
        "volume": None,
        "asin": "B09K7F2YN6",
        "parent_asin": "B09K7BW5KH",
    },
    {
        "rank": 3,
        "keyword": "reiniger ultraschallgerät",
        "volume": None,
        "asin": "B08L8GKY47",
        "parent_asin": "B08LCD86RD",
    },
    {
        "rank": 16,
        "keyword": "reiniger ultraschallgerät",
        "volume": None,
        "asin": "B08L8GKY47",
        "parent_asin": "B08LCD86RD",
    },
    {
        "rank": 2,
        "keyword": "brille reinigen ultraschall",
        "volume": None,
        "asin": "B08L8GKY47",
        "parent_asin": "B08LCD86RD",
    },
    {
        "rank": 14,
        "keyword": "brille reinigen ultraschall",
        "volume": None,
        "asin": "B08L8GKY47",
        "parent_asin": "B08LCD86RD",
    },
    {
        "rank": 5,
        "keyword": "brillen ultraschallreiniger",
        "volume": None,
        "asin": "B09K7F2YN6",
        "parent_asin": "B09K7BW5KH",
    },
    {
        "rank": 19,
        "keyword": "brillen ultraschallreiniger",
        "volume": None,
        "asin": "B09K7F2YN6",
        "parent_asin": "B09K7BW5KH",
    },
    {
        "rank": 4,
        "keyword": "brillen ultraschallreiniger",
        "volume": None,
        "asin": "B08L8GKY47",
        "parent_asin": "B08LCD86RD",
    },
    {
        "rank": 13,
        "keyword": "brillen ultraschallreiniger",
        "volume": None,
        "asin": "B08L8GKY47",
        "parent_asin": "B08LCD86RD",
    },
    {
        "rank": 2,
        "keyword": "schmuck reiniger",
        "volume": None,
        "asin": "B08LCB95V8",
        "parent_asin": "B08LCD86RD",
    },
    {
        "rank": 17,
        "keyword": "schmuck reiniger",
        "volume": None,
        "asin": "B08LCB95V8",
        "parent_asin": "B08LCD86RD",
    },
]

What I tried so far:

df_sponsored = pd.DataFrame(values_from_api_call)
df_sponsored = df_sponsored.drop_duplicates(subset=["asin", "keyword"], keep="first")
if not df_sponsored.empty:
    df_sponsored[lists] = df_sponsored[lists].fillna(0)
    df_sponsored = df_sponsored.assign(
        rank=df_sponsored["rank"].astype(str)
    ).pivot_table(
        index=["keyword", "volume"],
        columns=["parent_asin", "asin"],
        values="rank",
        aggfunc="min",
        fill_value=0,
        margins=True,
        margins_name="Best_ranking",
    )
    print(df_sponsored)

Output(to save space, I just showed the result format):

                     parent_asin      xxxxxx      xxxxxx   xxxxxx  Best_ranking
     keyword         asin             xxxxxx      xxxxxx   xxxxxx  2
     somekeywords    volume           1            3          3    1
     Best_ranking                     1            3          3    1

As you can see in the output, the margin Best_ranking appears twice once in the last column and once as the value in the first column. If the data frame values are large, the margins calculations are sometimes wrong or not accurate, It doesn't show the min value, it shows the max instead.

My desired output would be:

                parent_asin      Best_ranking  xxxxxx      xxxxxx   xxxxxx 
     keyword         asin                      xxxxxx      xxxxxx   xxxxxx  
     somekeywords    volume           1          3           3        1

user_11246056 · Answer

If I'm not mistaken, margins creates a grand total column, not the minimum you expect.

Here is one way to do it:

df = (
    pd.DataFrame(values_from_api_call)
    .drop_duplicates(subset=["asin", "keyword"], keep="first")
    .fillna(0)
    .pivot_table(
        index=["keyword", "volume"],
        columns=["parent_asin", "asin"],
        fill_value=0,
        values="rank",
    ).assign(best_ranking=lambda df: df.apply(min, axis=1))
)

print(df)
# Output

Most minimum ranks are 0 since that's what you choose as a filling value when you pivot the table.

If you want the minimum rank apart from 0, here is what you can do:

df = (
    pd.DataFrame(values_from_api_call)
    .drop_duplicates(subset=["asin", "keyword"], keep="first")
    .fillna(0)
    .pivot_table(
        index=["keyword", "volume"],
        columns=["parent_asin", "asin"],
        fill_value=999_999,
        values="rank",
    )
    .assign(best_ranking=lambda df: df.apply(min, axis=1))
    .replace(999_999, 0)
)

Then, you can move the new column like this to get the final dataframe:

df = df.reindex(
    columns=pd.MultiIndex.from_tuples(
        [("best_ranking", "")]
        + [col for col in df.columns.tolist() if col != ("best_ranking", "")],
        names=["parent_asin", "asin"],
    )
)

print(df)
# Output

How to add a column with minimum as values to a pivot table?

1 Answers