Pivot a Two Column DataFrame With No Numeric Column To Aggregate On

user_304661 0
Viewed 35

I have a dataframe with input like this:

df1 = pd.DataFrame(
     {'StoreId':
           [244, 391, 246, 246, 130, 130] , 'PackageStatus': ['IN TRANSIT','IN TRANSIT','IN TRANSIT', 'IN TRANSIT','IN TRANSIT','COLLECTED',]}   
    )

        StoreId     PackageStatus
0           244        IN TRANSIT
1           391        IN TRANSIT
2           246        IN TRANSIT
3           246        IN TRANSIT
4           130        IN TRANSIT
5           130        COLLECTED

The output I'm expecting is to look like this with the package status pivoting to columns and their counts becoming the values:

StoreId     IN TRANSIT       COLLECTED
244         1                0
391         1                0
246         2                0 
130         1                1

All the examples I come across are with a third numeric column with which some aggregation (sum, mean, average etc) is done.

When I tried

 df1.pivot_table(index='StoreId',values='PackageStatus', aggfunc='count')

I get the following instead:

              PackageStatus
StoreId               
130                       2
244                       1
246                       2
391                       1

In my case I need a simple transpose/pivot with the count. How to accomplish this? Thank you.

pandas pivot-table dataframe python-3.x
1 Answers

Use columns="PackageStatus" parameter:

print(
    df1.pivot_table(
        index="StoreId", columns="PackageStatus", aggfunc="size", fill_value=0
    )
)

Prints:

PackageStatus  COLLECTED  IN TRANSIT
StoreId                             
130                    1           1
244                    0           1
246                    0           2
391                    0           1

With .reset_index():

print(
    df1.pivot_table(
        index="StoreId", columns="PackageStatus", aggfunc="size", fill_value=0
    )
    .reset_index()
    .rename_axis("", axis=1)
)

Prints:

   StoreId  COLLECTED  IN TRANSIT
0      130          1           1
1      244          0           1
2      246          0           2
3      391          0           1
user_100359850
Related