CODEWITHSUNDEEP
python-3.xpandas
coelidonum
coelidonum

Reputation: 543

Copy the value in the empty rows of a column if the values of the corresponding rows of the other columns are equal (Pandas)

I have a pandas dataframe like this:

PLAYER | PRODUCT | HUB | PHONE
________________________________
A      | W       | AQ  |
A      | W       | AQ  | 0024
A      | Q       | AW  | 9888
B      | W       | QW  | 
B      | W       | QW  | 0456
B      | Z       | QW  |
C      | F       | FZ  | 0999
C      | F       | FZ  |
C      | F       | FZ  |

I would like to copy the value in the column 'PHONE' in the empty rows if the corresponding rows of the other 3 columns (PLAYER, PRODUCT, HUB) are equal among them.

So the expected output is:

PLAYER | PRODUCT | HUB | PHONE
________________________________
A      | W       | AQ  | 0024
A      | W       | AQ  | 0024
A      | Q       | AW  | 9888
B      | W       | QW  | 0456
B      | W       | QW  | 0456
B      | Z       | QW  |
C      | F       | FZ  | 0999
C      | F       | FZ  | 0999
C      | F       | FZ  | 0999

Note that the sixth row is different from the previous two, so the value of the phone is not copied there. Could someone help me?

Upvotes: 1

Views: 45

Answers (1)

jezrael
jezrael

Reputation: 862921

Use GroupBy.apply with forward and back filling missing values created by Series.replace (if necessary):

df['PHONE'] = df['PHONE'].replace('', np.nan)
df['PHONE'] = (df.groupby(['PLAYER','PRODUCT','HUB'])['PHONE']
                 .apply(lambda x: x.ffill().bfill())
                 .fillna(''))
print (df)
  PLAYER PRODUCT HUB PHONE
0      A       W  AQ  0024
1      A       W  AQ  0024
2      A       Q  AW  9888
3      B       W  QW  0456
4      B       W  QW  0456
5      B       Z  QW      
6      C       F  FZ  0999
7      C       F  FZ  0999
8      C       F  FZ  0999

Upvotes: 2

Related Questions