Day Ahead Prices is missing 1 row due to DST

[timgitty212]

def ENTSO_fetch(start, end, country_code):
client = EntsoePandasClient(api_key)

df_temp = client.query_day_ahead_prices(country_code, start=start, end=end)
return df_temp

start_market_date = pd.to_datetime('2023-12-31 23:00:00').tz_localize('UTC')
end_market_date = pd.to_datetime('2024-12-31 22:00:00').tz_localize('UTC')

df_market_price = ENTSO_fetch(start_market_date, end_market_date, country_code)

2024-01-01 00:00:00+01:00 53.79
2024-01-01 01:00:00+01:00 48.98
2024-01-01 02:00:00+01:00 27.45
2024-01-01 03:00:00+01:00 24.48
2024-01-01 04:00:00+01:00 24.01
...
2024-12-31 19:00:00+01:00 31.10
2024-12-31 20:00:00+01:00 29.37
2024-12-31 21:00:00+01:00 24.08
2024-12-31 22:00:00+01:00 20.62
2024-12-31 23:00:00+01:00 21.94
Length: 8783, dtype: float64

[timgitty212]

Hi does anybody know how to resolve this issue, my entsoepy is the latest with 0.6.18. Even after localizing to their timezone im still missing 1 row in the middle

[fleimgruber]

What do you mean exactly by "missing 1 row in the middle"?

[timgitty212]

start_market_date = pd.to_datetime('2023-12-31 23:00:00').tz_localize('UTC')
end_market_date = pd.to_datetime('2024-12-31 22:00:00').tz_localize('UTC')

all_dates = pd.date_range(start=df_market_price.index.min(), end=df_market_price.index.max(), freq='h') # Adjust freq as needed

Identify missing dates by comparing with the existing index

missing_rows = all_dates.difference(df_market_price.index)

Display the missing rows

print("Missing rows:", missing_rows)

Missing rows: DatetimeIndex(['2024-12-31 00:00:00+01:00'], dtype='datetime64[ns, Europe/Oslo]', freq='h')

This is the code i ran and the missing data is the one on 31st december

[fleimgruber]

Please put your code in proper python code blocks, see also: https://docs.github.com/en/get-started/writing-on-github/working-with-advanced-formatting/creating-and-highlighting-code-blocks#syntax-highlighting.

That way comments starting with # do not get interpreted as Markdown sections.

Also, please provide a coherent minimal reproducible example of code that shows your issue.

[fboerman]

hi @timgitty212 , this should not happen and I cannot reproduce this. as @fleimgruber says please provide a minimal reproducible example so I can investigate, thanks!

[mstuyven]

@fboerman I am also experiencing this issue

Steps to reproduce:

from entsoe import EntsoePandasClient
from bs4 import XMLParsedAsHTMLWarning
import warnings

warnings.filterwarnings("ignore", category=XMLParsedAsHTMLWarning)

entsoe_api_key = "<API KEY>"
client = EntsoePandasClient(api_key=entsoe_api_key)
da = client.query_day_ahead_prices(
    country_code="BE",
    start=pd.Timestamp("2024-01-01", tz="Europe/Brussels"),
    end=pd.Timestamp("2025-01-02", tz="Europe/Brussels"),
    resolution="60min"
).to_frame(name="day_ahead")
da.index.name = "timestamp"
da.index = da.index.map(lambda x: x.isoformat())

da.to_csv("data/day_ahead.csv")

Observe that 2024-12-31T00:00:00+01:00 is missing and that there are only 23 rows for 2024-12-31

Screenshot

Interestingly, the missing timestamp changes when you change the start timestamp. For example, if you change the start in the above code to "2023-12-01", the missing time will be 2024-11-30T00:00:00+01:00.

[fboerman]

hi all, it seems this bug only occurs when you request large amounts of data. I am a little stumped by this and need to do more debugging on why this happens. If anybody has any help let me know!

[mstuyven]

From my limited testing this seems to be caused by the frame = frame.loc[interval_mask] filtering in year_limited. It assumes neigboring frames share a common timestamp and tries to remove those. This might also affect other function that use a combination of year_limited and documents_limited, or it might be specific to query_day_ahead_prices, not sure.

[fleimgruber]

Hi @mstuyven, thanks for the analysis! I can confirm that it is an issue with interval_mask, but in

entsoe-py/entsoe/decorators.py

Lines 138 to 144 in 0f4aac8

	interval_mask = frame.index <= _end
	else:
	interval_mask = (
	(frame.index <= _end)
	& (frame.index > _start)
	)
	frame = frame.loc[interval_mask]

at

entsoe-py/entsoe/decorators.py

Line 142 in 0f4aac8

& (frame.index > _start)

where at the problematic block iteration _start has the value Timestamp('2024-12-31 00:00:00+0100', tz='Europe/Brussels') which is the missing index in the reproduction and the first index of frame and thus is not included in interval_mask.

I also found the related report #363 but I am not sure that #368 is the correct fix.

As for possible fixes, I would propose a final check on frame to drop duplicates which should be more robust with respect to upstream changes and edge cases of masking logic as was surfaced here.

edit given the usage of year_limited for most of the public API functions and the effects of changes @mstuyven mentions, I would like to take this as an opportunity to bump #131.