# Exclude last value in a line graph of kibana
**URL:** https://discuss.elastic.co/t/exclude-last-value-in-a-line-graph-of-kibana/225348
**Category:** Kibana
**Created:** [March 27, 2020, 7:52am UTC](https://discuss.elastic.co/t/exclude-last-value-in-a-line-graph-of-kibana/225348 "2020-03-27T07:52:12Z")
**Posts on this page:** 20
**Page:** 1
### Author:  [@vidhyadhara](https://discuss.elastic.co/u/vidhyadhara)
#### Post date: [March 27, 2020, 7:52am UTC](https://discuss.elastic.co/t/exclude-last-value-in-a-line-graph-of-kibana/225348/1 "2020-03-27T07:52:12Z")
Hello,
Here is my situation-
I need to plot a line graph in kibana, which will have 2 lines. First Line(say line A) will represent 10 data points, Second line(say line B) should only represent 9 data points.
To be exact with my question, I'm plotting some numerical values against a release id of my product. Line A shows the actual values related to the product and line B will show the predicted value my product is making. So basically I'm trying to predict value for the next release of my project so as of now i'l not be having actual value for it. So line B which represent the actual values which will have 9 points and line A which represent the predicted values will have one point more than line A i.e., 10 here.
I'm importing data from a excel sheet, if i leave the cell empty for the 10th value, That release id will not be shown at all in kibana so i'm forced to give a value so I've given 0(zero). So in the graph the line drops down to 0, which i don't want to happen.
I don't want the purple line in graph in graph to hit zero,instead i want it to stop at its value in point 1022 on x-axis. I'm new to kibana and looking forward for some help.
I've also attached the screenshot of the graph i'm getting now.
Thanks in advance.
---
### Author:  [@flash1293](https://discuss.elastic.co/u/flash1293)
#### Post date: [March 27, 2020, 9:03am UTC](https://discuss.elastic.co/t/exclude-last-value-in-a-line-graph-of-kibana/225348/2 "2020-03-27T09:03:27Z")
Hi @vidhyadhara,
this depends on the data structure and the aggregations you are using to build that chart.
I managed to get to this kind of chart using the following:
These are the documents:
```auto
[
{
"rel" : 1,
"actual" : 5,
"prediction" : 6
},
{
"rel" : 2,
"actual" : 5,
"prediction" : 6
},
{
"rel" : 3,
"actual" : 5,
"prediction" : 6
},
{
"rel" : 4,
"actual" : 5,
"prediction" : 6
},
{
"rel" : 5,
"actual" : 5,
"prediction" : 6
},
{
"rel" : 6,
"prediction" : 6
}
]
```
Notice for the last document, I'm omitting the `actual` key completely.
Then I'm using the following chart config:
For the last bucket of the x axis (`rel: 6`), `actual` won't have a value and won't produce a data point:
As you can see, the green line stops one data point early.
Let me know whether that helps your use case.
---
### Author:  [@vidhyadhara](https://discuss.elastic.co/u/vidhyadhara)
#### Post date: [March 27, 2020, 9:19am UTC](https://discuss.elastic.co/t/exclude-last-value-in-a-line-graph-of-kibana/225348/3 "2020-03-27T09:19:18Z")
Thank you so much for your quick response @flash1293... I wonder why this isn't working for me. I'm also using MAX aggregation. May be i'm doing something wrong in pushing data from python script.
I'm pushing data which is present in a excel sheet(Refer the attachment for data) using the Python script(also attached in attachment). If you can see if I've left last actual value blank in excel, in that case when i try to plot graph of release ID n X-axis release ID 6(Because there is no actual value for release id 6) will not be shown at all on the graph at all. Can you help on this?
Thank you.
 
---
### Author:  [@flash1293](https://discuss.elastic.co/u/flash1293)
#### Post date: [March 27, 2020, 10:33am UTC](https://discuss.elastic.co/t/exclude-last-value-in-a-line-graph-of-kibana/225348/4 "2020-03-27T10:33:43Z")
Maybe panda fills in a zero automatically for the missing value.
Log your data frame and the resulting JSON to see what exactly is in there and make sure its not `0`.
Maybe this can help: [https://stackoverflow.com/questions/48956575/turn-zero-values-to-empty-cells-in-pandas](https://stackoverflow.com/questions/48956575/turn-zero-values-to-empty-cells-in-pandas)
---
### Author:  [@vidhyadhara](https://discuss.elastic.co/u/vidhyadhara)
#### Post date: [March 27, 2020, 11:34am UTC](https://discuss.elastic.co/t/exclude-last-value-in-a-line-graph-of-kibana/225348/5 "2020-03-27T11:34:19Z")
No... That value went to zero because i gave the value as zero.
I think we have a misunderstanding here, I filled that cell value with zero because if i don't fill up the cell value then that release ID will not be present at all. I think i can explain better with the screenshots of what i'm telling. You can also see the aggregations in the screenshot.
if you can observe I've 6 Rel ID put only 5 show up in my kibana graph x axis. To confirm again I'm using MAX aggregation itself. I've also attached output of running the python script also.
     
---
### Author:  [@flash1293](https://discuss.elastic.co/u/flash1293)
#### Post date: [March 27, 2020, 11:59am UTC](https://discuss.elastic.co/t/exclude-last-value-in-a-line-graph-of-kibana/225348/6 "2020-03-27T11:59:11Z")
For me it looks like the problem is on the python side - I’m not familiar with pandas so I’m not sure where to look. Could you check the index in discover to see what actually gets ingested in Elasticsearch?
---
### Author:  [@vidhyadhara](https://discuss.elastic.co/u/vidhyadhara)
#### Post date: [March 27, 2020, 12:06pm UTC](https://discuss.elastic.co/t/exclude-last-value-in-a-line-graph-of-kibana/225348/7 "2020-03-27T12:06:06Z")
This is what is being pushed to the elasticsearch. Thanks for reminding me to check the discover tab.
6th release id data is not being pushed at all. Thank you for that. I really appreciate you for helping me out.
Okay forget about pandas and python, I want to push/ingest data to elasticsearch from my local database (or excel for now) without using logstash, what would you recommend?
---
### Author:  [@flash1293](https://discuss.elastic.co/u/flash1293)
#### Post date: [March 27, 2020, 12:31pm UTC](https://discuss.elastic.co/t/exclude-last-value-in-a-line-graph-of-kibana/225348/8 "2020-03-27T12:31:23Z")
If it doesn't have to happen programmatically, you can use the "File Data Visualizer" which allows upload of CSV files directly in Kibana as a way of ingesting data: [https://www.elastic.co/blog/importing-csv-and-log-data-into-elasticsearch-with-file-data-visualizer](https://www.elastic.co/blog/importing-csv-and-log-data-into-elasticsearch-with-file-data-visualizer)
Otherwise, you can piece those things together with just about any programming language - I'm sure that your python/pandas approach isn't necessarily wrong or anything, and I'm sure someone can help you out with it in the Stackoverflow python forum.
To debug where exactly the problem happens, check whether the `df.to_dict` actually iterates over all of the entries , whether the stuff passed into `e.bulk` is what you expect and so on.
Completely personal: As I'm most familiar with JS, I would probably go with a node.js script using [https://www.npmjs.com/package/excel](https://www.npmjs.com/package/excel) to load the data and [https://www.npmjs.com/package/elasticsearch](https://www.npmjs.com/package/elasticsearch) to ingest it in Elasticsearch
---
### Author:  [@flash1293](https://discuss.elastic.co/u/flash1293)
#### Post date: [March 27, 2020, 12:32pm UTC](https://discuss.elastic.co/t/exclude-last-value-in-a-line-graph-of-kibana/225348/9 "2020-03-27T12:32:54Z")
Another thought I just had - if you are using a specified mapping for the `dar21` index, could you share it here (together with the input of `e.bulk`)? Maybe it's passing something in here which is rejected by Elasticsearch.
---
### Author:  [@vidhyadhara](https://discuss.elastic.co/u/vidhyadhara)
#### Post date: [March 27, 2020, 12:40pm UTC](https://discuss.elastic.co/t/exclude-last-value-in-a-line-graph-of-kibana/225348/10 "2020-03-27T12:40:15Z")
@flash1293 Thank you so much for your suggestions I'l surely look into them. I don't know anything about specified mapping and stuff so i think i'm not doing anything like that 😆 . I've shared all the files i'm using now and I'l be happy to provide what ever further info. you need.
---
### Author:  [@vidhyadhara](https://discuss.elastic.co/u/vidhyadhara)
#### Post date: [March 27, 2020, 12:52pm UTC](https://discuss.elastic.co/t/exclude-last-value-in-a-line-graph-of-kibana/225348/11 "2020-03-27T12:52:40Z")
Hello @flash1293,
I just looked into "File Data Visualizer", but that is limited to files. I actually want to connect my database in future, So file data visualizer wont help me much. Since the data is not getting ingested at all(I mean the release ID 6), I think there is problem with my python script only. I'l check it. Mean while i'l so check into your other suggestions.
---
### Author:  [@vidhyadhara](https://discuss.elastic.co/u/vidhyadhara)
#### Post date: [March 27, 2020, 2:02pm UTC](https://discuss.elastic.co/t/exclude-last-value-in-a-line-graph-of-kibana/225348/12 "2020-03-27T14:02:33Z")
Hi @flash1293,
I looked into the script and found that json cant handle "NaN" value...Its asking me to enable non numeric values. Have attached a screenshot of it also. Let me know if you have any idea on it.
---
### Author:  [@flash1293](https://discuss.elastic.co/u/flash1293)
#### Post date: [March 27, 2020, 2:08pm UTC](https://discuss.elastic.co/t/exclude-last-value-in-a-line-graph-of-kibana/225348/13 "2020-03-27T14:08:54Z")
That `NaN` has to be removed before you are sending it to Elasticsearch.
You could try setting it to an empty string in your python script like this:
(slight variation of [https://stackoverflow.com/questions/48956575/turn-zero-values-to-empty-cells-in-pandas](https://stackoverflow.com/questions/48956575/turn-zero-values-to-empty-cells-in-pandas) linked above)
```python
import numpy as np
df[df.eq(np.nan)] = ''
```
---
### Author:  [@vidhyadhara](https://discuss.elastic.co/u/vidhyadhara)
#### Post date: [March 27, 2020, 4:39pm UTC](https://discuss.elastic.co/t/exclude-last-value-in-a-line-graph-of-kibana/225348/14 "2020-03-27T16:39:39Z")
Looks like elasticsearch index doesn't store and handle null values and treats empty values with NaN. I'm actually blank now. df[df.eq(np.nan)] = '' also didnt work actually.
---
### Author:  [@flash1293](https://discuss.elastic.co/u/flash1293)
#### Post date: [March 27, 2020, 4:41pm UTC](https://discuss.elastic.co/t/exclude-last-value-in-a-line-graph-of-kibana/225348/15 "2020-03-27T16:41:18Z")
Could you share the output of `rec_to_actions(df)` with setting the nan values to empty strings?
---
### Author:  [@vidhyadhara](https://discuss.elastic.co/u/vidhyadhara)
#### Post date: [March 27, 2020, 4:45pm UTC](https://discuss.elastic.co/t/exclude-last-value-in-a-line-graph-of-kibana/225348/16 "2020-03-27T16:45:51Z")
Here it is @flash1293.
Hope this is what you asked.
---
### Author:  [@flash1293](https://discuss.elastic.co/u/flash1293)
#### Post date: [March 27, 2020, 4:48pm UTC](https://discuss.elastic.co/t/exclude-last-value-in-a-line-graph-of-kibana/225348/17 "2020-03-27T16:48:56Z")
That's the response from Elasticsearch, I was talking about the request. Anyway, it's clear in the error message that it's still sending that `NaN` value - you have to replace it with an empty string.
I'm definitely the wrong person to help with python problems, I suggest posting this question on Stackoverflow or get help from a colleague.
---
### Author:  [@vidhyadhara](https://discuss.elastic.co/u/vidhyadhara)
#### Post date: [March 27, 2020, 4:51pm UTC](https://discuss.elastic.co/t/exclude-last-value-in-a-line-graph-of-kibana/225348/18 "2020-03-27T16:51:52Z")
I totally appreciate your concern and happy that you lend a hand in helping me solve this error. Thank you @flash1293
Will keep posted if i get a solution for this.
---
### Author:  [@vidhyadhara](https://discuss.elastic.co/u/vidhyadhara)
#### Post date: [April 2, 2020, 1:22pm UTC](https://discuss.elastic.co/t/exclude-last-value-in-a-line-graph-of-kibana/225348/19 "2020-04-02T13:22:45Z")
I was using python scripts because i was not to figure out how to connect logstash to AWS ES, fortunately i found out how to connect logstash to AWS ES. Logstash handles even the NULL values and doesn't throw up any error. So my problem is solved.
---
### Author:  [@flash1293](https://discuss.elastic.co/u/flash1293)
#### Post date: [April 2, 2020, 1:34pm UTC](https://discuss.elastic.co/t/exclude-last-value-in-a-line-graph-of-kibana/225348/20 "2020-04-02T13:34:26Z")
Glad it worked out for you!
[Next page](https://discuss.elastic.co/t/exclude-last-value-in-a-line-graph-of-kibana/225348.md?page=2)