Transpose Dataframe (dictionary or series) to Table, show Keys now as rows

Dash Python
1

Hello,

Using this as my base https://vanguard-report.herokuapp.com/dash-vanguard-report

I’m trying to populate the “Fast Facts” table from the database, rather than a CSV.

I’ve been able to print out my Dataframe (originally a dictionary) to terminal and it looks similar to the format of the CSV but i cant get my keys into column 1 or the table ever

Please help

    html.H4('Database DataTable 3'),
    db_table3()
    ])

I’m pretty confident

def db_table3():
    sql_conn = connectSQLServer()
    cursor = sql_conn.cursor(as_dict=True)
    cursor.execute("SELECT * FROM [dbo].[Portfolio] WHERE PortfolioCode = 'G003'")
    data = cursor.fetchall() 
    data_series = pd.Series(data[0], index=data[0])
    print(data_series)
    print(data)
    return make_dash_table(data_series)
    ```

print of data_series

PortfolioCode                     G003
AssetClass1                      Bonds
AssetClass2              Global Credit
Style            Smart Beta Plus (SBP)
ManagementFee                     0.65
BuySellSpread                     0.15
InceptionDate      2010-03-11 00:00:00
Currency                           AUD


print of data

[{'PortfolioCode': 'G003', 'AssetClass1': 'Bonds', 'AssetClass2': 'Global Credit', 'Style': 'Smart Beta Plus (SBP)', 'ManagementFee': Decimal('0.65'), 'BuySellSpread': Decimal('0.15'), 'InceptionDate': datetime.datetime(2010, 3, 11, 0, 0), 'Currency': 'AUD'}]

![test|690x84](upload://sqNM2EO7TiuTMTv0cNN6jIUlGvd.jpeg)
2

I’ve had a another crack.

    sql_conn = connectSQLServer()
    cursor = sql_conn.cursor(as_dict=True)
    cursor.execute("SELECT [PortfolioCode],[AssetClass1],[AssetClass2],[Style],[ManagementFee],[BuySellSpread],[InceptionDate] ,[Currency] FROM [Roosevelt].[dbo].[Portfolio] WHERE PortfolioCode = 'G003'")
    data = cursor.fetchall()
    df = pd.DataFrame.from_dict(data)
    conv_array = df.values.tolist()
    key_array = list(df.keys())

    layout = dict(width=500, height=300)

    trace = go.Table(
        header=dict(values=['A Scores', 'B Scores'],
                    line=dict(color='#7D7F80'),
                    fill=dict(color='#a1c3d1'),
                    align=['left'] * 5),
        cells=dict(values=[key_array, conv_array[0]],
                   line=dict(color='#7D7F80'),
                   fill=dict(color='#EDFAFF'),
                   align=['left'] * 5)
    )

    print(trace)

    data = [trace]
    fig = dict(data=data, layout=layout)
    return fig

this prints out a table which looks structurally ok… but i get an error Error loading dependencies

Table({
‘cells’: {‘align’: [left, left, left, left, left],
‘fill’: {‘color’: ‘#EDFAFF’},
‘line’: {‘color’: ‘#7D7F80’},
‘values’: [[‘AssetClass1’, ‘AssetClass2’, ‘BuySellSpread’,
‘Currency’, ‘InceptionDate’, ‘ManagementFee’,
‘PortfolioCode’, ‘Style’], [‘Bonds’, ‘Global Credit’,
Decimal(‘0.15’), ‘AUD’, Timestamp(‘2010-03-11 00:00:00’),
Decimal(‘0.65’), ‘G003’, ‘Smart Beta Plus (SBP)’]]},
‘header’: {‘align’: [left, left, left, left, left],
‘fill’: {‘color’: ‘#a1c3d1’},
‘line’: {‘color’: ‘#7D7F80’},
‘values’: [A Scores, B Scores]}
})

3

this got it done, terrible form by me

def db_table3():
    sql_conn = connectSQLServer()
    cursor = sql_conn.cursor(as_dict=True)
    cursor.execute("SELECT [PortfolioCode],[AssetClass1],[AssetClass2],[Style],[ManagementFee],[BuySellSpread],[InceptionDate] ,[Currency] FROM .[dbo].[Portfolio] WHERE PortfolioCode = 'G003'")
    data = cursor.fetchall()
    df = pd.DataFrame.from_dict(data)
    conv_array = df.values.tolist()
    key_array = list(df.keys())

    DF_SIMPLE = pd.DataFrame({
        'labels': key_array,
        'values':  conv_array[0]
    })
    
    return make_dash_table(DF_SIMPLE)


name_title = 'Stats from SQL Server'
app = dash.Dash(__name__)

app.layout = html.Div([
    html.H1('Page 1'),
    html.Br(),

    html.H4('Database DataTable 3'),
    html.Table(db_table3())
])