Holi uwu
- User
Holi uwu
- Assistant
¡Hola uwu! ¿Cómo estás? 🌸✨
- User
Bien, por favor ayudame a resoolver el siguiente probelma.
Fix the following problem code in delta and pyspark:
AnalysisException: [AMBIGUOUS_REFERENCE] Reference wallet_id is ambiguous, could be: [current.wallet_id, previous.wallet_id].
script: window_spec = Window.partitionBy("wallet_id").orderBy(F.col("period_wallet_day").desc())
df_joined = aggregated_df.alias("current")
.join( raw_wallet_dto_by_period_flypass.alias("previous"), (F.col("current.wallet_id") == F.col("previous.wallet_id")) & (F.col("current.period_wallet_day") > F.col("previous.period_wallet_day")), "left" )
.withColumn("previous_closing_balance", F.last("previous.closing_balance", True).over(window_spec))
.select( "current.wallet_id", "current.period_wallet_day", "current.quantity_payments", "current.total_value_payment", "current.quantity_consumptions", "current.total_value_consumption", "current.quantity_commissions", "current.total_value_commission", "current.closing_balance", "current.processed_date" )df_joined.show()
- Assistant
The error
AnalysisException: [AMBIGUOUS_REFERENCE] Reference wallet_id is ambiguousoccurs becausewallet_idexists in both thecurrentandpreviousDataFrames after the join, and Spark is confused about which one to refer to. When working in PySpark (and Delta), you need to disambiguate column names carefully by aliasing or fully qualifying column references during transformations to avoid conflicts.Here’s the corrected code for your problem:
Solution
The main fix involves explicitly qualifying the column names with their respective aliases (
currentfor the first DataFrame andpreviousfor the second DataFrame). This clears up the ambiguity.from pyspark.sql import functions as F from pyspark.sql.window import Window # Define the window specification (based on wallet_id from the `current` DataFrame) window_spec = Window.partitionBy("wallet_id").orderBy(F.col("period_wallet_day").desc()) df_joined = ( aggregated_df.alias("current") # Alias for the first DataFrame .join( raw_wallet_dto_by_period_flypass.alias("previous"), # Alias for the second DataFrame ( (F.col("current.wallet_id") == F.col("previous.wallet_id")) & (F.col("current.period_wallet_day") > F.col("previous.period_wallet_day")) ), "left" ) # Use `previous.` prefix to ensure column references are explicit .withColumn( "previous_closing_balance", F.last(F.col("previous.closing_balance"), True).over(window_spec) # Apply the window specification ) # Select relevant columns, qualifying each column with its alias .select( F.col("current.wallet_id").alias("wallet_id"), F.col("current.period_wallet_day").alias("period_wallet_day"), F.col("current.quantity_payments").alias("quantity_payments"), F.col("current.total_value_payment").alias("total_value_payment"), F.col("current.quantity_consumptions").alias("quantity_consumptions"), F.col("current.total_value_consumption").alias("total_value_consumption"), F.col("current.quantity_commissions").alias("quantity_commissions"), F.col("current.total_value_commission").alias("total_value_commission"), F.col("current.closing_balance").alias("closing_balance"), F.col("current.processed_date").alias("processed_date"), F.col("previous_closing_balance").alias("previous_closing_balance") ) ) df_joined.show()Explanation of the Fix
-
Aliasing the DataFrames:
- When performing a join, alias both DataFrames as
currentandprevious. - This ensures you can clearly determine the source of the columns (
current.wallet_idvs.previous.wallet_id).
- When performing a join, alias both DataFrames as
-
Disambiguating Columns:
- Use the
F.col()method with fully-qualified names (e.g.,current.wallet_idandprevious.wallet_id) to access the columns explicitly. - Spark will no longer be confused by ambiguous column names.
- Use the
-
Window Specification:
- The
Window.partitionBy("wallet_id").orderBy(F.col("period_wallet_day").desc())is defined globally with no ambiguity sincepartitionBy("wallet_id")refers to thecurrentDataFrame.
- The
-
Column Selection:
- When selecting columns at the end (with
.select()), fully qualify any column reference as necessary (using the aliases), ensuring there’s no ambiguity.
- When selecting columns at the end (with
Result
Now,
df_joined.show()will show the correct output without any ambiguity in thewallet_idreference. -
- User
Sigue presentandose el mismo error.
Voy a darte una posible solución haciendo referencia a un join que anteriormente había hecho, fijese que le di un alias al period_wallet_day como last_period_wallet_day , no sé si puedas adaptar el codigo que presenta el problema para solucionar nuestro problema:
Ejemplo: window_spec = Window.partitionBy("wallet_id").orderBy(f.col("period_wallet_day").desc())
last_wallet_by_period = ( aggregated_df.alias("source") .join( trusted_delta_table.toDF().alias("target"), (f.col("target.wallet_id") == f.col("source.wallet_id")), "left" ) .selectExpr("target.*", "source.period_wallet_day as last_period_wallet_day") .withColumn("rank", f.row_number().over(window_spec)) .where("rank = 1") .drop("rank") ) aggregated_df_with_closing_balance = ( aggregated_df.alias("source") .join( last_wallet_by_period.alias("target"), f.expr(""" source.wallet_id = target.wallet_id AND source.period_wallet_day = target.last_period_wallet_day """), "left" ) .withColumn( "closing_balance", f.coalesce(f.col("target.closing_balance"), f.lit(0)) + f.col("source.total_value_payment") - f.col("source.total_value_commission") - f.col("source.total_value_consumption") ) .select("source.*", "closing_balance"))Script con el problema a resolver (adaptalo)
from pyspark.sql import functions as F from pyspark.sql.window import Window
window_spec = Window.partitionBy("wallet_id").orderBy(F.col("period_wallet_day").desc())
df_joined = ( aggregated_df.alias("current")
.join( raw_wallet_dto_by_period_flypass.alias("previous"),
( (F.col("current.wallet_id") == F.col("previous.wallet_id")) & (F.col("current.period_wallet_day") > F.col("previous.period_wallet_day")) ), "left" ) .withColumn( "previous_closing_balance", F.last(F.col("previous.closing_balance"), True).over(window_spec)
) .select( F.col("current.wallet_id").alias("wallet_id"), F.col("current.period_wallet_day").alias("period_wallet_day"), F.col("current.quantity_payments").alias("quantity_payments"), F.col("current.total_value_payment").alias("total_value_payment"), F.col("current.quantity_consumptions").alias("quantity_consumptions"), F.col("current.total_value_consumption").alias("total_value_consumption"), F.col("current.quantity_commissions").alias("quantity_commissions"), F.col("current.total_value_commission").alias("total_value_commission"), F.col("current.closing_balance").alias("closing_balance"), F.col("current.processed_date").alias("processed_date") ) ) - Assistant
Gracias por aportar el ejemplo previo, que permite entender un contexto más claro para resolver el problema. Adaptaré y corregiré el script con el problema a resolver usando la lógica suministrada en el ejemplo con un enfoque basado en aliasing adecuado y evitando ambigüedad con columnas. En esta solución, se agregará el uso explícito de alias y renombramiento de columnas (
last_period_wallet_day) para garantizar la desambiguación completa.Solución Adaptada
En lugar de confiar directamente en la columna que causa ambigüedad (
wallet_idoperiod_wallet_day), usaré la lógica del ejemplo para seleccionar explícitamente las columnas relevantes después deljoiny darles nombres claros. Esto incluye crear un alias para las columnas deperiod_wallet_dayrelacionadas con el rango y usarlas con claridad.Aquí está el código adaptado:
from pyspark.sql import functions as F from pyspark.sql.window import Window # Crear la especificación de ventana basada en `wallet_id` y `period_wallet_day` window_spec = Window.partitionBy("wallet_id").orderBy(F.col("period_wallet_day").desc()) # Primero, obtenemos el último `period_wallet_day` del periodo anterior para cada wallet_id previous_wallet_by_period = ( raw_wallet_dto_by_period_flypass.alias("previous") .join( aggregated_df.alias("current"), (F.col("current.wallet_id") == F.col("previous.wallet_id")) & (F.col("current.period_wallet_day") > F.col("previous.period_wallet_day")), "inner" ) .selectExpr( "previous.*", # Seleccionamos todas las columnas del dataframe "anterior" "current.period_wallet_day as last_period_wallet_day" # Renombramos la columna periodo de "current" ) .withColumn("rank", F.row_number().over(window_spec)) # Agregamos un rank para escoger un único registro por partición .filter(F.col("rank") == 1) # Seleccionamos solo la fila con el último periodo .drop("rank") # Eliminamos la columna temporal `rank` ) # Ahora agregamos el `closing_balance` previo a los registros actuales aggregated_df_with_closing_balance = ( aggregated_df.alias("current") .join( previous_wallet_by_period.alias("previous"), F.expr(""" current.wallet_id = previous.wallet_id AND current.period_wallet_day = previous.last_period_wallet_day """), "left" ) # Calculamos el balance previo usando columnas explícitas .withColumn( "previous_closing_balance", F.coalesce(F.col("previous.closing_balance"), F.lit(0)) # Si no existe, usamos 0 como predeterminado ) # Seleccionamos las columnas necesarias .select( F.col("current.wallet_id").alias("wallet_id"), F.col("current.period_wallet_day").alias("period_wallet_day"), F.col("current.quantity_payments").alias("quantity_payments"), F.col("current.total_value_payment").alias("total_value_payment"), F.col("current.quantity_consumptions").alias("quantity_consumptions"), F.col("current.total_value_consumption").alias("total_value_consumption"), F.col("current.quantity_commissions").alias("quantity_commissions"), F.col("current.total_value_commission").alias("total_value_commission"), F.col("current.closing_balance").alias("closing_balance"), F.col("current.processed_date").alias("processed_date"), F.col("previous_closing_balance").alias("previous_closing_balance") # Nuevo campo necesario ) ) # Visualizamos el resultado aggregated_df_with_closing_balance.show()
Cambios en el Código
-
Calculo de la Columna
last_period_wallet_day:- En el primer
join, se selecciona y renombra explícitamenteperiod_wallet_daycomolast_period_wallet_daypara el DataFrameprevious_wallet_by_period. Esto desambigua los nombres y permite crear una referencia clara.
- En el primer
-
Uso de
rankpara Selección del Últimoperiod_wallet_day:- Similar al ejemplo dado por ti, se usa la función
row_number()para ordenar cadawallet_idporperiod_wallet_dayde forma descendente. La fila conrank = 1selecciona el últimoperiod_wallet_daypor wallet.
- Similar al ejemplo dado por ti, se usa la función
-
Evitamos la Ambigüedad en el Join:
- En el segundo
join, usamos una condición explícita mencionando ambos campos (wallet_idyperiod_wallet_day) y su correspondencia entrecurrentyprevious. Esto asegura claridad en los datos conectados, evitando superposición o duplicados.
- En el segundo
-
Cálculo de la Columna
previous_closing_balance:- La columna
previous_closing_balance(el balance de cierre del periodo anterior) se calcula usando una combinación decoalesceywithColumnpara tomar el valor declosing_balancedel periodo anterior o usar0en caso de valores nulos.
- La columna
-
Selección de Columnas Claras en el Resultado:
- En el paso final, seleccionamos explícitamente las columnas deseadas, renombrándolas cuando sea necesario para el resultado final (
wallet_id,period_wallet_day, y demás métricas pertenecientes alcurrentDataFrame).
- En el paso final, seleccionamos explícitamente las columnas deseadas, renombrándolas cuando sea necesario para el resultado final (
Beneficios de este Enfoque
- Desambiguación Garantizada: Al usar alias y nuevas columnas renombradas como
last_period_wallet_day, nunca hay ambigüedad entre las columnas del DataFrame actual y el previo. - Flexibilidad: El uso de particiones y ordenamiento con
row_numberpermite seleccionar dinámicamente el último periodo sin asumir demasiados detalles sobre los datos. - Escalabilidad: Este enfoque se escala fácilmente a grandes volúmenes de datos debido a las consultas completamente declarativas y optimizadas en PySpark.
¡Intenta ejecutar este código y me confirmas si resuelve el problema! 😊
-
- User
AnalysisException: [UNRESOLVED_COLUMN.WITH_SUGGESTION] A column or function parameter with name
current.closing_balancecannot be resolved. Did you mean one of the following? [current.closing_day,previous_closing_balance,previous.closing_balance,current.period_id,previous.closing_day].; 'Project [wallet_id#675 AS wallet_id#1863, period_wallet_day#1321 AS period_wallet_day#1864, quantity_payments#1353L AS quantity_payments#1865L, total_value_payment#1355 AS total_value_payment#1866, quantity_consumptions#1357L AS quantity_consumptions#1867L, total_value_consumption#1359 AS total_value_consumption#1868, quantity_commissions#1361L AS quantity_commissions#1869L, total_value_commission#1363 AS total_value_commission#1870, 'current.closing_balance AS closing_balance#1871, processed_date#1364 AS processed_date#1872, previous_closing_balance#1840 AS previous_closing_balance#1873] +- Project [wallet_id#675, period_wallet_day#1321, period_id#1349, closing_day#1351, quantity_payments#1353L, total_value_payment#1355, quantity_consumptions#1357L, total_value_consumption#1359, quantity_commissions#1361L, total_value_commission#1363, processed_date#1364, wallet_id#51, period_id#52, period_wallet_day#53, closing_balance#54, total_value_payment#55, total_value_consumption#56, total_value_commission#57, processed_date#58, closing_day#59, last_period_wallet_day#1742, coalesce(closing_balance#54, cast(0 as double)) AS previous_closing_balance#1840] +- Join LeftOuter, ((wallet_id#675 = wallet_id#51) AND (period_wallet_day#1321 = last_period_wallet_day#1742)) :- SubqueryAlias current : +- Aggregate [wallet_id#675, period_wallet_day#1321], [wallet_id#675, period_wallet_day#1321, first(period_id#694, false) AS period_id#1349, first(closing_day#1298, false) AS closing_day#1351, coalesce(sum(quantity_payments#1322), cast(0 as bigint)) AS quantity_payments#1353L, coalesce(sum(total_value_payment#1323), cast(0 as double)) AS total_value_payment#1355, coalesce(sum(quantity_consumptions#1324), cast(0 as bigint)) AS quantity_consumptions#1357L, coalesce(sum(total_value_consumption#1325), cast(0 as double)) AS total_value_consumption#1359, coalesce(sum(quantity_commissions#1326), cast(0 as bigint)) AS quantity_commissions#1361L, coalesce(sum(total_value_commission#1327), cast(0 as double)) AS total_value_commission#1363, current_timestamp() AS processed_date#1364] : +- Project [period_id#694, wallet_id#675, closing_day#1298, to_date(concat(period_id#694, lpad(cast(closing_day#1298 as string), 2, cast(0 as string))), Some(yyyyMMdd), Some(GMT), false) AS period_wallet_day#1321, CASE WHEN ((transaction_type#687 = PAYMENT) OR ((transaction_type#687 = ADJUSTMENT) AND (origin#693 = PAYMENT))) THEN 1 ELSE 0 END AS quantity_payments#1322, CASE WHEN ((transaction_type#687 = PAYMENT) OR ((transaction_type#687 = ADJUSTMENT) AND (origin#693 = PAYMENT))) THEN amount#690 END AS total_value_payment#1323, CASE WHEN ((transaction_type#687 = CONSUMPTION) OR ((transaction_type#687 = ADJUSTMENT) AND (origin#693 = CONSUMPTION))) THEN 1 ELSE 0 END AS quantity_consumptions#1324, CASE WHEN ((transaction_type#687 = CONSUMPTION) OR ((transaction_type#687 = ADJUSTMENT) AND (origin#693 = CONSUMPTION))) THEN amount#690 ELSE cast(0 as double) END AS total_value_consumption#1325, CASE WHEN ((transaction_type#687 = COMMISSION) OR ((transaction_type#687 = ADJUSTMENT) AND (origin#693 = COMMISSION))) THEN 1 ELSE 0 END AS quantity_commissions#1326, CASE WHEN ((transaction_type#687 = COMMISSION) OR ((transaction_type#687 = ADJUSTMENT) AND (origin#693 = COMMISSION))) THEN amount#690 ELSE cast(0 as double) END AS total_value_commission#1327] : +- Project [uuid#674, wallet_id#675, transaction_id#676, attention_point_id#677L, adjustment_id#678L, created_date#679, application_date#680, reference#681, movement_reference#682, movement_description#683, movement_description_direction#684, movement_description_tag#685, movement_description_attention_point#686, transaction_type#687, fly_key#688, fly_key_type#689, amount#690, commission#691, consumption_type#692, origin#693, period_id#694, dayofmonth(cast(application_date#680 as date)) AS closing_day#1298] : +- Deduplicate [reference#681, adjustment_id#678L, period_id#694, commission#691, created_date#679, movement_description_tag#685, uuid#674, fly_key#688, consumption_type#692, application_date#680, amount#690, transaction_id#676, movement_description#683, movement_description_attention_point#686, origin#693, movement_reference#682, fly_key_type#689, attention_point_id#677L, movement_description_direction#684, transaction_type#687, wallet_id#675] : +- Relation [uuid#674,wallet_id#675,transaction_id#676,attention_point_id#677L,adjustment_id#678L,created_date#679,application_date#680,reference#681,movement_reference#682,movement_description#683,movement_description_direction#684,movement_description_tag#685,movement_description_attention_point#686,transaction_type#687,fly_key#688,fly_key_type#689,amount#690,commission#691,consumption_type#692,origin#693,period_id#694] parquet +- SubqueryAlias previous +- Project [wallet_id#51, period_id#52, period_wallet_day#53, closing_balance#54, total_value_payment#55, total_value_consumption#56, total_value_commission#57, processed_date#58, closing_day#59, last_period_wallet_day#1742] +- Filter (rank#1754 = 1) +- Project [wallet_id#51, period_id#52, period_wallet_day#53, closing_balance#54, total_value_payment#55, total_value_consumption#56, total_value_commission#57, processed_date#58, closing_day#59, last_period_wallet_day#1742, rank#1754] +- Project [wallet_id#51, period_id#52, period_wallet_day#53, closing_balance#54, total_value_payment#55, total_value_consumption#56, total_value_commission#57, processed_date#58, closing_day#59, last_period_wallet_day#1742, rank#1754, rank#1754] +- Window [row_number() windowspecdefinition(wallet_id#51, period_wallet_day#53 DESC NULLS LAST, specifiedwindowframe(RowFrame, unboundedpreceding())) AS rank#1754], [wallet_id#51], [period_wallet_day#53 DESC NULLS LAST] +- Project [wallet_id#51, period_id#52, period_wallet_day#53, closing_balance#54, total_value_payment#55, total_value_consumption#56, total_value_commission#57, processed_date#58, closing_day#59, last_period_wallet_day#1742] +- Project [wallet_id#51, period_id#52, period_wallet_day#53, closing_balance#54, total_value_payment#55, total_value_consumption#56, total_value_commission#57, processed_date#58, closing_day#59, period_wallet_day#1321 AS last_period_wallet_day#1742] +- Join Inner, ((wallet_id#1777 = wallet_id#51) AND (period_wallet_day#1321 > period_wallet_day#53)) :- SubqueryAlias previous : +- Relation [wallet_id#51,period_id#52,period_wallet_day#53,closing_balance#54,total_value_payment#55,total_value_consumption#56,total_value_commission#57,processed_date#58,closing_day#59] parquet +- SubqueryAlias current +- Aggregate [wallet_id#1777, period_wallet_day#1321], [wallet_id#1777, period_wallet_day#1321, first(period_id#1796, false) AS period_id#1349, first(closing_day#1298, false) AS closing_day#1351, coalesce(sum(quantity_payments#1322), cast(0 as bigint)) AS quantity_payments#1353L, coalesce(sum(total_value_payment#1323), cast(0 as double)) AS total_value_payment#1355, coalesce(sum(quantity_consumptions#1324), cast(0 as bigint)) AS quantity_consumptions#1357L, coalesce(sum(total_value_consumption#1325), cast(0 as double)) AS total_value_consumption#1359, coalesce(sum(quantity_commissions#1326), cast(0 as bigint)) AS quantity_commissions#1361L, coalesce(sum(total_value_commission#1327), cast(0 as double)) AS total_value_commission#1363, current_timestamp() AS processed_date#1364] +- Project [period_id#1796, wallet_id#1777, closing_day#1298, to_date(concat(period_id#1796, lpad(cast(closing_day#1298 as string), 2, cast(0 as string))), Some(yyyyMMdd), Some(GMT), false) AS period_wallet_day#1321, CASE WHEN ((transaction_type#1789 = PAYMENT) OR ((transaction_type#1789 = ADJUSTMENT) AND (origin#1795 = PAYMENT))) THEN 1 ELSE 0 END AS quantity_payments#1322, CASE WHEN ((transaction_type#1789 = PAYMENT) OR ((transaction_type#1789 = ADJUSTMENT) AND (origin#1795 = PAYMENT))) THEN amount#1792 END AS total_value_payment#1323, CASE WHEN ((transaction_type#1789 = CONSUMPTION) OR ((transaction_type#1789 = ADJUSTMENT) AND (origin#1795 = CONSUMPTION))) THEN 1 ELSE 0 END AS quantity_consumptions#1324, CASE WHEN ((transaction_type#1789 = CONSUMPTION) OR ((transaction_type#1789 = ADJUSTMENT) AND (origin#1795 = CONSUMPTION))) THEN amount#1792 ELSE cast(0 as double) END AS total_value_consumption#1325, CASE WHEN ((transaction_type#1789 = COMMISSION) OR ((transaction_type#1789 = ADJUSTMENT) AND (origin#1795 = COMMISSION))) THEN 1 ELSE 0 END AS quantity_commissions#1326, CASE WHEN ((transaction_type#1789 = COMMISSION) OR ((transaction_type#1789 = ADJUSTMENT) AND (origin#1795 = COMMISSION))) THEN amount#1792 ELSE cast(0 as double) END AS total_value_commission#1327] +- Project [uuid#1776, wallet_id#1777, transaction_id#1778, attention_point_id#1779L, adjustment_id#1780L, created_date#1781, application_date#1782, reference#1783, movement_reference#1784, movement_description#1785, movement_description_direction#1786, movement_description_tag#1787, movement_description_attention_point#1788, transaction_type#1789, fly_key#1790, fly_key_type#1791, amount#1792, commission#1793, consumption_type#1794, origin#1795, period_id#1796, dayofmonth(cast(application_date#1782 as date)) AS closing_day#1298] +- Deduplicate [reference#1783, adjustment_id#1780L, period_id#1796, commission#1793, created_date#1781, movement_description_tag#1787, uuid#1776, fly_key#1790, consumption_type#1794, application_date#1782, amount#1792, transaction_id#1778, movement_description#1785, movement_description_attention_point#1788, origin#1795, movement_reference#1784, fly_key_type#1791, attention_point_id#1779L, movement_description_direction#1786, transaction_type#1789, wallet_id#1777] +- Relation [uuid#1776,wallet_id#1777,transaction_id#1778,attention_point_id#1779L,adjustment_id#1780L,created_date#1781,application_date#1782,reference#1783,movement_reference#1784,movement_description#1785,movement_description_direction#1786,movement_description_tag#1787,movement_description_attention_point#1788,transaction_type#1789,fly_key#1790,fly_key_type#1791,amount#1792,commission#1793,consumption_type#1794,origin#1795,period_id#1796] parquet - Assistant
El error
AnalysisException: [UNRESOLVED_COLUMN.WITH_SUGGESTION]indica que la columnacurrent.closing_balanceno existe en el DataFramecurrent. Según el rastreo, parece que el nombre de la columnaclosing_balanceno está presente en la selección previa del DataFrameaggregated_df(aliascurrent), por lo que Spark no puede resolver este nombre de columna.Esto puede ser causado porque:
- La columna
closing_balanceno forma parte del DataFrameaggregated_df(aliascurrent). - Existe una confusión de nombres de columnas debido al aliasing o transformación previa.
Solución
La solución implica asegurarnos de que:
- La columna
closing_balanceesté disponible y seleccionada explícitamente desde el DataFrameaggregated_dfo de su fuente original. - Renombrar columnas en los lugares adecuados para garantizar que no se pierda referencia durante el proceso.
Voy a ajustar el código para abordar este problema:
Código Adaptado
En este ajuste, verificaré las columnas disponibles explícitamente y me aseguraré de que se seleccionen las columnas correctas desde el origen.
from pyspark.sql import functions as F from pyspark.sql.window import Window # Crear la especificación de ventana para el particionamiento window_spec = Window.partitionBy("wallet_id").orderBy(F.col("period_wallet_day").desc()) # Paso 1: Obtener el `last_period_wallet_day` asegurándonos de seleccionar solo las columnas requeridas previous_wallet_by_period = ( raw_wallet_dto_by_period_flypass.alias("previous") .join( aggregated_df.alias("current"), (F.col("current.wallet_id") == F.col("previous.wallet_id")) & (F.col("current.period_wallet_day") > F.col("previous.period_wallet_day")), "inner" ) .selectExpr( "previous.wallet_id as wallet_id", "previous.period_id as period_id", "previous.period_wallet_day as period_wallet_day", "previous.closing_balance as closing_balance", "previous.total_value_payment", "previous.total_value_consumption", "previous.total_value_commission", "previous.processed_date", "previous.closing_day", "current.period_wallet_day as last_period_wallet_day" # Renombrar la columna relacionada con "last_period_wallet_day" ) # Crear una columna "rank" por cada partición en "window_spec" .withColumn("rank", F.row_number().over(window_spec)) # seleccionar el último valor del periodo (rank = 1) .filter(F.col("rank") == 1) .drop("rank") ) # Paso 2: Realizar el join entre `aggregated_df` y `previous_wallet_by_period` aggregated_df_with_closing_balance = ( aggregated_df.alias("current") .join( previous_wallet_by_period.alias("previous"), (F.col("current.wallet_id") == F.col("previous.wallet_id")) & (F.col("current.period_wallet_day") == F.col("previous.last_period_wallet_day")), "left" ) # Añadir la columna `previous_closing_balance` (coalesce maneja valores nulos) .withColumn( "previous_closing_balance", F.coalesce(F.col("previous.closing_balance"), F.lit(0)) # Si no se encuentra, usar 0 como predeterminado ) # Seleccionar las columnas deseadas para el resultado final .select( F.col("current.wallet_id").alias("wallet_id"), F.col("current.period_wallet_day").alias("period_wallet_day"), F.col("current.quantity_payments").alias("quantity_payments"), F.col("current.total_value_payment").alias("total_value_payment"), F.col("current.quantity_consumptions").alias("quantity_consumptions"), F.col("current.total_value_consumption").alias("total_value_consumption"), F.col("current.quantity_commissions").alias("quantity_commissions"), F.col("current.total_value_commission").alias("total_value_commission"), F.col("current.processed_date").alias("processed_date"), F.col("previous_closing_balance").alias("previous_closing_balance") # Include la nueva columna calculada ) ) # Paso 3: Mostrar el resultado final aggregated_df_with_closing_balance.show()
Cambios Realizados
-
Selección Explícita de
closing_balancedesde el Origen (raw_wallet_dto_by_period_flypass):- En el paso de
previous_wallet_by_period, la columnaclosing_balancese selecciona explícitamente desde el DataFrameraw_wallet_dto_by_period_flypass(aliasprevious) para garantizar su disponibilidad. - Esto asegura que la columna no se pierda en operaciones intermedias.
- En el paso de
-
Renombramiento de
last_period_wallet_day:- Igual que en el ejemplo dado,
period_wallet_daydesde el DataFrameaggregated_df(aliascurrent) se renombra comolast_period_wallet_day. Esto desambigua la referencia a esta columna en pasos posteriores.
- Igual que en el ejemplo dado,
-
Conexión con
current.closing_balancedesambiguada:- El error original aparece debido a que la columna
closing_balanceno estaba presente en el DataFramecurrent. Ahora ya no hay dependencia de esta columna encurrent.
- El error original aparece debido a que la columna
-
Inclusión de
previous_closing_balance:- Se calcula esta columna mediante
coalesce, adoptando elclosing_balanceprevio si está disponible o un valor predeterminado de 0.
- Se calcula esta columna mediante
-
Selección de las Columnas Finales:
- En el paso final, se seleccionan las columnas explícitas necesarias. Eliminé cualquier referencia errónea a columnas no existentes o ambiguas.
Resultados Esperados
Con las correcciones anteriores:
- La columna
closing_balanceahora se selecciona solo desde su origen (raw_wallet_dto_by_period_flypass) y no desdeaggregated_df, resolviendo la causa raíz del error. - Se asegura que los nombres de columnas sean únicos y claros a lo largo del script.
- Se obtiene un DataFrame con las columnas requeridas y el valor
previous_closing_balancecorrectamente calculado.
Intenta este ajuste y me confirmas si el problema se resuelve o si necesitas más ayuda. 😊
- La columna
- User
Mejora para que muestre las columnas que me interesan:
aggregated_df_with_closing_balance.show()
necesito, wallet_id, period_wallet_day, total_value_payment, total_value_consumption, total_value_commission y previous_closing_balance
- Assistant
Para mostrar solo las columnas que te interesan, basta con ajustar la selección final del
DataFramepara incluir únicamente las columnas requeridas (wallet_id,period_wallet_day,total_value_payment,total_value_consumption,total_value_commissionyprevious_closing_balance) antes de usar.show().Aquí está el código actualizado para lograr esto:
Código Modificado
aggregated_df_with_closing_balance = ( aggregated_df.alias("current") .join( previous_wallet_by_period.alias("previous"), (F.col("current.wallet_id") == F.col("previous.wallet_id")) & (F.col("current.period_wallet_day") == F.col("previous.last_period_wallet_day")), "left" ) # Añadir la columna `previous_closing_balance` (coalesce para manejar valores nulos) .withColumn( "previous_closing_balance", F.coalesce(F.col("previous.closing_balance"), F.lit(0)) # Default value = 0 ) # Seleccionar las columnas que te interesan .select( F.col("current.wallet_id").alias("wallet_id"), F.col("current.period_wallet_day").alias("period_wallet_day"), F.col("current.total_value_payment").alias("total_value_payment"), F.col("current.total_value_consumption").alias("total_value_consumption"), F.col("current.total_value_commission").alias("total_value_commission"), F.col("previous_closing_balance").alias("previous_closing_balance") ) ) # Mostrar el resultado final con las columnas requeridas aggregated_df_with_closing_balance.show()
Cambios Clave
-
Selección de Columnas Específicas:
- En el método
.select(), ajustamos el script para elegir únicamente las columnas solicitadas:wallet_idperiod_wallet_daytotal_value_paymenttotal_value_consumptiontotal_value_commissionprevious_closing_balance
- En el método
-
Eliminación de Columnas No Relevantes:
- Cualquier columna intermedia que no era importante (como
quantity_payments,quantity_commissions,processed_date, etc.) ha sido eliminada en esta última etapa.
- Cualquier columna intermedia que no era importante (como
-
Uso de Alias Consistente:
- Todas las columnas están referenciadas explícitamente usando el alias del DataFrame correspondiente (
currentoprevious).
- Todas las columnas están referenciadas explícitamente usando el alias del DataFrame correspondiente (
-
Método
.show():- Se muestra únicamente el contenido requerido con las columnas específicas seleccionadas.
Resultado Esperado
Tras ejecutar este código, se mostrará una tabla con las columnas requeridas:
wallet_id period_wallet_day total_value_payment total_value_consumption total_value_commission previous_closing_balance 12345 2023-10-15 1000.00 500.00 100.00 2000.00 67890 2023-10-14 150.00 75.00 10.00 0.00
Este ajuste asegura que el DataFrame final solo contenga las columnas que necesitas y que el output sea más claro y directamente relevante. ¿Esto resuelve tu solicitud? 😊
-
- User
No no pues, que en el show como hago para modificarlo y seleccionar solo esas columnas en el dataframe final, o sea no es que quiera que saques las otras columnas del aggrgated_df
- Assistant
¡Gracias por la aclaración! Si quieres que el
aggregated_df_with_closing_balanceconserve todas las columnas, pero solo quieres mostrar determinadas columnas al usar.show(), no es necesario cambiar el contenido delDataFrame. Simplemente puedes usar el método.select()solo para el propósito de mostrar las columnas que te interesen.Aquí está la forma en la que puedes ajustar el script para mantener
aggregated_df_with_closing_balancecompleto, pero mostrar únicamente"wallet_id","period_wallet_day","total_value_payment","total_value_consumption","total_value_commission", y"previous_closing_balance":
Código Ajustado para el
.show()# Mostrar únicamente las columnas deseadas sin modificar permanentemente el DataFrame aggregated_df_with_closing_balance.select( "wallet_id", "period_wallet_day", "total_value_payment", "total_value_consumption", "total_value_commission", "previous_closing_balance" ).show()Explicación:
-
Mantener el DataFrame Intacto:
- Este ajuste mantiene el
aggregated_df_with_closing_balancecomo está, con todas las columnas que fueron seleccionadas al construir el DataFrame previamente.
- Este ajuste mantiene el
-
Seleccionar Temporalmente las Columnas para Mostrar:
- Usamos el método
.select()justo antes de llamar a.show()para filtrar únicamente las columnas que quieres mostrar. Esto no modifica el DataFrame original.
- Usamos el método
-
Columnas en el
.show():- Solo aparecerán las columnas especificadas en
.select()(wallet_id,period_wallet_day, etc.) en la tabla que se imprime.
- Solo aparecerán las columnas especificadas en
-
No Alta Sobrecarga:
- Esto es un paso ligero porque Spark simplemente selecciona las columnas indicadas y las muestra temporalmente, lo que no impacta el pipeline global.
Ejemplo del Resultado con
.show()wallet_id period_wallet_day total_value_payment total_value_consumption total_value_commission previous_closing_balance 12345 2023-10-15 1000.00 500.00 100.00 2000.00 67890 2023-10-14 150.00 75.00 10.00 0.00
Resumen
Si todo el objetivo es no modificar el contenido del
DataFrame originaly solo restringir qué columnas aparecen al imprimir con.show(), este enfoque es perfecto. Así puedes conservar el DataFrame completo para otros usos futuros pero limitar la cantidad de columnas en la visualización. 😊¿Esto cubre la funcionalidad que buscabas? ¡Avísame si necesitas más aclaraciones!
-