🦾 Pivot from long to wide format#

Data is returned as a long format, dataframe. This means there is one unique observation (air temperature, humidity, wind speed, etc.) per row. It’s often useful to pivot variables into columns. This can be done with Polars. I encourage users of SynopticPy to become familiar with Polars for DataFrame manipulation.

[1]:

from datetime import datetime, timedelta

import matplotlib.pyplot as plt
import polars as pl
import seaborn as sns

import synoptic

[2]:

s = synoptic.Latest(stid="ukbkb,wbb", vars="air_temp,dew_point_temperature")
df = s.df()
df

🚚💨 Speedy delivery from Synoptic latest service.
📦 Received data from 2 stations.

[2]:

shape: (4, 20)

date_time	variable	sensor_index	is_derived	value	units	id	stid	name	elevation	latitude	longitude	mnet_id	state	timezone	elev_dem	period_of_record_start	period_of_record_end	is_restricted	is_active
datetime[μs, UTC]	str	u32	bool	f64	str	u32	str	str	f64	f64	f64	u32	str	str	f64	datetime[μs, UTC]	datetime[μs, UTC]	bool	bool
2024-10-18 05:55:00 UTC	"air_temp"	1	false	4.983	"Celsius"	1	"WBB"	"U of U William Browning Buildi…	4806.0	40.76623	-111.84755	153	"UT"	"America/Denver"	4727.7	1997-01-01 00:00:00 UTC	2024-10-18 05:10:00 UTC	false	true
2024-10-18 05:55:00 UTC	"dew_point_temperature"	1	true	3.28	"Celsius"	1	"WBB"	"U of U William Browning Buildi…	4806.0	40.76623	-111.84755	153	"UT"	"America/Denver"	4727.7	1997-01-01 00:00:00 UTC	2024-10-18 05:10:00 UTC	false	true
2024-10-18 05:45:00 UTC	"air_temp"	1	false	7.222	"Celsius"	37032	"UKBKB"	"EW2355 Spanish Fork"	4734.0	40.09867	-111.62767	65	"UT"	"America/Denver"	4740.8	2013-03-13 00:00:00 UTC	2024-10-18 05:00:00 UTC	false	true
2024-10-18 05:45:00 UTC	"dew_point_temperature"	1	true	5.18	"Celsius"	37032	"UKBKB"	"EW2355 Spanish Fork"	4734.0	40.09867	-111.62767	65	"UT"	"America/Denver"	4740.8	2013-03-13 00:00:00 UTC	2024-10-18 05:00:00 UTC	false	true

SynopticPy provides a basic pivot method that likely gets the job done in most cases.

[3]:

df.synoptic.pivot()

[3]:

shape: (2, 7)

date_time	stid	latitude	longitude	elevation	air_temp	dew_point_temperature
datetime[μs, UTC]	str	f64	f64	f64	f64	f64
2024-10-18 05:45:00 UTC	"WBB"	40.76623	-111.84755	4806.0	4.989	3.14
2024-10-18 05:45:00 UTC	"UKBKB"	40.09867	-111.62767	4734.0	7.222	5.18

With a pivotted DataFrame, you can use the with_wind_uv method.

[8]:

synoptic.TimeSeries(stid="WBB", vars="wind_speed,wind_speed", recent="12h").df()

🚚💨 Speedy delivery from Synoptic timeseries service.
📦 Received data from 1 stations.

[8]:

shape: (716, 20)

date_time	variable	sensor_index	is_derived	value	units	id	stid	name	elevation	latitude	longitude	mnet_id	state	timezone	elev_dem	period_of_record_start	period_of_record_end	is_restricted	is_active
datetime[μs, UTC]	str	u32	bool	f64	str	u32	str	str	f64	f64	f64	u32	str	str	f64	datetime[μs, UTC]	datetime[μs, UTC]	bool	bool
2024-10-17 17:56:00 UTC	"wind_speed"	1	false	1.451	"m/s"	1	"WBB"	"U of U William Browning Buildi…	4806.0	40.76623	-111.84755	153	"UT"	"America/Denver"	4727.7	1997-01-01 00:00:00 UTC	2024-10-18 05:40:00 UTC	false	true
2024-10-17 17:57:00 UTC	"wind_speed"	1	false	1.029	"m/s"	1	"WBB"	"U of U William Browning Buildi…	4806.0	40.76623	-111.84755	153	"UT"	"America/Denver"	4727.7	1997-01-01 00:00:00 UTC	2024-10-18 05:40:00 UTC	false	true
2024-10-17 17:58:00 UTC	"wind_speed"	1	false	1.554	"m/s"	1	"WBB"	"U of U William Browning Buildi…	4806.0	40.76623	-111.84755	153	"UT"	"America/Denver"	4727.7	1997-01-01 00:00:00 UTC	2024-10-18 05:40:00 UTC	false	true
2024-10-17 17:59:00 UTC	"wind_speed"	1	false	1.44	"m/s"	1	"WBB"	"U of U William Browning Buildi…	4806.0	40.76623	-111.84755	153	"UT"	"America/Denver"	4727.7	1997-01-01 00:00:00 UTC	2024-10-18 05:40:00 UTC	false	true
2024-10-17 18:00:00 UTC	"wind_speed"	1	false	1.816	"m/s"	1	"WBB"	"U of U William Browning Buildi…	4806.0	40.76623	-111.84755	153	"UT"	"America/Denver"	4727.7	1997-01-01 00:00:00 UTC	2024-10-18 05:40:00 UTC	false	true
…	…	…	…	…	…	…	…	…	…	…	…	…	…	…	…	…	…	…	…
2024-10-18 05:47:00 UTC	"wind_speed"	1	false	2.66	"m/s"	1	"WBB"	"U of U William Browning Buildi…	4806.0	40.76623	-111.84755	153	"UT"	"America/Denver"	4727.7	1997-01-01 00:00:00 UTC	2024-10-18 05:40:00 UTC	false	true
2024-10-18 05:48:00 UTC	"wind_speed"	1	false	2.274	"m/s"	1	"WBB"	"U of U William Browning Buildi…	4806.0	40.76623	-111.84755	153	"UT"	"America/Denver"	4727.7	1997-01-01 00:00:00 UTC	2024-10-18 05:40:00 UTC	false	true
2024-10-18 05:49:00 UTC	"wind_speed"	1	false	1.857	"m/s"	1	"WBB"	"U of U William Browning Buildi…	4806.0	40.76623	-111.84755	153	"UT"	"America/Denver"	4727.7	1997-01-01 00:00:00 UTC	2024-10-18 05:40:00 UTC	false	true
2024-10-18 05:50:00 UTC	"wind_speed"	1	false	2.593	"m/s"	1	"WBB"	"U of U William Browning Buildi…	4806.0	40.76623	-111.84755	153	"UT"	"America/Denver"	4727.7	1997-01-01 00:00:00 UTC	2024-10-18 05:40:00 UTC	false	true
2024-10-18 05:55:00 UTC	"wind_speed"	1	false	null	"m/s"	1	"WBB"	"U of U William Browning Buildi…	4806.0	40.76623	-111.84755	153	"UT"	"America/Denver"	4727.7	1997-01-01 00:00:00 UTC	2024-10-18 05:40:00 UTC	false	true

[6]:

df = (
    synoptic.TimeSeries(stid="WBB", vars="wind_speed,wind_direction", recent="12h")
    .df()
    .synoptic.pivot()
    .synoptic.with_wind_uv()
)
df

🚚💨 Speedy delivery from Synoptic timeseries service.
📦 Received data from 1 stations.

[6]:

shape: (713, 9)

date_time	stid	latitude	longitude	elevation	wind_speed	wind_direction	wind_u	wind_v
datetime[μs, UTC]	str	f64	f64	f64	f64	f64	f64	f64
2024-10-17 17:54:00 UTC	"WBB"	40.76623	-111.84755	4806.0	1.255	183.9	0.085359	1.252094
2024-10-17 17:55:00 UTC	"WBB"	40.76623	-111.84755	4806.0	0.324	183.3	0.018651	0.323463
2024-10-17 17:56:00 UTC	"WBB"	40.76623	-111.84755	4806.0	1.451	206.4	0.645166	1.299678
2024-10-17 17:57:00 UTC	"WBB"	40.76623	-111.84755	4806.0	1.029	202.4	0.392121	0.951358
2024-10-17 17:58:00 UTC	"WBB"	40.76623	-111.84755	4806.0	1.554	190.8	0.291191	1.526474
…	…	…	…	…	…	…	…	…
2024-10-18 05:42:00 UTC	"WBB"	40.76623	-111.84755	4806.0	2.042	271.1	2.041624	-0.039201
2024-10-18 05:43:00 UTC	"WBB"	40.76623	-111.84755	4806.0	1.734	285.7	1.669307	-0.469221
2024-10-18 05:44:00 UTC	"WBB"	40.76623	-111.84755	4806.0	2.809	281.9	2.748632	-0.579228
2024-10-18 05:45:00 UTC	"WBB"	40.76623	-111.84755	4806.0	2.999	285.6	2.888525	-0.806491
2024-10-18 05:50:00 UTC	"WBB"	40.76623	-111.84755	4806.0	null	null	null	null

[ ]:

Customized pivots#

[10]:

df.pivot(
    on="variable",
    index=["date_time", "stid", "latitude", "longitude", "elevation"],
    values="value",
)

[10]:

shape: (2, 7)

date_time	stid	latitude	longitude	elevation	air_temp	dew_point_temperature
datetime[μs, UTC]	str	f64	f64	f64	f64	f64
2024-10-18 05:40:00 UTC	"WBB"	40.76623	-111.84755	4806.0	5.089	3.19
2024-10-18 05:30:00 UTC	"UKBKB"	40.09867	-111.62767	4734.0	7.222	5.18

Special Case: Multiple Sensors#

If your set of stations has multiple sensors the measure the same variable (e.g., NAA measures outdoor air temperature and greenhouse air temperature) you will need to take care with how you perform your pivot. Either filter the rows for a single sensor ID, or pivot on both the variable and sensor columns.

[4]:

# Notice that NAA has two different reports for air temperature
df = synoptic.Latest(stid="naa", vars="air_temp,wind_speed").df()
df

🚚💨 Speedy delivery from Synoptic latest service.
📦 Received data from 1 stations.

[4]:

shape: (3, 20)

date_time	variable	sensor_index	is_derived	value	units	id	stid	name	elevation	latitude	longitude	mnet_id	state	timezone	elev_dem	period_of_record_start	period_of_record_end	is_restricted	is_active
datetime[μs, UTC]	str	u32	bool	f64	str	u32	str	str	f64	f64	f64	u32	str	str	f64	datetime[μs, UTC]	datetime[μs, UTC]	bool	bool
2024-10-18 05:25:00 UTC	"air_temp"	1	false	5.306	"Celsius"	43136	"NAA"	"Neil Armstrong Academy"	4260.0	40.71152	-112.01448	153	"UT"	"America/Denver"	4252.0	2014-05-27 00:00:00 UTC	2024-10-18 05:40:00 UTC	false	true
2024-10-18 05:25:00 UTC	"air_temp"	2	false	11.306	"Celsius"	43136	"NAA"	"Neil Armstrong Academy"	4260.0	40.71152	-112.01448	153	"UT"	"America/Denver"	4252.0	2014-05-27 00:00:00 UTC	2024-10-18 05:40:00 UTC	false	true
2024-10-18 05:25:00 UTC	"wind_speed"	1	false	2.639	"m/s"	43136	"NAA"	"Neil Armstrong Academy"	4260.0	40.71152	-112.01448	153	"UT"	"America/Denver"	4252.0	2014-05-27 00:00:00 UTC	2024-10-18 05:40:00 UTC	false	true

[5]:

# Filter sensor ID, then pivot
df.filter(sensor_index=1).pivot(
    "variable", index=["stid", "date_time", "latitude", "longitude"], values="value"
)

[5]:

shape: (1, 6)

stid	date_time	latitude	longitude	air_temp	wind_speed
str	datetime[μs, UTC]	f64	f64	f64	f64
"NAA"	2024-10-18 05:25:00 UTC	40.71152	-112.01448	5.306	2.639

[6]:

# Pivot on both variable and sensor column
df.pivot(
    ["variable", "sensor_index"],
    index=["stid", "date_time", "latitude", "longitude"],
    values="value",
)

[6]:

shape: (1, 7)

stid	date_time	latitude	longitude	{"air_temp",1}	{"air_temp",2}	{"wind_speed",1}
str	datetime[μs, UTC]	f64	f64	f64	f64	f64
"NAA"	2024-10-18 05:25:00 UTC	40.71152	-112.01448	5.306	11.306	2.639

[8]:

df.pivot(
    ["variable", "sensor_index"],
    index=["stid", "date_time", "latitude", "longitude"],
    values="value",
).columns

[8]:

['stid',
 'date_time',
 'latitude',
 'longitude',
 '{"air_temp",1}',
 '{"air_temp",2}',
 '{"wind_speed",1}']

Special Case: Measured and derived values#

Some stations have both measured and derived variables, like dew point temperature. Your pivot options are 1. Filter to use only the measured value 1. Pivot on both variable and derived columns. 1. Use an aggregattion function to take mean or first of the two values.

[9]:

df = synoptic.Latest(stid="KU69", vars="wind_speed,dew_point_temperature").df()
df

🚚💨 Speedy delivery from Synoptic latest service.
📦 Received data from 1 stations.

[9]:

shape: (3, 20)

date_time	variable	sensor_index	is_derived	value	units	id	stid	name	elevation	latitude	longitude	mnet_id	state	timezone	elev_dem	period_of_record_start	period_of_record_end	is_restricted	is_active
datetime[μs, UTC]	str	u32	bool	f64	str	u32	str	str	f64	f64	f64	u32	str	str	f64	datetime[μs, UTC]	datetime[μs, UTC]	bool	bool
2024-10-17 05:35:00 UTC	"dew_point_temperature"	1	false	4.0	"Celsius"	6470	"KU69"	"DUCHESNE"	5826.0	40.1919	-110.38099	1	"UT"	"America/Denver"	5810.4	2007-10-11 00:00:00 UTC	2024-10-17 05:15:00 UTC	false	true
2024-10-17 05:35:00 UTC	"wind_speed"	1	false	1.543	"m/s"	6470	"KU69"	"DUCHESNE"	5826.0	40.1919	-110.38099	1	"UT"	"America/Denver"	5810.4	2007-10-11 00:00:00 UTC	2024-10-17 05:15:00 UTC	false	true
2024-10-17 05:35:00 UTC	"dew_point_temperature"	1	true	3.93	"Celsius"	6470	"KU69"	"DUCHESNE"	5826.0	40.1919	-110.38099	1	"UT"	"America/Denver"	5810.4	2007-10-11 00:00:00 UTC	2024-10-17 05:15:00 UTC	false	true

[10]:

# Pivot, calculate the mean if more than one value per variable
df.pivot("variable", index="stid", values="value", aggregate_function="mean")

[10]:

shape: (1, 3)

stid	dew_point_temperature	wind_speed
str	f64	f64
"KU69"	3.965	1.543

[11]:

# Pivot, use the first value if more than one value per variable
df.pivot("variable", index="stid", values="value", aggregate_function="first")

[11]:

shape: (1, 3)

stid	dew_point_temperature	wind_speed
str	f64	f64
"KU69"	4.0	1.543

[13]:

# Pivot on both variable and derived columns
df.sort("variable").pivot(
    ["variable", "is_derived"],
    index=["stid", "latitude", "longitude"],
    values="value",
)

[13]:

shape: (1, 6)

stid	latitude	longitude	{"dew_point_temperature",false}	{"dew_point_temperature",true}	{"wind_speed",false}
str	f64	f64	f64	f64	f64
"KU69"	40.1919	-110.38099	4.0	3.93	1.543

Pivot many station variables#

Let’s pivot requests with more station data…

[14]:

df = synoptic.Latest(
    state="UT",
    network=[1, 2],
    vars="air_temp,dew_point_temperature",
    units="english",
    complete=1,  # to get mesonet shortname
).df()

# Pivot
df = df.pivot(
    "variable",
    index=["stid", "latitude", "longitude", "shortname"],
    values="value",
    aggregate_function="first",
)
df

🚚💨 Speedy delivery from Synoptic latest service.
📦 Received data from 131 stations.

[14]:

shape: (131, 6)

stid	latitude	longitude	shortname	air_temp	dew_point_temperature
str	f64	f64	str	f64	f64
"KSLC"	40.77069	-111.96503	"ASOS/AWOS"	71.6	35.55
"KU42"	40.6196	-111.99016	"ASOS/AWOS"	68.0	35.06
"KHIF"	41.11112	-111.96229	"ASOS/AWOS"	68.18	35.6
"KOGD"	41.19406	-112.01681	"ASOS/AWOS"	71.6	37.35
"KBMC"	41.5464	-112.0601	"ASOS/AWOS"	71.42	30.02
…	…	…	…	…	…
"TT509"	37.32322	-112.18486	"RAWS"	49.0	24.3
"KSPK"	40.145	-111.6677	"ASOS/AWOS"	64.4	37.4
"TT773"	38.62606	-111.9419	"RAWS"	43.0	23.95
"KU64"	37.93243	-109.34122	"ASOS/AWOS"	45.32	35.06
"K40U"	40.9833	-109.6833	"ASOS/AWOS"	57.56	34.7

[15]:

# Plot on a map

from herbie.toolbox import EasyMap, ccrs, pc

ax = EasyMap("10m", figsize=(8, 8)).STATES().LAKES().ROADS().ax
art = sns.scatterplot(
    df.filter(pl.col("longitude") < -60),  # One station has bad longitude metadata
    ax=ax,
    x="longitude",
    y="latitude",
    hue="air_temp",
    style="shortname",
    edgecolor="k",
    palette="Spectral_r",
)

/home/blaylock/miniconda3/envs/synoptic2/lib/python3.12/site-packages/pyproj/__init__.py:89: UserWarning: pyproj unable to set database path.
  _pyproj_global_context_initialize()

../../_images/user_guide_gallery_how-to-pivot_23_1.png