Find the time difference in two dataframes-CodePudding

df1:

    time_1            a      b        c
0   1.675168e 09    -90.56  5.28    -6.23
1   1.675168e 09    -87.98  5.27    -5.68
2   1.675168e 09    -83.96  14.74   -9.44
3   1.675168e 09    -85.58  -5.72   -5.27
4   1.675168e 09    -95.13  -4.15   -5.46
5   1.675168e 09    -90.56  5.28    -6.23
6   1.675168e 09    -87.98  5.27    -5.68
7   1.675168e 09    -83.96  14.74   -9.44
8   1.675168e 09    -85.58  -5.72   -5.27
9   1.675168e 09    -95.13  -4.15   -5.46

df2:

     time_2              x          y         z
0   1.675168e 09        -6.64   542.397494  2.25
1   1.675168e 09        -6.64   541.233179  2.25
2   1.675169e 09        -6.63   567.644365  2.25
3   1.675169e 09        -6.63   530.368776  2.25
4   1.675170e 09        -6.63   552.896863  2.25

I would like to get the difference of time. ie,time_1 in the df1 minus all the time_2 values in df2.

df:

       time_1 - time_2              a      b     c    y
0     1.675168e 09 - 1.675168e 09
1     1.675168e 09 - 1.675168e 09
2     1.675168e 09 - 1.675169e 09
3     1.675168e 09 - 1.675169e 09
4     1.675168e 09 - 1.675170e 09
5
6
7

and go on

CodePudding user response：

df = pd.DataFrame({'time_1': [1, -1, 1.5]},
                 )

df1 = pd.DataFrame({'time_2': [-2.5, 1.5, 2.6]},
                  )
#combine the two dataframes into one, concat on column axis
df2 = pd.concat([df, df1],axis="columns")
#assign new column with difference between time
df2 = df2.assign(Time_diff = df2['time_1'] - df2['time_2'])

CodePudding user response：

Update according your comment, use merge with how='cross':

out = df1.merge(df2, how='cross').assign(time=lambda x: x.pop('time_1') - x.pop('time_2'))
print(out)

# Output
        a      b     c     x           y     z    time
0  -90.56   5.28 -6.23 -6.64  542.397494  2.25     0.0
1  -90.56   5.28 -6.23 -6.64  541.233179  2.25     0.0
2  -90.56   5.28 -6.23 -6.63  567.644365  2.25 -1000.0
3  -90.56   5.28 -6.23 -6.63  530.368776  2.25 -1000.0
4  -90.56   5.28 -6.23 -6.63  552.896863  2.25 -2000.0
...
45 -95.13  -4.15 -5.46 -6.64  542.397494  2.25     0.0
46 -95.13  -4.15 -5.46 -6.64  541.233179  2.25     0.0
47 -95.13  -4.15 -5.46 -6.63  567.644365  2.25 -1000.0
48 -95.13  -4.15 -5.46 -6.63  530.368776  2.25 -1000.0
49 -95.13  -4.15 -5.46 -6.63  552.896863  2.25 -2000.0

You can join your dataframes (based on indexes):

out = df2.join(df1).assign(time=lambda x: x.pop('time_1') - x.pop('time_2'))
print(out)

# Output
      x           y     z      a      b     c    time
0 -6.64  542.397494  2.25 -90.56   5.28 -6.23     0.0
1 -6.64  541.233179  2.25 -87.98   5.27 -5.68     0.0
2 -6.63  567.644365  2.25 -83.96  14.74 -9.44 -1000.0
3 -6.63  530.368776  2.25 -85.58  -5.72 -5.27 -1000.0
4 -6.63  552.896863  2.25 -95.13  -4.15 -5.46 -2000.0