How do i delete empty dates from a Bokeh chart?

[Dorivaldo_Gomes]

Hi there…
I’m trying to render a chart, which is based on a model object on my django.models file.

I tried the following on my get() function:

def get(self, request):

        retrive=HistoricalData.objects.all()
        get_symbol=retrive.filter(instrument__symbol="BTUSD").order_by('datetime')
        fields=get_symbol.values('datetime','opening_price','closing_price','min_price','max_price','volume')
        df=pd.DataFrame.from_records(fields)
        source = ColumnDataSource(data=df)
        plot = figure(title="New title", width=780, height=400,
                      x_axis_label="Date", y_axis_label="Max Price", x_axis_type="datetime")
       
        plot.line(x='datetime', y='opening_price', source=source, line_width=3)

        #Tried to implement the line that follows from the documentation example, but it does not work for me.
        
        plot.xaxis.major_label_overrides = {i: date.strftime('%b %d') for i, date in zip(df.index, df["datetime"])}
        plot.xaxis.ticker = list(range(df.index[0], df.index[-1], 5))
        plot.toolbar.autohide = True

        script, div = components(plot)
        if user.is_authenticated:
            return render(request,self.template_name, {'form':form,'days_of_month': [i for i in range(1,32)],
         'script':script, 'div':div,
        })
        return redirect('home')

You can find a more complete code here: django - Bokeh, how do i remove datetimes without corresponding values? - Stack Overflow

This is the final result, you can see those long lines in blue on the chart, they are here because there are some days that are not present in my database, the days that the market is closed, and the Bokeh chart understands that it needs to render these days anyway because i pass the data of the x axis as ‘datetime’ data.

Note that i already tried to turn the ‘datetime’ field into a String column using the line:

df[‘datetime’] = df[‘datetime’].astype(str)

But it does not work, it shows an empty chart.

I need you guys to suggest me an approach for example, of turning my ‘x’ axis into a string axis, so that my data renders without those empty days.

Thank you in advance.

[nmasnadi]

One possible solution is to fill in the missing days in your dataframe with null values. See this example:

from bokeh.plotting import figure
from bokeh.models import ColumnDataSource
df = pd.DataFrame(
    {'date': ['2024-04-01', '2024-04-02', '2024-04-03', '2024-04-08', '2024-04-09', '2024-04-10'],
     'value': [3, 5, 4, 3, 5, 4]
    }
)
df['date'] = pd.to_datetime(df['date'])
# fills in the missing dates with null values
df = df.merge(pd.DataFrame({'date': pd.date_range(df['date'].min(), df['date'].max())}), how='right')

source = ColumnDataSource(data=df)
p = figure(x_axis_type='datetime', height=300)
p.line(x='date', y='value', source=source, width=3)
show(p)

Here is how it looks:

And this is what it looks like without filling missing dates with Null values:

[Dorivaldo_Gomes]

Thank you very much Mr. @nmasnadi for your contribution, i appreciate the attention and the time that you dedicated to me, it means a lot. Thank you.

When i try to implement your suggestion this way:

def get(self, request):
        user=request.user
        historical_me=FinancialInstrument.objects.all()
        form = self.form_class(historical_me=historical_me)

        retrive=HistoricalData.objects.all()
        get_symbol=retrive.filter(instrument__symbol="BTUSD").order_by('datetime')
        fields=get_symbol.values('datetime','opening_price','closing_price','min_price','max_price','volume')
        df=pd.DataFrame.from_records(fields)

        df['datetime'] = pd.to_datetime(df['datetime'])
        df.to_csv('data.csv', index=False)  # Save DataFrame to a CSV file without including the index
        df = df.merge(pd.DataFrame({'datetime': pd.date_range(df['datetime'].min(), df['datetime'].max())}), how='right')

        source = ColumnDataSource(data=df)
        plot = figure(title="New title", width=780, height=400,
                      x_axis_label="Date", y_axis_label="Max Price", x_axis_type="datetime",
                      )

        plot.line(x='datetime', y='opening_price', source=source, line_width=3)
        #plot.xaxis.major_label_overrides = {i: date.strftime('%b %d') for i, date in zip(df.index, df["datetime"])}
        #plot.xaxis.ticker = list(range(df.index[0], df.index[-1], 1))
        
        plot.toolbar.autohide = True

        script, div = components(plot)
        if user.is_authenticated:
            return render(request,self.template_name, {'form':form,'days_of_month': [i for i in range(1,32)],
         'script':script, 'div':div,
        })
        return redirect('home')

I get the following chart, which leads me to some doubts:

Thank you dear @nmasnadi

[Bryan]

@Dorivaldo_Gomes

Note that i already tried to turn the ‘datetime’ field into a String column using the line:

df[‘datetime’] = df[‘datetime’].astype(str)

But it does not work, it shows an empty chart.

“Converting to a string” means using a Categorical axis. You will have to define an appropriate factor range with all the date values that are present as factors. See Categorical plots — Bokeh 3.4.1 Documentation for lots of information about how to set up categorical plots.

You can see a complete example of using categorical values to skip “missing dates” here: missing_dates — Bokeh 3.4.1 Documentation

[nmasnadi]

oops sorry I didn’t realize your data is in datetime. My solution only works if there is one value per each date. The example Bryan shared is the way to do it.