如何通过同时包含数字和字符串的列对 pandas 数据框进行排序? [英] How to sort a pandas dataframe by a column that has both numbers and strings?

问题描述

我有一个看起来像这样的数据框

I have a dataframe that looks like this

         col0         col1  col2   col4
         1    '1ZE7999'  865545   20    20
         2    'R022428'  865584  297     0
         3    34         865665  296     0 
         4    56         865700  297     0
         5    100        865628  292     5

我想通过'col0'对其进行排序，首先是数字值，然后是字符串，这是Excel排序的方式

I want to sort it by 'col0', first the numerical values, then the strings, the way that Excel sorts

       col0         col1  col2   col4
  3    34         865665  296     0 
  4    56         865700  297     0
  5    100        865628  292     5
  1    '1ZE7999'  865545   20    20
  2    'R022428'  865584  297     0

我用过

df.sort_values(by='col1', ascending=True)

但这并不能这样排序，而是从0-9到a-z进行排序

But that does not sort it that way, it sorts it from 0-9 then a-z

      col0         col1  col2   col4
 1    '1ZE7999'  865545   20    20
 5    100        865628  292     5
 3    34         865665  296     0 
 4    56         865700  297     0
 2    'R022428'  865584  297     0

推荐答案

pd.to_numeric + sort_values + loc-

df.loc[pd.to_numeric(df.col0, errors='coerce').sort_values().index]

        col0    col1  col2  col4
3         34  865665   296     0
4         56  865700   297     0
5        100  865628   292     5
1  '1ZE7999'  865545    20    20
2  'R022428'  865584   297     0

---

详细信息

pd.to_numeric将非整数值强制为NaN-

i = pd.to_numeric(df.col0, errors='coerce')
i

1      NaN
2      NaN
3     34.0
4     56.0
5    100.0
Name: col0, dtype: float64

sort_values对列进行排序，而忽略NaN.

sort_values sorts the column, ignoring NaNs.

j = i.sort_values()
j

3     34.0
4     56.0
5    100.0
1      NaN
2      NaN
Name: col0, dtype: float64

观察索引.您需要做的就是使用索引 reindex 数据框. loc或reindex都可以.

Observe the index. All you need to do is use the index to reindex the dataframe. Either loc or reindex will do it.

df.loc[j.index]

        col0    col1  col2  col4
3         34  865665   296     0
4         56  865700   297     0
5        100  865628   292     5
1  '1ZE7999'  865545    20    20
2  'R022428'  865584   297     0

df.reindex(index=j.index)

        col0    col1  col2  col4
3         34  865665   296     0
4         56  865700   297     0
5        100  865628   292     5
1  '1ZE7999'  865545    20    20
2  'R022428'  865584   297     0

如果您需要重置索引，这很容易做到.

If you need to reset the index, that's easily done.

df.loc[j.index].reset_index(drop=True)

        col0    col1  col2  col4
0         34  865665   296     0
1         56  865700   297     0
2        100  865628   292     5
3  '1ZE7999'  865545    20    20
4  'R022428'  865584   297     0

这篇关于如何通过同时包含数字和字符串的列对 pandas 数据框进行排序?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！