How to maintain order when selecting rows in pandas dataframe?

Question

I want to select rows in a particular order given in a list. For example

This dataframe

a=[['car',1],['bike',3],['jewel',2],['tv',5],['phone',6]]

df=pd.DataFrame(a,columns=['items','quantity'])

>>> df
   items  quantity
0    car         1
1   bike         3
2  jewel         2
3     tv         5
4  phone         6

I want to get the rows with this order ['tv','car','phone'], that is, first row tv and then car and then phone. I tried this method but it doesn't maintain order

arr=['tv','car','phone']

df.loc[df['items'].isin(arr)]

   items  quantity
0    car         1
3     tv         5
4  phone         6

cs95 · Accepted Answer · 2019-06-19T01:18:11.117

Here's a non-intrusive solution using Index.get_indexer that doesn't involve setting the index:

df.iloc[pd.Index(df['items']).get_indexer(['tv','car','phone'])]

   items  quantity
3     tv         5
0    car         1
4  phone         6

Note that if this is going to become a frequent thing (by thing, I mean "indexing" with a list on a column), you're better off turning that column into an index. Bonus points if you sort it.

df2 = df.set_index('items')
df2.loc[['tv','car','phone']]  

       quantity
items          
tv            5
car           1
phone         6

BENY · Answer 2 · 2019-06-19T01:46:03.943

7

IIUC Categorical

df=df.loc[df['items'].isin(arr)]
df.iloc[pd.Categorical(df['items'],categories=arr,ordered=True).argsort()]
Out[157]: 
   items  quantity
3     tv         5
0    car         1
4  phone         6

Or reindex :Notice only different is this will not save the pervious index and if the original index do matter , you should using Categorical (Mentioned by Andy L, if you have duplicate in items ,reindex will failed )

df.set_index('items').reindex(arr).reset_index()
Out[160]: 
   items  quantity
0     tv         5
1    car         1
2  phone         6

Or loop via the arr

pd.concat([df[df['items']==x] for x in arr])
Out[171]: 
   items  quantity
3     tv         5
0    car         1
4  phone         6

edited Jun 19 '19 at 01:46

answered Jun 19 '19 at 00:51

BENY

317,841
20
164
234

`reindex` will fail if his real data has duplicates `items` ? – Andy L. Jun 19 '19 at 01:44
1

@AndyL. yelp it will, However, he can he can check other methods I provided :-) – BENY Jun 19 '19 at 01:45
cool! just asking to make sure I don't misunderstand `reindex` :) – Andy L. Jun 19 '19 at 01:48

score 5 · Answer 3 · answered Jun 19 '19 at 02:05

5

merge to the rescue:

(pd.DataFrame({'items':['tv','car','phone']})
   .merge(df, on='items')
)

Output:

   items  quantity
0     tv         5
1    car         1
2  phone         6

answered Jun 19 '19 at 02:05

Quang Hoang

146,074
10
56
74

df is not in order of the array. – Yaakov Bressler Jun 19 '19 at 15:10
@YaakovBressler Yes, and merge with the dataframe constructed from the array takes care of that. – Quang Hoang Jun 19 '19 at 15:11
I don't assume the array's order will be important, but what if it were? Would there be a way of maintaining that in a merge? – Yaakov Bressler Jun 19 '19 at 15:16
I don't understandt. It IS the purpose of using merge here. That is `merge` preserve the order of the GIVEN array. – Quang Hoang Jun 19 '19 at 15:18
The order of the array _(not that of the df)_ – Yaakov Bressler Jun 19 '19 at 15:29
Yes, THE ORDER OF THE ARRAY is maintained by `merge`. Did you even try? – Quang Hoang Jun 19 '19 at 15:30
1

I see. _My mistake, sorry._ However, the original index was lost. – Yaakov Bressler Jun 19 '19 at 15:35

score 4 · Answer 4 · answered Jun 19 '19 at 04:15

For all items to be chosen existing in input df, here's one with searchsorted and should be good on performance -

In [43]: sidx = df['items'].argsort()

In [44]: df.iloc[sidx[df['items'].searchsorted(['tv','car','phone'],sorter=sidx)]]
Out[44]: 
   items  quantity
3     tv         5
0    car         1
4  phone         6

Chris Farr · Answer 5 · 2019-06-19T01:52:29.483

3

Here is another variety that uses .loc.

# Move items to the index, select, then reset.
df.set_index("items").loc[arr].reset_index()

Or another that doesn't change the index.

df.loc[df.reset_index().set_index("items").loc[arr]["index"]]

edited Jun 19 '19 at 01:52

answered Jun 19 '19 at 01:00

Chris Farr

3,580
1
21
24

Note that `.loc` does not preserve the order in multiindexed Series/DataFrames. This discrepancy with `.loc` between single and multiindexed dataframes is considered a [bug](https://github.com/pandas-dev/pandas/issues/22797) and the [solution](https://github.com/pandas-dev/pandas/pull/28933) is part of the milestones of Pandas 1.1.0. – Ivan De Paz Centeno Jul 16 '20 at 12:00

score 3 · Answer 6 · answered Jun 19 '19 at 01:32

I would create a dictionary from arr and map it to items and dropna, sort_values

d = dict(zip(arr, range(len(arr))))

Out[684]: {'car': 1, 'phone': 2, 'tv': 0}

df.loc[df['items'].map(d).dropna().sort_values().index]

Out[693]:
   items  quantity
3     tv         5
0    car         1
4  phone         6

score 0 · Answer 7 · answered Jun 19 '19 at 01:01

0

Why not:

>>> df.iloc[df.loc[df['items'].isin(arr), 'items'].apply(arr.index).sort_values().index]
   items  quantity
3     tv         5
0    car         1
4  phone         6
>>>

answered Jun 19 '19 at 01:01

U13-Forward

69,221
14
89
114

score 0 · Answer 8 · answered Jun 19 '19 at 15:14

Why not search for index, filter and re-order:

df['new_order'] = df['items'].apply(lambda x: arr.index(x) if x in arr else -1)

df_new = df[df['new_order']>=0].sort_values('new_order')


   items  quantity  new_order
3     tv         5          0
0    car         1          1
4  phone         6          2

How to maintain order when selecting rows in pandas dataframe?

8 Answers8

Linked