How to sort values in DataFrame for both numerical and string values? [duplicate]

Question

I have the following pandas DataFrame with mixed data types: string and integer values. I want to sort values of this DataFrame in descending order using multiple columns: Price and Name. The string values (i.e. Name) should be sorted in the alphabetical order, or actually can be ignored at all, because the most important ones are numerical values.

The problem is that the list of target columns can contain both string and integer columns, e.g. target_columns = ["Price","Name"]

d = {'1': ['25', 'AAA', 2], '2': ['30', 'BBB', 3], '3': ['5', 'CCC', 2], \
     '4': ['300', 'DDD', 2], '5': ['30', 'DDD', 3],  '6': ['100', 'AAA', 3]}

columns=['Price', 'Name', 'Class']

target_columns = ['Price', 'Name']
order_per_cols = [False] * len(target_columns)

df = pd.DataFrame.from_dict(data=d, orient='index')
df.columns = columns
df.sort_values(list(target_columns), ascending=order_per_cols, inplace=True)

Currently, this code fails with the following message:

TypeError: '<' not supported between instances of 'str' and 'int'

The expected output:

Price    Name    Class
300      DDD     2
100      AAA     3
30       DDD     3
30       BBB     3
25       AAA     2
5        CCC     2

@Erfan: Please see my update. But as I said, the sorting of string columns can be ignored if there is some way to identify them in target_columns. — Fluxy
– Fluxy, Commented Dec 3, 2019 at 20:43
Then why not just df = df.sort_values('Price', ascending=False)? — Erfan
– Erfan, Commented Dec 3, 2019 at 20:44

Erfan · Accepted Answer · 2019-12-03 20:54:59Z

1

If I understand you correctly, you want a generic way that excludes the object columns from your selection.

We can use DataFrame.select_dtypes for this, then sort on the numeric columns:

# df['Price'] = pd.to_numeric(df['Price'])
numeric = df[target_columns].select_dtypes('number').columns.tolist()
df = df.sort_values(numeric, ascending=[False]*len(numeric))

   Price Name  Class
4    300  DDD      2
6    100  AAA      3
2     30  BBB      3
5     30  DDD      3
1     25  AAA      2
3      5  CCC      2

edited Dec 3, 2019 at 20:54

answered Dec 3, 2019 at 20:48

Erfan

43.3k10 gold badges75 silver badges86 bronze badges

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

5 Comments

Fluxy Over a year ago

Does numeric permit int64, float?

Erfan Over a year ago

Yes, quoted from documentation: "To select all numeric types, use np.number or 'number'"

Fluxy Over a year ago

Sorry, in my case all columns might be 'object'. Those that are indeed numeric might be also object or datetime, as you can see in case of Price.

Fluxy Over a year ago

Oh, haven't seen your line df['Price'] = pd.to_numeric(df['Price']). But how can I know that Price should be transformed to numeric? I do not have any info about column types in advance.

Kardi Teknomo Over a year ago

AttributeError: 'Series' object has no attribute 'select_dtypes'

Suraj Rao · Accepted Answer · 2022-02-22 06:15:37Z

0

One more solution could be -

Using 'by' parameter in sort_values function

d = ({'1': ['25', 'AAA', 2], '2': ['30', 'BBB', 3], '3': ['5', 'CCC', 2], \
     '4': ['300', 'DDD', 2], '5': ['30', 'DDD', 3],  '6': ['100', 'AAA', 3]})

df = pd.DataFrame.from_dict(data=d,columns=['Price','Name','Class'],orient='index')
df['Price'] = pd.to_numeric(df['Price'])
df.sort_values(**by** = ['Price','Name'],ascending=False)

edited Feb 22, 2022 at 6:15

Suraj Rao

29.7k11 gold badges96 silver badges104 bronze badges

answered Feb 22, 2022 at 6:14

Akash Singh

11 silver badge1 bronze badge

Collectives™ on Stack Overflow

How to sort values in DataFrame for both numerical and string values? [duplicate]

2 Answers 2

5 Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

Comments

Linked

Related