Gcs Medarbejdere Data

GCS 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: gs://$GCS_BUCKET/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: gs://$GCS_BUCKET/silver/work permits/*/data.parquet

Worker Safety Reports

Path: gs://$GCS_BUCKET/silver/worker safety/*/data.parquet

Stable Fires (Incidents)

Path: gs://$GCS_BUCKET/silver/stable fires/*/data.parquet

Transport Accidents

Path: gs://$GCS_BUCKET/silver/transportation accidents/*/data.parquet

Bronze Layer

DMA Permits

Path: gs://$GCS_BUCKET/bronze/dma/*/data.parquet

Common Queries

Get Inspection History for CVR

import pyarrow.parquet as pq
from google.cloud import storage
import io

client = storage.Client()
bucket = client.bucket('$GCS_BUCKET')

# Read arbejdstilsynet inspections
blob = bucket.blob('gold/arbejdstilsynet_inspections/2025-01-10/data.parquet')
buffer = io.BytesIO()
blob.download_to_file(buffer)
buffer.seek(0)
df = pq.read_table(buffer).to_pandas()

# 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
blob = bucket.blob('silver/work permits/2025-01-10/data.parquet')
buffer = io.BytesIO()
blob.download_to_file(buffer)
buffer.seek(0)
permits = pq.read_table(buffer).to_pandas()

# 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)

GCS Paths Reference

# List arbejdstilsynet inspection data
gsutil ls gs://$GCS_BUCKET/gold/arbejdstilsynet_inspections/

# List work permit data
gsutil ls gs://$GCS_BUCKET/silver/work\ permits/

# List worker safety data
gsutil ls gs://$GCS_BUCKET/silver/worker\ safety/

# List incident data
gsutil ls gs://$GCS_BUCKET/silver/stable\ fires/
gsutil ls gs://$GCS_BUCKET/silver/transportation\ accidents/

# List DMA permit data
gsutil ls gs://$GCS_BUCKET/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