3
$\begingroup$

I have 16 different dataframes with the same number of rows/columns and another 2 separate dataframes with that same shape that i'm using to compare with the 16 dataframe values.

I need to loop over all dataframes at the same time, and compare all row values with the separate dataframes, and then create another dataframe with the results like so:

comparison: sum(row_values_of_dataframe) - sum(row_values_of_reference). In the example below, the cell df_a_ref_a is equal to $(1 + 2 + 3 + 4) - (5 + 5 + 5 + 5) = -10$

Dataframe A (df_a)

col1 | col 2 | col 3 | col 4
1       2       3       4
2       4       6       8
[...]

Dataframe B (df_b)

col1 | col 2 | col 3 | col 4
10      5       2       1
4       4       6       2
[...]

Reference Dataframe 1 (ref_1)
col1 | col 2 | col 3 | col 4
5       5       5       5
5       5       5       5
[...]

Reference Dataframe 2 (ref_2)
col1 | col 2 | col 3 | col 4
3       3       3       3
3       3       3       3
[...]

Final dataframe should be:

df_a_ref_1 | df_a_ref_2 | df_b_ref_1 | df_b_ref_2 | ....
    -10          -2           -2           6        ....
     0           8            -4           4
[...]

This behaviour resembles zip() function in python.

Thanks in advance,

$\endgroup$

1 Answer 1

3
$\begingroup$

First: I think you want the product functionality, not zip, since you are checking every df with every ref. In zip, you would check df_a with ref_1 and df_b with ref_2 only.

Second: Your can look at the equation $(1+2+3+4)−(5+5+5+5)$ as $(1-5) + (2-5) + ...$ which is simply subtracting data frames and sum over columns.

With these two consideration, assuming you have defined your objects as follows:

df_a = {
    'name': 'df_a',
    'value': pd.DataFrame([[1, 2, 3, 4], [2, 4, 6, 8]])
}
df_b = {
    'name': 'df_b',
    'value': pd.DataFrame([[10, 5, 2, 1], [4, 4, 6, 2]])
}

ref_1 = {
    'name': 'ref_1',
    'value': pd.DataFrame([[5, 5, 5, 5], [5, 5, 5, 5]])
}
ref_2 = {
    'name': 'ref_b',
    'value': pd.DataFrame([[3, 3, 3, 3], [3, 3, 3, 3]])
}

I did this because I want to use the names in creating the name of the columns of your final df. Then your code would be:

from itertools import product

final_result = pd.DataFrame(
    {
        '{}_{}'.format(df['name'], ref['name']): (df['value']-ref['value']).sum(axis=1)
        for (df, ref) in product([df_a, df_b], [ref_1, ref_2])
    }
)
  • I have used dictionary comprehension to skip the ugly loop/append solution.
  • product function from itertools does your iteration. product on (ab, cd) gives you ac, ad, bc, bd
  • as for keys, df names are joined together with _, and as for values, I have subtracted two dfs and sum over columns (axis=1)

The result would then be as you expect:

   df_a_ref_1  df_a_ref_b  df_b_ref_1  df_b_ref_b
0         -10          -2          -2           6
1           0           8          -4           4

Still if you want to expand the dictionary comprehension or do not want to define dictionaries of names/values, of course you can imagine how you can write simple for loops with the same logic:

for (df, ref) in product([df_a, df_b], [ref_1, ref_2]):
    # your desired columns
    col = (df - ref).sum(axis=1)
$\endgroup$
3
  • $\begingroup$ Sorry, i must have said that these are not the actual values of the dataframes. That said, i don't have a dataframe of 5's to rewrite the equation like you said. However, i will do something like you proposed. $\endgroup$ Mar 27, 2020 at 19:13
  • $\begingroup$ @heresthebuzz rewriting the equation has nothing to do with the real values. it's simple math. you apply negation to the values inside parenthesis and pack each negated value with the respective positive value. It'll work even if you have values other than 5 or anything else $\endgroup$
    – Alireza
    Mar 28, 2020 at 10:17
  • $\begingroup$ And if the solution for a question is acceptable, then you might want to accept the solution as the final solution, so that the topic is concluded. $\endgroup$
    – Alireza
    Mar 28, 2020 at 10:19

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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