r2-medarbejdere-data

name: r2-medarbejdere-data description: | Activates when querying employee and workplace safety data from R2. Use this skill for: Arbejdstilsynet inspections, work permits, safety violations, workplace accidents, compliance rates, foreign workers, incident tracking. Keywords: medarbejdere, employees, worker, arbejdstilsynet, inspection, tilsyn, safety, arbejdsmiljø, accident, ulykke, compliance

R2 Medarbejdere (Employees) Data Catalog

Employee and workplace safety data from regulatory inspections and incident reports.

Frontend Metrics Supported

Available Datasets

Gold Layer

Arbejdstilsynet Inspections (536 rows)

Path: r2://landbruget-data/gold/arbejdstilsynet_inspections/*/data.parquet

Schema (introspected):

date: date32
case_count: int64
decision: string
work_env_issue: string
cvr_number: string
company_name: string
industry: string
severity_score: double
company_compliance_rate: double
is_repeat_offender: bool
inspector_id: string
follow_up_date: date32
fine_amount_dkk: double
corrective_deadline: date32
[29 columns total]

Silver Layer

Work Permits

Path: r2://landbruget-data/silver/work permits/*/data.parquet

Worker Safety Reports

Path: r2://landbruget-data/silver/worker safety/*/data.parquet

Stable Fires (Incidents)

Path: r2://landbruget-data/silver/stable fires/*/data.parquet

Transport Accidents

Path: r2://landbruget-data/silver/transportation accidents/*/data.parquet

Bronze Layer

DMA Permits

Path: r2://landbruget-data/bronze/dma/*/data.parquet

Common Queries

Get Inspection History for CVR

import duckdb
from common.storage.filesystem import setup_duckdb_cloud_auth

conn = duckdb.connect()
setup_duckdb_cloud_auth(conn)

# Read arbejdstilsynet inspections
df = conn.execute("""
    SELECT * FROM read_parquet('r2://landbruget-data/gold/arbejdstilsynet_inspections/2025-01-10/data.parquet')
""").df()

# Filter by CVR
cvr = '31373077'
company_inspections = df[df['cvr_number'] == cvr]

print(f"Total inspections: {len(company_inspections)}")
print(f"Total cases: {company_inspections['case_count'].sum()}")
print(f"Average severity: {company_inspections['severity_score'].mean():.2f}")
print(f"Compliance rate: {company_inspections['company_compliance_rate'].iloc[-1]:.2%}")

Calculate Industry Compliance Rates

# Aggregate by industry
industry_stats = df.groupby('industry').agg({
    'cvr_number': 'nunique',
    'case_count': 'sum',
    'severity_score': 'mean',
    'company_compliance_rate': 'mean',
    'is_repeat_offender': 'sum'
}).reset_index()

industry_stats.columns = ['industry', 'companies', 'total_cases',
                          'avg_severity', 'avg_compliance', 'repeat_offenders']
industry_stats = industry_stats.sort_values('avg_compliance', ascending=True)

Find Repeat Offenders

# Companies with multiple violations
repeat_offenders = df[df['is_repeat_offender'] == True]

# Group by company
offender_summary = repeat_offenders.groupby(['cvr_number', 'company_name']).agg({
    'case_count': 'sum',
    'severity_score': 'mean',
    'fine_amount_dkk': 'sum'
}).reset_index()
offender_summary = offender_summary.sort_values('case_count', ascending=False)

Severity Analysis by Issue Type

# Analyze by work environment issue category
issue_analysis = df.groupby('work_env_issue').agg({
    'case_count': 'sum',
    'severity_score': 'mean',
    'fine_amount_dkk': 'sum'
}).reset_index()
issue_analysis = issue_analysis.sort_values('severity_score', ascending=False)

Monthly Inspection Trends

import pandas as pd

# Convert to datetime
df['inspection_month'] = pd.to_datetime(df['date']).dt.to_period('M')

monthly_stats = df.groupby('inspection_month').agg({
    'cvr_number': 'nunique',
    'case_count': 'sum',
    'severity_score': 'mean'
}).reset_index()
monthly_stats.columns = ['month', 'companies_inspected', 'total_cases', 'avg_severity']

Calculate Fines by Municipality

# Join with CVR address data for geographic analysis
# (requires joining with okonomi/cvr_enrichment data)
from gcs_data_catalog.okonomi import read_cvr_data

cvr_geo = read_cvr_data()
inspections_with_geo = df.merge(
    cvr_geo[['cvr_number', 'municipality']],
    on='cvr_number',
    how='left'
)

municipal_fines = inspections_with_geo.groupby('municipality').agg({
    'fine_amount_dkk': 'sum',
    'case_count': 'sum'
}).reset_index()

Foreign Worker Analysis

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

# Count by nationality
nationality_counts = permits.groupby('nationality').agg({
    'worker_count': 'sum'
}).reset_index()
nationality_counts = nationality_counts.sort_values('worker_count', ascending=False)

# Companies with most foreign workers
company_workers = permits.groupby('cvr_number').agg({
    'worker_count': 'sum'
}).reset_index()
company_workers = company_workers.sort_values('worker_count', ascending=False)

Decision Types

Work Environment Issues

Common categories in work_env_issue:

• Ergonomi - Ergonomic issues (lifting, posture)
• Kemisk arbejdsmiljø - Chemical hazards
• Psykisk arbejdsmiljø - Psychological work environment
• Ulykker - Accident prevention
• Støj - Noise exposure
• Maskinsikkerhed - Machine safety
• Bygge og anlæg - Construction safety

Join Keys

Data Quality Notes

Arbejdstilsynet Inspections

• Update frequency: After inspection completion
• Coverage: All inspected agricultural businesses
• Source: Danish Working Environment Authority
• Caveat: Not all farms inspected - risk-based selection

Work Permits

• Update frequency: Monthly
• Coverage: All registered foreign worker permits
• Source: SIRI (Danish Immigration Service)

Incidents (Fires, Accidents)

• Update frequency: After incident reporting
• Coverage: Reported incidents only
• Caveat: Underreporting possible

Compliance Score Interpretation

The company_compliance_rate field (0-1 scale):

• 0.90-1.00: Excellent compliance history
• 0.75-0.89: Good compliance, minor issues
• 0.50-0.74: Moderate compliance, attention needed
• 0.00-0.49: Poor compliance, likely repeat offender

Related Skills

• okonomi/ - Company financial data for CVR context
• landbrugsareal/ - Land area and farm size context
• miljo/ - Environmental compliance (related violations)
• husdyr/ - Livestock operations (animal handling safety)

R2 Paths Reference

# List arbejdstilsynet inspection data
rclone lsd r2:landbruget-data/gold/arbejdstilsynet_inspections/

# List work permit data
rclone ls r2:landbruget-data/silver/work\ permits/

# List worker safety data
rclone ls r2:landbruget-data/silver/worker\ safety/

# List incident data
rclone ls r2:landbruget-data/silver/stable\ fires/
rclone ls r2:landbruget-data/silver/transportation\ accidents/

# List DMA permit data
rclone lsd r2:landbruget-data/bronze/dma/

Agricultural-Specific Safety Concerns

Common issues in agricultural workplace inspections:

1. Machine safety - Tractors, harvesters, feed machinery
2. Chemical exposure - Pesticides, fertilizers, cleaning agents
3. Animal handling - Large animal injuries, zoonotic diseases
4. Ergonomics - Repetitive lifting, awkward postures
5. Confined spaces - Silos, manure pits, grain bins
6. Environmental - Heat stress, cold exposure, UV radiation