How can I copy values from one dataframe column to another based on the difference between the value-CodePudding

I have two csv mirror files generated by two different servers. Both files have the same number of lines and should have the exact same unix timestamp column. However, due to some clock issues, some records in one file, might have asmall difference of a nanosecond than it's counterpart record in the other csv file, see below an example, the difference is always of 1:

dataframe_A                                          dataframe_B

|          | ts_ns              |            |          | ts_ns              |
| -------- | ------------------ |            | -------- | ------------------ |
| 1        | 1661773636777407794|            | 1        | 1661773636777407793|
| 2        | 1661773636786474677|            | 2        | 1661773636786474677|
| 3        | 1661773636787956823|            | 3        | 1661773636787956823|
| 4        | 1661773636794333099|            | 4        | 1661773636794333100|

Since these are huge files with milions of lines, I use pandas and dask to process them, but before I process, I need to ensure they have the same timestamp column. I need to check the difference between column ts_ns in A and B and if there is a difference of 1 or -1 I need to replace the value in B with the corresponding ts_ns value in A so I can finally have the same ts_ns value in both files for corresponding records.

How can I do this in a decent way using pandas/dask?

CodePudding user response：

If you're sure that the timestamps should be identical, why don't you simply use the timestamp column from dataframe A and overwrite the timestamp column in dataframe B with it?

Why even check whether the difference is there or not?

CodePudding user response：

You can use the pandas merge_asof function for this, see https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.merge_asof.html . The tolerance allows for a int or timedelta which should be set to the 1 for your example with direction being nearest.

CodePudding user response：

Assuming your files are identical except from your ts_ns column you can perform a .merge on indices.

df_a = pd.DataFrame({'ts_ns': [1661773636777407794, 1661773636786474677, 1661773636787956823, 1661773636794333099]})
df_b = pd.DataFrame({'ts_ns': [1661773636777407793, 1661773636786474677, 1661773636787956823, 1661773636794333100]})

df_b = (df_b
    .merge(df_a, how='left', left_index=True, right_index=True, suffixes=('', '_a'))
    .assign(
        ts_ns = lambda df_: np.where(abs(df_.ts_ns - df_.ts_ns_a) <= 1, df_.ts_ns_a, df_.ts_ns)
    )
    .loc[:, ['ts_ns']]
)

But I agree with @ManEngel, just overwrite all the values if you know they are identical.