generating a Chart.js chart with python data

Question

I am working on a litte stock-market project in Python. Every week, a status.xlsx file is generated that tells me what sectors make up my portfolio, e.g.

xls = pd.ExcelFile('Status_week_9.xlsx')
df1=pd.read_excel(xls)
print(df1)

I am looping over all files, so I get a dataframe similar to this for every file:

sector              pct              
Consumer Cyclical   0.319638
Industrials         0.203268
Financial Services  0.200217
...

Now I would like to loop through these weekly reports and pass the data to a Chart.js template in order to get a stacked bar for each week that shows the increase / decrease of sectors over time, e.g. https://jsfiddle.net/qg1ya5vk/4/ (demo only, does not add up to 1)

The template file looks like this:

The idea was to use a template with placeholders for the chart and use

from string import Template

to replace the placeholders with the corresponding values. The variables are "labels" and "dataset". "Labels" consists of the calendar weeks. This can be extracted from the filenames. So far, so good ;-)

Now for the "dataset", I'm not sure how to get information from the n dataframes.

One dataset would have to look like this:

 {label: 'Energy',
           data: [0.037975, 0.038512, 0.039817, 0.065010],}

So this would mean that the Energy sector had a share of 3.7975% in week 1, 3.8512% in week 2 etc. Complicating things even further, it's also possible that one sector is not present in one week, so I would have to add a 0 then, because the sector is not part of the original dataframe.

There can be n (comma-separated) datasets.

I probably have to transpose the dataframe but it doesn't quite do the trick (yet).

So far my code looks like this:

import pandas as pd
import glob
import os
from string import Template

labels=[]
dataset=[]
files =...
for i in files:
    cw = i.split('_')[3].split('.')[0] ## extract calendar week from filename
    xls = pd.ExcelFile(i)
    df1 = pd.read_excel(xls, 'Sectors') ## select sheet 'Sectors'
    df1['CW'] = cw   ## add cw to df1
    df1_t = df1.T. ## transpose df1
    sectors = df1.sector.to_list()
    share = df1.pct.to_list()
    labels.append(cw)
    dataset.append(df1_t) ##??


# {
#       label: 'Energy',
#       data: [0.037975, 0.038512, 0.039817, 0.065010],
#     }


d = {'label' : labels, 'datasets' : dataset}

## open Chart template and put in values:
with open('template.txt', 'r') as f:
    src = Template(f.read())
    result = src.substitute(d)
    print(result)

How would you generate the datasets?

I am also thinking this is a little bit long-winded and error-prone. Maybe there is another way to tackle this?

Template for chart:

var label = $label;
var ctx = document.getElementById("myChart4").getContext('2d');
var myChart = new Chart(ctx, {
  type: 'bar',
  data: {
    labels: label,
    datasets: [
    $datasets      //**this is the culprit**
  ],
  },
options: {
    tooltips: {
      displayColors: true,
      callbacks:{
        mode: 'x',
      },
    },
    scales: {
      xAxes: [{
        stacked: true,
        gridLines: {
          display: false,
        }
      }],
      yAxes: [{
        stacked: true,
        ticks: {
          beginAtZero: true,
        },
        type: 'linear',
      }]
    },
    responsive: true,
    maintainAspectRatio: false,
    legend: { position: 'bottom' },
  }
});

Gilles Ottervanger · Accepted Answer · 2021-03-11 19:43:20Z

2

What you are looking for is the json module. You can simply arrange your data in the correct form in python and write to a JSON that Chart.js can understand.

import json

data = {'label' : labels, 'datasets' : dataset}
with open('data.json', 'w') as f:
    json.dump(data, f)

This question is about how to get your JSON into Chart.js.

answered Mar 11, 2021 at 19:43

Gilles Ottervanger

6713 silver badges13 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

RazzleDazzle Over a year ago

Thanks for suggesting json! Just to be clear, does this mean I would have to create a JSON file from the xlsx-files/dataframes and then use the JSON with Charts.js?

Gilles Ottervanger Over a year ago

Yes basically. What you are doing gets close but the problem is that Template does not know how to turn python dict into a string in the way that you expect it to do.

Collectives™ on Stack Overflow

generating a Chart.js chart with python data

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related