5 Temporal analysis

import pandas as pd import altair as alt

pd.to_datetime("today")

pd.to_datetime('2020-05-07 3pm')

pd.to_datetime('12.6.1929')

pd.to_datetime('12.6.1929', dayfirst=True)

sessions=["2.4.2020", "9.4.2020", "16.4.2020", "23.4.2020", "7.5.2020", "14.5.2020", "28.5.2020", "4.6.2020", "11.6.2020", "25.6.2020", "2.7.2020", "9.7.2020"] pd.to_datetime(sessions, dayfirst=True)

pd.to_datetime('2020-05-07', format="%Y-%m-%d")

# pd.to_datetime('8.5.1945 23:01', format="")

# data downloaded from OPSD - see how to filter elements on this page: # https://data.open-power-system-data.org/time_series/2020-10-06 url = "https://data.open-power-system-data.org/index.php?package=time_series&version=2020-10-06&action=customDownload&resource=3&filter%5BRegion%5D%5B%5D=DE&filter%5BVariable%5D%5B%5D=load_actual_entsoe_transparency&filter%5BVariable%5D%5B%5D=solar_generation_actual&filter%5BVariable%5D%5B%5D=wind_generation_actual&downloadCSV=Download+CSV" opsd = pd.read_csv(url, parse_dates=['utc_timestamp']) # parse timestamp column

opsd = opsd.drop(columns=["cet_cest_timestamp"])

opsd = opsd.rename({ 'utc_timestamp': 'datetime', 'DE_load_actual_entsoe_transparency': 'load', 'DE_solar_generation_actual': 'solar', 'DE_wind_generation_actual': 'wind' }, axis=1)

opsd["datetime"] = opsd["datetime"].dt.tz_convert("Europe/Berlin") opsd

datetimedatetime64[ns, Europe/Berlin]

2015-01-01 00:00:00+01:00 - 2020-10-01 01:00:00+02:00

loadfloat64

2015-01-01 00:00:00+01:00

nan

2015-01-01 01:00:00+01:00

41151

2015-01-01 02:00:00+01:00

40135

2015-01-01 03:00:00+01:00

39106

2015-01-01 04:00:00+01:00

38765

2015-01-01 05:00:00+01:00

38941

2015-01-01 06:00:00+01:00

39045

2015-01-01 07:00:00+01:00

40206

2015-01-01 08:00:00+01:00

41133

2015-01-01 09:00:00+01:00

42963

start_date = pd.to_datetime('2015-01-01').tz_localize('Europe/Berlin') end_date = pd.to_datetime('2019-12-31').tz_localize('Europe/Berlin') opsd = opsd[(opsd['datetime'] >= start_date) & (opsd['datetime'] <= end_date)] opsd

datetimedatetime64[ns, Europe/Berlin]

2015-01-01 00:00:00+01:00 - 2019-12-31 00:00:00+01:00

loadfloat64

31307.0 - 77549.0

2015-01-01 00:00:00+01:00

nan

2015-01-01 01:00:00+01:00

41151

2015-01-01 02:00:00+01:00

40135

2015-01-01 03:00:00+01:00

39106

2015-01-01 04:00:00+01:00

38765

2015-01-01 05:00:00+01:00

38941

2015-01-01 06:00:00+01:00

39045

2015-01-01 07:00:00+01:00

40206

2015-01-01 08:00:00+01:00

41133

2015-01-01 09:00:00+01:00

42963

opsd.sample(10)

datetimedatetime64[ns, Europe/Berlin]

2016-05-15 22:00:00+02:00 - 2019-12-01 04:00:00+01:00

loadfloat64

41691.0 - 73766.0

13970

2016-08-05 03:00:00+02:00

41691

38613

2019-05-28 22:00:00+02:00

54943

28517

2018-04-03 06:00:00+02:00

56575

26380

2018-01-04 04:00:00+01:00

49105

43084

2019-12-01 04:00:00+01:00

42365

12021

2016-05-15 22:00:00+02:00

43273

30357

2018-06-18 22:00:00+02:00

56281

40122

2019-07-30 19:00:00+02:00

61082

30768

2018-07-06 01:00:00+02:00

47216

25675

2017-12-05 19:00:00+01:00

73766

opsd.info()

<class 'pandas.core.frame.DataFrame'>
Index: 43801 entries, 0 to 43800
Data columns (total 4 columns):
 #   Column    Non-Null Count  Dtype                        
---  ------    --------------  -----                        
 0   datetime  43801 non-null  datetime64[ns, Europe/Berlin]
 1   load      43800 non-null  float64                      
 2   solar     43697 non-null  float64                      
 3   wind      43726 non-null  float64                      
dtypes: datetime64[ns, Europe/Berlin](1), float64(3)
memory usage: 1.7 MB

opsd = opsd.set_index("datetime")

opsd.info()

<class 'pandas.core.frame.DataFrame'>
DatetimeIndex: 43801 entries, 2015-01-01 00:00:00+01:00 to 2019-12-31 00:00:00+01:00
Data columns (total 3 columns):
 #   Column  Non-Null Count  Dtype  
---  ------  --------------  -----  
 0   load    43800 non-null  float64
 1   solar   43697 non-null  float64
 2   wind    43726 non-null  float64
dtypes: float64(3)
memory usage: 1.3 MB

opsd.index.year.unique()

opsd.loc["2017-06-21"]

loadfloat64

44933.0 - 68585.0

solarfloat64

0.0 - 25015.0

2017-06-21 00:00:00+02:00

47979

2017-06-21 01:00:00+02:00

46039

2017-06-21 02:00:00+02:00

44933

2017-06-21 03:00:00+02:00

45097

2017-06-21 04:00:00+02:00

45432

2017-06-21 05:00:00+02:00

47851

303

2017-06-21 06:00:00+02:00

55562

2095

2017-06-21 07:00:00+02:00

61846

5926

2017-06-21 08:00:00+02:00

65043

11143

2017-06-21 09:00:00+02:00

65999

16371

opsd.loc["2017-06"]

loadfloat64

33835.0 - 70267.0

solarfloat64

0.0 - 26535.0

2017-06-01 00:00:00+02:00

46114

2017-06-01 01:00:00+02:00

44291

2017-06-01 02:00:00+02:00

43148

2017-06-01 03:00:00+02:00

43231

2017-06-01 04:00:00+02:00

43845

2017-06-01 05:00:00+02:00

46426

238

2017-06-01 06:00:00+02:00

54244

2018

2017-06-01 07:00:00+02:00

60469

5949

2017-06-01 08:00:00+02:00

63710

11069

2017-06-01 09:00:00+02:00

64178

16486

opsd.loc["2017-10-04":"2017-10-06"]

loadfloat64

41195.0 - 70469.0

solarfloat64

0.0 - 11624.0

2017-10-04 00:00:00+02:00

42974

2017-10-04 01:00:00+02:00

41380

2017-10-04 02:00:00+02:00

41195

2017-10-04 03:00:00+02:00

41717

2017-10-04 04:00:00+02:00

43598

2017-10-04 05:00:00+02:00

47971

2017-10-04 06:00:00+02:00

57442

2017-10-04 07:00:00+02:00

63539

172

2017-10-04 08:00:00+02:00

65798

2295

2017-10-04 09:00:00+02:00

66820

5490

sums = opsd.resample("Y").sum() sums

loadfloat64

solarfloat64

2015-12-31 00:00:00+01:00

479454142

34796869

2016-12-31 00:00:00+01:00

486992473

34373377

2017-12-31 00:00:00+01:00

492119683

35882978

2018-12-31 00:00:00+01:00

498893806

41231973

2019-12-31 00:00:00+01:00

489392709

41878185

# first we create the sums per week weekly_sums = opsd.resample("W").sum() # then we generate the weekly means for each quarter quarterly_means = weekly_sums.resample("Q").mean() # for readability we'll revert the values back to integers quarterly_means.astype(int)

loadint64

1243071 - 10140973

solarint64

53215 - 1276058

2015-03-31 00:00:00+02:00

9292593

349758

2015-06-30 00:00:00+02:00

8748509

1013038

2015-09-30 00:00:00+02:00

8956924

978015

2015-12-31 00:00:00+01:00

9523976

326500

2016-03-31 00:00:00+02:00

9692957

314387

2016-06-30 00:00:00+02:00

8942758

986066

2016-09-30 00:00:00+02:00

8895056

1046789

2016-12-31 00:00:00+01:00

9738834

292048

2017-03-31 00:00:00+02:00

9963887

376470

2017-06-30 00:00:00+02:00

9030343

1108256

presidents = pd.read_csv("us_presidents.csv") presidents

nameobject

Harry S. Truman7.1%

Dwight D. Eisenhower7.1%

12 others85.7%

startint64

1945 - 2021

Harry S. Truman

1945

Dwight D. Eisenhower

1953

John F. Kennedy

1961

Lyndon B. Johnson

1963

Richard Nixon

1969

Gerald Ford

1974

Jimmy Carter

1977

Ronald Reagan

1981

George H. W. Bush

1989

Bill Clinton

1993

presidents['start'] = pd.to_datetime(presidents['start'], format="%Y") presidents['end'] = pd.to_datetime(presidents['end'], format="%Y")

# we add height as a parameter to make the time spans appear less clunky bars = alt.Chart(presidents).mark_bar(height=5).encode( # we use two columns for the x-positions where the bar starts and ends x='start', x2='end', # we sort by start dates and hide the axis, as names will be added below y=alt.Y('name').sort('x').axis(None), color="party" ) # the labels are added to the chart using the mark_text command # no need to specify x and y as this is re-used from the bar chart labels = bars.mark_text(align='right', dx=-5).encode( text='name' ) # both charts integrated into a layered chart through the magic of a plus sign bars + labels

days = opsd.resample("D").sum()

opsd.reset_index().melt("datetime")

datetimedatetime64[ns, Europe/Berlin]

variableobject

2015-01-01 00:00:00+01:00

load

2015-01-01 01:00:00+01:00

load

2015-01-01 02:00:00+01:00

load

2015-01-01 03:00:00+01:00

load

2015-01-01 04:00:00+01:00

load

2015-01-01 05:00:00+01:00

load

2015-01-01 06:00:00+01:00

load

2015-01-01 07:00:00+01:00

load

2015-01-01 08:00:00+01:00

load

2015-01-01 09:00:00+01:00

load

alt.data_transformers.disable_max_rows()

alt.Chart(days.reset_index().melt("datetime")).mark_circle().encode( x='datetime', y='value', color='variable', ).properties(width=700, height=400)

alt.Chart(days.reset_index().melt("datetime")).mark_line(strokeWidth=1).encode( x='datetime', y='value', color='variable', ).properties(width=700, height=400)

# we reduce the granularity to months and sum up the values months = opsd.resample("M").sum() # to make the line a bit smoother, we include an interpolate parameter alt.Chart(months.reset_index().melt("datetime")).mark_line(opacity=0.75, interpolate="basis").encode( x='datetime', y='value', color='variable', ).properties(width=700, height=400)

days = opsd.resample("D").sum() months = days.resample("M").mean()

chart1 = alt.Chart(days.reset_index().melt("datetime")).mark_line(strokeWidth=1, opacity=0.25).encode( x='datetime', y='value', color='variable', ).properties(width=700, height=400) # not necessary to repeat below (the 2nd chart uses this) chart2 = alt.Chart(months.reset_index().melt("datetime")).mark_line(interpolate="basis", opacity=1).encode( x='datetime', y='value', color='variable', ) chart1 + chart2

rolling = days.rolling(60, center=True, win_type="triang").mean() chart1 = alt.Chart(rolling.reset_index().melt("datetime")).mark_line(strokeWidth=1.5, opacity=1).encode( x='datetime', y='value', color='variable', ).properties(width=700, height=400) # same as the two charts in previous code cell, except more transparent chart2 = alt.Chart(days.reset_index().melt("datetime")).mark_line(strokeWidth=1, opacity=0.1).encode( x='datetime', y='value', color='variable', ) chart3 = alt.Chart(months.reset_index().melt("datetime")).mark_line(interpolate="basis", opacity=.25).encode( x='datetime', y='value', color='variable', ) chart1 + chart2 + chart3

burglind = opsd.loc["2018-01-01":"2018-01-07"] alt.Chart(burglind.reset_index().melt("datetime")).mark_line().encode( x='datetime', y='value', color='variable' ).properties(width=700, height=400)

years = opsd.resample("Y").sum()

years["renewable"] = years["solar"]+years["wind"]

renewable = years.drop(columns=["load", "solar", "wind"])

renewable["year"] = years.index.year renewable

renewablefloat64

yearint32

2015-12-31 00:00:00+01:00

111960508

2015

2016-12-31 00:00:00+01:00

110300016

2016

2017-12-31 00:00:00+01:00

138537805

2017

2018-12-31 00:00:00+01:00

149804911

2018

2019-12-31 00:00:00+01:00

165277959

2019

renewable = renewable.reset_index(drop=True) renewable

renewablefloat64

yearint32

111960508

2015

110300016

2016

138537805

2017

149804911

2018

165277959

2019

alt.Chart(renewable).mark_bar(width=30, fill="lightgreen").encode( x='year:O', # O ~ treat the years as ordinal data y='renewable:Q' # Q ~ treat renewable as quantitative data ).properties(width=300, height=300)

# remove solar and wind columns weekdays = opsd.drop(columns=["solar", "wind"]) # add a column for weekdays weekdays["weekday"] = weekdays.index.weekday

weekdays = weekdays.groupby("weekday").mean() alt.Chart(weekdays.reset_index()).mark_bar(width=20).encode( x='weekday:O', # O ~ treat weekday as ordinal data y='load:Q' # Q ~ treat load as quantitative data ).properties(width=300, height=300)

weeks = opsd.resample("W").sum() days = opsd.resample("D").sum() brush = alt.selection_interval(encodings=['x']) upper = alt.Chart(weeks.reset_index().melt("datetime")).mark_area(interpolate="basis").encode( x = alt.X('datetime:T', axis=None), y = alt.Y('value:Q', axis=None), color='variable' ).properties(width=700, height=50).add_params(brush) lower = alt.Chart(days.reset_index().melt("datetime")).mark_line(strokeWidth=1).encode( x = alt.X('datetime:T', scale=alt.Scale(domain=brush)), y='value', color='variable', ).properties(width=700, height=300) upper & lower