3_SHARE_Data_in_the_Wide_World.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# SHARE Data in the Wide World\n",
    "\n",
    "This notebook will focus on how to export SHARE data into different formats, and how to query SHARE for specific information from your institution, say from a list of names or from a list of emails or ORCIDs that act as reseearcher identifiers.\n",
    "\n",
    "\n",
    "## Exporting a DataFrame to csv and Excel\n",
    "\n",
    "When doing an aggregation on SHARE data, it might be beneficial to export the data to a format that is easier to widely distribute, such as a csv file or and Excel file.\n",
    "\n",
    "First, we'll do a SHARE aggregation query for documents from each source that have a description, turn it into a pandas DataFrame, and export the data into both csv and Excel formats."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "\n",
    "from sharepa import ShareSearch\n",
    "from sharepa.helpers import pretty_print\n",
    "\n",
    "description_search = ShareSearch()\n",
    "\n",
    "description_search = description_search.query(\n",
    "    'query_string', # Type of query, will accept a lucene query string\n",
    "    query='description:*', # This lucene query string will find all documents that don't have a description\n",
    "    analyze_wildcard=True  # This will make elasticsearch pay attention to the asterisk (which matches anything)\n",
    ")\n",
    "\n",
    "description_search.aggs.bucket(\n",
    "    'sources',  # Every aggregation needs a name\n",
    "    'significant_terms',  # There are many kinds of aggregations\n",
    "    field='_type',  # We store the source of a document in its type, so this will aggregate by source\n",
    "    min_doc_count=0,\n",
    "    percentage={}, # Will make the score value the percentage of all results (doc_count/bg_count)\n",
    "    size=0\n",
    ")\n",
    "\n",
    "description_results = description_search.execute()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "description_dataframe = pd.DataFrame(description_results.aggregations.sources.to_dict()['buckets'])\n",
    "\n",
    "# We will add our own \"percent\" column to make things clearer\n",
    "description_dataframe['percent'] = (description_dataframe['score'] * 100)\n",
    "\n",
    "# Let's set the source name as the index, and then drop the old column\n",
    "description_dataframe = description_dataframe.set_index(description_dataframe['key'])\n",
    "description_dataframe = description_dataframe.drop('key', 1)\n",
    "\n",
    "# Finally, we'll show the results!\n",
    "description_dataframe"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Let's export this pandas dataframe to a csv file, and to an excel file.\n",
    "\n",
    "The next cell will work when running locally!"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# Note: Uncomment the following lines if running locally:\n",
    "\n",
    "# description_dataframe.to_csv('SHARE_Counts_with_Descriptions.csv')\n",
    "# description_dataframe.to_excel('SHARE_Counts_with_Descriptions.xlsx')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Working with outside data\n",
    "\n",
    "Let's say we had a list of names of researchers that were from a particular University. We're interested in seeing if their full names appear in any sources across the SHARE data set."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "names = [\"Susan Jones\", \"Ravi Patel\"]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "name_search = ShareSearch()\n",
    "\n",
    "for name in names:\n",
    "    name_search = name_search.query(\n",
    "        {\n",
    "            \"bool\": {\n",
    "                \"should\": [\n",
    "                    {\n",
    "                        \"match\": {\n",
    "                            \"contributors.name\": {\n",
    "                                \"query\": name, \n",
    "                                \"operator\": \"and\",\n",
    "                                \"type\" : \"phrase\"\n",
    "                            }\n",
    "                        }\n",
    "                    }\n",
    "                ]\n",
    "            }\n",
    "        }\n",
    "    )\n",
    "\n",
    "\n",
    "name_results = name_search.execute()\n",
    "\n",
    "print('There are {} documents with contributors who have any of those names.'.format(name_search.count()))\n",
    "print('Here are the first 10:')\n",
    "print('---------')\n",
    "for result in name_results:\n",
    "    print(\n",
    "        '{} -- with contributors {}'.format(\n",
    "            result.title.encode('utf-8'),\n",
    "            ', '.join([contributor.name.encode('utf-8') for contributor in result.contributors])\n",
    "        )\n",
    "    )\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "If we were interested to see an analysis of what sources these names came from, we can add an aggregation."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "name_search.aggs.bucket(\n",
    "    'sources',  # Every aggregation needs a name\n",
    "    'terms',  # There are many kinds of aggregations, terms is a pretty useful one though\n",
    "    field='_type',  # We store the source of a document in its type, so this will aggregate by source\n",
    "    size=0,  # These are just to make sure we get numbers for all the sources, to make it easier to combine graphs\n",
    "    min_doc_count=1\n",
    ")\n",
    "\n",
    "name_results = name_search.execute()\n",
    "\n",
    "pd.DataFrame(name_results.aggregations.sources.to_dict()['buckets'])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Say instead of names, which can be a little more arbitrary, we'd like to search by email address or ORCID instead."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "orcids = [\n",
    "    'http://orcid.org/0000-0003-1942-4543',\n",
    "    'http://orcid.org/0000-0003-4875-1447',\n",
    "    'http://orcid.org/0000-0002-6085-4433',\n",
    "    'http://orcid.org/0000-0002-7995-9948',\n",
    "    'http://orcid.org/0000-0002-2170-853X',\n",
    "    'http://orcid.org/0000-0002-8899-9087'\n",
    "]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "orcid_search = ShareSearch()\n",
    "\n",
    "for orcid in orcids:\n",
    "    orcid_search = orcid_search.query(\n",
    "        {\n",
    "            \"bool\": {\n",
    "                \"should\": [\n",
    "                    {\n",
    "                        \"match\": {\n",
    "                            \"contributors.sameAs\": {\n",
    "                                \"query\": orcid, \n",
    "                                \"operator\": \"and\",\n",
    "                                \"type\" : \"phrase\"\n",
    "                            }\n",
    "                        }\n",
    "                    }\n",
    "                ]\n",
    "            }\n",
    "        }\n",
    "    )\n",
    "\n",
    "orcid_search.aggs.bucket(\n",
    "    'sources',  # Every aggregation needs a name\n",
    "    'terms',  # There are many kinds of aggregations, terms is a pretty useful one though\n",
    "    field='_type',  # We store the source of a document in its type, so this will aggregate by source\n",
    "    size=0,  # These are just to make sure we get numbers for all the sources, to make it easier to combine graphs\n",
    "    min_doc_count=1\n",
    ")\n",
    "\n",
    "orcid_results = orcid_search.execute()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "print('There are {} documents with contributors who have any of those orcids.'.format(orcid_search.count()))\n",
    "\n",
    "all_agg_df = pd.DataFrame()\n",
    "all_agg_df['title'] = [result.title for result in orcid_results]\n",
    "all_agg_df['docID'] = [result.shareProperties.docID for result in orcid_results]\n",
    "all_agg_df['source'] = [result.shareProperties.source for result in orcid_results]\n",
    "all_agg_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "## Exporting SHARE Records as a CSV File - Evaluating Contributors\n",
    "\n",
    "import requests\n",
    "from sharepa import basic_search\n",
    "\n",
    "from pandas.io.json import json_normalize\n",
    "\n",
    "results = requests.get('https://osf.io/api/v1/share/search/?sort=providerUpdatedDateTime').json()['results']\n",
    "flattened = json_normalize(results, 'contributors', ['shareProperties', 'title'])\n",
    "flattened"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 2",
   "language": "python",
   "name": "python2"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 2
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython2",
   "version": "2.7.11"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 0
}