# Generate 3D "matrix" with Pandas, based on comparing two dataframes [Python]

Good morning everyone. I am working with Python and Pandas.

I have two DataFrames, of the following type:

df_C = pd.DataFrame(data=[[-3,-1,-1], [5,3,3], [3,3,1], [-1,-1,-3], [-3,-1,-1], [2,3,1], [1,1,1]], columns=['C1','C2','C3'])

C1  C2  C3
0  -3  -1  -1
1   5   3   3
2   3   3   1
3  -1  -1  -3
4  -3  -1  -1
5   2   3   1
6   1   1   1

df_F = pd.DataFrame(data=[[-1,1,-1,-1,-1],[1,1,1,1,1],[1,1,1,-1,1],[1,-1,-1,-1,1],[-1,0,0,-1,-1],[1,1,1,-1,0],[1,1,-1,1,-1]], columns=['F1','F2','F3','F4','F5'])

F1  F2  F3  F4  F5
0  -1   1  -1  -1  -1
1   1   1   1   1   1
2   1   1   1  -1   1
3   1  -1  -1  -1   1
4  -1   0   0  -1  -1
5   1   1   1  -1   0
6   1   1  -1   1  -1


I would like to be able to "cross" these two DataFrames, to generate or one in 3D, as follows:

The new data that is generated must compare the values of the df_F with the values of the df_C, taking into account the following:

• If both values are positive, generate 1
• If both values are negative, generate 1
• If one value is positive and the other negative, it generates 0
• If any of the values is zero, it generates None (NaN)

True table

Comparison of the data df_C vs df_F

df_C vs df_F = 3D
+       +     1
+       -     0
+       0     None
-       +     0
-       -     1
-       0     None
0       +     None
0       -     None
0       0     None


You, who are experts in programming, could you please guide me, as I generate this matrix, I compare the values. I wish to do it with Pandas. I have done it with loops (for) and conditions (if), but it is visually unpleasant and I think that with Pandas it is more efficient and elegant.

Thank you.

• I don't know Python well, but MATLAB has a function called bsxfun which is capable of this operation. Maybe Python has it too, potentially through a package? Commented May 22, 2021 at 10:45

Pandas does not provide a three-dimensional data structure at this time, at some time I do enjoy this structure, but the option (as of today) is obsolete and has been removed. However, it is possible to express this type of data using the long format (also known as EAV) with three key columns (or index levels).

Bearing this in mind; Jan Šimbera from the DataScience group, suggests the following code:

(
df_C
# Transform to long format (two columns: former column names under variable
# and corresponding values under value) plus the original index.
.melt(ignore_index=False)
# Join with the other dataframe, similarly transformed. join() implicitly joins
# on indexes, so this will generate all combinations of the variable column values.
.join(df_F.melt(ignore_index=False), lsuffix='_C', rsuffix='_F')
# Make the index a regular column.
.rename_axis('index')
.reset_index()
# Your rules can be expressed by multiplying the two value columns and examining the sign.
.assign(combined=lambda df: df.value_C * df.value_F)
.assign(output=lambda df:
# Uses the Pandas nullable boolean type (three values: True, False, NA).
pd.Series(pd.NA, index=df.index, dtype='boolean')
# If combined is positive, both values were non-zero with the same sign.
# If combined is negative, both values were non-zero with opposite signs.
# If combined is zero, either of the values was zero, and the NA is retained.
)
# Remove intermediary values. The first three columns can also be transformed
# to a MultiIndex.
[['index', 'variable_C', 'variable_F', 'output']]
)


The complete code would be:

import pandas as pd

df_C = pd.DataFrame(data=[[-3,-1,-1], [5,3,3], [3,3,1], [-1,-1,-3], [-3,-1,-1], [2,3,1], [1,1,1]], columns=['C1','C2','C3'])

df_F = pd.DataFrame(data=[[-1,1,-1,-1,-1],[1,1,1,1,1],[1,1,1,-1,1],[1,-1,-1,-1,1],[-1,0,0,-1,-1],[1,1,1,-1,0],[1,1,-1,1,-1]], columns=['F1','F2','F3','F4','F5'])

eav = df_C.melt(ignore_index=False)
.join(df_F.melt(ignore_index=False), lsuffix='_C', rsuffix='_F')
.rename_axis('index')
.reset_index()
.assign(combined=lambda df: df.value_C * df.value_F)
.assign( output=lambda df:
pd.Series(pd.NA, index=df.index, dtype='boolean')
[['index', 'variable_C', 'variable_F', 'output']]


Which gives us a DataFrame as a result, with the following structure:

>>> eav

index variable_C variable_F  output
0        0         C1         F1    True
1        0         C1         F2   False
2        0         C1         F3    True
3        0         C1         F4    True
4        0         C1         F5    True
..     ...        ...        ...     ...
100      6         C3         F1    True
101      6         C3         F2    True
102      6         C3         F3   False
103      6         C3         F4    True
104      6         C3         F5   False


Another possible solution would be to use Numpy ... in this case we have two possible solutions, one "long" and the other short.

1st. Solution [Numpy]

This solution, thanks to Cassandra Sinclair from the Computer Science Meta group, she suggests:

# The first step is to observe that the relationship can be achieved by multiplication of signs. With numpy.sign(x) we get 0 if x is zero, 1 if positive and -1 if negative, since you check for sign equality, multiplication by the same sign value will always be 1, multiplication by 0 always yields 0 and multiplication by opposite signs yields -1.

import numpy as np
import pandas as pd

df_C = pd.DataFrame(data=[[-3,-1,-1], [5,3,3], [3,3,1], [-1,-1,-3], [-3,-1,-1], [2,3,1], [1,1,1]], columns=['C1','C2','C3'])

df_F = pd.DataFrame(data=[[-1,1,-1,-1,-1],[1,1,1,1,1],[1,1,1,-1,1],[1,-1,-1,-1,1],[-1,0,0,-1,-1],[1,1,1,-1,0],[1,1,-1,1,-1]], columns=['F1','F2','F3','F4','F5'])

Cs = np.sign(df_C.values)
Fs = np.sign(df_F.values)

# The next step is to make the correct kind of broadcast. Using A[:, None], we introduce a new dimension after the first:

assert Cs[:, None].shape == (7, 1, 3)

# So we will expand F with an additional dimension in the middle, so that we can do element wise multiplication of every value in the column of F with one value in the column of C. We also need to expand C, so that the last axis has just a single value.

F2 = Fs[:, None]
C2 = Cs[:,:, None]

#Finally, we multiply and cache the intermediate values so that we can use np.where to replace 0 with None and -1 with 0. However, you should keep -1,0,1 as it uses less memory, avoids multiple copies and is easier to work with.

S = F2*C2
assert S.shape == (7,3,5)
S = np.where(S==0, None, S)
S = np.where(S==-1, 0, S)


And we get as a result, a 'numpy.ndarray', with the following structure:

>>> S
array([[[1, 0, 1, 1, 1],
[1, 0, 1, 1, 1],
[1, 0, 1, 1, 1]],

[[1, 1, 1, 1, 1],
[1, 1, 1, 1, 1],
[1, 1, 1, 1, 1]],

[[1, 1, 1, 0, 1],
[1, 1, 1, 0, 1],
[1, 1, 1, 0, 1]],

[[0, 1, 1, 1, 0],
[0, 1, 1, 1, 0],
[0, 1, 1, 1, 0]],

[[1, None, None, 1, 1],
[1, None, None, 1, 1],
[1, None, None, 1, 1]],

[[1, 1, 1, 0, None],
[1, 1, 1, 0, None],
[1, 1, 1, 0, None]],

[[1, 1, 0, 1, 0],
[1, 1, 0, 1, 0],
[1, 1, 0, 1, 0]]], dtype=object)


Lastly...

2nd. Solution [Numpy]

Shubham Sharma from the stackoverflow team does not suggest a very elegant way to solve it ... he tells us:

Broadcast and multiply the values in df_C with the values from df_F in such a way that the shape of the resulting product matrix will be (3, 7, 5), then test for the condition where the values in the product matrix are positive, negative or zero and assign the corresponding values 1, 0 and NaN where the condition holds True

import numpy as np
import pandas as pd

df_C = pd.DataFrame(data=[[-3,-1,-1], [5,3,3], [3,3,1], [-1,-1,-3], [-3,-1,-1], [2,3,1], [1,1,1]], columns=['C1','C2','C3'])

df_F = pd.DataFrame(data=[[-1,1,-1,-1,-1],[1,1,1,1,1],[1,1,1,-1,1],[1,-1,-1,-1,1],[-1,0,0,-1,-1],[1,1,1,-1,0],[1,1,-1,1,-1]], columns=['F1','F2','F3','F4','F5'])

a = df_C.values.T[:, :, None] * df_F.values
a = np.select([a > 0, a < 0], [1, 0], np.nan)


Which throws us, a 'numpy.ndarray', with the following structure:

>>> a
array([[[ 1.,  0.,  1.,  1.,  1.],
[ 1.,  1.,  1.,  1.,  1.],
[ 1.,  1.,  1.,  0.,  1.],
[ 0.,  1.,  1.,  1.,  0.],
[ 1., nan, nan,  1.,  1.],
[ 1.,  1.,  1.,  0., nan],
[ 1.,  1.,  0.,  1.,  0.]],

[[ 1.,  0.,  1.,  1.,  1.],
[ 1.,  1.,  1.,  1.,  1.],
[ 1.,  1.,  1.,  0.,  1.],
[ 0.,  1.,  1.,  1.,  0.],
[ 1., nan, nan,  1.,  1.],
[ 1.,  1.,  1.,  0., nan],
[ 1.,  1.,  0.,  1.,  0.]],

[[ 1.,  0.,  1.,  1.,  1.],
[ 1.,  1.,  1.,  1.,  1.],
[ 1.,  1.,  1.,  0.,  1.],
[ 0.,  1.,  1.,  1.,  0.],
[ 1., nan, nan,  1.,  1.],
[ 1.,  1.,  1.,  0., nan],
[ 1.,  1.,  0.,  1.,  0.]]])


To all, THANK YOU VERY MUCH for your help! You are wonderful programmers and the solutions you have offered us are brilliant. Total thanks!