0
$\begingroup$

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:

Matrix 3D

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.

$\endgroup$
0
$\begingroup$

Pandas does not provide a 3D data structure per se (Panel has been deprecated and removed) - however, it is possible to express this kind of data using the long format (also known as EAV) with three key columns (or index levels), instead of the wide format you are suggesting.

Starting from your definitions, I believe the following achieves your goal:

(
    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']]
)
$\endgroup$
1
  • $\begingroup$ Jan, thank you very much, Your solution is flawless, with a perfect explanation. Very elegant! $\endgroup$ May 22 at 18:42

Not the answer you're looking for? Browse other questions tagged or ask your own question.