New tool to transform JSON-stat to CSV

[Xavier Badosa]

I just published a new tool to transform a JSON-stat REST API into a JSON/CSV REST API.

With this tool any JSON-stat REST end-point can be translated into a CSV or JSON end-point. The idea behing this tool is to ease the connection between a JSON-stat dataset server and applications that support web-accessible CSVs, like Google Sheets (via =IMPORTDATA()) or MS Excel and MS PowerBI (via PowerQuery).

https://jsonstat.com/api/

For example, Eurostat's API end-point

https://ec.europa.eu/eurostat/api/dissemination/statistics/1.0/data/une_rt_a?lang=en&lastTimePeriod=10&age=Y15-74&unit=PC_ACT

can be consumed in CSV form using the following API request:

https://jsonstat.com/api/?url=https%3A%2F%2Fec.europa.eu%2Feurostat%2Fapi%2Fdissemination%2Fstatistics%2F1.0%2Fdata%2Fune_rt_a%3Flang%3Den%26lastTimePeriod%3D10%26age%3DY15-74%26unit%3DPC_ACT&format=csv&delim=%2C&labels=1

Please, don't abuse this service and always use filtered queries that return a limited number of observations.

I hope this is helpful for PowerBI and Excels users.

Xavier

[Vincenzo Patruno]

Great! Thanks

--
You received this message because you are subscribed to the Google Groups "json-stat" group.
To unsubscribe from this group and stop receiving emails from it, send an email to json-stat+...@googlegroups.com.
To view this discussion visit https://groups.google.com/d/msgid/json-stat/efc87046-e34c-4a10-9cbf-f7c8ed3239e0n%40googlegroups.com.

[Xavier Badosa]

One of the use cases of https://jsonstat.com/api/ is to send data to AI models in a format that is more "readable".

For example, https://lambda.chat/ offers free access to DeepSeek R1 and has a very interesting feature: it allows you to customize the system prompt, which in practical terms means that you can set up how the model should act and what it should expect from the user. For example, the system prompt can be set to:

"The user will provide a JSON string describing a dataset. The data model is cubic (dimensions describe each data point). The JSON file is a string exposing an array of objects. Each object has a value and a status property (data point) plus dimension properties (coordinates of the cube). Help the user understand the information in the dataset."

After that, you simply need to paste the JSON content in the user prompt field.

For example, select a dataset from https://jsonstat.com/eurostat/ and get its API end-point. For example,

https://ec.europa.eu/eurostat/api/dissemination/statistics/1.0/data/tipslm100?lang=en

You can view the contents of this dataset at

https://www.idescat.cat/dev/api/taules/visor/?u=https%3A%2F%2Fec.europa.eu%2Feurostat%2Fapi%2Fdissemination%2Fstatistics%2F1.0%2Fdata%2Ftipslm100%3Flang%3Den&q=&lang=en

You can convert the JSON-stat into a simpler JSON structure at https://jsonstat.com/api/:

https://jsonstat.com/api/?url=https%3A%2F%2Fec.europa.eu%2Feurostat%2Fapi%2Fdissemination%2Fstatistics%2F1.0%2Fdata%2Ftipslm100%3Flang%3Den&format=json&delim=%2C&labels=1

You can paste the content returned by the previous request to Lambda Chat and this is what it will return in Markdown (the analysis seems to be basically correct, except for the fact of not mentioning Malta or Czechia in the top performers list):

### Dataset Overview:
This dataset tracks **employment rates** (as a percentage of the total population aged 20–64) across EU countries, the Euro area, and individual EU member states from **2003 to 2024**. Below is a structured breakdown of the key insights:

---

### **Key Dimensions**:
1. **Geographic Scope**:
   - **EU-27** (post-2020) and **Euro area-20** (post-2023).
   - **27 individual countries** (e.g., Germany, France, Italy, Spain, Greece, Poland).
2. **Time Range**: 2003–2024 (annual data).
3. **Metrics**:
   - `value`: Employment rate (% of population aged 20–64).
   - `status`: Flags (e.g., `b` = break in time series, `d` = definition change).

---

### **Major Trends**:
#### 1. **EU-Wide Trends**:
- **2009–2024**:
  - Steady growth from **67.3% (2009)** to **75.8% (2024)**.
  - Post-2013 recovery after the Eurozone crisis.
  - COVID-19 dip in **2020** (71.7%), rebounding to **75.3% by 2023**.

#### 2. **Country Highlights**:
- **Top Performers (2024)**:
  - **Netherlands**: 83.5%  
  - **Sweden**: 81.9%  
  - **Germany**: 81.3%  
- **Recovery Success Stories**:
  - **Greece**: From **52.5% (2013)** to **69.3% (2024)** post-debt crisis.
  - **Spain**: From **59.6% (2012)** to **71.4% (2024)**.
- **Struggling Economies**:
  - **Italy**: Lagging at **67.1% (2024)** despite gradual growth.
  - **Romania**: Lowest in the EU at **69.5% (2024)**.

#### 3. **COVID-19 Impact (2020)**:
  - Sharp drops in most countries (e.g., France: **72.1% → 72.1%**, Spain: **68% → 65.7%**).
  - Strong rebound by 2022–2023 (e.g., Ireland: **72.1% → 79.1%**).

#### 4. **Notable Outliers**:
  - **Bulgaria**: Rapid growth from **62.3% (2011)** to **76.8% (2024)**.
  - **Czechia**: Highest in the EU at **82.3% (2024)**.

---

### **Data Quality Notes**:
- **Missing Data**: Pre-2009 values are mostly `null` (likely due to methodology changes).
- **Status Flags**:
  - `b` (break in series): E.g., Belgium (2017), Denmark (2016–2017).
  - `d` (definition change): Spain (2021–2024) – use caution when comparing years.

---

### **Visualization Suggestions**:
1. **Line Charts**: Compare EU-wide vs. country-specific trends over time.
2. **Heatmaps**: Highlight regional disparities (e.g., North vs. South EU).
3. **Bar Graphs**: Top/bottom performers in 2024.

---

### **Conclusion**:
The dataset reflects **strong post-crisis recovery** and **resilience post-COVID**, with significant variation across countries. Southern EU nations (e.g., Greece, Spain) show improvement but lag behind Northern/Western peers. Use status flags (`b`, `d`) to interpret anomalies.