Pandas: How to sort rows based on particular suffix values?

Question

My Pandas data frame contains the following data reading from a csv file:

id,values
 1001-MAC,     10
 1034-WIN,     20
 2001-WIN,     15
 3001-MAC,     45
 4001-LINUX,   12
 4001-MAC,     67

df = pd.read_csv('example.csv')
df.set_index('id', inplace=True)

I have to sort this data frame based on the id column order by given suffix list = ["WIN", "MAC", "LINUX"]. Thus, I would like to get the following output:

 id,values
 1034-WIN,     20
 2001-WIN,     15
 1001-MAC,     10
 3001-MAC,     45
 4001-MAC,     67
 4001-LINUX,   12

How can I do that?

Mohamad Al Mdfaa · Accepted Answer · 2022-10-23 12:00:55Z

4

Here is one way to do that:

import pandas as pd
df = pd.read_csv('example.csv')
idx = df.id.str.split('-').str[1].sort_values(ascending=False).index

df = df.loc[idx]
df.set_index('id', inplace=True)
print(df)

answered Oct 23, 2022 at 12:00

Mohamad Al Mdfaa

1,1051 gold badge14 silver badges17 bronze badges

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

Comments

Andrej Kesely · Accepted Answer · 2022-10-23 11:44:39Z

2

Try:

df = df.sort_values(
    by=["id"], key=lambda x: x.str.split("-").str[1], ascending=False
)
print(df)

Prints:

           id  values
1    1034-WIN      20
2    2001-WIN      15
0    1001-MAC      10
3    3001-MAC      45
5    4001-MAC      67
4  4001-LINUX      12

answered Oct 23, 2022 at 11:44

Andrej Kesely

196k15 gold badges60 silver badges105 bronze badges

Comments

mlokos · Accepted Answer · 2022-10-23 11:37:48Z

1

Add a column to a dataframe that would contain only prefixes (use str.split() function for that) and sort whole df based on that new column.

answered Oct 23, 2022 at 11:37

mlokos

4175 silver badges13 bronze badges

1 Comment

Daniel Schneider Over a year ago

Your answer could be improved with additional supporting information. Please edit to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers in the help center.

Lowin Li · Accepted Answer · 2022-10-23 11:41:44Z

1

import pandas as pd
df = pd.DataFrame({
    "id":["1001-MAC", "1034-WIN", "2001-WIN", "3001-MAC", "4001-LINUX", "4001-MAC"],
    "values":[10, 20, 15, 45, 12, 67]
})
df["id_postfix"] = df["id"].apply(lambda x: x.split("-")[1])
df = df.sort_values("id_postfix", ascending=False)
df = df[["id", "values"]]
print(df)

answered Oct 23, 2022 at 11:41

Lowin Li

1396 bronze badges

Comments

G.G · Accepted Answer · 2022-10-23 12:32:13Z

1

Please be sure to answer the question. Provide details and share your research!

answered Oct 23, 2022 at 12:32

G.G

7654 silver badges5 bronze badges

Collectives™ on Stack Overflow

Pandas: How to sort rows based on particular suffix values?

5 Answers 5

Comments

Comments

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

Comments

Comments

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related