Iterate over multiple dataframe rows at the same time

Question

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,

Answer 1

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)