Skip to content Skip to sidebar Skip to footer

How To Maintain Order When Selecting Rows In Pandas Dataframe?

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=['item

Solution 1:

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         50    car         14  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

Solution 2:

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         51    car         12  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

Solution 3:

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

Solution 4:

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         50    car         14  phone         6

Solution 5:

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         50    car         14  phone         6

Post a Comment for "How To Maintain Order When Selecting Rows In Pandas Dataframe?"