I am trying to build a Dashboard with Django… I wish to filter the dashboard with category, month and Year. The dataFrame is coming from python code than I am adding the charts with JS.
I don’t know how to proceed to be able to filter it as I want.
views.py:
def get_expense_api_data(request, *args, **kwargs):
sort_order = ['January','February','March','April','May','June','July','August','September','October','November','December']
exp_df = pd.DataFrame(Expenses.objects.filter(user=request.user).all().values('date_created', 'amount'))
categories = dict()
if exp_df.empty:
default_items_exp = [0]
labels_exp = [0]
else:
categories['expenses'] = pd.unique(pd.DataFrame(Expenses.objects.filter(user=request.user).all().values('categories')).categories.values).tolist()
exp_df['date_created'] = pd.to_datetime(exp_df['date_created'])
exp_df = pd.DataFrame(exp_df.groupby(exp_df['date_created'].dt.strftime('%B'))['amount'].sum())
exp_df["date_created"] = exp_df.index
exp_df.index = pd.CategoricalIndex(exp_df["date_created"], categories=sort_order, ordered=True)
exp_df = exp_df.sort_index().reset_index(drop=True)
default_items_exp = exp_df.amount.tolist()
labels_exp = exp_df.date_created.tolist()
inc_df = pd.DataFrame(Incomes.objects.filter(user=request.user).all().values('date_created', 'amount'))
if inc_df.empty:
default_items_inc = [0]
labels_inc = [0]
else:
categories['incomes'] = pd.unique(pd.DataFrame(Incomes.objects.filter(user=request.user).all().values('categories')).categories.values).tolist()
inc_df['date_created'] = pd.to_datetime(inc_df['date_created'])
inc_df = pd.DataFrame(inc_df.groupby(inc_df['date_created'].dt.strftime('%B'))['amount'].sum())
inc_df["date_created"] = inc_df.index
inc_df.index = pd.CategoricalIndex(inc_df["date_created"], categories=sort_order, ordered=True)
inc_df = inc_df.sort_index().reset_index(drop=True)
default_items_inc = inc_df.amount.tolist()
labels_inc = inc_df.date_created.tolist()
try:
net_df = pd.merge(inc_df, exp_df, how='outer', on='date_created')
net_df = net_df.fillna(0)
net_df['amount'] = net_df['amount_x'] - net_df['amount_y']
net_df.index = pd.CategoricalIndex(net_df["date_created"], categories=sort_order, ordered=True)
net_df = net_df.sort_index().reset_index(drop=True)
default_items_net = net_df.amount.tolist()
labels_net = net_df.date_created.tolist()
except KeyError:
if inc_df.empty:
net_df = exp_df
elif exp_df.empty:
net_df = inc_df
net_df.index = pd.CategoricalIndex(net_df["date_created"], categories=sort_order, ordered=True)
net_df = net_df.sort_index().reset_index(drop=True)
default_items_net = net_df.amount.tolist()
labels_net = net_df.date_created.tolist()
savings_df = pd.DataFrame(Savings.objects.filter(user=request.user).all().values('date_created', 'amount'))
if savings_df.empty:
default_items_savings = [0]
labels_savings = [0]
else:
categories['savings'] = pd.unique(pd.DataFrame(Savings.objects.filter(user=request.user).all().values('categories')).categories.values).tolist()
savings_df['date_created'] = pd.to_datetime(savings_df['date_created'])
savings_df = pd.DataFrame(savings_df.groupby(savings_df['date_created'].dt.strftime('%B'))['amount'].sum())
savings_df["date_created"] = savings_df.index
savings_df.index = pd.CategoricalIndex(savings_df["date_created"], categories=sort_order, ordered=True)
savings_df = savings_df.sort_index().reset_index(drop=True)
default_items_savings = savings_df.amount.tolist()
labels_savings = savings_df.date_created.tolist()
labels = {'expenses': labels_exp,
'incomes': labels_inc,
'net': labels_net,
'savings': labels_savings}
default_items = {'expenses': default_items_exp,
'incomes': default_items_inc,
'net': default_items_net,
'savings': default_items_savings}
data = {
'labels': labels,
'default': default_items,
'categories': categories
}
return JsonResponse(data, safe=False)
home.js:
let chartUsed = null;
document.getElementById("showExpChart").addEventListener("click", funShowExpChart, false);
document.getElementById("showIncChart").addEventListener("click", funShowIncChart, false);
document.getElementById("showNetChart").addEventListener("click", funShowNetChart, false);
document.getElementById("showSaveChart").addEventListener("click", funShowSaveChart, false);
chartInsToDel = document.getElementById("chart-instruc")
function funShowExpChart() {
var endpoint = 'expenses/api/data'
var defaultData = []
var labels = []
if(chartUsed != null){
chartUsed.destroy();
};
try {
chartInsToDel.remove();
}
finally {
console.log('Deleted already.')
}
$.ajax({
method:"GET",
url: endpoint,
success: function(data){
labels = data.labels
defaultData = data.default
categories = data.categories.expenses
$('#category')
.find('option')
.remove()
.end()
.append('<option value="">category</option>')
.val('whatever');
newSelect = document.getElementById("category");
for(element in categories)
{
var opt = document.createElement("option");
opt.value= categories[element];
opt.innerHTML = categories[element]; // whatever property it has
// then append it to the select element
newSelect.appendChild(opt);
}
const chartEle = document.getElementById('myChart').getContext("2d");
chartUsed = new Chart(chartEle, {
type: 'line',
data: {
labels: labels.expenses,
datasets: [{
label: 'Expenses',
data: defaultData.expenses,
borderWidth: 1
}]
},
options: {
scales: {
y: {
xAxes: [{
type: 'time'
}]
}
},
responsive: true,
maintainAspectRatio: false
}
}
);
},
error: function(error_data){
console.log("error")
console.log(error_data)
}
})
};
function funShowIncChart() {
var endpoint = 'expenses/api/data'
var defaultData = []
var labels = []
if(chartUsed != null){
chartUsed.destroy();
};
try {
chartInsToDel.remove();
}
finally {
console.log('Deleted already.')
}
$.ajax({
method:"GET",
url: endpoint,
success: function(data){
labels = data.labels
defaultData = data.default
categories = data.categories.incomes
$('#category')
.find('option')
.remove()
.end()
.append('<option value="">category</option>')
.val('whatever');
newSelect = document.getElementById("category");
for(element in categories)
{
var opt = document.createElement("option");
opt.value= categories[element];
opt.innerHTML = categories[element]; // whatever property it has
// then append it to the select element
newSelect.appendChild(opt);
}
const chartEle = document.getElementById('myChart').getContext("2d");
chartUsed = new Chart(chartEle, {
type: 'line',
data: {
labels: labels.incomes,
datasets: [{
label: 'Incomes',
data: defaultData.incomes,
borderWidth: 1
}]
},
options: {
scales: {
y: {
xAxes: [{
type: 'time'
}]
}
},
responsive: true,
maintainAspectRatio: false
}
}
);
},
error: function(error_data){
console.log("error")
console.log(error_data)
}
})
};
function funShowNetChart() {
var endpoint = 'expenses/api/data'
var defaultData = []
var labels = []
if(chartUsed != null){
chartUsed.destroy();
};
try {
chartInsToDel.remove();
}
finally {
console.log('Deleted already.')
}
$.ajax({
method:"GET",
url: endpoint,
success: function(data){
labels = data.labels
defaultData = data.default
$('#category')
.find('option')
.remove()
.end()
.append('<option value="">category</option>')
.val('whatever');
const chartEle = document.getElementById('myChart').getContext("2d");
chartUsed = new Chart(chartEle, {
type: 'line',
data: {
labels: labels.net,
datasets: [{
label: 'Net',
data: defaultData.net,
borderWidth: 1
}]
},
options: {
scales: {
y: {
xAxes: [{
type: 'time'
}]
}
},
responsive: true,
maintainAspectRatio: false
}
}
);
},
error: function(error_data){
console.log("error")
console.log(error_data)
}
})
};
function funShowSaveChart() {
var endpoint = 'expenses/api/data'
var defaultData = []
var labels = []
if(chartUsed != null){
chartUsed.destroy();
};
try {
chartInsToDel.remove();
}
finally {
console.log('Deleted already.')
}
$.ajax({
method:"GET",
url: endpoint,
success: function(data){
labels = data.labels
defaultData = data.default
categories = data.categories.savings
$('#category')
.find('option')
.remove()
.end()
.append('<option value="">category</option>')
.val('whatever');
newSelect = document.getElementById("category");
for(element in categories)
{
var opt = document.createElement("option");
opt.value= categories[element];
opt.innerHTML = categories[element]; // whatever property it has
// then append it to the select element
newSelect.appendChild(opt);
}
const chartEle = document.getElementById('myChart').getContext("2d");
chartUsed = new Chart(chartEle, {
type: 'line',
data: {
labels: labels.savings,
datasets: [{
label: 'Savings',
data: defaultData.savings,
borderWidth: 1
}]
},
options: {
scales: {
y: {
xAxes: [{
type: 'time'
}]
}
},
responsive: true,
maintainAspectRatio: false
}
}
);
},
error: function(error_data){
console.log("error")
console.log(error_data)
}
})
};
home.html:
{% extends "boost/base.html" %}
{% load static %}
{% block content %}
<!-- Main Content Area -->
<div class="content">
<h2>Hello {{ request.user }}</h2>
</div>
<div class="container">
<div class="container overflow-hidden">
<div class="row gx-5">
<div class="col">
<div class="card text-white bg-info mb-3" style="max-width: 18rem;">
<div class="card-chart">
<a id="showExpChart">
<div class="card-header text-center"><h2 class="card-title">Expenses</h2></div>
<div class="card-body">
<h3 class="card-text text-center">{{expenses_total}}</h3>
</div>
</a>
</div>
</div>
</div>
<div class="col">
<div class="card text-white bg-info mb-3" style="max-width: 18rem;">
<div class="card-chart">
<a id="showIncChart">
<div class="card-header text-center"><h2 class="card-title">Incomes</h2></div>
<div class="card-body">
<h3 class="card-text text-center">{{incomes_total}}</h3>
</div>
</a>
</div>
</div>
</div>
<div class="col">
<div class="card text-white bg-info mb-3" style="max-width: 18rem;">
<div class="card-chart">
<a id="showNetChart">
<div class="card-header text-center"><h2 class="card-title">Net</h2></div>
<div class="card-body">
<h3 class="card-text text-center">{{net}}</h3>
</div>
</a>
</div>
</div>
</div>
<div class="col">
<div class="card text-white bg-info mb-3" style="max-width: 18rem;">
<div class="card-chart">
<a id="showSaveChart">
<div class="card-header text-center"><h2 class="card-title">Savings</h2></div>
<div class="card-body">
<h3 class="card-text text-center">{{savings_total}}</h3>
</div>
</a>
</div>
</div>
</div>
<div id='app'>
<div id='dateFilter'>
<h4>Filter by month, year or category</h4>
<table class='optionsTable'>
<tr class='optionsRow'>
<td>
<select id='month' name="month">
<option value='' selected='selected'>Month</option>
<option value='1'>January</option>
<option value='2'>February</option>
<option value='3'>March</option>
<option value='4'>April</option>
<option value='5'>May</option>
<option value='6'>June</option>
<option value='7'>July</option>
<option value='8'>August</option>
<option value='9'>September</option>
<option value='10'>October</option>
<option value='11'>November</option>
<option value='12'>December</option>
</select>
</td>
<td>
<input id='year' name="year_to_filter" placeholder='{{year}}' type='number' min='1900' max='2020'/>
</td>
<td>
<select id='category' name="category">
<option value='' selected='selected'>Category</option>
</select>
</td>
</tr>
</table>
</div>
</div>
<h3 id="chart-instruc" class="text-center" style="margin-top: 10rem;">Click on the cards above to show a chart!</h3>
<div style="height: 30rem;">
<canvas id="myChart"></canvas>
</div>
</div>
</div>
<div class="row">
<div class="col-sm-12" url-endpoin='{% url "boost:api_expenses_data" %}'>
</div>
</div>
<script src="https://cdn.jsdelivr.net/npm/chart.js"></script>
<script src="{% static 'boost/js/home.js' %}"></script>
{% endblock content %}
I hope I would be able to filter… I don’t know if it is easier to send the raw data to JS and then apply the filters with JS or if it is easier to continue with Python.