merge_and_split_files.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "path = '/data/users/kgruber/other-data/impacts_paper/'\n",
    "results_path = path + 'individual/'"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "read main results file"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "columns = ['Timestamp','1a. SCOPUS ID','1b. DOI link','1c. Last name of the first author','1d. Year of publication:','2. VRES technology:','3. Metrics used:','4a. Numeric value, e.g., 0.3, 1.5, 3','4b. Is power-related component of the land-use requirement expressed as energy e.g., ha/GWh/year?','4c-1. Measurement unit:','4c-2. Measurement unit:','4d. Type of value','4e. Geographical scale:','5a.  Temporary needed areas for the plant construction are included ','5b. Main modules of the plant, e.g., wind turbines,  solar PV, heliostats, etc. are included:','5c. Roads are included:','5d. The substation is included:','5e. Transmission lines are included:','5f. Other infrastructure included: ','6a. Area occupied by the infrastructure (questions 5a-f) was:','6b. Were the following data sources used to measure the area occupied by the infrastructure:','7. Area of the infrastructure, which is not located nearby the facility but necessary for its operation (e.g., mines, plants that manufacture PV-modules etc.),  is reported:','8a. Is spacing area included?','8b. Spacing area was quantified by using:','9a. Project area is reported:','9b. Project area refer to ','9c. Data on project were derived from','10. Power-related component of land-use requirements  is represented by: ','11a. Installation year(s):','11b. Area is estimated per: ','11c. Power / energy is estimated per: ','Additional characteristics reported:','Characterstic-1','Value-1','Unit-1','More characteristics are reported:','Characterstic-2','Value-2','Unit-2','More characteristics are reported:','Characterstic-3','Value-3','Unit-3','More characteristics are reported:','Characterstic-4','Value-4','Unit-4','More characteristics are reported:','Characterstic-5','Value-5','Unit-5','More characteristics are reported:','Characterstic-6','Value-6','Unit-6','More characteristics are reported:','Characterstic-7','Value-7','Unit-7','More characteristics are reported:','Characterstic-8','Value-8','Unit-8','More characteristics are reported:','Characterstic-9','Value-9','Unit-9','More characteristics are reported:','Characterstic-10','Value-10','Unit-10','More characteristics are reported:','Reviewed by','Comments','Country:','Subregion, e.g., state, biome, county, etc.:','','Any additional information about the location of the facility:','Provide the reference to the place in the paper, where the value can be found, e.g., table X, figure X, page XX paragraph X']"
   ]
  },
  {
   "cell_type": "raw",
   "metadata": {},
   "source": [
    "results = pd.read_excel(path + 'lit-review (Responses).xlsx',engine='openpyxl')#,names=columns)\n",
    "# doesn't seem to work... had to convert it to csv"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "results = pd.read_csv(path + 'lit-review (Responses).csv').iloc[:,:79]\n",
    "results.columns = results.columns.str.replace('  ',' ').str.strip()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "read additional results files and merge"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "additional_files = ['Ciliberti_Johannes.xlsx','DeMeo_1997.xlsx','Lingzhi_2018.xlsx','MacKey_2013.xlsx',\n",
    "                    'Olga_Enevoldsen2021.xlsx','Reddy_2012.xlsx']\n",
    "multitab_file = 'additional-values-for-review-johannes.xlsx'\n",
    "onetab_file = 'Pletk_2007.xlsx'\n",
    "sheets = pd.ExcelFile(path + multitab_file, engine='openpyxl').sheet_names\n",
    "sheet = 'lines_256_266'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "for additional_file in additional_files:\n",
    "    f1 = pd.read_excel(path + additional_file,engine='openpyxl').iloc[:,:79]\n",
    "    f1.columns = list(f1.columns[:-1].str.replace('  ',' ').str.strip()) + [results.columns[-1]]\n",
    "    #f1.columns = results.columns\n",
    "    results = pd.concat([results,f1],axis=0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "f2 = pd.read_excel(path + onetab_file,engine='openpyxl',sheet_name=sheet).iloc[:,:79]\n",
    "#f2.columns = list(f2.columns[:-1].str.replace('  ',' ').str.strip()) + [results.columns[-1]]\n",
    "f2.columns = results.columns\n",
    "results = pd.concat([results,f2],axis=0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "for additional_sheet in sheets:\n",
    "    if additional_sheet=='Capellan-Perez':\n",
    "        f2 = pd.read_excel(path + multitab_file, engine='openpyxl',sheet_name=additional_sheet,header=None).iloc[:,:79]\n",
    "    else:\n",
    "        f2 = pd.read_excel(path + multitab_file, engine='openpyxl',sheet_name=additional_sheet).iloc[:,:79]\n",
    "    f2.columns = results.columns\n",
    "    results = pd.concat([results,f2],axis=0)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "remove duplicates"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "results_no_duplicates = results.drop_duplicates()\n",
    "results_no_duplicates.index = range(len(results_no_duplicates))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "create new columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [],
   "source": [
    "ID = pd.DataFrame({'ID':np.arange(len(results_no_duplicates))})\n",
    "DATA_SOURCE = pd.DataFrame({'1e. Data source':None},index=range(len(results_no_duplicates)))\n",
    "PV_AC_DC = pd.DataFrame({'2b. Is PV AC or DC:':None},index=range(len(results_no_duplicates)))\n",
    "VALUE_CALC_FORMULA = pd.DataFrame({'4f. Is the value original or derived:':None,\n",
    "                                   '4g. Formula for calculation:':None,\n",
    "                                   '4h. File for calculations:':None},index=range(len(results_no_duplicates)))\n",
    "\n",
    "CHECKED = pd.DataFrame({'Checked':None},index=range(len(results_no_duplicates)))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [],
   "source": [
    "results_no_duplicates_new_columns = pd.concat([ID,results_no_duplicates.iloc[:,:5],DATA_SOURCE,\n",
    "                                               results_no_duplicates.iloc[:,5:6],PV_AC_DC,\n",
    "                                               results_no_duplicates.iloc[:,6:13],VALUE_CALC_FORMULA,\n",
    "                                               results_no_duplicates.iloc[:,13:],CHECKED],axis=1)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "fill in missing names"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [],
   "source": [
    "results_no_duplicates_new_columns['Reviewed by'] = results_no_duplicates_new_columns['Reviewed by'].fillna('Missing_name')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "split in single files"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [],
   "source": [
    "for name in results_no_duplicates_new_columns['Reviewed by'].unique():\n",
    "    results_name = results_no_duplicates_new_columns[results_no_duplicates_new_columns['Reviewed by']==name]\n",
    "    results_name.to_excel(results_path + 'results_' + name + '.xlsx')"
   ]
  }
 ],
 "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
}