How to custom merge dataframes? (something between outer and cross merge)

user_9490769 0
Viewed 46

I want to perform a custom merge of the two below dataframes, where I produce all combinations of CUSTOMER_ID, TERM_ID and SHIFT_ID from both dataframe.

Value dataframe

   CUSTOMER_ID TERM_ID  VALUE
0            5     10M  0.041
1            5      2Y  0.082
2            5      3Y  0.046
3            8     11M  0.035
4            8     18M  0.057
5            8      3Y  0.030
6           10      1Y  0.088
7           10      2Y  0.022
8           10      3Y  0.017

Shift dataframe

    CUSTOMER_ID  SHIFT_ID TERM_ID  YEAR_FRAC  SHIFT
0             5      2490      2Y      2.039  0.818
1             5      2490      5Y      5.078  0.673
2             5      2491      2Y      2.036  0.816
3             5      2491      5Y      5.078  0.585
4             5      2492      2Y      2.039  0.865
5             5      2492      5Y      5.083  0.594
6             8      2490      2Y      2.039  0.887
7             8      2490      5Y      5.078  0.615
8             8      2491      2Y      2.036  0.953
9             8      2491      5Y      5.078  0.691
10            8      2492      2Y      2.039  0.982
11            8      2492      5Y      5.083  0.789
12           10      2490      2Y      2.039  1.066
13           10      2490      5Y      5.078  0.857
14           10      2491      2Y      2.036  1.123
15           10      2491      5Y      5.078  0.915
16           10      2492      2Y      2.039  1.190
17           10      2492      5Y      5.083  0.999

Output after merge (where I have all combinations of CUSTOMER_ID, SHIFT_ID and TERM_ID)

    CUSTOMER_ID TERM_ID  VALUE  SHIFT_ID  YEAR_FRAC  SHIFT
0             5     10M  0.041      2490        NaN    NaN
1             5      2Y  0.082      2490      2.039  0.818
2             5      3Y  0.046      2490        NaN    NaN
3             5      5Y    NaN      2490      5.078  0.673
4             5     10M  0.041      2491        NaN    NaN
5             5      2Y  0.082      2491      2.036  0.816
6             5      3Y  0.046      2491        NaN    NaN
7             5      5Y    NaN      2491      5.078  0.585
8             5     10M  0.041      2492        NaN    NaN
9             5      2Y  0.082      2492      2.039  0.865
10            5      3Y  0.046      2492        NaN    NaN
11            5      5Y    NaN      2492      5.083  0.594
12            8     11M  0.035      2490        NaN    NaN
13            8     18M  0.057      2490        NaN    NaN
14            8      2Y    NaN      2490      2.039  0.887
15            8      3Y  0.030      2490        NaN    NaN
16            8      5Y    NaN      2490      5.078  0.615
17            8     11M  0.035      2491        NaN    NaN
18            8     18M  0.057      2491        NaN    NaN
19            8      2Y    NaN      2491      2.036  0.953
20            8      3Y  0.030      2491        NaN    NaN
21            8      5Y    NaN      2491      5.078  0.691
22            8     11M  0.035      2492        NaN    NaN
23            8     18M  0.057      2492        NaN    NaN
24            8      2Y    NaN      2492      2.039  0.982
25            8      3Y  0.030      2492        NaN    NaN
26            8      5Y    NaN      2492      5.083  0.789
27           10      1Y  0.088      2490        NaN    NaN
28           10      2Y  0.022      2490      2.039  1.066
29           10      3Y  0.017      2490        NaN    NaN
30           10      5Y    NaN      2490      5.078  0.857
31           10      1Y  0.088      2491        NaN    NaN
32           10      2Y  0.022      2491      2.036  1.123
33           10      3Y  0.017      2491        NaN    NaN
34           10      5Y    NaN      2491      5.078  0.915
35           10      1Y  0.088      2492        NaN    NaN
36           10      2Y  0.022      2492      2.039  1.190
37           10      3Y  0.017      2492        NaN    NaN
38           10      5Y    NaN      2492      5.083  0.999

EDIT: I have found a working but clunky solution, which looks as follows (where value_df is the first dataframe, and shift_df is the second dataframe)

value_df['key'] = 1
tmp_df = pd.DataFrame({'SHIFT_ID': [2490, 2491, 2492], 'key': 1})
final_df = value_df\
    .merge(tmp_df)\
    .merge(shift_df, how='outer')\
    .sort_values(['CUSTOMER_ID', 'SHIFT_ID', 'TERM_ID'])\
    .reset_index(drop=True)\
    .drop('key', 1)
pandas dataframe python
1 Answers

Here is one way to do it:

import pandas as pd
from itertools import product
value_df = ...
shift_df = ...

# Find all possible combinations and use them as index for an empty dataframe
products = sorted(
    set(
        product(
            set(pd.concat([value_df["CUSTOMER_ID"], shift_df["CUSTOMER_ID"]])),
            set(pd.concat([value_df["TERM_ID"], shift_df["TERM_ID"]])),
            shift_df["SHIFT_ID"],
        )
    )
)
df = pd.DataFrame(
    data=[None for _ in range(len(products))], columns=["temp"], index=products
).rename_axis("index")

# Use three key columns as index for 'shift_df'
shift_df = (
    shift_df.assign(
        index=[
            (a, b, c)
            for (a, b, c) in zip(
                shift_df["CUSTOMER_ID"], shift_df["TERM_ID"], shift_df["SHIFT_ID"]
            )
        ]
    )
    .set_index("index")
    .drop(columns=["CUSTOMER_ID", "TERM_ID", "SHIFT_ID"])
)

# Extend 'value_df' with missing values and use same index as 'shift_df'
value_df = (
    pd.concat([value_df, value_df, value_df])
    .reset_index(drop=True)
    .assign(
        SHIFT_ID=[2490 for _ in range(len(value_df))]
        + [2491 for _ in range(len(value_df))]
        + [2492 for _ in range(len(value_df))]
    )
)
value_df = value_df.assign(
    index=[
        (a, b, c)
        for (a, b, c) in zip(
            value_df["CUSTOMER_ID"], value_df["TERM_ID"], value_df["SHIFT_ID"]
        )
    ]
).set_index("index")

# Merge all three dataframes
df = (
    df.pipe(
        lambda df_: pd.merge(df_, value_df, how="left", on="index").drop(columns="temp")
    )
    .pipe(lambda df_: pd.merge(df_, shift_df, how="outer", on="index"))
    .reset_index(drop=True)
)

And so:

print(df)
# Output

    CUSTOMER_ID TERM_ID  VALUE  SHIFT_ID  YEAR_FRAC  SHIFT
0           5.0     10M  0.041    2490.0        NaN    NaN
1           5.0     10M  0.041    2491.0        NaN    NaN
2           5.0     10M  0.041    2492.0        NaN    NaN
3           NaN     NaN    NaN       NaN        NaN    NaN
4           NaN     NaN    NaN       NaN        NaN    NaN
..          ...     ...    ...       ...        ...    ...
58         10.0      3Y  0.017    2491.0        NaN    NaN
59         10.0      3Y  0.017    2492.0        NaN    NaN
60          NaN     NaN    NaN       NaN      5.078  0.857
61          NaN     NaN    NaN       NaN      5.078  0.915
62          NaN     NaN    NaN       NaN      5.083  0.999

[63 rows x 6 columns]
user_112460560
Related