Pandas move row based on condition

Question

I am trying to move every row that contains a specific character in a column to the bottom of the dataframe.....

    Index  Closer  Running
      0      4      AQ
      1      -      AQ
      2      -      AD
      3      2      AP
      4      9      AP
      5      5      AQ

I am trying to move every row in the Closer column that contains "-" to the bottom of the dataframe so my resultant dataframe looks like this, how do I go about doing this?

      Index  Closer  Running
      0      4        AQ
      1      2        AP
      2      9        AP
      3      5        AQ
      4      -        AQ
      5      -        AD

get the index of those rows where "-" is present. Use `loc` to index into two dataframes and simply concat. — Siddhant Tandon, Sep 26 '19 at 14:29

moys · Accepted Answer · 2019-09-27T06:31:58.673

6

IIUC, this is what you need.

m = df.Closer.str.isdigit()
df[m].append(df[~m]).reset_index(drop=True)

Output

Index   Closer  Running
0   0   4   AQ
1   3   2   AP
2   4   9   AP
3   5   5   AQ
4   1   -   AQ
5   2   -   AD

edited Sep 27 '19 at 06:31

answered Sep 26 '19 at 14:32

moys

7,747
2
11
42

1

better is `df[m].append(df[~m], ignore_index=True)` – jezrael Sep 27 '19 at 06:33

score 4 · Answer 2 · answered Sep 26 '19 at 14:33

A one line solution is to filter DataFrame first, selecting rows with '-' and rows without it. Afterwards, you create a new DataFrame, which initially doesn't contain rows with '-', and second DF where only rows with '-' are stored. Then you concat them together.

d = pd.DataFrame(data = {'Closer':[4,'-','-',2,9,5],'Running':'AQ','AQ','AD','AP','AP','AQ']})
d = pd.concat([d[d.Closer!='-'],d[d.Closer=='-']])
d

score 2 · Answer 3 · answered Sep 26 '19 at 14:33

2

Use a new dataframe, to filter on the condition then concatenate the two dataframe.

e.g. assuming your data in df

df1 = pd.DataFrame()
df1 = df1.append(df[df["Closer"] != "-"])
df1 = df1.append(df[df["Closer"] == "-"])

Here is a screenshot:

answered Sep 26 '19 at 14:33

Aziz Alto

19,057
5
77
60

BENY · Answer 4 · 2019-09-26T14:53:34.190

1

IIUC

df.loc[(~df.Closer.str.isdigit()).sort_values(kind='mergesort').index]
Out[24]: 
   Index Closer Running
0      0      4      AQ
3      3      2      AP
4      4      9      AP
5      5      5      AQ
1      1      -      AQ
2      2      -      AD

edited Sep 26 '19 at 14:53

answered Sep 26 '19 at 14:34

BENY

317,841
20
164
234

1

Note that I don't think this is guaranteed to preserve the order, because the default sort method IIUC is a non-stable quicksort. You'd have to specify `kind='mergesort'` to guarantee the same results as the other approaches. – DSM Sep 26 '19 at 14:46

Pandas move row based on condition

4 Answers4

Linked