How to set xticks for the index of string with hvplot

Question

I have a dataframe region_cumulative_df_sel as below:

Month-Day  regions  RAIN_PERCENTILE_25  RAIN_PERCENTILE_50  RAIN_PERCENTILE_75  RAIN_MEAN   RAIN_MEDIAN
07-01      1        0.0611691028        0.2811064720        1.9487996101    1.4330813885    0.2873695195
07-02      1        0.0945720226        0.8130480051        4.5959815979    2.9420840740    1.0614821911
07-03      1        0.2845511734        1.1912839413        5.5803232193    3.7756001949    1.1988518238
07-04      1        0.3402922750        3.2274529934        7.4262523651    5.2195668221    3.2781836987
07-05      1        0.4680584669        5.2418060303        8.6639881134    6.9092760086    5.3968687057
07-06      1        2.4329853058        7.3453550339        10.8091869354   8.7898645401    7.5020875931
... ...
... ...
... ...
06-27      1        382.7809448242      440.1162109375      512.6233520508  466.4956665039  445.0971069336
06-28      1        383.8329162598      446.2222900391      513.2116699219  467.9851379395  451.1973266602
06-29      1        385.7786254883      449.5384826660      513.4027099609  469.5671691895  451.2281188965
06-30      1        386.7952270508      450.6524658203      514.0201416016  471.2863159180  451.2484741211

The index "Month-Day" is a type of String indicating the first day and the last day of a calendar year instead of type of datetime.

I need to use hvplot to develop an interactive plot.

region_cumulative_df_sel.hvplot(width=900)

It is hard to view the labels on the x axis. How can change the xticks to show only 1st of each month, e.g. "07-01", "08-01", "09-01", ... ..., "06-01"?

I tried @Redox code as below:

region_cumulative_df_sel['Month-Day'] = pd.to_datetime(region_cumulative_df_sel['Month-Day'],format="%m-%d") ##Convert to datetime

from bokeh.models.formatters import DatetimeTickFormatter
## Set format for showing x-axis ... you only need days, but in case counts change
formatter = DatetimeTickFormatter(days=["%m-%d"], months=["%m-%d"], years=["%m-%d"])

region_cumulative_df_sel.plot(x='Month-Day', xformatter=formatter, y=['RAIN_PERCENTILE_25','RAIN_PERCENTILE_50','RAIN_PERCENTILE_75','RAIN_MEAN','RAIN_MEDIAN'], width=900, ylabel="Rainfall (mm)",
                        rot=90, title="Cumulative Rainfall")

This is what I have generated.

How can I shift the xticks on the x-axis to align with the Month-Day values. Also the popup window shows "1900" as year for Month-Day column. Can the year segment be removed?

score 0 · Answer 1 · answered Sep 16 '22 at 06:10

The x-axis data is in string format. So, holoviews thinks this is categorical and plotting every row. You need to convert it to datetime and this will allow the plotting to be in the format you need. I am taking a simple example and showing how to do this... should work in your case as well...

##My month-day column is string -  07-01    07-02   07-03   07-04 ... 12-31

df['Month-Day']=pd.to_datetime(df['Month-Day'],format="%m-%d") ##Convert to datetime
df['myY']=np.random.randint(100, size=(len(df)))  ##Random Y data

from bokeh.models.formatters import DatetimeTickFormatter
## Set format for showing x-axis ... you only need days, but in case counts change
formatter = DatetimeTickFormatter(days=["%m-%d"], months=["%m-%d"], years=["%m-%d"])

##Plot graph
df.plot(x='Month-Day',xformatter=formatter)#.opts(xticks=4, xrotation=90)

Thanks for the prompt reply. I think it is getting closer to what I wanted to achieve. Please note the Month-Day is from 07-01, 07-02, 07-03, ... .., 12-29, 12-30, 12-31, 01-01, 01-02, 01-03, ... ..., 06-29, 06-30. This is from 1st of July to 30th of June of the next year. When I tried your code, the x-axis is from 01-01 to 12-31. Please see my edited question. — alextc, Sep 16 '22 at 10:53

score 0 · Answer 2 · answered Sep 27 '22 at 03:16

@Redox is on the right track here. The issue is with the way the Month-Day column is converted to a datetime; pandas is assuming the year is 1900 for every row.

Essentially you need to attach a year to the Month-Day in some way.

See the example below, this takes the first month-day string, prepends "2022-" and generates sequential daily values for every row (but there are a few ways of doing this).

code:

import pandas as pd
import numpy as np
import hvplot.pandas
from bokeh.models.formatters import DatetimeTickFormatter

dates = pd.date_range("2021-07-01", "2022-06-30", freq="D")


df = pd.DataFrame({
    "md": dates.strftime("%m-%d"),
    "ign": np.cumsum(np.random.normal(10, 5, len(dates))),
    "sup": np.cumsum(np.random.normal(20, 10, len(dates))),
    "imp": np.cumsum(np.random.normal(30, 15, len(dates))),
})

df["time"] = pd.date_range("2021-" + df.md[0], periods=len(df.index), freq="D")

formatter = DatetimeTickFormatter(
    days=["%m-%d"], months=["%m-%d"], years=["%m-%d"])

df.hvplot(x='time', xformatter=formatter, y=['ign', 'sup', 'imp'],
 width=900, ylabel="Index", rot=90, title="Cumulative ISI")

How to set xticks for the index of string with hvplot

2 Answers2