r2-okonomi-data

name: r2-okonomi-data description: | Activates when querying financial/economic data from R2. Use this skill for: subsidies, farm financials, property values, CVR enrichment, ownership data, støtte per hektar, samlet støtte, grundværdi. Keywords: økonomi, finance, subsidies, støtte, tilskud, CVR, property, ejendom, grundværdi

R2 Økonomi (Finance) Data Catalog

Financial and economic data for Danish agricultural businesses.

Frontend Metrics Supported

Available Datasets

Silver Layer

Subsidies (554K rows)

Path: r2://landbruget-data/silver/subsidies/*/data.parquet

Schema (introspected):

cvr_number: string
markbloknummer: string
marknummer: string
afg_kode_navn: string
imkareal: double
ansoegtha_grundbet: double
tilskudsberetha_grundbet: double
[28 columns total - multiple subsidy types]

Property Owners (8.2M rows)

Path: r2://landbruget-data/silver/property_owners/*/data.parquet

Nested Structure:

bestemtFastEjendomBFENr: int64
ejendePerson:
  - Beskyttelser: list
  - Navn: struct
  - Standardadresse: struct
ejendeVirksomhed:
  - CVRNummer: string
  - beliggenhedsadresse: struct

Gold Layer

CVR Enrichment

Path: r2://landbruget-data/gold/cvr_enrichment/*/

Contains multiple enrichment files:

• address_geocoding.parquet - Geocoded company addresses
• company_summary.parquet - Aggregated company data

Common Queries

Get Total Subsidies by CVR

import duckdb
from common.storage.filesystem import setup_duckdb_cloud_auth

conn = duckdb.connect()
setup_duckdb_cloud_auth(conn)

# Read subsidies
df = conn.execute("""
    SELECT * FROM read_parquet('r2://landbruget-data/silver/subsidies/2025-01-10T00:00:26.377177/data.parquet')
""").df()

# Calculate total subsidies per CVR
totals = df.groupby('cvr_number').agg({
    'grundbetaling_dkk': 'sum',
    'oeko_tilskud_dkk': 'sum',
    'imkareal': 'sum'
}).reset_index()

# Add subsidy per hectare
totals['subsidy_per_ha'] = (
    totals['grundbetaling_dkk'] + totals['oeko_tilskud_dkk']
) / totals['imkareal']

Find Property Owner by CVR

# Read property owners
df = conn.execute("""
    SELECT * FROM read_parquet('r2://landbruget-data/silver/property_owners/2025-01-10/data.parquet')
""").df()

# Filter by CVR (need to handle nested structure)
import pandas as pd

# Extract CVR from nested structure
df['owner_cvr'] = df['ejendeVirksomhed'].apply(
    lambda x: x.get('CVRNummer') if x else None
)
company_properties = df[df['owner_cvr'] == '31373077']

Subsidies for Specific Field Block

# Get all subsidies for a markblok
field_block_subsidies = df[df['markbloknummer'] == '610341-27']
print(f"Total area: {field_block_subsidies['imkareal'].sum():.2f} ha")
print(f"Total basic payment: {field_block_subsidies['grundbetaling_dkk'].sum():.2f} DKK")

Calculate Municipal Subsidy Statistics

# Join with municipality data for geographic aggregation
# Requires joining with field data that has municipality info
from gcs_data_catalog.landbrugsareal import read_field_data

fields = read_field_data(year=2024)
subsidies_with_geo = df.merge(
    fields[['markbloknummer', 'municipality']],
    on='markbloknummer',
    how='left'
)

municipal_stats = subsidies_with_geo.groupby('municipality').agg({
    'grundbetaling_dkk': 'sum',
    'imkareal': 'sum'
}).reset_index()

Join Keys

Data Quality Notes

Subsidies

• Update frequency: Annual (after payment year closes)
• Coverage: All EU agricultural subsidy recipients in Denmark
• Caveat: Some CVR numbers may be inactive/closed companies

Property Owners

• Update frequency: Weekly
• Coverage: All registered property owners in Denmark
• Caveat: Nested JSON structure requires careful handling
• Privacy: Personal owner data (ejendePerson) may have restrictions

Related Skills

• landbrugsareal/ - Field boundaries for geographic context
• miljo/ - Environmental compliance affecting subsidies
• husdyr/ - Livestock data for animal-based subsidies
• medarbejdere/ - Employee data for labor cost analysis

R2 Paths Reference

# List available subsidy snapshots
rclone lsd r2:landbruget-data/silver/subsidies/

# List CVR enrichment data
rclone lsd r2:landbruget-data/gold/cvr_enrichment/

# List property owner snapshots
rclone lsd r2:landbruget-data/silver/property_owners/