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

Share Improve this question Follow edited Dec 10, 2024 at 11:40 jqurious 21.7k55 gold badges2020 silver badges3939 bronze badges asked Nov 20, 2024 at 19:22 ArtupArtup 7988 bronze badges

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 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