Loop through a pandas matrix (in the form of a dataframe) and change elements based on conditions

user_13298611 0
Viewed 53

I have a Pandas Dataframe in the form of a matrix below which represents similarity scores between the elements (people) in the rows and the columns.

|            |     A     |     B     |    C     |
|------------|-----------|---- ------|----------|
|      D     |    0.4    |    0.1    |   0.1    |
|      E     |    0.2    |    0.1    |   0.4    |
|      F     |    0.9    |    0.4    |   0.3    |
|      G     |    0.4    |    0.2    |   0.6    |
|      H     |    0.3    |    0.1    |   0.7    |

Further, I have a list of location identifiers for these elements.

A - London
B - Sydney
C - Paris
D - Paris
E - Delhi
F - London
G - Melbourne
H - Mumbai

I want to loop through the matrix and make the similarity score equal to 0 if the location is same between the two elements. In this example, I want to replace the intersection of A and F which is 0.9 and the intersection of D and C which is 0.1 with 0 each.

Thanks!

Edit:

The final expected output I am looking for is as below:

|            |     A     |     B     |    C     |
|------------|-----------|---- ------|----------|
|      D     |    0.4    |    0.1    |   0.0    |
|      E     |    0.2    |    0.1    |   0.4    |
|      F     |    0.0    |    0.4    |   0.3    |
|      G     |    0.4    |    0.2    |   0.6    |
|      H     |    0.3    |    0.1    |   0.7    |
pandas python-3.x matrix python loops
1 Answers

For match columns names with cities was created dictionary. Then is rename index and columns and compare with numpy broadcasting, last pass mask to DataFrame.mask:

d = {'A': 'London', 'B': 'Sydney', 'C': 'Paris', 'D': 'Paris', 
     'E': 'Delhi', 'F': 'London', 'G':'Melbourne','H':'Mumbai'}


df1 = df.rename(index=d, columns=d)
df = df.mask(df1.index.to_numpy()[:, None] == df1.columns.to_numpy(), 0)
print (df)
     A    B    C
D  0.4  0.1  0.0
E  0.2  0.1  0.4
F  0.0  0.4  0.3
G  0.4  0.2  0.6
H  0.3  0.1  0.7

Details:

print (df1)
           London  Sydney  Paris
Paris         0.4     0.1    0.1
Delhi         0.2     0.1    0.4
London        0.9     0.4    0.3
Melbourne     0.4     0.2    0.6
Mumbai        0.3     0.1    0.7

print (df1.index.to_numpy()[:, None] == df1.columns.to_numpy())
[[False False  True]
 [False False False]
 [ True False False]
 [False False False]
 [False False False]]
user_29010020
Related