问题描述
如何对 DataFrame 进行排序,以便回收"重复列中的行?
How can I sort a DataFrame so that rows in the duplicate column are "recycled"?
例如,我原来的 DataFrame 是这样的:
For example, my original DataFrame looks like this:
In [3]: df Out[3]: A B 0 r1 0 1 r1 1 2 r2 2 3 r2 3 4 r3 4 5 r3 5
我希望它转向:
In [3]: df_sorted Out[3]: A B 0 r1 0 2 r2 2 4 r3 4 1 r1 1 3 r2 3 5 r3 5
对行进行排序,使得列 A 中的行处于回收"状态.时尚.
Rows are sorted such that rows in columns A are in a "recycled" fashion.
我在 Pandas 中搜索过 API,但似乎没有任何合适的方法可以这样做.我可以编写一个复杂的函数来完成此操作,但只是想知道是否有任何智能方法或现有的 pandas 方法可以做到这一点?提前非常感谢.
I have searched APIs in Pandas, but it seems there isn't any proper method to do so. I can write a complicated function to accomplish this, but just wondering is there any smart way or existing pandas method can do this? Thanks a lot in advance.
更新:为错误的陈述道歉.在我真正的问题中,列 B 包含字符串值.
Update: Apologies for a wrong statement. In my real problem, column B contains string values.
推荐答案
你可以使用cumcount 用于计算列 A 中的重复项,然后是 sort_values 首先由 A (在示例没必要,在实际数据中可能很重要),然后通过 C.最后删除列 C 由 <代码>丢弃:
You can use cumcount for counting duplicates in column A, then sort_values first by A (in sample not necessary, in real data maybe important) and then by C. Last remove column C by drop:
df['C'] = df.groupby('A')['A'].cumcount() df.sort_values(by=['C', 'A'], inplace=True) print (df) A B C 0 r1 0 0 2 r2 2 0 4 r3 4 0 1 r1 1 1 3 r2 3 1 5 r3 5 1 df.drop('C', axis=1, inplace=True) print (df) A B 0 r1 0 2 r2 2 4 r3 4 1 r1 1 3 r2 3 5 r3 5
时间安排:
小df (len(df)=6)
In [26]: %timeit (jez(df)) 1000 loops, best of 3: 2 ms per loop In [27]: %timeit (boud(df1)) 100 loops, best of 3: 2.52 ms per loop
大 df (len(df)=6000)
In [23]: %timeit (jez(df)) 100 loops, best of 3: 3.44 ms per loop In [28]: %timeit (boud(df1)) 100 loops, best of 3: 2.52 ms per loop
计时代码:
df = pd.concat([df]*1000).reset_index(drop=True) df1 = df.copy() def jez(df): df['C'] = df.groupby('A')['A'].cumcount() df.sort_values(by=['C', 'A'], inplace=True) df.drop('C', axis=1, inplace=True) return (df) def boud(df): df['C'] = df.groupby('A')['B'].rank() df = df.sort_values(['C', 'A']) df.drop('C', axis=1, inplace=True) return (df) 100 loops, best of 3: 4.29 ms per loop