Fill dataframe with two loops and if condition in python

Question

I have two DataFrames, one looks something like this:

df1:

x    y    Counts
a    b    1
a    c    3
b    c    2
c    d    1

The other has both an index and columns — a list of unique values ​​in the first two columns:

df2

   a  b  c  d
a
b
c
d

What I would like to do is populate a second DataFrame with the values ​​from the first, given that the intersection of the column and index is the same row from the first DataFrame, for example:

   a    b   c   d
a   0   1   3   0
b   1   0   2   0
c   3   2   0   1
d   0   0   1   0

While I try to use two for loops with a double if-condition, it makes a block of the computer (provided that the real DataFrame contains more than 1000 lines).

The part of the code I'm trying to implement (and which makes the calculations seem to be too "heavy" for the computer to work):

for i in df2.index:
    for j in df2.columns:
        if (i==df1.x.any() and j==df1.y.any()):
            df2.loc[i,j]=df1.Counts

, (.. DataFrame) , , .

- , . - . , , igraph. DataFrame, , , igraph. , python-igraph dataframe , numpy. , , .

( ).

Answer 1

- :

import pandas as pd

#df = pd.read_clipboard()
#df2 = df.copy()
df3=df2.pivot(index='x',columns='y',values='Counts')
print df3
print
new=sorted((set(df3.columns.tolist()+df3.index.tolist())))
df3 = df3.reindex(new,columns=new).fillna(0).applymap(int)
print df3

:

y    b    c    d
x               
a  1.0  3.0  NaN
b  NaN  2.0  NaN
c  NaN  NaN  1.0

y  a  b  c  d
x            
a  0  1  3  0
b  0  0  2  0
c  0  0  0  1
d  0  0  0  0

Answer 2

stack df2 fillna df1

idx = pd.Index(np.unique(df1[['x', 'y']]))
df2 = pd.DataFrame(index=idx, columns=idx)

df2.stack(dropna=False).fillna(df1.set_index(['x', 'y']).Counts) \
    .unstack().fillna(0).astype(int)

   a  b  c  d
a  0  1  3  0
b  0  0  2  0
c  0  0  0  1
d  0  0  0  0