CODEWITHSUNDEEP
pythonpandasdataframepython-3.6
IamTheWalrus
IamTheWalrus

Reputation: 604

Sort Pandas Dataframe by substrings of a column

Given a DataFrame:

    name             email
0   Carl    [email protected]
1    Bob     [email protected]
2  Alice   [email protected]
3  David  [email protected]
4    Eve     [email protected]

How can it be sorted according to the email's domain name (alphabetically, ascending), and then, within each domain group, according to the string before the "@"?

The result of sorting the above should then be:

    name             email
0    Bob     [email protected]
1    Eve     [email protected]
2  David  [email protected]
3  Alice   [email protected]
4   Carl    [email protected]

Upvotes: 9

Views: 3683

Answers (2)

jezrael
jezrael

Reputation: 863116

Use:

df = df.reset_index(drop=True)
idx = df['email'].str.split('@', expand=True).sort_values([1,0]).index
df = df.reindex(idx).reset_index(drop=True)
print (df)
    name             email
0    Bob     [email protected]
1    Eve     [email protected]
2  David  [email protected]
3  Alice   [email protected]
4   Carl    [email protected]

Explanation:

  1. First reset_index with drop=True for unique default indices
  2. Then split values to new DataFrame and sort_values
  3. Last reindex to new order

Upvotes: 7

cs95
cs95

Reputation: 402783

Option 1
sorted + reindex

df = df.set_index('email')
df.reindex(sorted(df.index, key=lambda x: x.split('@')[::-1])).reset_index()

              email   name
0     [email protected]    Bob
1     [email protected]    Eve
2  [email protected]  David
3   [email protected]  Alice
4    [email protected]   Carl

Option 2
sorted + pd.DataFrame
As an alternative, you can ditch the reindex call from Option 1 by re-creating a new DataFrame. 

pd.DataFrame(
    sorted(df.values, key=lambda x: x[1].split('@')[::-1]), 
    columns=df.columns
)

    name             email
0    Bob     [email protected]
1    Eve     [email protected]
2  David  [email protected]
3  Alice   [email protected]
4   Carl    [email protected]

Upvotes: 3

Related Questions