Python max() function doesn't seem to get the max value of aging days as intended-CodePudding

Case: My script returns a pivot table but it couldn't correctly get the max Aging Days. For example, CELOVIS should be 12 Max Aging Days, but I kept getting 1.

Code:

import pandas as pd
from collections import Counter

data = pd.read_csv("my_problem.csv")
def pivot_table(df):
    df_count = Counter(df['Supplier Name'])
    courier_count = pd.DataFrame(df_count.items(), columns=['Supplier Name', 'Count of Tracking Number'])

    s = df.groupby(['Supplier Name','Aging Day']).size()
    pivot_table = s.loc[s.groupby(level=0).idxmax()].reset_index().drop(0,axis=1)
    pivot_table.rename(columns = {'Aging Day':'Max Aging Day'}, inplace = True)
    pivot_table = pivot_table.merge(courier_count, on = "Supplier Name", how="left")
    pivot_table.set_index("Supplier Name",inplace=True)
    pivot_table.sort_values(by=["Max Aging Day"], ascending=[False], inplace=True)

    pivot_table.loc['Grand Total'] = pivot_table.iloc[:, 0:-1].max()
    pivot_table["Count of Tracking Number"][len(pivot_table)-1] = pivot_table["Count of Tracking Number"].sum()
    pivot_table = pivot_table.reset_index(level=0)
    
    return pivot_table

Here's the csv data from pastebin: https://pastebin.com/g2k7HxzJ

I intended to get the max Aging Days for each shop, accompanied by their count of tracking numbers in the pivot table. The count of tracking numbers are correct but the max Aging Day computation is wrong.

CodePudding user response：

Can you try this? I changed two lines for this new one :

pivot_table = df[['Supplier Name','Aging Day']].groupby(by='Supplier Name')['Aging Day'].max().reset_index()

import pandas as pd
from collections import Counter

data = pd.read_csv("my_problem.csv")
def pivot_table(df):
    df_count = Counter(df['Supplier Name'])
    courier_count = pd.DataFrame(df_count.items(), columns=['Supplier Name', 'Count of Tracking Number'])   
    pivot_table = df[['Supplier Name','Aging Day']].groupby(by='Supplier Name')['Aging Day'].max().reset_index()
    pivot_table.rename(columns = {'Aging Day':'Max Aging Day'}, inplace = True)
    pivot_table = pivot_table.merge(courier_count, on = "Supplier Name", how="left")
    pivot_table.set_index("Supplier Name",inplace=True)
    pivot_table.sort_values(by=["Max Aging Day"], ascending=[False], inplace=True)

    pivot_table.loc['Grand Total'] = pivot_table.iloc[:, 0:-1].max()
    pivot_table["Count of Tracking Number"][len(pivot_table)-1] = pivot_table["Count of Tracking Number"].sum()
    pivot_table = pivot_table.reset_index(level=0)

return pivot_table