Create rows in a session.sql DataFrame and organize columns into the rows

[st.jfern]

Summary

Hi everyone! I’m working with python in snowpark. I have this dataframe created using session.sql to pull my sql query and organize it. I’m able to select the columns I want. But how would I create a second row and organize column_6, column_7, column_8, column_9 to be in that second row and align them directly under the other columns. So should appear as column_6 in row 2 directly under column_1, column_7 in row 2 under column_2, etc. And add a row label into the dataframe. Lets call it Row_1, Row_2. Appreciate all help and guidance.

Steps to reproduce

‘’’

df = session.sql(“select * from table(insert sql query here”).to_pandas()
st.dataframe(df.select(col(" column_1"),col(“column_2”),col(“column_3”),col(“column_4”),col(“column_5”),col(“column_6”),col(“column_7”),col(“column_8”),col(“column_9”)))

‘’’

[dataprofessor]

Hi @st.jfern

It may also be helpful to provide a schematic illustration of the intended dataframe layout.

As the data has been converted to a Pandas DataFrame via `to_pandas(), you can utilize Pandas capability to restructure the DataFrame. Thus, you may want to look into the pivot function and there’s a great in-depth tutorial in the article below:

Pandas Pivot — The Ultimate Guide

Hope this helps!

[st.jfern]

Here’s what I have vs. what I’m looking to have. Not quite a pandas pivot but I appreciate the input…

[Goyo]

There may be smarter ways, but a straighforward, brute force approach should work (with pandas, it may or may not make sense for snowpark).

1. Create a DataFrame with the first 5 columns. Add a column for the row label. Set a sequence of consecutive even numbers as the index, starting from 0.
2. Create another DataFrame with the columns 6 to 9. Rename the columns to mach the ones in the first DataFrame. Add a column for the row label. Set a sequence of consecutive odd numbers as the index, starting from 1.
3. Concatenate both DataFrames. Sort the result by index if necessary.