Delete rows by conditional and fill in new column in pandas dataframe

Question

I want to do conditional selection for row 6 in origin dataframe

original dataframe:

      B1   B2   B3  B4        BCS  ULCA             MIMO
 3   26A   1A                 0,1     .               1A
 4   28A   1A                 0,1     .               1A
 5   19A   3A   1A              0     .           1A, 3A
 6    3A   1A                 0,1     .    1A, 3A, 1A-3A

Step1. Do row extend for BCS and MIMO

         B1   B2   B3  B4  BCS ULCA    MIMO  
  4     26A   1A            0    .      1A    
  5     26A   1A            1    .      1A  
  6     28A   1A            0    .      1A 
  7     28A   1A            1    .      1A   
  8     19A   3A   1A       0    .      1A  
  9     19A   3A   1A       0    .      3A   
  10     3A   1A            0    .      1A   
  11     3A   1A            1    .      1A   
  12     3A   1A            0    .      3A    
  13     3A   1A            1    .      3A 
  14     3A   1A            0    .   1A-3A   
  15     3A   1A            1    .   1A-3A

Step.2 And then contrast column B1-B4 with MIMO, if it's equal: then put 4 in new column(Bx_m), if not, put 2

cols = ['B1','B2','B3','B4']
arr = np.where(b[cols].eq(b['MIMO'], axis=0), '4','2')
b = b.join(pd.DataFrame(arr, columns=cols, index=b.index).add_suffix('_m'))


      B1   B2   B3  B4  BCS ULCA    MIMO  B1_m  B2_m  B3_m  B4_m
4    26A   1A             0    .      1A    2     4     2    2
5    26A   1A             1    .      1A    2     4     2    2
6    28A   1A             0    .      1A    2     4     2    2
7    28A   1A             1    .      1A    2     4     2    2
8    19A   3A   1A        0    .      1A    2     2     4    2
9    19A   3A   1A        0    .      3A    2     4     2    2
10    3A   1A             0    .      1A    2     4     2    2
11    3A   1A             1    .      1A    2     4     2    2
12    3A   1A             0    .      3A    4     2     2    2
13    3A   1A             1    .      3A    4     2     2    2
14    3A   1A             0    .   1A-3A    2     2     2    2
15    3A   1A             1    .   1A-3A    2     2     2    2

Requirements

But here's an exceptional requirements for the format with row 6 in origin dataframe.
Rules:
Each values in MIMO alternate fill in 4 in correspond Bx_m
If there's value for two value together(1A-3A), then just fill in 4 in Bx_m simultaneously

That is:
If the value format is like 1A, 3A, 1A-3A in MIMO column (instead of 1A, 3A)
Then the output only need to keep 1A-3A in Step.1
And fill in 4 in B1_m and B2_n columns simultaneously in Step.2

Original data:

      B1   B2   B3  B4        BCS  ULCA             MIMO
 6    3A   1A                 0,1     .    1A, 3A, 1A-3A

Original output(wants to change): (6 rows)

          B1   B2   B3  B4  BCS ULCA    MIMO  B1_m  B2_m  B3_m  B4_m
    10    3A   1A             0    .      1A    2     4     2    2
    11    3A   1A             1    .      1A    2     4     2    2
    12    3A   1A             0    .      3A    4     2     2    2
    13    3A   1A             1    .      3A    4     2     2    2
    14    3A   1A             0    .   1A-3A    2     2     2    2
    15    3A   1A             1    .   1A-3A    2     2     2    2

Require target: (only 2 rows. B1_m & B2_m both fill in 4)

          B1   B2   B3  B4  BCS ULCA    MIMO  B1_m  B2_m  B3_m  B4_m
    14    3A   1A             0    .   1A-3A    4     4     2    2
    15    3A   1A             1    .   1A-3A    4     4     2    2

Please help me how to solve it. Thanks.

Update

df = pd.concat([b1.set_index('index'),b2.set_index('index')]).sort_index()
print(df)


        B1   B2   B3  B4 BCS ULCA    MIMO B1_m B2_m B3_m B4_m
index                                                        
0      42A  19A            0    .       .    2    2    2    2
1      18A   1A            0    .      1A    2    4    2    2
10      3A   1A            0    .      3A    4    2    2    2
100    41A  28A   3A       0    .      3A    2    2    4    2
101    41A  28A   3A       0    .     41A    4    2    2    2
102    42A  28A   3A       0    .      3A    2    2    4    2
103    42A  41A   3A       0    .      3A    2    2    4    2
104    42A  41A   3A       0    .     41A    2    4    2    2
105    41C   3A            0    .      3A    2    4    2    2
106    41C   3A            0    .     41C    4    2    2    2
107    41C   3A            0    .  3A-41C    4    4    2    2
108    42C   3A            0    .      3A    2    4    2    2
109    42C  41A            0    .     41A    2    4    2    2
11      3A   1A            1    .      3A    4    2    2    2

jezrael · Accepted Answer · 2018-11-05 12:30:19Z

1

Use:

from  itertools import product
#convert index to strings and then to column for last sorting by index - proper ordering
df = df.rename(str).reset_index()
#check if - in column MIMO
m = df['MIMO'].str.contains('-').copy()

#solution process only rows with - filtered by boolene indexing
df1 = df[m].fillna('').apply(lambda x: x.str.split(',\s*'))

b = pd.DataFrame([j for i in df1.values for j in product(*i)], columns=df1.columns)
#remove non - rows
b1 = b[b['MIMO'].str.contains('-')].copy()
print (b1)
  index  B1  B2 B3 B4 BCS ULCA   MIMO
2     6  3A  1A         0    .  1A-3A
5     6  3A  1A         1    .  1A-3A

#check substrings per rows
b1['B1_m'] = np.where([i in j for i, j in zip(b1['B1'], b1['MIMO'])], '4', '2')
b1['B2_m'] = np.where([i in j for i, j in zip(b1['B2'], b1['MIMO'])], '4', '2')
b1['B3_m'] = np.where(b1['B3'] == b1['MIMO'], '4', '2')
b1['B4_m'] = np.where(b1['B4'] == b1['MIMO'], '4', '2')
print (b1)
  index  B1  B2 B3 B4 BCS ULCA   MIMO B1_m B2_m B3_m B4_m
2     6  3A  1A         0    .  1A-3A    4    4    2    2
5     6  3A  1A         1    .  1A-3A    4    4    2    2

#processes rows with no -
df2 = df[~m].fillna('').apply(lambda x: x.str.split(',\s*'))

b2 = pd.DataFrame([j for i in df2.values for j in product(*i)], columns=df2.columns)
print (b2)
  index   B1  B2  B3 B4 BCS ULCA MIMO
0     3  26A  1A          0    .   1A
1     3  26A  1A          1    .   1A
2     4  28A  1A          0    .   1A
3     4  28A  1A          1    .   1A
4     5  19A  3A  1A      0    .   1A
5     5  19A  3A  1A      0    .   3A

cols = ['B1','B2','B3','B4']
arr = np.where(b2[cols].eq(b2['MIMO'], axis=0), '4','2')
b2 = b2.join(pd.DataFrame(arr, columns=cols, index=b2.index).add_suffix('_m'))
print (b2)
  index   B1  B2  B3 B4 BCS ULCA MIMO B1_m B2_m B3_m B4_m
0     3  26A  1A          0    .   1A    2    4    2    2
1     3  26A  1A          1    .   1A    2    4    2    2
2     4  28A  1A          0    .   1A    2    4    2    2
3     4  28A  1A          1    .   1A    2    4    2    2
4     5  19A  3A  1A      0    .   1A    2    2    4    2
5     5  19A  3A  1A      0    .   3A    2    4    2    2

#join together, convert index values to integers and sorting
df = pd.concat([b1.set_index('index'), b2.set_index('index')]).rename(int).sort_index()
print (df)
        B1  B2  B3 B4 BCS ULCA   MIMO B1_m B2_m B3_m B4_m
index                                                    
3      26A  1A          0    .     1A    2    4    2    2
3      26A  1A          1    .     1A    2    4    2    2
4      28A  1A          0    .     1A    2    4    2    2
4      28A  1A          1    .     1A    2    4    2    2
5      19A  3A  1A      0    .     1A    2    2    4    2
5      19A  3A  1A      0    .     3A    2    4    2    2
6       3A  1A          0    .  1A-3A    4    4    2    2
6       3A  1A          1    .  1A-3A    4    4    2    2

edited Nov 5, 2018 at 12:30

answered Nov 5, 2018 at 8:22

jezrael

868k103 gold badges1.4k silver badges1.3k bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

xiumpt Over a year ago

Hi, thnks for remind. But is there any way to deal with 1A, 3A, 1A-3A in question?

jezrael Over a year ago

@xiumpt - Not understand, can you explain more?

xiumpt Over a year ago

I've updated the question, tell me if there's anything unclear. thanks

xiumpt Over a year ago

Thank you! But I have a little problem at sorting the index... (update my compile output in question)

xiumpt Over a year ago

Got it! Grateful for your help.

|

Collectives™ on Stack Overflow

Delete rows by conditional and fill in new column in pandas dataframe

Requirements

Update

1 Answer 1

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

Requirements

Update

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related