 Transform a 3 columns (x, y, result) Python Pandas DataFrame to a DataFrame of result values with x (unique) as row and
By : user2163320
Date : March 29 2020, 07:55 AM
|
How to store mySQL query result into pandas DataFrame with pymysql?
By : Shane R
Date : March 29 2020, 07:55 AM
I hope this helps you . Use Pandas.read_sql() for this: code :
query = "SELECT * FROM orders WHERE date_time BETWEEN ? AND ?"
df = pd.read_sql(query, connection, params=(start_date, end_date))
|
 Read pandas dataframe row by row, call API and then store each result in a separate pandas dataframe
By : 陳柏憲
Date : March 29 2020, 07:55 AM
To fix the issue you can do If you're calling row by API call, and want to add that row in another data frame, here's code for that. code :
import pandas as pd
df = pd.DataFrame()
df.append(result)
|
how to print out and save the result of the query in a pandas dataframe
By : Anh Tuấn
Date : March 29 2020, 07:55 AM
hop of those help? If you mean load the results in a pandas DataFrame then, first save it locally code :
hive -e "select * from t1" > /home/yourfile.tsv
import pandas as pd
df = pd.read_csv("/home/yourfile.tsv",delimiter = "\t")
|
 how to assure a deterministic result of a query that uses mode() in snowflake
By : user3581304
Date : March 29 2020, 07:55 AM
will help you I use snowflake and I want to use multiple mode() expressions in one select statement. So it looks like: , so mode seems to prefer the first value it see in a tie breaker. code :
with data as (
select x, col1, col2, col3 from values (1, 1, 1, 3), (1, 1, 2,3), (1, 2, 2,3)
,(4, 1, 20, 30), (4, 1, 2, 3), (4, 2, 2, 30), (4,2,20,3) v(x,col1,col2,col3)
)
select x
,mode(col1)
,mode(col2)
,mode(col3)
from data
group by 1
order by 1;
with data as (
select x, col1, col2, col3 from values (1, 1, 1, 3), (1, 1, 2,3), (1, 2, 2,3)
,(4, 1, 20, 30), (4, 1, 2, 3), (4, 2, 2, 30), (4,2,20,3) v(x,col1,col2,col3)
)
select x
,col1
,col2
,col3
,count(col1)over(partition by x,col1) c_col1
,count(col2)over(partition by x,col2) c_col2
,count(col3)over(partition by x,col3) c_col3
from data ;
with data as (
select x, col1, col2, col3 from values (1, 1, 1, 3), (1, 1, 2,3), (1, 2, 2,3)
,(4, 1, 20, 30), (4, 1, 2, 3), (4, 2, 2, 30), (4,2,20,3) v(x,col1,col2,col3)
)
select x
,col1
,col2
,col3
,row_number() over (partition by x order by c_col1 desc, col1) as r1
,row_number() over (partition by x order by c_col2 desc, col2) as r2
,row_number() over (partition by x order by c_col3 desc, col3) as r3
from (
select x
,col1
,col2
,col3
,count(col1)over(partition by x,col1) c_col1
,count(col2)over(partition by x,col2) c_col2
,count(col3)over(partition by x,col3) c_col3
from data
)
order by 1;
X COL1 COL2 COL3 R1 R2 R3
1 1 2 3 2 1 1
1 2 2 3 3 2 2
1 1 1 3 1 3 3
4 1 2 3 2 1 1
4 2 20 3 4 4 2
4 2 2 30 3 2 3
4 1 20 30 1 3 4
QUALIFY row_number() over (partition by x order by c_col1 desc, col1) = 1
AND row_number() over (partition by x order by c_col2 desc, col2) = 1
AND row_number() over (partition by x order by c_col3 desc, col3 desc) = 1
with data as (
select x, col1, col2, col3 from values (1, 1, 1, 3), (1, 1, 2,3), (1, 2, 2,3)
,(4, 1, 20, 30), (4, 1, 2, 3), (4, 2, 2, 30), (4,2,20,3) v(x,col1,col2,col3)
),col1_m as (
select x, col1, count(*) as c
from data
group by 1,2
QUALIFY row_number() over (partition by x order by c desc, col1) = 1
),col2_m as (
select x, col2, count(*) as c
from data
group by 1,2
QUALIFY row_number() over (partition by x order by c desc, col2) = 1
),col3_m as (
select x, col3, count(*) as c
from data
group by 1,2
QUALIFY row_number() over (partition by x order by c desc, col3) = 1
), base as (
select distinct x from data
)
select b.x
,c1.col1
,c2.col2
,c3.col3
from base as b
left join col1_m as c1 on b.x = c1.x
left join col2_m as c2 on b.x = c2.x
left join col3_m as c3 on b.x = c3.x
order by 1;
X COL1 COL2 COL3
1 1 2 3
4 1 2 3
|
Related Posts :
» 
code :
