automated_quality_check_round2.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import numpy as np\n",
    "import pandas as pd\n",
    "import pycountry\n",
    "import glob"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "path = '/data/users/kgruber/other-data/impacts_paper/'\n",
    "results_path = path + 'automated-quality-check-round2/'\n",
    "review_path = path + 'review_rounds/'\n",
    "output_path = path + 'after-automated-quality-check-round2/'"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "select file names\n",
    "\n",
    "- if there is more than 1 file, select xlsx, not csv\n",
    "- -> always the longest file name"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "fs = glob.glob(results_path + '*results*')\n",
    "names = [f[76:79] for f in fs]\n",
    "lens = [len(f) for f in fs]\n",
    "d = pd.Series(fs,index=[np.array(names),np.array(lens)])\n",
    "results_files = d.loc[pd.Series(lens,index=names).groupby(names).max().reset_index().apply(tuple,axis=1)].values\n",
    "\n",
    "fs = glob.glob(results_path + '*result_*')\n",
    "names = [f[76:79] for f in fs]\n",
    "lens = [len(f) for f in fs]\n",
    "d = pd.Series(fs,index=[np.array(names),np.array(lens)])\n",
    "results_check_files = d.loc[pd.Series(lens,index=names).groupby(names).max().reset_index().apply(tuple,axis=1)].values"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "read and merge files from those lists of files"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "for file in results_files:\n",
    "    if file[-1] == 'x':\n",
    "        f = pd.read_excel(file,engine='openpyxl',index_col=0)\n",
    "    else:\n",
    "        f = pd.read_csv(file,index_col=0)\n",
    "    f = f[~f.index.isna()]\n",
    "    if 'responses' in globals():\n",
    "        responses = pd.concat([responses,f],axis=0)\n",
    "    else:\n",
    "        responses = f"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "for file in results_check_files:\n",
    "    if file[-1] == 'x':\n",
    "        f = pd.read_excel(file,engine='openpyxl',index_col=0)\n",
    "    else:\n",
    "        try:\n",
    "            f = pd.read_csv(file,index_col=0)\n",
    "        except:\n",
    "            f = pd.read_csv(file,index_col=0,encoding='unicode_escape')\n",
    "    f = f[~f.index.isna()]\n",
    "    if 'results_check' in globals():\n",
    "        results_check = pd.concat([results_check,f],axis=0)\n",
    "    else:\n",
    "        results_check = f"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## entry has been individually checked"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "individual_check = responses['Checked'].replace(1,'Passed').replace(np.nan,'Failed')\n",
    "individual_check.name = 'individual_check'"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## iso codes"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "test passes if code is iso code or \"-\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "iso_codes = [list(pycountry.countries)[i].alpha_2 for i in range(len(list(pycountry.countries)))]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {
    "code_folding": [
     0
    ]
   },
   "outputs": [],
   "source": [
    "def check_iso(code):\n",
    "    if (code in iso_codes) or (code =='-'):\n",
    "        return 'Passed'\n",
    "    else:\n",
    "        return 'Failed'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "is_iso_code = pd.Series(responses['Country:'].apply(check_iso),name='iso_code')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "check if there are fails due to spaces"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "(responses['Country:'][is_iso_code=='Failed'].str.replace(' ','').apply(check_iso)=='Passed').sum()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## numeric values"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {
    "code_folding": [
     0
    ]
   },
   "outputs": [],
   "source": [
    "def check_float(number):\n",
    "    try:\n",
    "        if np.isnan(float(number)):\n",
    "            return 'Failed'\n",
    "        else:\n",
    "            return 'Passed'\n",
    "    except:\n",
    "        return 'Failed'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [],
   "source": [
    "value_is_numeric = pd.Series(responses['4a. Numeric value, e.g., 0.3, 1.5, 3'].apply(check_float),name='numeric_value')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Unit match metrics"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### prepare unit columns"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### merge columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {
    "code_folding": [
     0
    ]
   },
   "outputs": [],
   "source": [
    "def merge_col(line):\n",
    "    return line.dropna().values"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [],
   "source": [
    "measurement_unit = responses[['4c-1. Measurement unit:','4c-2. Measurement unit:']].apply(merge_col,axis=1)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "there are two entries where there are values in both columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "2"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "(measurement_unit.apply(len)!=1).sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {
    "code_folding": []
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0.0          [DC, MW/ha]\n",
       "1.0    [DC, MWh/year/ha]\n",
       "dtype: object"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "measurement_unit[(measurement_unit.apply(len)!=1)]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "only use second value, first value is referring to current (DC)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [],
   "source": [
    "measurement_unit[measurement_unit.apply(len)!=1] = measurement_unit[measurement_unit.apply(len)!=1].apply(lambda x: [x[1]])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [],
   "source": [
    "measurement_unit = measurement_unit.apply(lambda x: x[0])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### unify units - replace similar units with one writing"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [],
   "source": [
    "replace = pd.DataFrame({'replace':['acres/MW', 'm²/GWh', 'ha/GWh/y',    'W m-2', 'W/m²', 'WP/m2', 'W_p/m²', 'We m-2', 'We m−2', 'We/m²', 'W_e/m2', 'w/ft2', 'kW_e/m²', 'MWi km−2', 'GW_e/m2', 'kWh/year/m²', 'kWh/m²year',  'kWh/m2/year', 'kWh/m²/year', 'MWh/year/m²', 'GWh/yr/m2',   'GJ/m2/year', 'rho_e W_e/m2', 'm^2', 'm2/VPM (Vehicl mile traveled)'],\n",
    "                        'with':   ['acre/MW',  'm2/GWh', 'ha/GWh/year', 'W/m2',  'W/m2', 'Wp/m2', 'Wp/m2',  'We/m2',  'We/m2',  'We/m2', 'We/m2',  'W/ft2', 'kWe/m2',  'MWi/km2', 'GWe/m2',   'kWh/year/m2', 'kWh/year/m2', 'kWh/year/m2', 'kWh/year/m2', 'MWh/year/m2', 'GWh/year/m2', 'GJ/year/m2', 'rhoe We/m2',   'm2',  'm2/VPM (Vehicle mile traveled)']})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [],
   "source": [
    "for i in range(len(replace)):\n",
    "    measurement_unit = measurement_unit.replace(replace['replace'][i],replace['with'][i])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### categorise metrics and units"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [],
   "source": [
    "# metrics\n",
    "power_related_metrics = ['power_density','installed_power_density','output_power_density','power per unit area',\n",
    "                         'capacity_density']\n",
    "energy_related_metrics = ['energy_density','surface_performance_ratio','energy_yields',\n",
    "                          'aperture_specific_net_electrical_output']\n",
    "land_related_metrics = ['land_use_efficiency','land_requirements','total_impact_area','direct_impact_area_permanent',\n",
    "                        'direct_impact_area_temporary','direct_impact_area','land_transformation','land_use_footprints',\n",
    "                        'land_use_requirements','area_requirements','direct_land_requirements','land_occupation',\n",
    "                        'spatial_footprint','land-use intensity','land use intensity',' land use intensity',\n",
    "                        'land_use_intensity','land_use','land_area','area_required_by_system','land_occupational_value'] # land_occupational_value had to be added\n",
    "other_metrics = ['land_use per vehicle mile traveled (based on land_use_intensity)','land-use per vehicle mile',\n",
    "          'land-use impact (total habitat developed)']\n",
    "# units\n",
    "footprint_power_related = ['m2/W','m2/Wp','m2/kW','m2/kWp','m2/MW','ha/MW','ha/MWp','acre/MW','acre/MW-DC',\n",
    "                           'acre/MW-AC','km2/MW']\n",
    "footprint_energy_related = ['m2/MWh','m2/GWh','km2/GWh','km2/TWh']\n",
    "footprint_annual_energy_related = ['m2/MWh/year','ha/MWh/year','ha/GWh/year','ha/TWh/year','km2/TWh/year']\n",
    "power_density = ['W/m2','Wp/m2','We/m2','W/ft2','kW/m2','kWp/m2','kWe/m2','kW/ft2','kW/ha','kW/acre','MW/m2',\n",
    "                 'MW/ha','MWp/ha','MW/km2','MWi/km2','MW/acre','GWe/m2'] # kW/ft2 had to be added\n",
    "energy_density = ['Wh/cm2/day','kWh/year/m2','kWh/year/acre','kWh/year/ft2','kWh/year/ha','MWh/year/acre',\n",
    "                  'MWh/year/m2','MWh/year/ha','GWh/year/m2','GWh/year/km2','GJ/year/m2','TWh/year/km2']\n",
    "unclear_units = ['MJ/m2','GJ/unit/year','km2 year/GWh','kW/ft','km/GWh','m2/VPM (Vehicle mile traveled)',\n",
    "                 'm2','rhoe We/m2','MW/h/ha']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [],
   "source": [
    "metrics_classification = pd.Series(['power']*len(power_related_metrics)+\n",
    "                                   ['energy']*len(energy_related_metrics)+\n",
    "                                   ['footprint']*len(land_related_metrics)+\n",
    "                                   ['other']*len(other_metrics),\n",
    "                                  index = power_related_metrics+energy_related_metrics+land_related_metrics+other_metrics)\n",
    "unit_classification = pd.Series(['power']*len(power_density)+\n",
    "                                ['energy']*len(energy_density)+\n",
    "                                ['footprint']*(len(footprint_power_related)+len(footprint_energy_related)+len(footprint_annual_energy_related))+\n",
    "                                ['other']*len(unclear_units),\n",
    "                                index = power_density+energy_density+footprint_power_related+footprint_energy_related+footprint_annual_energy_related+unclear_units)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "check if all metrics and units are included"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "1.0"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "responses['3. Metrics used:'].apply(lambda x: x in metrics_classification.index.values).mean()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "1.0"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "measurement_unit.apply(lambda x: x in unit_classification.index.values).mean()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### check units"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {
    "code_folding": [
     0
    ]
   },
   "outputs": [],
   "source": [
    "def check_unit(data):\n",
    "    metric = metrics_classification[data['Metric']]\n",
    "    unit = unit_classification[data['Unit']]\n",
    "    if metric =='other' or unit =='other':\n",
    "        return 'Unclear'\n",
    "    if metric ==  unit:\n",
    "        return 'Passed'\n",
    "    else:\n",
    "        return 'Failed'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [],
   "source": [
    "metric_unit = pd.DataFrame({'Metric':responses['3. Metrics used:'].values,\n",
    "                            'Unit':measurement_unit.values},\n",
    "                           index=responses.index)\n",
    "metric_fits_unit = pd.Series(metric_unit.apply(check_unit,axis=1),name='metric_fits_unit')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [],
   "source": [
    "metric_unit_classification = pd.DataFrame({'metric_classification':metric_unit.Metric.map(metrics_classification),\n",
    "                                           'unit_classification':metric_unit.Unit.map(unit_classification)})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [],
   "source": [
    "metric_fits_unit_classification = pd.concat([metric_fits_unit,metric_unit_classification],axis=1)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Check range of values and whether Wp (Watt peak) has capacity related power component"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### unify units"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "add combined measurement units as column"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [],
   "source": [
    "responses2  = pd.concat([responses.loc[:,:'4c-2. Measurement unit:'],\n",
    "                         pd.Series(measurement_unit,name='4c. Measurement unit:'),\n",
    "                         responses.loc[:,'4d. Type of value':]],axis=1)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### conversion"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [],
   "source": [
    "m2peracre = 4046.86\n",
    "m2perft2 = 10.7639\n",
    "\n",
    "WattUnits = pd.DataFrame({'start':    ['W/ft2',   'kW/m2', 'kW/ft2',     'kW/ha','kW/acre',      'MW/m2', 'MW/ha','MW/km2','MW/acre'],\n",
    "                          'target':   'W/m2',\n",
    "                          'factor':   [1/m2perft2, 1000,   1000/m2perft2, 0.1,    1000/m2peracre, 1/10**6, 100,    1,       10**6/m2peracre]})\n",
    "OtherWattUnits = pd.DataFrame({'start':   ['kWp/m2', 'MWp/ha', 'kWe/m2', 'GWe/m2', 'MWi/km2'],\n",
    "                               'target':  ['Wp/m2',  'Wp/m2',  'We/m2',  'We/m2',  'Wi/m2'],\n",
    "                               'factor':  [1000,     100,      1000,     10**6,    1]})\n",
    "WatthUnits = pd.DataFrame({'start':  ['Wh/cm2/day', 'kWh/year/acre', 'kWh/year/ft2','kWh/year/ha','MWh/year/acre','MWh/year/m2','MWh/year/ha','GWh/year/m2','GWh/year/km2','GJ/year/m2','TWh/year/km2'],\n",
    "                           'target': 'kWh/year/m2',\n",
    "                           'factor': [3650,         1/m2peracre,     1/m2perft2,    1/10**4,      1000/m2peracre, 1000,         0.1,          10**6,        1,             10**6/3600,  1000]})\n",
    "AreaPowerUnits = pd.DataFrame({'start':  ['m2/W',  'm2/Wp',  'm2/MW',  'ha/MW', 'ha/MWp', 'acre/MW',      'acre/MW-DC',   'acre/MW-AC',   'km2/MW'],\n",
    "                               'target': ['m2/kW', 'm2/kWp', 'm2/kW',  'm2/kW', 'm2/kWp', 'm2/kW',        'm2/kW-DC',     'm2/kW-AC',     'm2/kW'],\n",
    "                               'factor': [1000,    1000,     1/1000,   10,      10,       m2peracre/1000, m2peracre/1000, m2peracre/1000, 1000]})\n",
    "AreaEnergyUnits = pd.DataFrame({'start':  ['m2/MWh','m2/GWh','km2/GWh','km2/TWh','m2/MWh/year','ha/MWh/year','ha/GWh/year','ha/TWh/year','km2/TWh/year'],\n",
    "                                'target': ['m2/kWh','m2/kWh','m2/kWh', 'm2/kWh', 'm2/kWh/year','m2/kWh/year','m2/kWh/year','m2/kWh/year','m2/kWh/year'],\n",
    "                                'factor': [1/1000,  1/10**6, 1,        1/1000,   1/1000,       10,           0.01,         1/10**5,      1]})\n",
    "\n",
    "unit_conversion = pd.concat([WattUnits,OtherWattUnits,WatthUnits,AreaPowerUnits,AreaEnergyUnits],axis=0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [],
   "source": [
    "new_unit = responses2['4c. Measurement unit:'].map(unit_conversion.set_index('start').target)\n",
    "factor = responses2['4c. Measurement unit:'].map(unit_conversion.set_index('start').factor)\n",
    "# fill in lines where unit stays the same\n",
    "new_unit[new_unit.isna()] = responses2['4c. Measurement unit:'][new_unit.isna()]\n",
    "factor = factor.fillna(1)\n",
    "new_value = responses2['4a. Numeric value, e.g., 0.3, 1.5, 3'][value_is_numeric=='Passed'].apply(float)*factor[value_is_numeric=='Passed']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {},
   "outputs": [],
   "source": [
    "responses3 = pd.concat([responses2.loc[:,:'4a. Numeric value, e.g., 0.3, 1.5, 3'],\n",
    "                        pd.Series(responses2.index.map(new_value),name='4a-1. Converted value',index=responses2.index),\n",
    "                        responses2.loc[:,'4b. Is power-related component of the land-use requirement expressed as energy e.g., ha/GWh/year?':'4c. Measurement unit:'],\n",
    "                        pd.Series(new_unit,name='4c-3. Converted measurement unit'),\n",
    "                        responses2.loc[:,'4d. Type of value':]],axis=1)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "categorise power related components for grouping and checking whether where unit is Wp the power component is capacity related"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {},
   "outputs": [],
   "source": [
    "capacity_related_components = ['nameplate (installed) capacity','nameplate (installed) capacity DC','nameplate_capacity',\n",
    "                               'nominal nameplate capacity','peak_rated_power','peak_capacity',\n",
    "                               'number of turbines of a particular type']\n",
    "\n",
    "energy_related_components = ['nameplate capacity multiplied by capacity factor',\n",
    "                             'nameplate (installed) capacity multiplied by capacity factor',\n",
    "                             'wind-density multiplied by capacity factor','wind density multiplied by capacity factor',\n",
    "                             'solar constant/insolation multiplied by capacity factor/efficiency',\n",
    "                             'typical solar insolation at average-insolation location',\n",
    "                             'typical solar insolation at high-insolation location',\n",
    "                             'estimated energy generation (unsure what it means)','modelled energy generation',\n",
    "                             'simulated using ﬂow-sheet computer program based softbeen simulated using ﬂow-sheet computer program based software Cycle-Tempo',\n",
    "                             'reported energy generation','net energy generation (electricity generation after substracting energy needed for manufacturing/dismantling, construction/operation, and transportation)',\n",
    "                             'experimentally measured','annual energy production','net output']\n",
    "\n",
    "unclear_components = ['unclear','author assumes a typical power per unit area of 2.5\\u2009W\\u2009m−2',\n",
    "                      'commercial module output','module','no power component','no power']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {},
   "outputs": [],
   "source": [
    "powercomponent_classification = pd.Series(['capacity']*len(capacity_related_components)+\n",
    "                                          ['energy']*len(energy_related_components)+\n",
    "                                          ['unclear']*len(unclear_components),\n",
    "                                          index = capacity_related_components+energy_related_components+unclear_components)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "classify"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {},
   "outputs": [],
   "source": [
    "powercomponent_class = responses['10. Power-related component of land-use requirements is represented by:'].map(powercomponent_classification)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### check if Wp is only related with capacity related components"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {
    "code_folding": [
     0
    ]
   },
   "outputs": [],
   "source": [
    "def check_Wp_capacity(data):\n",
    "    if data.new_unit=='Wp/m2':\n",
    "        if data.powercomp_class=='capacity':\n",
    "            return 'Passed'\n",
    "        else:\n",
    "            return 'Failed'\n",
    "    else:\n",
    "        return 'Not applicable'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {},
   "outputs": [],
   "source": [
    "Wp_is_capacity = pd.Series(pd.DataFrame({'powercomp_class':powercomponent_class,\n",
    "                                         'new_unit':new_unit}).apply(check_Wp_capacity,axis=1),name='Wp_is_capacity')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### find outliers"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "only look for outliers where there are more than 10 values -> determine frequency of unit-powercomponentclass combinations"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {},
   "outputs": [],
   "source": [
    "unit_powercomponentclass = responses3['4c-3. Converted measurement unit'] + '_' + powercomponent_class\n",
    "unit_powercomponentclass[value_is_numeric=='Failed'] = 'non-numeric'\n",
    "frequency_units = responses3['4c-3. Converted measurement unit'].groupby(unit_powercomponentclass).count()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "metadata": {
    "code_folding": [
     0,
     13
    ]
   },
   "outputs": [],
   "source": [
    "def find_outliers(data):\n",
    "    #define a list to accumlate anomalies\n",
    "    outliers = []\n",
    "    # Set upper and lower limit to 3 standard deviation\n",
    "    stdev = np.std(data)\n",
    "    avg = np.mean(data)\n",
    "    lower_limit  = avg - stdev*3 \n",
    "    upper_limit = avg + stdev*3\n",
    "    # Find outliers\n",
    "    def test_if_in_range(value):\n",
    "        return in_range(lower_limit,upper_limit,value)\n",
    "    return data.apply(test_if_in_range)\n",
    "\n",
    "def in_range(lower,upper,value):\n",
    "    if (value > lower) & (value < upper):\n",
    "        return 'Passed'\n",
    "    else:\n",
    "        return 'Failed'"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "only use numeric values"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "metadata": {},
   "outputs": [],
   "source": [
    "responses3_numeric = responses3[value_is_numeric=='Passed']\n",
    "unit_powercomponentclass_numeric = unit_powercomponentclass[value_is_numeric=='Passed']"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "find outliers within groups of combinations of units and powercomponentclasses larger 10"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "metadata": {},
   "outputs": [],
   "source": [
    "large_groups = frequency_units.index[(frequency_units>10)]\n",
    "outliers_per_group = [find_outliers(responses3_numeric[unit_powercomponentclass_numeric==unit]['4a-1. Converted value']) \n",
    "                      for unit in large_groups.values]\n",
    "outliers = pd.concat(outliers_per_group,axis=0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "metadata": {},
   "outputs": [],
   "source": [
    "value_within_range = pd.Series(responses3.index.map(outliers).fillna('Too few values within this group'),\n",
    "                               name='value_range',index=responses3.index)\n",
    "value_within_range[value_is_numeric=='Failed'] = 'non-numeric'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "value_range\n",
       "Failed                                25\n",
       "Passed                              1797\n",
       "Too few values within this group     181\n",
       "non-numeric                           32\n",
       "Name: value_range, dtype: int64"
      ]
     },
     "execution_count": 43,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "value_within_range.groupby(value_within_range).count()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## check if matches: DOI + first Author + scopus_id + year of publication"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "metadata": {},
   "outputs": [],
   "source": [
    "rev_file = pd.read_csv(review_path + 'SCOPUS_DOI2.csv',encoding = \"utf-8\",dtype=str).dropna().drop_duplicates()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "metadata": {},
   "outputs": [],
   "source": [
    "mapped_SCOPUS = responses3['1b. DOI link'].map(rev_file.set_index('DOI').SCOPUS)\n",
    "scopusID_fits_doi = pd.Series(responses3.index.map(mapped_SCOPUS.dropna()==responses3['1a. SCOPUS ID'].apply(str)[mapped_SCOPUS.notna()]),\n",
    "                              index=responses3.index,name='scopusID_fits_doi'\n",
    "                             ).fillna('Not available').replace(True,'Passed').replace(False,'Failed')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# merge results"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "metadata": {},
   "outputs": [],
   "source": [
    "results_quality_check = pd.concat([individual_check,is_iso_code,value_is_numeric,metric_fits_unit,value_within_range,Wp_is_capacity,scopusID_fits_doi],axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "individual_check\n",
       "Passed    2035\n",
       "Name: individual_check, dtype: int64"
      ]
     },
     "execution_count": 47,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "individual_check.groupby(individual_check).count()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "iso_code\n",
       "Failed      74\n",
       "Passed    1961\n",
       "Name: iso_code, dtype: int64"
      ]
     },
     "execution_count": 48,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "is_iso_code.groupby(is_iso_code).count()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "numeric_value\n",
       "Failed      32\n",
       "Passed    2003\n",
       "Name: numeric_value, dtype: int64"
      ]
     },
     "execution_count": 49,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "value_is_numeric.groupby(value_is_numeric).count()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "metric_fits_unit\n",
       "Failed       40\n",
       "Passed     1973\n",
       "Unclear      22\n",
       "Name: metric_fits_unit, dtype: int64"
      ]
     },
     "execution_count": 50,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "metric_fits_unit.groupby(metric_fits_unit).count()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "value_range\n",
       "Failed                                25\n",
       "Passed                              1797\n",
       "Too few values within this group     181\n",
       "non-numeric                           32\n",
       "Name: value_range, dtype: int64"
      ]
     },
     "execution_count": 51,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "value_within_range.groupby(value_within_range).count()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Wp_is_capacity\n",
       "Failed              13\n",
       "Not applicable    2013\n",
       "Passed               9\n",
       "Name: Wp_is_capacity, dtype: int64"
      ]
     },
     "execution_count": 52,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "Wp_is_capacity.groupby(Wp_is_capacity).count()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 53,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "scopusID_fits_doi\n",
       "Not available    1448\n",
       "Passed            587\n",
       "Name: scopusID_fits_doi, dtype: int64"
      ]
     },
     "execution_count": 53,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "scopusID_fits_doi.groupby(scopusID_fits_doi).count()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# remove passed rows and columns and merge comments"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 54,
   "metadata": {},
   "outputs": [],
   "source": [
    "# remove lines where all tests passed or are not available or not applicable\n",
    "passed_results = (results_quality_check == 'Passed').sum(axis=1)\n",
    "not_available_results = (results_quality_check == 'Not available').sum(axis=1)\n",
    "not_applicable_results = (results_quality_check == 'Not applicable').sum(axis=1)\n",
    "results_quality_check_clean_rows = results_quality_check[passed_results+not_available_results+not_applicable_results<results_quality_check.shape[1]]\n",
    "# remove columns where all tests passed or are not available\n",
    "passed_results = (results_quality_check_clean_rows == 'Passed').sum(axis=0)\n",
    "not_available_results = (results_quality_check_clean_rows == 'Not available').sum(axis=0)\n",
    "not_applicable_results = (results_quality_check_clean_rows == 'Not applicable').sum(axis=0)\n",
    "results_quality_check_clean = results_quality_check_clean_rows.loc[:,passed_results+not_available_results+not_applicable_results<results_quality_check_clean_rows.shape[0]]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "check if there are no rows with only 'Passed' left"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 55,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0"
      ]
     },
     "execution_count": 55,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "((results_quality_check_clean=='Passed').sum(axis=1)==results_quality_check_clean.shape[1]).sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 56,
   "metadata": {},
   "outputs": [],
   "source": [
    "for col in results_quality_check_clean.columns:\n",
    "    if 'merged_results_quality_check_clean' in globals():\n",
    "        try:\n",
    "            res = pd.concat([results_quality_check_clean[col],\n",
    "                             results_check[col+'_comment']],axis=1).dropna(subset=[col])\n",
    "            merged_results_quality_check_clean = pd.concat([merged_results_quality_check_clean,res],axis=1)\n",
    "        except:\n",
    "            res = results_quality_check_clean[col].dropna()\n",
    "            merged_results_quality_check_clean = pd.concat([merged_results_quality_check_clean,res],axis=1)\n",
    "    else:\n",
    "        merged_results_quality_check_clean = pd.concat([results_quality_check_clean[col],\n",
    "                                                        results_check[col+'_comment']],axis=1).dropna(subset=[col])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 57,
   "metadata": {},
   "outputs": [],
   "source": [
    "comments = pd.DataFrame(np.nan * np.ones(shape=results_quality_check_clean.shape),\n",
    "                        columns=results_quality_check_clean.columns.values + '_comment_round2',\n",
    "                        index=results_quality_check_clean.index)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 58,
   "metadata": {},
   "outputs": [],
   "source": [
    "merged_results_quality_check_clean_with_comments = pd.concat([merged_results_quality_check_clean,comments],axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 59,
   "metadata": {},
   "outputs": [],
   "source": [
    "merged_results_quality_check_clean_with_comments.to_csv(output_path + 'results_automated_quality_check_round2.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 60,
   "metadata": {},
   "outputs": [],
   "source": [
    "responses3.to_csv(output_path + 'results_merged_unified_after_automated_check_round2.csv')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "heading_collapsed": true
   },
   "source": [
    "#### notes"
   ]
  },
  {
   "cell_type": "raw",
   "metadata": {
    "hidden": true
   },
   "source": [
    "'author assumes a typical power per unit area of 2.5\\u2009W\\u2009m−2',\n",
    "                                    'commercial module output','module'] -> mark as unclear\n",
    "\n",
    "'nameplate (installed) capacity','nameplate (installed) capacity DC',\n",
    "                                    'nameplate_capacity','nominal nameplate capacity','peak_rated_power',\n",
    "                                    'peak_capacity' -> capacity\n",
    "\n",
    "power_related_components_capacity_factor = ['nameplate capacity multiplied by capacity factor',\n",
    "                                            'nameplate (installed) capacity multiplied by capacity factor',\n",
    "                                            'wind-density multiplied by capacity factor',\n",
    "                                            'wind density multiplied by capacity factor',\n",
    "                                            'solar constant/insolation multiplied by capacity factor/efficiency',\n",
    "                                            'typical solar insolation at average-insolation location',\n",
    "                                            'typical solar insolation at high-insolation location']\n",
    "energy_related_components = ['estimated energy generation (unsure what it means)','modelled energy generation',\n",
    "                             'simulated using ﬂow-sheet computer program based softbeen simulated using ﬂow-sheet computer program based software Cycle-Tempo',\n",
    "                             'reported energy generation','net energy generation (electricity generation after substracting energy needed for manufacturing/dismantling, construction/operation, and transportation)',\n",
    "                             'experimentally measured','annual energy production','net output'] -> energy\n",
    "\n",
    "unclear_components = ['unclear', -> unclear\n",
    "\n",
    "'number of turbines of a particular type', -> capacity\n",
    "\n",
    "'no power component','no power'] -> unclear\n",
    "\n",
    "\n",
    "\n",
    "use those for RANGE together with units\n",
    "\n",
    "\n",
    "check if ...Wp is capacity\n",
    "\n",
    "check if there are no only passed rows left"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python [conda env:.conda-py39]",
   "language": "python",
   "name": "conda-env-.conda-py39-py"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.9.6"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}