Having trouble sorting the Cabin values of the Titanic Dataset properly
pandas, sorting
Solution
# setup regex for str.extract
# ?P<letter> tells pandas to make that a column with name 'letter'
regex = '(?P<letter>\D+)(?P<digit>\d*)'
# easy access to column names I'm making in extract step
cols = ['letter', 'digit']
# run extract. will pull out letter and digit
split_df = df.Cabin.str.extract(regex, expand=True)
# make sure digit column is numeric and fill with 0
split_df['digit'] = pd.to_numeric(split_df['digit'], 'coerce').fillna(0)
# sort by cols gets us the right sort
split_df.sort_values(cols, inplace=True)
# use sorted split_df.index for a slice
df = df.ix[split_df.index]
df.head(20)
Problem
So I have a series of Cabin values; the left is the index and the right column holds the Cabin values. After using the sort_values method, I was only able to partially sort the values. ``` x = Cabin_Fare=Cabin_Fare.sort_values(['Cabin' ]) 210 A31 186 A32 446 A34 1185 A34 1266 A34 807 A36 97 A 24 A6 175 A7 1058 B10 738 B101 816 B102 1107 B11 330 B18 524 B18 171 B19 691 B20 660 D48 682 D49 626 D50 22 D56 783 D6 276 D7 628 D9 430 E10 718 E101 304 E101 124 E101 461 E12 752 E121 1234 NaN 1252 NaN 1257 NaN 73 NaN 121 NaN ``` The problem I'm having is despite being able to sort the cabin letters, I'm having trouble sorting by the numbers attached to the Cabin letters. So my desired output is ``` 97 A 24 A6 175 A7 210 A31 186 A32 446 A34 1185 A34 1266 A34 807 A36 1058 B10 1107 B11 330 B18 524 B18 171 B19 691 B20 738 B101 816 B102 ........ 1234 NaN 1252 NaN 1257 NaN 73 NaN 121 NaN ``` I'm not particular about the NaN values, but I would like them at the end of the series. The lone Cabin values such as the lone "A" could have a "0" added to it if necessary, but I want the letters without numbers attached to them, to be first on the list. I was given a few ideas but It turns out this code(below), messes with the order of the letters. I want to retain the alphabetical order. ``` x.reindex(x[x.notnull()].str[1:].replace('', 0).astype(int).sort_values().index) ``` Thanks.