python - How to populate a square, symmetric DataFrame with elements from a Series based on pair names?

Question

In the following empty DataFrame, which is a square, symmetric matrix,

df = pd.DataFrame(np.zeros((6,6)), index=names, columns=names)

df looks like

    MCD   VZ  JPM   PG  WBA  NKE
MCD  0.0  0.0  0.0  0.0  0.0  0.0
VZ   0.0  0.0  0.0  0.0  0.0  0.0
JPM  0.0  0.0  0.0  0.0  0.0  0.0
PG   0.0  0.0  0.0  0.0  0.0  0.0
WBA  0.0  0.0  0.0  0.0  0.0  0.0
NKE  0.0  0.0  0.0  0.0  0.0  0.0

how to receive the corresponding elements from the last column of a different DataFrame, shown below, df2.mi, according to pair-names like ('MCD','VZ'), as well as place the same values for (i,j) in those for (j,i) (because of matrix symmetry)? For example, 0.263357 should appear in df for the two places ('MCD','VZ') and ('VZ','MCD'). A toy answer with only 3 names would be fine.

Alternatively, is there a common algorithm for populating a square, symmetric matrix by for loop, according to the (i,j) rules described?

Answer 1

You can pivot and add values + transposed values (.T) to df.

Assuming df2 has the structure of the DataFrame posted as a picture (values below are random):

z = df2.pivot('asset1', 'asset2', 'mi')
df.add(z, fill_value=0).add(z.T, fill_value=0)

Output:

          JPM       MCD       NKE        PG        VZ       WBA
JPM  0.000000  0.532617  0.322079  0.377284  0.242471  0.020071
MCD  0.532617  0.000000  0.504874  1.494942  0.737827  0.036683
NKE  0.322079  0.504874  0.000000  0.726165  0.255161  0.645917
PG   0.377284  1.494942  0.726165  0.000000  0.654995  0.593592
VZ   0.242471  0.737827  0.255161  0.654995  0.000000  0.107172
WBA  0.020071  0.036683  0.645917  0.593592  0.107172  0.000000