DEV Community

Ed Legaspi
Ed Legaspi

Posted on • Originally published at czetsuyatech.com

How to Convert Vertically Stored Asset Data into Columnar Format for Cointegration Analysis

Introduction

This piece of code fetches asset information from a table stored vertically.

Image description

Dependencies

Install the following package.

conda install pandas
conda install numpy as np
conda install mysql-connector-python
conda install sqlalchemy
conda install pymysql
Enter fullscreen mode Exit fullscreen mode

Hands-on Coding

Connect to the database

def query_df(query):
    try: 
        engine_uri = f"mysql+pymysql://db_user:db_pass_123@localhost:3306/tradewise_pse"
        db_conn = create_engine(engine_uri)        
        df_result = pd.read_sql(query, db_conn)    
        return df_result

    except Exception as e:    
        print(str(e))
Enter fullscreen mode Exit fullscreen mode

Fetching the Dataset

if not load_existing:
    sql_distinct_tickers = "select ticker from candlestick where event_time='2023-12-29' and ticker not like '^%%'"
    df_tickers = query_df(sql_distinct_tickers)

    df = pd.DataFrame(index=['event_time'])

    ### Get the candlesticks
    for ticker in df_tickers['ticker']:
        sql_ticker_col = "select event_time, close from candlestick where ticker='{0}'"
        df_temp = query_df(sql_ticker_col.format(ticker))
        df_temp.set_index('event_time', inplace=True)
        df_temp.rename(columns={'close': ticker}, inplace=True)        
        df = df.add(df_temp, fill_value=0)

    df.to_csv(file_name)
Enter fullscreen mode Exit fullscreen mode

Load the dataset from file

df = pd.read_csv(file_name, index_col=0)
df.drop(index=df.index[-1],axis=0, inplace=True)
Enter fullscreen mode Exit fullscreen mode

Drop NA

df.dropna(axis=1, inplace=True)

Print the Dataset

print(f"Shape: {df.shape}")
print(f"Null values: {df.isnull().values.any()}")
df

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/6ypd0yfpe8ihrfvx58cl.png)
Enter fullscreen mode Exit fullscreen mode

AWS Security LIVE!

Join us for AWS Security LIVE!

Discover the future of cloud security. Tune in live for trends, tips, and solutions from AWS and AWS Partners.

Learn More

Top comments (0)

Billboard image

The Next Generation Developer Platform

Coherence is the first Platform-as-a-Service you can control. Unlike "black-box" platforms that are opinionated about the infra you can deploy, Coherence is powered by CNC, the open-source IaC framework, which offers limitless customization.

Learn more

👋 Kindness is contagious

Please leave a ❤️ or a friendly comment on this post if you found it helpful!

Okay