Skip to content

Commit 4609453

Browse files
RudraPratik30RudraPratik30
authored
[docs]: Transforming Data for Charts Docs (ToolJet#10915)
* transforming data for charts docs * updated right component library
1 parent 5cb279e commit 4609453

File tree

14 files changed

+326
-1
lines changed

14 files changed

+326
-1
lines changed

‎docs/docs/widgets/chart/transforming-data-for-charts.md‎

Lines changed: 324 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,324 @@
1+
---
2+
id: transforming-data-for-charts
3+
title: Transforming Data for Charts
4+
---
5+
6+
This guide explains how to transform data using RunJS and RunPy in ToolJet and visualize it using the chart component. The chart component in ToolJet enables the creation of various types of charts, with the option to integrate with Plotly for enhanced customization and advanced visualizations. While ToolJet provides the ability to connect to multiple databases, APIs, and data sources for integration, this guide will focus on using ToolJet DB to fetch the required data.
7+
8+
<div style={{paddingTop:'24px'}}>
9+
10+
## Plotting a Simple Pie Chart
11+
12+
To create a simple pie chart, the data has been stored in the table with the following structure in ToolJet DB:
13+
14+
| <div style={{ width:"100px"}}> id </div> | <div style={{ width:"550px"}}> course </div> |
15+
|:-- | :---- |
16+
| 1 | Maths |
17+
| 2 | Full Stack Web Development |
18+
| 3 | Digital Marketing Strategy |
19+
| 4 | Business Ethics |
20+
| 5 | Maths |
21+
| 6 | Full Stack Web Development |
22+
| 7 | Digital Marketing Strategy |
23+
| 8 | Financial Accounting |
24+
| 9 | Maths |
25+
| 10 | Chemistry |
26+
| 11 | Financial Accounting |
27+
| 12 | Physics |
28+
| 13 | Full Stack Web Development |
29+
| 14 | Maths |
30+
31+
### Query to Fetch Data
32+
33+
1. Click on **+ Add** button of the query manager at the bottom panel of the editor.
34+
2. Select the ToolJet Database as the data source.
35+
3. Select your table from the dropdown.
36+
4. Select the **List rows** operation.
37+
5. Click on the **Preview** button to preview the output or Click on the **Run** button to trigger the query.
38+
39+
<img className="screenshot-full" src="/img/widgets/chart/transforming-data/pie-fetch-data.png" alt="Fetch Data Query" />
40+
41+
This query will fetch the data from the ToolJet DB.
42+
43+
### Transform the Data
44+
45+
To restructure the data into a format compatible with the chart component, we will be using RunJS transformation.
46+
47+
Create a new **RunJS** Query and add the following code:
48+
49+
```js
50+
await queries.getRevenueDetails.run();
51+
52+
let data = queries.getRevenueDetails.getData();
53+
54+
const courseCounts = data.reduce((counts, obj) => {
55+
if (obj.course) {
56+
counts[obj.course] = (counts[obj.course] || 0) + 1;
57+
}
58+
return counts;
59+
}, {});
60+
61+
const courseData = Object.keys(courseCounts).map(course => ({
62+
x: course,
63+
y: courseCounts[course]
64+
}));
65+
66+
return {courseData};
67+
```
68+
69+
<img className="screenshot-full" src="/img/widgets/chart/transforming-data/pie-js-query.png" alt="Transform JS Query" />
70+
71+
This query will calculate the number of each course and return an array of objects that can be utilized to plot the pie chart.
72+
73+
### Plotting Pie Chart
74+
75+
1. Add a chart component from the component library available on right to the canvas.
76+
2. Under Properties section select **Pie** as the chart type from the dropdown.
77+
3. Under chart data section input `{{queries.<Your RunJS Query Name>.data.courseData}}` to input the data from the query.
78+
79+
<img className="screenshot-full" src="/img/widgets/chart/transforming-data/pie-chart.png" alt="Pie Chart" />
80+
81+
</div>
82+
83+
<div style={{paddingTop:'24px'}}>
84+
85+
## Plotting a Line Chart with RunJS Transformation
86+
87+
To create the line chart, the data has been stored in the table with the following structure in ToolJet DB:
88+
89+
| <div style={{ width:"20px"}}> id </div> | <div style={{ width:"300px"}}> x </div> | <div style={{ width:"80px"}}> y </div> | <div style={{ width:"150px"}}> region </div> | <div style={{ width:"100px"}}> rdate </div>|
90+
|:---|:--|:--|:-------|:-----|
91+
| 1 | Social Media Engagement | 15 | North America | 15-01-2024 |
92+
| 2 | Email Marketing | 10 | Europe | 10-02-2024 |
93+
| 3 | SEO Optimization | 20 | Asia | 05-03-2024 |
94+
| 4 | Content Creation | 25 | North America | 20-04-2024 |
95+
| 5 | Paid Advertising | 30 | Europe | 12-05-2024 |
96+
| 6 | Analytics and Reporting | 18 | Asia | 18-06-2024 |
97+
| 7 | Influencer Marketing | 12 | North America | 30-07-2024 |
98+
| 8 | Market Research | 22 | Europe | 25-08-2024 |
99+
| 9 | Web Development | 17 | Asia | 15-09-2024 |
100+
| 10 | Customer Relationship Management | 28 | North America | 02-10-2024 |
101+
102+
### Query to Fetch Data
103+
104+
1. Click on **+ Add** button of the query manager at the bottom panel of the editor.
105+
2. Select the ToolJet Database as the data source.
106+
3. Select your table from the dropdown.
107+
4. Select the **List rows** operation.
108+
5. Click on the **Preview** button to preview the output or Click on the **Run** button to trigger the query.
109+
110+
<img className="screenshot-full" src="/img/widgets/chart/transforming-data/line-fetch-data.png" alt="Fetch Data Query" />
111+
112+
This query will fetch the data from the ToolJet DB.
113+
114+
### Transform the Data
115+
116+
Create a new **RunJS** Query and add the following code:
117+
118+
```js
119+
const data = queries.getLinechartData.data;
120+
121+
const calculateAverage = (arr) => arr.reduce((sum, item) => sum + item.y, 0) / arr.length;
122+
123+
const regionData = data.filter(item => item.region !== "Asia");
124+
125+
const transformedData = regionData.map(item => {
126+
if (item.rdate && typeof item.rdate === 'string') {
127+
const dateParts = item.rdate.split('-');
128+
129+
if (dateParts.length === 3) {
130+
const year = dateParts[2];
131+
const month = dateParts[1];
132+
return {
133+
x: `${year}-${month}`,
134+
y: item.y && !isNaN(item.y) ? item.y : 0
135+
};
136+
}
137+
}
138+
139+
return { x: 'Invalid Date', y: 0 };
140+
});
141+
142+
const validData = transformedData.filter(item => item.x !== 'Invalid Date');
143+
const averageY = calculateAverage(validData);
144+
145+
const finalData = transformedData.map(item => ({
146+
x: item.x,
147+
y: item.y - averageY
148+
}));
149+
150+
return finalData;
151+
```
152+
153+
<img className="screenshot-full" src="/img/widgets/chart/transforming-data/line-js-query.png" alt="Transform JS Query" style={{marginBottom:'15px'}}/>
154+
155+
### Plotting Line Chart
156+
157+
1. Add a chart component from the component library available on right to the canvas.
158+
2. Under Properties section select **Line** as the chart type from the dropdown.
159+
3. Under chart data section input `{{queries.<Your RunJS Query Name>.data}}` to input the data from the query.
160+
161+
<img className="screenshot-full" src="/img/widgets/chart/transforming-data/line-chart.png" alt="Line Chart" />
162+
163+
</div>
164+
165+
<div style={{paddingTop:'24px'}}>
166+
167+
## Plotting Candle Stick Chart Using Plotly
168+
169+
To create a candlestick chart, the data has been stored in the table with the following structure in ToolJet DB:
170+
171+
| <div style={{ width:"60px"}}> id </div> | <div style={{ width:"150px"}}> sdate </div> | <div style={{ width:"100px"}}> open </div> | <div style={{ width:"100px"}}> high </div> | <div style={{ width:"100px"}}> low </div> | <div style={{ width:"100px"}}> sclose </div> |
172+
|:---|:------|:-----|:-----|:----|:-------|
173+
| 1 | 2024-04-02 | 115 | 125 | 115 | 120 |
174+
| 2 | 2024-04-03 | 120 | 130 | 120 | 125 |
175+
| 3 | 2024-04-04 | 125 | 135 | 125 | 130 |
176+
177+
### Query to Fetch Data
178+
179+
1. Click on **+ Add** button of the query manager at the bottom panel of the editor.
180+
2. Select the ToolJet Database as the data source.
181+
3. Select your table from the dropdown.
182+
4. Select the **List rows** operation.
183+
5. Click on the **Preview** button to preview the output or Click on the **Run** button to trigger the query.
184+
185+
<img className="screenshot-full" src="/img/widgets/chart/transforming-data/cs-fetch-data.png" alt="Fetch Data Query" />
186+
187+
This query will fetch the data from the ToolJet DB.
188+
189+
### Transform the Data
190+
191+
To restructure the data into a format compatible with the chart component, we will be using RunJS transformation.
192+
193+
Create a new **RunJS** Query and add the following code:
194+
195+
```js
196+
const dbData = queries.getCandlestickData.data;
197+
198+
if (!Array.isArray(dbData) || dbData.length === 0) {
199+
return { plotData: [] };
200+
}
201+
202+
let dates = [];
203+
let openPrices = [];
204+
let highPrices = [];
205+
let lowPrices = [];
206+
let closePrices = [];
207+
208+
dbData.forEach(row => {
209+
dates.push(String(row.sdate));
210+
openPrices.push(row.open);
211+
highPrices.push(row.high);
212+
lowPrices.push(row.low);
213+
closePrices.push(row.sclose);
214+
});
215+
216+
const transformedData = [
217+
{
218+
x: dates,
219+
open: openPrices,
220+
high: highPrices,
221+
low: lowPrices,
222+
close: closePrices,
223+
type: 'candlestick'
224+
}
225+
];
226+
227+
let result = {
228+
data: transformedData
229+
};
230+
231+
return JSON.stringify(result)
232+
```
233+
234+
<img className="screenshot-full" src="/img/widgets/chart/transforming-data/cs-js-query.png" alt="Transform JS Query" style={{marginBottom:'15px'}}/>
235+
236+
### Plotting Candlestick Chart
237+
238+
1. Add a chart component from the component library available on right to the canvas.
239+
2. Enable use plotly JSON schema under Plotly JSON Chart Schema section.
240+
3. Under JSON Description section input `{{queries.<Your RunJS Query Name>.data}}` to input the data from the query.
241+
242+
<img className="screenshot-full" src="/img/widgets/chart/transforming-data/cs-chart.png" alt="Candlestick Chart" />
243+
244+
</div>
245+
246+
<div style={{paddingTop:'24px'}}>
247+
248+
## Plotting heatmap Chart Using Plotly
249+
250+
To create a heatmap chart, the data has been stored in the table with the following structure in ToolJet DB:
251+
252+
| <div style={{ width:"60px"}}> id </div> | <div style={{ width:"150px"}}> x </div> | <div style={{ width:"100px"}}> y </div> | <div style={{ width:"100px"}}> value </div> |
253+
|:---|:------|:-----|:-----|
254+
| 1 | 0 | 0 | 0.32 |
255+
| 2 | 0 | 1 | 0.95 |
256+
| 3 | 0 | 2 | 0.57 |
257+
| 4 | 0 | 3 | 0.08 |
258+
| 5 | 0 | 4 | 0.82 |
259+
| 6 | 0 | 5 | 0.33 |
260+
| 7 | 0 | 6 | 0.9 |
261+
| 8 | 0 | 7 | 0.11 |
262+
| 9 | 0 | 8 | 0.73 |
263+
| 10 | 0 | 9 | 0.39 |
264+
265+
### Query to Fetch Data
266+
267+
1. Click on **+ Add** button of the query manager at the bottom panel of the editor.
268+
2. Select the ToolJet Database as the data source.
269+
3. Select your table from the dropdown.
270+
4. Select the **List rows** operation.
271+
5. Click on the **Preview** button to preview the output or Click on the **Run** button to trigger the query.
272+
273+
<img className="screenshot-full" src="/img/widgets/chart/transforming-data/hm-fetch-data.png" alt="Fetch Data Query" />
274+
275+
This query will fetch the data from the ToolJet DB.
276+
277+
### Transform the Data
278+
279+
To restructure the data into a format compatible with the chart component, we will be using RunPy transformation.
280+
281+
Create a new **RunPy** Query and add the following code:
282+
283+
```py
284+
import pandas as pd
285+
import json
286+
287+
data_raw = queries.fetchHeatmapData.getData()
288+
289+
data = data_raw.to_py() if hasattr(data_raw, 'to_py') else list(data_raw)
290+
291+
df = pd.DataFrame(data)
292+
heatmap_data = df.pivot(index='y', columns='x', values='value')
293+
294+
x_labels = [f"Column {i}" for i in heatmap_data.columns.tolist()]
295+
y_labels = [f"Row {i}" for i in heatmap_data.index.tolist()]
296+
297+
output = {
298+
"data": [
299+
{
300+
"z": heatmap_data.values.tolist(),
301+
"x": x_labels,
302+
"y": y_labels,
303+
"type": "heatmap"
304+
}
305+
]
306+
}
307+
308+
output_str = json.dumps(output)
309+
310+
output_str
311+
```
312+
313+
<img className="screenshot-full" src="/img/widgets/chart/transforming-data/hm-py-query.png" alt="Transform JS Query" style={{marginBottom:'15px'}}/>
314+
315+
### Plotting Heatmap Chart
316+
317+
1. Add a chart component from the component library available on right to the canvas.
318+
2. Enable use plotly JSON schema under Plotly JSON Chart Schema section.
319+
3. Under JSON Description section input `{{queries.<Your RunJS Query Name>.data}}` to input the data from the query.
320+
321+
<img className="screenshot-full" src="/img/widgets/chart/transforming-data/hm-chart.png" alt="Heatmap Chart" />
322+
323+
324+
</div>

‎docs/sidebars.js‎

Lines changed: 2 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -115,7 +115,8 @@ const sidebars = {
115115
'label': 'Chart',
116116
'items': [
117117
'widgets/chart/chart-properties',
118-
'widgets/chart/chart-examples'
118+
'widgets/chart/chart-examples',
119+
'widgets/chart/transforming-data-for-charts'
119120
]
120121
},
121122
'widgets/checkbox',

‎docs/static/img/widgets/chart/transforming-data/cs-chart.png‎

33.4 KB
Loading

‎docs/static/img/widgets/chart/transforming-data/cs-fetch-data.png‎

9.57 KB
Loading

‎docs/static/img/widgets/chart/transforming-data/cs-js-query.png‎

38.3 KB
Loading

‎docs/static/img/widgets/chart/transforming-data/hm-chart.png‎

45.9 KB
Loading

‎docs/static/img/widgets/chart/transforming-data/hm-fetch-data.png‎

9.54 KB
Loading

‎docs/static/img/widgets/chart/transforming-data/hm-py-query.png‎

56.2 KB
Loading

‎docs/static/img/widgets/chart/transforming-data/line-chart.png‎

59 KB
Loading

‎docs/static/img/widgets/chart/transforming-data/line-fetch-data.png‎

9.54 KB
Loading

0 commit comments

Comments
 (0)