CODEWITHSUNDEEP
pythonpython-3.xpandasdataframe
wflwo
wflwo

Reputation: 81

How to delete pandas values based on condition and accordingly shift the rows

For examples, I have a DataFrame like this:

       Rank #1       Rank #2      Rank #3        Rank #4     status 
0        A             B            D               C          in
1        B             C            A               D          out
2        A             D            C               B          in
3        A             B            C               D          out
4        D             C            B               A          out
5        D             A            C               B          out 
...

How can I delete A values if status == out and then shift the other row ranking values to the left?

My expected outcome is:

     Rank #1       Rank #2      Rank #3        Rank #4     status 
0        A             B            D               C        in
1        B             C            D                        out
2        A             D            C               B        in
3        B             C            D                        out
4        D             C            B                        out
5        D             C            B                        out
...

Upvotes: 1

Views: 69

Answers (1)

jpp
jpp

Reputation: 164753

Here is one way.

import pandas as pd, numpy as np

df = pd.DataFrame({1: ['A', 'B', 'A','A', 'D', 'D'],
                   2: ['B', 'C', 'D', 'B', 'C', 'A'],
                   3: ['D', 'A', 'C', 'C', 'B', 'C'],
                   4: ['C', 'D', 'B', 'D', 'A', 'B'],
                   'status': ['in', 'out', 'in', 'out', 'out', 'out']})

mask = df['status'] == 'out'
df.loc[mask, :] = df.loc[mask, :].replace('A', '')

A = [list(''.join(i)) for i in df.iloc[:, :-1].values]

df_res = pd.DataFrame(A).fillna('').assign(result=df.status)

Result

   0  1  2  3 result
0  A  B  D  C     in
1  B  C  D       out
2  A  D  C  B     in
3  B  C  D       out
4  D  C  B       out
5  D  C  B       out

Explanation

  1. Replace "A" with empty string for selected rows.
  2. Shift entries to the left via a list comprehension.
  3. Create result dataframe and add back "status" column.

Upvotes: 1

Related Questions