Skip to main content
100%

unpivot-postgres.sql

✓ Published0🌍 Public
Aantichaos
Last edited Jun 11, 2020
Created on Jun 11, 2020

This example reshapes two country-level datasets into a unified, chart-ready format for visualizing trends over time. It first pivots the CyberSecurityIndex table, converting three separate columns—National Cyber Security Index, Digital Development Level, and their difference—into a long-form structure using a `cross join lateral` with a `values` clause. It then appends rows from the PressFreedomIndex table, retaining its country, ISO code, year, and index value. The SQL uses `CAST` to normalize year and value types, ensuring consistent numeric and text formats across both sources before combining them with `UNION`.

AI-generated description

Similar vizzes

Loading thumbnail…

Fork of CSV Dataset Template - Cyber Security Indexes

This example forks a CSV dataset template to explore cyber security indexes across countries. It loads and parses data from `data.csv` using a raw `import`, then converts string values to numbers for analysis. A simple `container.innerHTML` assignment displays the first row of data as formatted JSON in a `<pre>` element, with the font size set via a template literal. The visualization shows a single data record rather than correlations or geographic distributions.

Ccrijos
55% match
Loading thumbnail…

Cyber_Attacks_V2

This example displays the first record from a dataset of cyber attacks, showing parsed fields such as the event date, actor, organization, industry, and motive. The code imports data from a CSV file and uses D3’s `utcParse` to convert the date strings into JavaScript `Date` objects, extracting the year for each entry. It also coerces the `industry_code` column into a numeric value with the unary plus operator. The visualization renders the resulting JSON representation of a single data point in a styled `<pre>` element, demonstrating the initial data processing and output structure.

Ccoreyfnolan@gmail.com
54% match
Loading thumbnail…

pandas, data wrangling, read_table, pivot, to_csv

This example demonstrates how to restructure a tabular dataset of radio station monthly play counts from a long format into a wide pivot table, showing each station’s reproductions across the twelve months of the year. The notebook first loads the data with `pandas.read_table` from a remote TSV file, displays the initial rows, then uses the `pivot` method to reorganize the data with radio stations as rows and months as columns. Finally, the reshaped table can be exported with `to_csv` for further analysis.

Aaaizemberg
53% match
Loading thumbnail…

Fork of CSV Dataset Template - Cyber Security Salaries

This example visualizes cybersecurity salary data from Kaggle, showing how salaries in USD vary across job titles, experience levels, and employee residences. The code loads a CSV dataset using a data import statement, converts string values to numbers with unary plus operators, and displays the first record as formatted JSON in a pre element. The visualization uses the imported data array and exports it for further analysis, though it currently only renders static text output rather than interactive charts.

Ccrijos
53% match
Loading thumbnail…

un-nest

This example demonstrates a data transformation workflow using D3.js. It loads tabular data containing FIPS codes, population totals, and Hispanic population percentages for multiple census years, then nests the records by year. A custom function unnests the data, flattening the hierarchical structure back into individual records while preserving the parent year key. The code uses D3's `d3.select`, `d3.tsv`, and `d3.nest` APIs to handle SVG rendering, file parsing, and data grouping, with the unnested result logged to the console.

SSpaceActuary
53% match
Loading thumbnail…

Gist 6114786

This example implements a private torque-style time-series visualization for geospatial data using PostgreSQL and PostGIS. It defines custom SQL functions that aggregate point features into web-Mercator grid cells, grouping counts by time steps and returning arrays of values and dates for rendering. The code uses `ST_SnapToGrid`, `CDB_XYZ_Resolution`, and `CDB_XYZ_Extent` to tile data at a given zoom level, with two variants: a standard response for instantaneous counts and a cumulative response that includes expiration dates for each cell. A metadata function returns the spatial bounds and time range from the source table, supporting both timestamped and numeric columns.

Aandrewxhill
52% match
Loading thumbnail…

Cyber Attacks

This example displays the first record from a CSV dataset of cyber security attacks as a raw JSON object in a preformatted text block. It loads the data with a JSON import, parses the CSV, converts the `Packet Length` and `Anomaly Scores` fields to numbers, and then renders the parsed object’s fields. The visualization relies on the native Web Platform APIs for parsing and DOM manipulation, without external charting libraries. The source data is Inscribo's Cyber Security Attacks dataset on Kaggle.

Ccoreyfnolan@gmail.com
51% match
Loading thumbnail…

just a bookmark of random psql queries.

This example visualizes spatial relationships between neighborhoods, city blocks, and demographic data in Portland and Laurelhurst through a series of SQL queries. The code shows how geographic joins and intersections are performed using PostGIS functions like `ST_Intersects`, combining building footprints, block polygons, and census block-group data. The queries calculate race-based percentages, such as percent white and percent black, from census fields like `h7x002` and `h7x003`, while filtering for specific places and positive population counts. The gist demonstrates iterative query building for choropleth-style analysis, revealing how raw geographic and tabular data are merged to support subsequent mapping or charting in a visualization tool.

CCaged
51% match