How to reorder rows in pandas dataframe by factor level in python?

Question

I've created a small dataset comparing coffee drink prices per cup size.

When I pivot my dataset the output automatically reorders the index (the 'Size' column) alphabetically.

Is there a way to assign the different sizes a numerical level (e.g. small = 0, medium = 1, large = 2) and reorder the rows this way instead?

I'm know this can be done in R using the forcats library (using fct_relevel for example), but I'm not aware of how to do this in python. I would prefer to keep the solution to using numpy and pandas.

data = {'Item': np.repeat(['Latte', 'Americano', 'Cappuccino'], 3),
        'Size': ['Small', 'Medium', 'Large']*3,
        'Price': [2.25, 2.60, 2.85, 1.95, 2.25, 2.45, 2.65, 2.95, 3.25]
       }

df = pd.DataFrame(data, columns = ['Item', 'Size', 'Price'])
df = pd.pivot_table(df, index = ['Size'], columns = 'Item')
df

#         Price
# Item    Americano Cappuccino  Latte
#   Size            
#  Large       2.45       3.25   2.85
# Medium       2.25       2.95   2.60
#  Small       1.95       2.65   2.25

df = df.reindex(["Small", "Medium", "Large"])?

not_speshal
– not_speshal

2021-11-30 18:09:37 +00:00
Commented Nov 30, 2021 at 18:09 — not_speshal
– not_speshal, Commented Nov 30, 2021 at 18:09

mozway · Accepted Answer · 2021-11-30 18:13:56Z

4

You can use a Categorical type with ordered=True:

df.index = pd.Categorical(df.index,
                          categories=['Small', 'Medium', 'Large'],
                          ordered=True)
df = df.sort_index()

output:

           Price                 
Item   Americano Cappuccino Latte
Small       1.95       2.65  2.25
Medium      2.25       2.95  2.60
Large       2.45       3.25  2.85

You can access the codes with:

>>> df.index.codes
array([0, 1, 2], dtype=int8)

If this was a Series:

>>> series.cat.codes

edited Nov 30, 2021 at 18:13

answered Nov 30, 2021 at 18:09

mozway

267k13 gold badges56 silver badges106 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

not_speshal Over a year ago

Just edited a typo, feel free to re-edit/revert. +1 :)

mozway Over a year ago

As sammy pointed out it's advantageous to convert to categorical before pivoting ;)

sammywemmy · Accepted Answer · 2021-11-30 18:13:15Z

One option is to create the categorical, before pivoting; for this case I am using encode_categorical from pyjanitor, primarily for convenience :

# pip install pyjanitor
import pandas as pd
import janitor
(df
 .encode_categorical(Size = (None, 'appearance'))
 .pivot_table(index='Size', columns='Item')
)

           Price                 
Item   Americano Cappuccino Latte
Size                             
Small       1.95       2.65  2.25
Medium      2.25       2.95  2.60
Large       2.45       3.25  2.85

This way, you do not have to bother about sorting, since pivoting implicitly does that. You can skip the pyjanitor and just stick to Pandas only:

(df
 .astype({'Size': pd.CategoricalDtype(categories = ['Small', 'Medium', 'Large'], 
                                      ordered = True)})
 .pivot_table(index='Size', columns='Item')
)

           Price                 
Item   Americano Cappuccino Latte
Size                             
Small       1.95       2.65  2.25
Medium      2.25       2.95  2.60
Large       2.45       3.25  2.85

Fatemeh Sangin · Accepted Answer · 2021-11-30 19:42:55Z

1st WAY:

pivot_table function sorts rows based on index. Thus, It is better to use lambda function when applying index in pivot_table function. This way, you don't need any further sorting steps(more time consuming) or any third-party library.

df = pd.pivot_table(df, index = (lambda row: 0 if df.loc[row,'Size']=="Small" else 1 if df.loc[row,'Size']=="Medium" else 2), 
                    columns = 'Item')

         Price                 
Item Americano Cappuccino Latte
0         1.95       2.65  2.25
1         2.25       2.95  2.60
2         2.45       3.25  2.85

2nd WAY:

You can also use your own code and then rename and sort the newly-created table:

df = pd.DataFrame(data, columns = ['Item', 'Size', 'Price'])
df = pd.pivot_table(df, index = ['Size'], columns = 'Item')

# rename:
df = df.rename(index= lambda x: 0 if x=="Small" else 1 if x=="Medium" else 2)

#sort:
df = df.sort_index(ascending = True)


         Price                 
Item Americano Cappuccino Latte
0         1.95       2.65  2.25
1         2.25       2.95  2.60
2         2.45       3.25  2.85

Collectives™ on Stack Overflow

How to reorder rows in pandas dataframe by factor level in python?

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related