My data is already sorted by groups based on one column. And within each group I am trying to reorder the values in another column. The below example illustrates what I mean:
Currently my data looks like this, its sorted by the carrier name. The 3rd column shows what type of products they’re holding. However, I would like chicken to be first on the list for every carrier, so that it looks like the second table below.
| Name | Amount | Product |
|---|---|---|
| Carrier A | 1 | Apples |
| Carrier A | 9 | Oranges |
| Carrier A | 8 | Bananas |
| Carrier A | 10 | Chicken |
| Carrier A | 28 | Total |
| Carrier B | 9 | Apples |
| Carrier B | 4 | Oranges |
| Carrier B | 6 | Bananas |
| Carrier B | 9 | Chicken |
| Carrier B | 28 | Total |
| Carrier C | 5 | Apples |
| Carrier C | 8 | Oranges |
| Carrier C | 9 | Bananas |
| Carrier C | 10 | Chicken |
| Carrier C | 32 | Total |
The below table is what I am trying to achieve.
| Name | Amount | Product |
|---|---|---|
| Carrier A | 10 | Chicken |
| Carrier A | 1 | Apples |
| Carrier A | 9 | Oranges |
| Carrier A | 8 | Bananas |
| Carrier A | 18 | Total |
| Carrier B | 9 | Chicken |
| Carrier B | 9 | Apples |
| Carrier B | 4 | Oranges |
| Carrier B | 6 | Bananas |
| Carrier B | 19 | Total |
| Carrier C | 10 | Chicken |
| Carrier C | 5 | Apples |
| Carrier C | 8 | Oranges |
| Carrier C | 9 | Bananas |
| Carrier C | 22 | Total |
Is there a way to do this with pandas? Instinctively I looked at feeding df.sort_value both the Name and Product columns, but realized that it can only sort by ascending and descending order.
Also the order of values in the “Name” column must be preserved for my task. In this example, its already ordered alphabetically, but its not necessarily the case nor my intention in my actual dataset.
Any insight would be appreciated!
Edit: added details for additional clarity
Advertisement
Answer
Use pd.CategoricalDType to accomplish that:
# create your ordered listcat = ['Chicken'] + sorted([p for p in df["Product"].unique() if not p in ['Chicken', 'Total']]) + ['Total']# set as categorydf = df.astype({'Product': pd.CategoricalDtype(cat, ordered=True)})>>> df.info()<class 'pandas.core.frame.DataFrame'>RangeIndex: 15 entries, 0 to 14Data columns (total 3 columns): # Column Non-Null Count Dtype--- ------ -------------- ----- 0 Name 15 non-null object 1 Amount 15 non-null int64 2 Product 15 non-null categorydtypes: category(1), int64(1), object(1)memory usage: 595.0+ bytes>>> df.sort_values(['Name', 'Product']) Name Amount Product3 Carrier A 10 Chicken0 Carrier A 1 Apples2 Carrier A 8 Bananas1 Carrier A 9 Oranges4 Carrier A 28 Total8 Carrier B 9 Chicken5 Carrier B 9 Apples7 Carrier B 6 Bananas6 Carrier B 4 Oranges9 Carrier B 28 Total13 Carrier C 10 Chicken10 Carrier C 5 Apples12 Carrier C 9 Bananas11 Carrier C 8 Oranges14 Carrier C 32 Total