I am writing a Pandas code generator based on an in-house DSL, and am running into a challenge around merges and suffixes. In SQL, accessing the columns from the result of a JOIN is very easy because columns from the JOIN's intermediate result are simply referenced via their alias. For example:
-- dataset_join_node(out, [a, b])
SELECT r1_1.amount AS "sales_vt", r1_2.amount AS "sales_ny", r1_1.amount + r1_2.amount AS "sales_total"
FROM
(
-- leaf_node(a, fact_sales)
SELECT ... AS amount
FROM ...
) AS r1_1
CROSS JOIN
(
-- leaf_node(b, fact_sales)
SELECT ... AS amount
FROM ...
) AS r1_2
Unfortunately, this does not appear to be so easy in Pandas due to column conflicts. It seems I need to use the suffixes keyword and keep track of the resulting column names after the merges are performed and take into account any potential column name conflicts. For example, my DSL query (not shown) that emitted the SQL displayed above would generate analogous Pandas code like this:
...
out_dataset_join_node = (
a_leaf_node_fact_sales
.merge(b_leaf_node_fact_sales, how="outer", left_on=["__x"], right_on=["__x"], suffixes=("__1", "__2"))
)
out_dataset_join_node["sales_vt"] = out_dataset_join_node["amount__1"]
out_dataset_join_node["sales_ny"] = out_dataset_join_node["amount__2"]
out_dataset_join_node["sales_total"] = out_dataset_join_node["amount__1"] + out_dataset_join_node["amount__2"]
out_dataset_join_node = out_dataset_join_node[["sales_vt", "sales_ny", "sales_total"]]
...
Pandas does not seem to provide an "intermediate" data frame where I could access the columns with an alias, as was done in the SQL above. In this example, the amount column was in both data frames being merged, so the suffixes came into play, and I needed to know that sales_vt was from the resulting amount__1 column, and sales_ny was from the resulting amount__2 column.
If we add a third data frame to the mix, the SQL approach is still straightforward due to the alias prefix of the similarly named amount columns:
-- dataset_join_node(out, [a, b, c])
SELECT r1_1.amount AS "sales_vt", r1_2.amount AS "sales_ny", r1_3.amount AS "sales_mi",
r1_1.amount + r1_2.amount + r1_3.amount AS "sales_total"
FROM
(
-- leaf_node(a, fact_sales)
SELECT ... AS amount
FROM ...
) AS r1_1
CROSS JOIN
(
-- leaf_node(b, fact_sales)
SELECT ... AS amount
FROM ...
) AS r1_2
CROSS JOIN
(
-- leaf_node(c, fact_sales)
SELECT ... AS amount
FROM ...
) AS r1_3
But the Pandas gets a bit trickier since the last amount column does not conflict with any previous columns, so it ends up in the resulting data frame as just amount (unlike the two previous amount columns, which conflict and get renamed to amount__1 and amount__2).
out_dataset_join_node = (
a_leaf_node_fact_sales
.merge(b_leaf_node_fact_sales, how="outer", left_on=["__x"], right_on=["__x"], suffixes=("__1", "__2"))
.merge(c_leaf_node_fact_sales, how="outer", left_on=["__x"], right_on=["__x"], suffixes=("__3", "__4"))
)
out_dataset_join_node["sales_vt"] = out_dataset_join_node["amount__1"]
out_dataset_join_node["sales_ny"] = out_dataset_join_node["amount__2"]
out_dataset_join_node["sales_mi"] = out_dataset_join_node["amount"]
out_dataset_join_node["sales_total"] = out_dataset_join_node["amount__1"] + out_dataset_join_node["amount__2"] + out_dataset_join_node["amount"]
out_dataset_join_node = out_dataset_join_node[["sales_vt", "sales_ny", "sales_mi", "sales_total"]]
I guess the emitted suffixes=("__3", "__4") in the second .merge() is unnecessary, but my code generator is not that smart yet. :-)
My question is, does Pandas have another / better way to accomplish the analogous SQL JOIN, possibly (hopefully) with a way to reference columns via an alias similar to how SQL lets you access columns via an alias and then rename them to whatever you want?
Or is the Pandas .merge() and suffixes keyword the only tools at my disposal, and my code generator therefore needs to keep track of possible column conflicts and emit code as described above?
Thanks for any insight.