How do I define Rows and Columns in a session.sql Dataframe

šŸŽˆ Using Streamlit
,
1

Summary

Share a clear and concise description of the issue. Aim for 2-3 sentences.
Hi everyone, Iā€™m trying to detail my session.sql created- dataframe with columns and rowsā€¦ Assume that the column names already exist as part of the sql query and weā€™re just calling them from there. The rows I want to make will be made up.
Appreciate the supportā€¦

Steps to reproduce

code snippet:

liq_df = session.sql("select * from table( ***insert sql query here*** ))").to_pandas()
st.dataframe(liq_df)

I want to see columns and rows that detail my dataframe.

2

What are you seeing instead of columns and rows?

3

I donā€™t have any row names. But I want to come up with some. And I only want to show certain columns.

4

I would recommend adding a column for row_name, and making that the index of the dataframe, and either filtering down to the columns you want in the query itself, or filtering the dataframe afterwards with pandas.

5

Alternatively, if this is a Snowpark session, you can use the Snowpark API after the query to select certain columns Working with DataFrames in Snowpark Python | Snowflake Documentation

6

You can set the row names with liq_df.index = row_names.

For showing only certain columns, just donā€™t include any other columns in your query. Or if you relly need columns that you donā€™t want to show: st.dataframe(liq_df[columns_to_show]).

7

I found this

Import the col function from the functions module.

from snowflake.snowpark.functions import col

Create a DataFrame that contains the id, name, and serial_number

columns in the ā€œsample_product_dataā€ table.

df = session.table(ā€œsample_product_dataā€).select(col(ā€œidā€), col(ā€œnameā€), col(ā€œserial_numberā€))
df.show()

To return the DataFrame as a table in a Python worksheet use return instead of show()

return df

|ā€œIDā€ |ā€œNAMEā€ |ā€œSERIAL_NUMBERā€ |

|1 |Product 1 |prod-1 |
|2 |Product 1A |prod-1-A |
|3 |Product 1B |prod-1-B |
|4 |Product 2 |prod-2 |
|5 |Product 2A |prod-2-A |
|6 |Product 2B |prod-2-B |
|7 |Product 3 |prod-3 |
|8 |Product 3A |prod-3-A |
|9 |Product 3B |prod-3-B |
|10 |Product 4 |prod-4 |

But how would I transform my code to use this example?

8

I think it would be something like this:

liq_df = session.sql("select * from table( ***insert sql query here*** ))")
liq_df.show()

But this wonā€™t display the DataFrame in your Streamlit application.

9 
df = session.sql("select * from table(***insert sql query here***")
st.dataframe(df.select(col("id"), col("name")))
10

It says dataframe object has no attribute ā€˜selectā€™.