I have a duckdb database at ../weather-4.duckdb There is a table named weather and…
- User
I have a duckdb database at ../weather-4.duckdb There is a table named weather and fields called station, report date, hour, and temperature_F. Can you 3d plot temperature , date, and hour for a given station?
- Assistant
Below is an example Python script that connects to your DuckDB database, retrieves the temperature data for a specific station, and then creates a 3D scatter plot of temperature vs. date and hour using Matplotlib.
Before running the script, make sure you have the required packages installed. You can install them via pip if needed:
pip install duckdb pandas matplotlibHere’s the complete script:
import duckdb import pandas as pd import matplotlib.pyplot as plt from mpl_toolkits.mplot3d import Axes3D # Although not used directly, this import is needed for 3D plotting # Specify the station you want to plot station_to_plot = 'YOUR_STATION_NAME' # Replace with the station of interest # Connect to the DuckDB database con = duckdb.connect(database="../weather-4.duckdb", read_only=True) # Construct and execute the query query = f""" SELECT "report date" AS report_date, hour, temperature_F FROM weather WHERE station = '{station_to_plot}'; """ df = con.execute(query).fetchdf() # Make sure the date column is parsed as datetime df['report_date'] = pd.to_datetime(df['report_date']) # For 3D plotting, converting dates to a numeric format is often helpful. # Here we convert each date to its ordinal value. df['date_numeric'] = df['report_date'].apply(lambda dt: dt.toordinal()) # Create a 3D scatter plot fig = plt.figure(figsize=(10, 7)) ax = fig.add_subplot(111, projection='3d') # Create the scatter plot with a colormap representing temperature_F. scat = ax.scatter(df['date_numeric'], df['hour'], df['temperature_F'], c=df['temperature_F'], cmap='viridis', marker='o') ax.set_xlabel('Date') ax.set_ylabel('Hour') ax.set_zlabel('Temperature (°F)') ax.set_title(f"Temperature vs Date and Hour for Station {station_to_plot}") # Add a color bar to indicate the temperature scale cbar = plt.colorbar(scat, pad=0.1) cbar.set_label('Temperature (°F)') # Customize the x-axis tick labels to display readable dates instead of numeric ordinals # Here we choose a few sample ticks for clarity num_ticks = 6 tick_locations = pd.to_datetime( pd.Series(df['date_numeric']) .drop_duplicates() .sort_values() .iloc[::max(1, len(df) // num_ticks)] ) tick_nums = [dt.toordinal() for dt in tick_locations] tick_labels = [dt.strftime('%Y-%m-%d') for dt in tick_locations] ax.set_xticks(tick_nums) ax.set_xticklabels(tick_labels, rotation=45, ha='right') plt.tight_layout() plt.show()How the Script Works
-
Database Connection and Query:
- The script connects to your DuckDB database located at
../weather-4.duckdb. - It then runs a SQL query on the
weathertable to select the fields"report date",hour, andtemperature_Ffor the specifiedstation.
- The script connects to your DuckDB database located at
-
Data Preparation:
- The
"report date"field is converted to a datetime object using Pandas. - Since dates cannot be directly plotted on a 3D axis, each date is converted to its ordinal (numeric) value which is then used for plotting.
- The
-
3D Plotting:
- A 3D scatter plot is created with the numeric date on the x-axis, hour on the y-axis, and temperature on the z-axis.
- A color map (
viridis) is applied to the points based on their temperature values. - The x-axis tick labels are then reformatted to show human-readable dates.
-
Displaying the Plot:
- Finally, the plot is rendered and shown.
Replace
"YOUR_STATION_NAME"with the actual station id you wish to visualize, and run the script. This should give you a 3D representation of how temperature varies by date and time (hour) for that station. -
- User
report_date is in timestamp format
- Assistant
If your
report_datefield is already in a timestamp format, you can still use the same approach—but you might skip or simplify the conversion step depending on your needs. The following example demonstrates how to query the database and create a 3D plot. In this example, we'll still convert the timestamps to their ordinal values (i.e. days) for plotting on the x-axis. If you need more precision (for example, including the time of day), you can instead convert the timestamps to UNIX epoch seconds.Below are two variants:
Variant 1: Converting Dates to Ordinal (Days Only)
This version converts each timestamp to a numeric value using
toordinal(), which represents only the date (ignoring the time part). Since you have a separatehourfield, this usually works well.import duckdb import pandas as pd import matplotlib.pyplot as plt from mpl_toolkits.mplot3d import Axes3D # Required for 3D plotting # Specify the station you want to plot station_to_plot = 'YOUR_STATION_NAME' # Replace with the station of interest # Connect to the DuckDB database con = duckdb.connect(database="../weather-4.duckdb", read_only=True) # Query the weather table for the station; note the "report date" is assumed to be a timestamp query = f""" SELECT "report date" AS report_date, hour, temperature_F FROM weather WHERE station = '{station_to_plot}'; """ df = con.execute(query).fetchdf() # Convert report_date to datetime (if not already) df['report_date'] = pd.to_datetime(df['report_date']) # Convert the timestamp to an ordinal number (date only) df['date_numeric'] = df['report_date'].apply(lambda dt: dt.toordinal()) # Create a 3D scatter plot fig = plt.figure(figsize=(10, 7)) ax = fig.add_subplot(111, projection='3d') scat = ax.scatter(df['date_numeric'], df['hour'], df['temperature_F'], c=df['temperature_F'], cmap='viridis', marker='o') ax.set_xlabel('Date') ax.set_ylabel('Hour') ax.set_zlabel('Temperature (°F)') ax.set_title(f"Temperature vs Date and Hour for Station {station_to_plot}") # Add a color bar to indicate the temperature scale cbar = plt.colorbar(scat, pad=0.1) cbar.set_label('Temperature (°F)') # Customize the x-axis tick labels to display human-readable dates # We'll choose a few sample ticks for clarity num_ticks = 6 unique_dates = df['report_date'].dt.normalize().drop_duplicates().sort_values() tick_dates = unique_dates.iloc[::max(1, len(unique_dates) // num_ticks)] tick_nums = tick_dates.apply(lambda dt: dt.toordinal()) tick_labels = tick_dates.dt.strftime('%Y-%m-%d').tolist() ax.set_xticks(tick_nums) ax.set_xticklabels(tick_labels, rotation=45, ha='right') plt.tight_layout() plt.show()
Variant 2: Converting Timestamps to UNIX Epoch Seconds
If you need a more precise representation that includes hours, minutes, and seconds from your timestamps, you can convert the timestamp to UNIX epoch time (seconds since January 1, 1970). In this case, you may adjust the x-axis tick formatting accordingly.
import duckdb import pandas as pd import matplotlib.pyplot as plt import matplotlib.dates as mdates from mpl_toolkits.mplot3d import Axes3D # Required for 3D plotting # Specify the station you want to plot station_to_plot = 'YOUR_STATION_NAME' # Replace with the station of interest # Connect to the DuckDB database con = duckdb.connect(database="../weather-4.duckdb", read_only=True) # Query the weather table for the station query = f""" SELECT "report date" AS report_date, hour, temperature_F FROM weather WHERE station = '{station_to_plot}'; """ df = con.execute(query).fetchdf() # Convert report_date to datetime (if not already) df['report_date'] = pd.to_datetime(df['report_date']) # Convert timestamps to UNIX epoch seconds df['date_numeric'] = df['report_date'].apply(lambda dt: dt.timestamp()) # Create a 3D scatter plot fig = plt.figure(figsize=(10, 7)) ax = fig.add_subplot(111, projection='3d') scat = ax.scatter(df['date_numeric'], df['hour'], df['temperature_F'], c=df['temperature_F'], cmap='viridis', marker='o') ax.set_xlabel('Timestamp') ax.set_ylabel('Hour') ax.set_zlabel('Temperature (°F)') ax.set_title(f"Temperature vs Date and Hour for Station {station_to_plot}") # Add a color bar to indicate the temperature scale cbar = plt.colorbar(scat, pad=0.1) cbar.set_label('Temperature (°F)') # Format the x-axis tick labels as dates # We create ticks based on the range of timestamps in the data num_ticks = 6 timestamps_min = df['date_numeric'].min() timestamps_max = df['date_numeric'].max() tick_vals = [timestamps_min + i*(timestamps_max - timestamps_min)/num_ticks for i in range(num_ticks+1)] tick_labels = [pd.to_datetime(ts, unit='s').strftime('%Y-%m-%d %H:%M:%S') for ts in tick_vals] ax.set_xticks(tick_vals) ax.set_xticklabels(tick_labels, rotation=45, ha='right') plt.tight_layout() plt.show()
Explanation:
-
Connecting to DuckDB and Querying the Data:
- The script connects to your DuckDB database located at
../weather-4.duckdband selects thereport date,hour, andtemperature_Ffields from theweathertable for the specified station.
- The script connects to your DuckDB database located at
-
Handling Timestamps:
- In both variants,
pd.to_datetimeensures thatreport_dateis in a proper datetime format. - Variant 1: Converts the datetime to an ordinal value (which only captures the date).
- Variant 2: Converts the datetime to UNIX epoch seconds if you need a more precise numeric representation.
- In both variants,
-
3D Plotting with Matplotlib:
- A 3D scatter plot is created with the x-axis as the numeric representation of the date, the y-axis as the hour, and the z-axis as
temperature_F. - An appropriate colormap (
viridis) is applied to visually indicate differences in temperature. - The x-axis tick labels are formatted to display human-readable dates.
- A 3D scatter plot is created with the x-axis as the numeric representation of the date, the y-axis as the hour, and the z-axis as
Replace
"YOUR_STATION_NAME"with the appropriate station identifier and run the script that best fits your needs. -
Other models, same or similar prompt
Responses were produced in different, uncontrolled contexts (system prompts, settings, prior turns). This is not a benchmark.