LSEG's SFI Snowflake Datawarehouse: A use-case evaluating public companies' Climate track record using Python and SQL

    	
            
## pip install snowflake-snowpark-python
## pip install "snowflake-connector-python[pandas]"
from snowflake import snowpark
import pandas as pd
import os # This is a native library that will allow us to collect authentication  details from the machine where we're running our code.
import plotly.graph_objects as go # `plotly` and its modules are useful for us to plot graphs
import numpy as np # `numpy` in this case will be useful for us to plot graphs

        
        Copy
    

    	
            
# Global variable
g_db_session = None
 
def get_session(user, password):
    global g_db_session
    
    # Establish a connection and assign it to the global session variable
    connection_parameters = { 
        "account": "rddpreprod1.east-us-2.azure",
        "user": user,
        "password": password,
        "warehouse": "SFI_READER_S_WH", 
        "database": "SHAREDB_SFI_MVP",
        "role": "SNO_SNOREFDBD_SFI_READER_ROLE",
        "schema": "DBO",
        "authenticator": "externalbrowser"
    }
    
    g_db_session = snowpark.Session.builder.configs(connection_parameters).create() 

        
        Copy
    

    	
            OrgPermID = "4295875633"
        
        Copy
    

    	
            get_session("john.doe@domain.com", os.getenv('SSO_PASSWD'))
        
        Copy
    

    	
            
# Retrieve the data from Snowflake
query_esg = f"""
    SELECT S.FY, S.SCORECALCDT, S.SCORE, I.TITLE
    FROM ESG2SCORES S 
    JOIN ESG2ITEM I ON S.ITEM = I.ITEM 
    WHERE S.ORGPERMID = {OrgPermID} and I.ITEM IN (2,4,5,6,7,8,9,10,11,12,13,14,15,16,65)
    """
 
# Execute the query and get the DataFrame
df_esg = g_db_session.sql(query_esg).toPandas()
 
display(df_esg)

        
        Copy
    

    	
            
# Pivot the DataFrame to get the desired format
pivot_df_esg = df_esg.pivot(index='FY', columns='TITLE', values='SCORE')
 
# Return most recent FY
pivot_df_esg = pivot_df_esg.iloc[[-1]].reset_index(drop=True)
pivot_df_esg.columns.name = None
 
display(pivot_df_esg)

        
        Copy
    

    	
            
# The Snowpark package is required for Python Worksheets. 
# You can add more packages by selecting them using the Packages control and then importing them.
import snowflake.snowpark as snowpark
from snowflake.snowpark.functions import col
import pandas as pd
 
def main(session: snowpark.Session): 
 
    OrgPermID = "4295875633"
    
    query = f"""
    SELECT TABLE_NAME
    FROM SHAREDB_SFI_MVP.INFORMATION_SCHEMA.TABLES
    WHERE TABLE_NAME ILIKE '%Perm%'
    """
    df = session.sql(query).toPandas()
    
    print(df)

        
        Copy
    

    	
            
    query = f"""
        SELECT TABLE_NAME
        FROM SHAREDB_SFI_MVP.INFORMATION_SCHEMA.TABLES
        WHERE TABLE_NAME ILIKE '%Perm%'
        """
    df = session.sql(query).toPandas()
    
    for table in df.TABLE_NAME:
        try:
            query = f"""
                SELECT * FROM SHAREDB_SFI_MVP.DBO.{table}
                WHERE ORGPERMID = '4295875633'
                """
            _df = session.sql(query).toPandas()
            if len(_df) > 0:
                print("\n")
                print(f"{table}")
                print(_df.T)
        except:
            print("\n")
            print(f"{table} didn't have a 'ORGPERMID' column")

        
        Copy
    

    	
            
    query = f"""
        SELECT TABLE_NAME
        FROM SHAREDB_SFI_MVP.INFORMATION_SCHEMA.TABLES
        WHERE TABLE_NAME ILIKE '%Perm%'
        """
    df = session.sql(query).toPandas()
    
    for table in df.TABLE_NAME:
        try:
            query = f"""
                SELECT * FROM SHAREDB_SFI_MVP.DBO.{table}
                WHERE ORGPERMID = '4295875633'
                """
            _df = session.sql(query).toPandas()
            columns_with_name = [col for col in _df.columns if _df[col].astype(str).str.contains('ENI', case=False, na=False).any()]
            if len(_df) > 0 and len(columns_with_name) > 0:
                print("\n")
                print(f"Table {table}'s columns where 'ENI' appears:", columns_with_name)
        except:
            print("\n")
            print(f"{table} didn't have a 'ORGPERMID' column")

        
        Copy
    

    	
            
SELECT *
FROM SHAREDB_SFI_MVP.DBO.PERMORGINFO
LIMIT 10;

        
        Copy
    

    	
            
SELECT *
FROM SFI_MASTER_MVP.DBO.CLMDataPoint
WHERE ORGPERMID = 4295875633
AND ITEM IN (
    SELECT ITEM
    FROM SFI_MASTER_MVP.DBO.CLMITEM
    WHERE FEEDFIELDNAME = 'ShortTermSet1GHGEmissionPercentageReductionTargeted'
);

        
        Copy
    

    	
            
SELECT 
    p.ORGPERMID,
    p.LEGALNAME,
    c.FY,
    c.VALUEPRCNT
FROM 
    SHAREDB_SFI_MVP.DBO.PERMORGINFO p
JOIN 
    SFI_MASTER_MVP.DBO.CLMDataPoint c
    ON p.ORGPERMID = c.ORGPERMID
WHERE 
    c.ORGPERMID = 4295875633
    AND c.ITEM IN (
        SELECT ITEM
        FROM SFI_MASTER_MVP.DBO.CLMITEM
        WHERE FEEDFIELDNAME = 'ShortTermSet1GHGEmissionPercentageReductionTargeted'
    );

        
        Copy
    

    	
            
query = f"""
    SELECT TABLE_NAME
    FROM SHAREDB_SFI_MVP.INFORMATION_SCHEMA.TABLES
    WHERE TABLE_NAME ILIKE '%Perm%'
    """
 
df = g_db_session.sql(query).toPandas()
 
display(df)

        
        Copy
    

    	
            
dfs = {}
for table in df.TABLE_NAME:
    try:
        query = f"""
            SELECT * FROM SHAREDB_SFI_MVP.DBO.{table}
            WHERE ORGPERMID = '4295875633'
            """
        _df = g_db_session.sql(query).toPandas()
        dfs[f"{table}"] = _df
        if len(_df) > 0:
            print(f"{table}")
            display(_df)
    except:
        print(f"{table} didn't have a 'ORGPERMID' column")

        
        Copy
    

    	
            
dfs = {}
for table in df.TABLE_NAME:
    try:
        query = f"""
            SELECT * FROM SHAREDB_SFI_MVP.DBO.{table}
            WHERE ORGPERMID = '4295875633'
            """
        _df = g_db_session.sql(query).toPandas()
        dfs[f"{table}"] = _df
        columns_with_name = [col for col in _df.columns if _df[col].astype(str).str.contains('ENI', case=False, na=False).any()]
        if len(_df) > 0 and len(columns_with_name) > 0:
            print(f"Table {table}'s columns where 'ENI' appears:", columns_with_name)
    except:
        print(f"{table} didn't have a 'ORGPERMID' column")

        
        Copy
    

    	
            
query_climate = f"""
    SELECT *
    FROM SFI_MASTER_MVP.DBO.CLMDataPoint
    WHERE ORGPERMID = {OrgPermID}
    AND ITEM IN (
        SELECT ITEM
        FROM SFI_MASTER_MVP.DBO.CLMITEM
        WHERE FEEDFIELDNAME = 'ShortTermSet1GHGEmissionPercentageReductionTargeted'
    );
    """
 
# Execute the query and get the DataFrame
df_climate1 = g_db_session.sql(query_climate).toPandas()
 
display(df_climate1)

        
        Copy
    

    	
            
df_climate2 = df_climate1[['ORGPERMID', 'FY', 'VALUERECDT', 'VALUEPRCNT']]
df_climate2

        
        Copy
    

    	
            
query_climate = f"""
    SELECT
        c.FY,
        p.ORGPERMID,
        p.LEGALNAME,
        c.VALUEPRCNT
    FROM 
        SHAREDB_SFI_MVP.DBO.PERMORGINFO p
    JOIN 
        SFI_MASTER_MVP.DBO.CLMDataPoint c
        ON p.ORGPERMID = c.ORGPERMID
    WHERE 
        c.ORGPERMID = 4295875633
        AND c.ITEM IN (
            SELECT ITEM
            FROM SFI_MASTER_MVP.DBO.CLMITEM
            WHERE FEEDFIELDNAME = 'ShortTermSet1GHGEmissionPercentageReductionTargeted'
        );
    """
 
# Execute the query and get the DataFrame
df_climate1 = g_db_session.sql(query_climate).toPandas()
 
display(df_climate1)

        
        Copy
    

    	
            
query_climate = f"""
    SELECT
        c.FY,
        p.ORGPERMID,
        p.LEGALNAME,
        c.VALUEPRCNT
    FROM 
        SHAREDB_SFI_MVP.DBO.PERMORGINFO p
    JOIN 
        SFI_MASTER_MVP.DBO.CLMDataPoint c
        ON p.ORGPERMID = c.ORGPERMID
    WHERE 
        p.LEGALNAME ILIKE '%Total%'
        AND c.ITEM IN (
            SELECT ITEM
            FROM SFI_MASTER_MVP.DBO.CLMITEM
            WHERE FEEDFIELDNAME = 'ShortTermSet1GHGEmissionPercentageReductionTargeted'
        );
    """
 
# Execute the query and get the DataFrame
df_climate1 = g_db_session.sql(query_climate).toPandas()
# df_climate1.drop_duplicates(subset=['FY']).sort_values(by=['FY'])
display(df_climate1)

        
        Copy
    

    	
            
query_climate = f"""
    SELECT *
    FROM 
        SHAREDB_SFI_MVP.DBO.PERMORGINFO p
    JOIN 
        SFI_MASTER_MVP.DBO.CLMDataPoint c
        ON p.ORGPERMID = c.ORGPERMID
    JOIN
        SFI_MASTER_MVP.DBO.CLMITEM k
        ON c.ITEM = k.ITEM
    WHERE 
        p.LEGALNAME ILIKE '%TotalEnergies%'
        AND c.ITEM IN (
            SELECT ITEM
            FROM SFI_MASTER_MVP.DBO.CLMITEM
        );
    """
 
# Execute the query and get the DataFrame
df_climate2 = g_db_session.sql(query_climate).toPandas()
# df_climate2.drop_duplicates(subset=['FY']).sort_values(by=['FY'])
display(df_climate2)

        
        Copy
    

    	
            df_climate2
        
        Copy
    

    	
            df_climate2.drop_duplicates().sort_values(by=['FY']).dropna(subset=['ITEM', 'VALUEPRCNT']).T
        
        Copy
    

    	
            
query_climate = f"""
    SELECT
        c.FY, p.LEGALNAME, p.ORGPERMID, c.VALUEPRCNT,
        k.ITEM, c.VALUE_, k.INCLUDEDINSCORES,
        k.POLARITY, k.UNITS, k.TABLECODE,
        k.DATATYPECODE, k.DESCRIPTION,
        k.TITLE, k.FEEDFIELDNAME, c.VALUEPRCNT,
        c.VALUETEXT, c.VALUEFLG, c.VALUEDATE
    FROM 
        SHAREDB_SFI_MVP.DBO.PERMORGINFO p
    JOIN 
        SFI_MASTER_MVP.DBO.CLMDataPoint c
        ON p.ORGPERMID = c.ORGPERMID
    JOIN
        SFI_MASTER_MVP.DBO.CLMITEM k
        ON c.ITEM = k.ITEM
    WHERE 
        p.LEGALNAME ILIKE '%Microsoft%'
        AND c.ITEM IN (
            SELECT ITEM
            FROM SFI_MASTER_MVP.DBO.CLMITEM
            WHERE (VALUETEXT = 'CO2' OR VALUETEXT = 'CO2 Per Tonne of Revenue') -- This line makes sure we only have 'CO2' OR 'CO2 Per Tonne of Revenue' publishing companies
        );
    """
df_climate3 = g_db_session.sql(query_climate).toPandas()
display(df_climate3)

        
        Copy
    

    	
            df_climate3.VALUETEXT.unique()
        
        Copy
    

    	
            
FEEDFIELDNAME_of_interest = ['CO2EquivalentsEmissionDirectScope1', 'CO2EquivalentsEmissionIndirectScope2', 'CO2EquivalentsEmissionIndirectScope3', 'CO2EquivalentsEmissionTotal', 'TotalCO2EquivalentEmissionsScope1andScope2andScope3']
FEEDFIELDNAME_of_interest_SQL_str = ' OR '.join([f"(FEEDFIELDNAME = '{i}')" for i in FEEDFIELDNAME_of_interest])
FEEDFIELDNAME_of_interest_SQL_str

        
        Copy
    

    	
            
query_climate = f"""
    SELECT
        c.FY, p.LEGALNAME, p.ORGPERMID,
        k.ITEM, k.INCLUDEDINSCORES,
        k.POLARITY, k.UNITS, k.TABLECODE,
        k.DATATYPECODE, k.DESCRIPTION,
        k.TITLE, k.FEEDFIELDNAME, c.VALUE_, 
        c.VALUEPRCNT, c.VALUETEXT,
        c.VALUEFLG, c.VALUEDATE
    FROM 
        SHAREDB_SFI_MVP.DBO.PERMORGINFO p
    JOIN 
        SFI_MASTER_MVP.DBO.CLMDataPoint c
        ON p.ORGPERMID = c.ORGPERMID
    JOIN
        SFI_MASTER_MVP.DBO.CLMITEM k
        ON c.ITEM = k.ITEM
    WHERE 
        p.LEGALNAME ILIKE '%Microsoft%'
        AND c.ITEM IN (
            SELECT ITEM
            FROM SFI_MASTER_MVP.DBO.CLMITEM
            WHERE {FEEDFIELDNAME_of_interest_SQL_str}
        );
    """
df_climate4 = g_db_session.sql(query_climate).toPandas()
df_climate4.head(3)

        
        Copy
    

    	
            
df_climate4.pivot_table(
    index='FY',
    columns='FEEDFIELDNAME',
    values=['VALUE_', 'VALUEPRCNT', 'VALUEFLG', 'VALUEDATE', 'VALUETEXT'],
    aggfunc={
        'VALUE_': 'sum',  # for numeric columns, we can use sum, mean, etc.
        'VALUEPRCNT': 'mean',
        'VALUEFLG': 'sum',
        'VALUEDATE': 'first',  # Assuming we want the first value in each group
        'VALUETEXT': 'first'
    }
)

        
        Copy
    

    	
            
_df_climate = df_climate4.pivot_table(
    index='FY',
    columns='FEEDFIELDNAME',
    values=['VALUE_'])['VALUE_']
_df_climate

        
        Copy
    

    	
            
query_climate = f"""
    SELECT
        c.FY, p.LEGALNAME, p.ORGPERMID, c.VALUEPRCNT,
        k.ITEM, c.VALUE_, k.INCLUDEDINSCORES,
        k.POLARITY, k.UNITS, k.TABLECODE,
        k.DATATYPECODE, k.DESCRIPTION,
        k.TITLE, k.FEEDFIELDNAME, c.VALUEPRCNT,
        c.VALUETEXT, c.VALUEFLG, c.VALUEDATE
    FROM
        SHAREDB_SFI_MVP.DBO.PERMORGINFO p
    JOIN
        SFI_MASTER_MVP.DBO.CLMDataPoint c
        ON p.ORGPERMID = c.ORGPERMID
    JOIN
        SFI_MASTER_MVP.DBO.CLMITEM k
        ON c.ITEM = k.ITEM
    WHERE
        p.LEGALNAME ILIKE '%Microsoft%'
        AND c.ITEM IN (
            SELECT ITEM
            FROM SFI_MASTER_MVP.DBO.CLMITEM
            WHERE (
                FEEDFIELDNAME ILIKE '%GHGEmissionCoveredbyTarget%')
                OR (FEEDFIELDNAME ILIKE '%PercentageReductionTargeted%')
                OR (FEEDFIELDNAME ILIKE '%LongTermSet%GHGEmissionScope%')
                OR (FEEDFIELDNAME ILIKE '%LongTermSet%GHGEmissionBaseYear%')
                OR (FEEDFIELDNAME ILIKE '%LongTermSet%GHGEmissionTargetYear%')
                OR (FEEDFIELDNAME ILIKE '%CO2EquivalentEmissionsTotalMarketbased%')
        );
    """
df_climate5 = g_db_session.sql(query_climate).toPandas()

        
        Copy
    

    	
            
legalName = df_climate5.LEGALNAME.iloc[0]
legalName

        
        Copy
    

    	
            
df_climate6 = pd.concat(
    [
        df_climate5.pivot_table(
            index='FY',
            columns='FEEDFIELDNAME',
            values=['VALUEPRCNT'],
        ),
        df_climate5.pivot_table(
            index='FY',
            columns='FEEDFIELDNAME',
            values=['VALUETEXT'],
            aggfunc={
                'VALUETEXT': 'first'
            }
        ),
        df_climate5.pivot_table(
            index='FY',
            columns='FEEDFIELDNAME',
            values=['VALUE_'],
        )
    ],
    axis=1)
df_climate6

        
        Copy
    

    	
            
for col in df_climate6.VALUEPRCNT.columns:
    _df_climate[col] = df_climate6.VALUEPRCNT[col].iloc[:,0]

        
        Copy
    

    	
            
for col in df_climate6.VALUETEXT.columns:
    _df_climate[col] = df_climate6.VALUETEXT[col]

        
        Copy
    

    	
            
for col in df_climate6.VALUE_.columns:
    _df_climate[col] = df_climate6.VALUE_[col]

        
        Copy
    

    	
            _df_climate
        
        Copy
    

    	
            
for col in _df_climate.columns:
    # Check if column name contains the target substrings
    if ('GHGEmissionPercentageReductionTargeted' in col or 'GHGEmissionCoveredbyTarget' in col):
        # Select the column and check if all numeric values (ignoring NaNs) are greater than 100
        if _df_climate[col].apply(pd.to_numeric, errors='coerce').dropna().gt(100).all():
            # If condition is met, divide the values by 100
            _df_climate[col] = _df_climate[col] / 100
_df_climate

        
        Copy
    

    	
            
df_climate = _df_climate[[
    "CO2EquivalentsEmissionDirectScope1",
    "CO2EquivalentsEmissionIndirectScope2",
    "CO2EquivalentsEmissionTotal"]]

        
        Copy
    

    	
            
for col in ["CO2EquivalentEmissionsTotalMarketbased",
            "LongTermSet1GHGEmissionPercentageReductionTargeted",
            "LongTermSet1PercentageofGHGEmissionCoveredbyTarget",
            "LongTermSet1GHGEmissionBaseYear",
            "LongTermSet1GHGEmissionTargetYear"]:
    df_climate[col] = _df_climate[_df_climate['LongTermSet1GHGEmissionScope'] == "Scope 1 and 2"][col]
df_climate

        
        Copy
    

    	
            
if not df_climate['LongTermSet1PercentageofGHGEmissionCoveredbyTarget'].apply(pd.to_numeric, errors='coerce').dropna().gt(80).all():
    print("Be weary of the fact that 'GHGEmissionCoveredbyTarget' is below 80%. The lower this figure, the less reliable the results.")
else:
    print("'GHGEmissionCoveredbyTarget' figures are above 80%. The higher this figure, the more reliable the results.")
    print("In this case, we consider them to be high enough to be considered reliable.")

        
        Copy
    

    	
            
baseEmissionYear = int(df_climate.LongTermSet1GHGEmissionBaseYear.iloc[-1])
targetEmissionReductionYear = int(df_climate.LongTermSet1GHGEmissionTargetYear.iloc[-1])
baseEmission = _df_climate.loc[baseEmissionYear].CO2EquivalentsEmissionTotal
targetReductionEmission = baseEmission * (df_climate['LongTermSet1GHGEmissionPercentageReductionTargeted'].iloc[-1] / 100) * (df_climate['LongTermSet1PercentageofGHGEmissionCoveredbyTarget'].iloc[-1] / 100)
targetEmission = baseEmission - targetReductionEmission
print(f"baseEmissionYear = {baseEmissionYear} & targetEmissionReductionYear = {targetEmissionReductionYear}")
print(f"baseEmission = {baseEmission} & targetReductionEmission = {targetReductionEmission} & targetEmission = {targetEmission}")

        
        Copy
    

    	
            
start_year = baseEmissionYear
end_year = targetEmissionReductionYear
start_value = baseEmission
end_value = targetEmission
 
# Calculate the decay constant 'k' for exponential decay
# We use the formula for exponential decay: y(t) = y0 * exp(-k * (t - t0))
# Solve for 'k' using y(t) at the end point
k = -np.log(end_value / start_value) / (end_year - start_year)
 
# Generate years and corresponding values using exponential decay
years = np.arange(start_year, end_year + 1)
values = start_value * np.exp(-k * (years - start_year))
 
# Create the plot
fig = go.Figure()
 
# Add the line trace
fig.add_trace(
    go.Scatter(
        x=years, y=values, mode='lines',
        name=f'Exponential Decay to target of {int(targetEmission)}'))
 
# Set plot title and labels
fig.update_layout(
    title=f"{legalName}'s GHG Emissions (Base in {baseEmissionYear} and Target in {targetEmissionReductionYear}) in CO2 Tons",
    xaxis_title="Year",
    yaxis_title="Value",
    template="plotly_dark"  # Optional: You can use different templates, such as 'plotly', 'ggplot2', etc.
)
 
# Add the dots representing the actual CO2 data from df_clmt0
fig.add_trace(go.Scatter(
    x=df_climate.index, 
    y=df_climate["CO2EquivalentsEmissionTotal"], 
    mode='lines+markers', 
    name='CO2 Equivalents Emission Total',
    marker=dict(color='red', size=8)  # Customize the dot color and size
))
 
fig.add_trace(go.Scatter(
    x=df_climate.index, 
    y=df_climate["CO2EquivalentEmissionsTotalMarketbased"], 
    mode='lines+markers', 
    name='CO2 Equivalent Emissions Total Marketbased',
    marker=dict(color='grey', size=8)  # Customize the dot color and size
))
 
# Show the plot
fig.show()

        
        Copy
    

    	
            
dfDict = GHGEmissionsBaseVsTargetUsingLSEGSFISnowflake(
        g_db_session=g_db_session,
        companyLegalName="Shell",
        exactCompanyLegalName=False)

        
        Copy