Python: Iterate over dataframes of different lengths, and compute new value with repeat values

0 votes
asked Sep 12, 2017 by sveinn

EDIT: I realized I set up my example incorrectly, the corrected version follows:

I have two dataframes:

df1 = pd.DataFrame({'x values': [11, 12, 13], 'time':[1,2.2,3.5})
df2 = pd.DataFrame({'x values': [11, 21, 12, 43], 'time':[1,2.1,2.6,3.1})

What I need to do is iterate over both of these dataframes, and compute a new value, which is a ratio of the x values in df1 and df2. The difficulty comes in because these dataframes are of different lengths.

If I just wanted to compute values in the two, I know that I could use something like zip, or even map. Unfortunately, I don't want to drop any values. Instead, I need to be able to compare the time column between the two frames to determine whether or not to copy over a value from a previous time to the computation of in the next time period.

So for instance, I would compute the first ratio:

df1["x values"][0]/df2["x values"][0]

Then for the second I check which update happens next, which in this case is to df2, so df1["time"] < df2["time"] and:

df1["x values"][0]/df2["x values"][1]

For the third I would see that df1["time"] > df2["time"], so the third computation would be:

df1["x values"][1]/df2["x values"][1]

The only time both values should be used to compute the ratio from the same "position" is if the times in the two dataframes are equal.

And so on. I'm very confused as to whether or not this is possible to execute using something like a lambda function, or itertools. I've made some attempts, but most have yielded errors. Any help would be appreciated.

1 Answer

0 votes
answered Mar 1, 2018 by vaishali

You can merge the two dataframes on time and then calculate ratios

new_df = df1.merge(df2, on = 'time', how = 'outer')
new_df['ratio'] = new_df['x values_x'] / new_df['x values_y']

You get

    time    x values_x  x values_y  ratio
0   1       11          11          1.000000
1   2       12          21          0.571429
2   2       12          12          1.000000
3   3       13          43          0.302326
Welcome to Q&A, where you can ask questions and receive answers from other members of the community.
Website Online Counter