I have 2 data frames, each containing 4 columns and hundreds of rows. Although the columns are the same, the rows may appear in any order. I need to reconcile these data frames and ensure that everything in DF1 is also in DF2 and vice versa, and highlight any rows that do not appear in both data frames.
However, the final column, "Net Price", will sometimes not be the exact same figure in each data frame. I need a tolerance on it of $1. that is, Net Cash can differ by $1 and still be accepted. I have attached 2 simplified data frames.
DF_Client | Client | Broker | Stock | Qty | Net Cash | | John | Bank 1 | FMG | 500 | 32,456.4532 | | Charlie | Bank 2 | CBA | 1,200 | 37,783.3740 | | Paul | Bank 3 | TLS | 780 | 210,237.9830 | | Richard | Bank 3 | WOW | 4,921 | 25,119.9952 | | John | Bank 1 | FMG | 1,595 | 545.8500 |
DF_Broker | Client | Broker | Stock | Qty | Net Cash | | Richard | Bank 3 | WOW | 4,921 | 25,119.8603 | | John | Bank 1 | FMG | 1,595 | 546.0000 | | Charlie | Bank 2 | CBA | 1,200 | 37,783.5892 | | Paul | Bank 3 | TLS | 780 | 210,237.7521 | | John | Bank 1 | FMG | 500 | 32,456.6600 |
I have tried to merge, however, because the net cash column is not always exact, it will not match them.
df_compare = pd.merge(df_Client, df_Broker, how="outer", indicator="Missing")
