During solving Excel BI challenge

I used Table.UnpivotOtherColumns(Source, {}, "Attribute", "Value"). And this gave me an output:

Next I was trying to solve the same challenge in polars and I used unpivot expression:

import polars as pl,datetime
dc={'Hall': ['Hall1', 'Hall1', 'Hall2', 'Hall2'],
 'Date': [datetime.date(2023, 2, 1),
  datetime.date(2023, 2, 2),
  datetime.date(2023, 2, 1),
  datetime.date(2023, 2, 4)],
 'Guest1': ['A', 'X', 'R', 'S'],
 'Guest2': ['B', 'Y', None, 'P'],
 'Guest3': [None, 'Z', None, None],
 'Guest4': [None, 'Q', None, None]}
df=pl.from_dict(dc).unpivot()
df

and this gave me output:

Is it possible to unpivot table with polars the same way as in power query ? I was thinking about iterating through each row and unpivoting each row separately and combining rows w hatack. But will it be efficient? I also checked pandas behavior and it works the same. Thanks in advance for any explanation. Artur

During solving Excel BI challenge

I used Table.UnpivotOtherColumns(Source, {}, "Attribute", "Value"). And this gave me an output:

Next I was trying to solve the same challenge in polars and I used unpivot expression:

import polars as pl,datetime
dc={'Hall': ['Hall1', 'Hall1', 'Hall2', 'Hall2'],
 'Date': [datetime.date(2023, 2, 1),
  datetime.date(2023, 2, 2),
  datetime.date(2023, 2, 1),
  datetime.date(2023, 2, 4)],
 'Guest1': ['A', 'X', 'R', 'S'],
 'Guest2': ['B', 'Y', None, 'P'],
 'Guest3': [None, 'Z', None, None],
 'Guest4': [None, 'Q', None, None]}
df=pl.from_dict(dc).unpivot()
df

and this gave me output:

Is it possible to unpivot table with polars the same way as in power query ? I was thinking about iterating through each row and unpivoting each row separately and combining rows w hatack. But will it be efficient? I also checked pandas behavior and it works the same. Thanks in advance for any explanation. Artur

• python
• python-polars
• unpivot
• polars

1 Answer 1

If I understand your problem correctly your issue is only with the final ordering of the rows in the final dataframe, you can add an index to sort with by adding a row index column and unpivoting on this column, then sorting by the index and removing the column afterward so it matches the desired dataframe. Just changing your last line of code:

import polars as pl,datetime
dc={'Hall': ['Hall1', 'Hall1', 'Hall2', 'Hall2'],
 'Date': [datetime.date(2023, 2, 1),
  datetime.date(2023, 2, 2),
  datetime.date(2023, 2, 1),
  datetime.date(2023, 2, 4)],
 'Guest1': ['A', 'X', 'R', 'S'],
 'Guest2': ['B', 'Y', None, 'P'],
 'Guest3': [None, 'Z', None, None],
 'Guest4': [None, 'Q', None, None]}
df=pl.from_dict(dc).with_row_index().unpivot(index = ["index"], variable_name = "Attribute").drop_nulls().sort("index").drop("index")
df

produces the following dataframe