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.
.mask(df.combined > 0, True)
# If combined is negative, both values were non-zero with opposite signs.
.mask(df.combined < 0, False)
# 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')
.mask(df.combined > 0, True)
.mask(df.combined < 0, False) )
[['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:
Numpy broadcasting and np.select
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!
