python – Create new column with min as values ​​in pivot table – Pandas Dataframe

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 = [
{
    "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": null,
    "asin": "B073WWXTRF",
    "parent_asin": "B073WW53SR"
},
{
    "rank": 23,
    "keyword": "classic ultraschallreiniger",
    "volume": null,
    "asin": "B073WWXTRF",
    "parent_asin": "B073WW53SR"
},
{
    "rank": 7,
    "keyword": "classic ultraschallreiniger",
    "volume": null,
    "asin": "B09K7F2YN6",
    "parent_asin": "B09K7BW5KH"
},
{
    "rank": 17,
    "keyword": "classic ultraschallreiniger",
    "volume": null,
    "asin": "B09K7F2YN6",
    "parent_asin": "B09K7BW5KH"
},
{
    "rank": 1,
    "keyword": "classic ultraschallreiniger",
    "volume": null,
    "asin": "B08L8GKY47",
    "parent_asin": "B08LCD86RD"
},
{
    "rank": 13,
    "keyword": "classic ultraschallreiniger",
    "volume": null,
    "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": null,
    "asin": "B073WWXTRF",
    "parent_asin": "B073WW53SR"
},
{
    "rank": 15,
    "keyword": "konzentrat für ultraschallreiniger",
    "volume": null,
    "asin": "B073WWXTRF",
    "parent_asin": "B073WW53SR"
},
{
    "rank": 3,
    "keyword": "konzentrat für ultraschallreiniger",
    "volume": null,
    "asin": "B09K7F2YN6",
    "parent_asin": "B09K7BW5KH"
},
{
    "rank": 16,
    "keyword": "konzentrat für ultraschallreiniger",
    "volume": null,
    "asin": "B09K7F2YN6",
    "parent_asin": "B09K7BW5KH"
},
{
    "rank": 2,
    "keyword": "konzentrat für ultraschallreiniger",
    "volume": null,
    "asin": "B08LCB95V8",
    "parent_asin": "B08LCD86RD"
},
{
    "rank": 14,
    "keyword": "konzentrat für ultraschallreiniger",
    "volume": null,
    "asin": "B08L8GKY47",
    "parent_asin": "B08LCD86RD"
},
{
    "rank": 1,
    "keyword": "reiniger ultraschallgerät",
    "volume": null,
    "asin": "B073WWXTRF",
    "parent_asin": "B073WW53SR"
},
{
    "rank": 14,
    "keyword": "reiniger ultraschallgerät",
    "volume": null,
    "asin": "B073WWXTRF",
    "parent_asin": "B073WW53SR"
},
{
    "rank": 5,
    "keyword": "reiniger ultraschallgerät",
    "volume": null,
    "asin": "B09K7F2YN6",
    "parent_asin": "B09K7BW5KH"
},
{
    "rank": 13,
    "keyword": "reiniger ultraschallgerät",
    "volume": null,
    "asin": "B09K7F2YN6",
    "parent_asin": "B09K7BW5KH"
},
{
    "rank": 3,
    "keyword": "reiniger ultraschallgerät",
    "volume": null,
    "asin": "B08L8GKY47",
    "parent_asin": "B08LCD86RD"
},
{
    "rank": 16,
    "keyword": "reiniger ultraschallgerät",
    "volume": null,
    "asin": "B08L8GKY47",
    "parent_asin": "B08LCD86RD"
},
{
    "rank": 2,
    "keyword": "brille reinigen ultraschall",
    "volume": null,
    "asin": "B08L8GKY47",
    "parent_asin": "B08LCD86RD"
},
{
    "rank": 14,
    "keyword": "brille reinigen ultraschall",
    "volume": null,
    "asin": "B08L8GKY47",
    "parent_asin": "B08LCD86RD"
},
{
    "rank": 5,
    "keyword": "brillen ultraschallreiniger",
    "volume": null,
    "asin": "B09K7F2YN6",
    "parent_asin": "B09K7BW5KH"
},
{
    "rank": 19,
    "keyword": "brillen ultraschallreiniger",
    "volume": null,
    "asin": "B09K7F2YN6",
    "parent_asin": "B09K7BW5KH"
},
{
    "rank": 4,
    "keyword": "brillen ultraschallreiniger",
    "volume": null,
    "asin": "B08L8GKY47",
    "parent_asin": "B08LCD86RD"
},
{
    "rank": 13,
    "keyword": "brillen ultraschallreiniger",
    "volume": null,
    "asin": "B08L8GKY47",
    "parent_asin": "B08LCD86RD"
},
{
    "rank": 2,
    "keyword": "schmuck reiniger",
    "volume": null,
    "asin": "B08LCB95V8",
    "parent_asin": "B08LCD86RD"
},
{
    "rank": 17,
    "keyword": "schmuck reiniger",
    "volume": null,
    "asin": "B08LCB95V8",
    "parent_asin": "B08LCD86RD"
}
]

What I tried so far

  df_sponsored = pd.DataFrame(values)
  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
         

  
  

Leave a Comment