I have a pandas dataframe
values
0 x15_2_30
1 x15_3_137
2 x15_6_26
3 x15_9_139
4 x15_10_143
.. ...
266 x208_6_153
267 x210_2_147
268 x211_1_155
269 x212_3_28
270 x212_5_25
I would like to sort this dataframe according to the ascending of number in the middle than number at last.
Preferred output is like that
x15_1_4
x12_1_8
x25_1_12
....
Use .str.extract() to extract the middle number and last number with regex all in one go:
import pandas as pd
from io import StringIO
text = """
values
x15_6_30
x15_6_26
x15_3_137
x15_9_139
x15_10_143
"""
df = pd.read_csv(StringIO(text), sep='\s+', header=0)
df[['middle_number', 'last_number']] = df['values'].str.extract(
pat='_([0-9]+)_([0-9]+)',
expand=True,
).astype(int)
df.sort_values(by=['middle_number', 'last_number', 'values'])
+----+------------+-----------------+---------------+
| | values | middle_number | last_number |
|----+------------+-----------------+---------------|
| 2 | x15_3_137 | 3 | 137 |
| 1 | x15_6_26 | 6 | 26 |
| 0 | x15_6_30 | 6 | 30 |
| 3 | x15_9_139 | 9 | 139 |
| 4 | x15_10_143 | 10 | 143 |
+----+------------+-----------------+---------------+
An even simpler solution would be to use .str.split() and split on the underscore.
Using parameter .str.split(expand=True) makes sure you get all 3 values from the split in 3 separate columns:
df[['first_value', 'middle_value', 'last_value']] = df['values'].str.split(
pat='_',
expand=True,
)
df[['middle_value', 'last_value']] = df[['middle_value', 'last_value']].astype(int)
df = df.sort_values(by=['middle_value', 'last_value', 'first_value'])
+----+------------+---------------+----------------+--------------+
| | values | first_value | middle_value | last_value |
|----+------------+---------------+----------------+--------------|
| 2 | x15_3_137 | x15 | 3 | 137 |
| 1 | x15_6_26 | x15 | 6 | 26 |
| 0 | x15_6_30 | x15 | 6 | 30 |
| 3 | x15_9_139 | x15 | 9 | 139 |
| 4 | x15_10_143 | x15 | 10 | 143 |
+----+------------+---------------+----------------+--------------+
Create a column temp in which you assign the value after the last _ using str.split().str[index]. Use it to sort and then drop the column
#If Using Last Number
df.assign(temp=df['values'].str.split('_').str[2]).sort_values(by='temp', ascending=False).drop('temp',1)
values
0 x15_2_30
269 x212_3_28
2 x15_6_26
270 x212_5_25
268 x211_1_155
266 x208_6_153
267 x210_2_147
4 x15_10_143
3 x15_9_139
1 x15_3_137