<jupyter_start><jupyter_text>Affiliation and Partner Affiliations Analysis with MAG  PrerequisitesComplete these tasks before you begin this tutorial:- Setting up provisioning of Microsoft Academic Graph to an Azure blob storage account. See [Get Microsoft Academic Graph on Azure storage](https://docs.microsoft.com/academic-services/graph/get-started-setup-provisioning).- Setting up Azure Databricks service. See [Set up Azure Databricks](https://docs.microsoft.com/academic-services/graph/get-started-setup-databricks).  Gather the informationBefore you begin, you should have these items of information:- The name of your Azure Storage (AS) account containing MAG dataset from [Get Microsoft Academic Graph on Azure storage](https://docs.microsoft.com/academic-services/graph/get-started-setup-provisioningnote-azure-storage-account-name-and-primary-key).- The access key of your Azure Storage (AS) account from [Get Microsoft Academic Graph on Azure storage](https://docs.microsoft.com/academic-services/graph/get-started-setup-provisioningnote-azure-storage-account-name-and-primary-key).- The name of the container in your Azure Storage (AS) account containing MAG dataset.  Import notebooks- [Import](https://docs.databricks.com/user-guide/notebooks/notebook-manage.htmlimport-a-notebook) samples/pyspark/MagClass.py in MAG dataset under your working folder.- [Import](https://docs.databricks.com/user-guide/notebooks/notebook-manage.htmlimport-a-notebook) this notebook under the same folder.  Initialize storage account and container details  | Variable  | Value | Description  |  | --------- | --------- | --------- |  | AzureStorageAccount | Replace **``** | This is the Azure Storage account containing MAG dataset. |  | AzureStorageAccessKey | Replace **``** | This is the Access Key of the Azure Storage account. |  | MagContainer | Replace **``** | This is the container name in Azure Storage account containing MAG dataset, usually in the form of `mag-yyyy-mm-dd`. |  | OutputContainer | Replace **``** | This is the container name in Azure Storage account where the output goes to, this container needs to be created before running this script. |<jupyter_code>AzureStorageAccount = '<AzureStorageAccount>'
AzureStorageAccessKey = '<AzureStorageAccessKey>'
MagContainer = '<MagContainer>'
OutputContainer = '<OutputContainer>'<jupyter_output><empty_output><jupyter_text>Define MicrosoftAcademicGraph classRun the MagClass notebook to define MicrosoftAcademicGraph class.<jupyter_code>%run "./MagClass"<jupyter_output><empty_output><jupyter_text>Create a MicrosoftAcademicGraph instance to access MAG datasetUse account=AzureStorageAccount, key=AzureStorageAccessKey, container=MagContainer.<jupyter_code>mag = MicrosoftAcademicGraph(account=AzureStorageAccount, key=AzureStorageAccessKey, container=MagContainer)<jupyter_output><empty_output><jupyter_text>Create a AzureStorageUtil to access other Azure Storage filesUse account=AzureStorageAccount, key=AzureStorageAccessKey, container=OutputContainer.<jupyter_code>asu = AzureStorageUtil(account=AzureStorageAccount, key=AzureStorageAccessKey, container=OutputContainer)
from pyspark.sql.functions import concat, lit, log, when, count, sum<jupyter_output><empty_output><jupyter_text>Step 1. Get target affiliation  Load Affiliations data<jupyter_code>affiliations = mag.getDataframe('Affiliations')

# Peek the result
display(affiliations.head(5))<jupyter_output><empty_output><jupyter_text>Extract AffiliationId and DisplayName for the affiliation<jupyter_code># Extract the AffiliationId for Microsoft
inputAffiliationName = 'microsoft'
orgAffiliation = affiliations.where(affiliations.NormalizedName == inputAffiliationName).select(affiliations.AffiliationId, affiliations.DisplayName)

# Peek the result
display(orgAffiliation)

# Save result
asu.save(orgAffiliation, 'Affiliation/Affiliation.tsv', coalesce=True)<jupyter_output><empty_output><jupyter_text>Step 2. Get Venues  Load ConferenceSeries data<jupyter_code>conferences = mag.getDataframe('ConferenceSeries')

# Peek result
display(conferences.head(5))<jupyter_output><empty_output><jupyter_text>Load Journals data<jupyter_code>journals = mag.getDataframe('Journals').drop('Publisher')

# Peek result
display(journals.head(5))<jupyter_output><empty_output><jupyter_text>Union ConferenceSeries and Journals as Venues<jupyter_code>conferences = conferences \
    .select(conferences.ConferenceSeriesId, conferences.DisplayName, conferences.NormalizedName) \
    .selectExpr('ConferenceSeriesId as VId', 'DisplayName as VenueName', 'NormalizedName as VenueShortName')

journals = journals \
    .select(journals.JournalId, journals.DisplayName, journals.NormalizedName) \
    .selectExpr('JournalId as VId', 'DisplayName as VenueName', 'NormalizedName as VenueShortName')

venues = conferences.union(journals)

# Count number of rows in result
print('Number of rows in venues: {}'.format(venues.count()))

# Peek result
display(venues.head(5))

# Save result
asu.save(venues, 'Affiliation/Venues.tsv', coalesce=True)<jupyter_output><empty_output><jupyter_text>Step 3. Get PaperAuthorAffiliation for the affiliation  Load PaperAuthorAffiliations data<jupyter_code>paperAuthorAffiliations = mag.getDataframe('PaperAuthorAffiliations')

# Peek result
display(paperAuthorAffiliations.head(5))<jupyter_output><empty_output><jupyter_text>Get PaperAuthorAffiliations for the affiliation<jupyter_code>orgPaperAuthorAffiliation = paperAuthorAffiliations \
    .join(orgAffiliation, paperAuthorAffiliations.AffiliationId == orgAffiliation.AffiliationId, 'inner') \
    .select(paperAuthorAffiliations.PaperId, paperAuthorAffiliations.AuthorId, \
            orgAffiliation.AffiliationId, paperAuthorAffiliations.AuthorSequenceNumber)

# Count number of rows in result
print('Number of rows in PaperAuthorAffiliation: {}'.format(orgPaperAuthorAffiliation.count()))

# Peek result
display(orgPaperAuthorAffiliation.head(5))

# Save result
asu.save(orgPaperAuthorAffiliation, 'Affiliation/PaperAuthorAffiliationRelationships.tsv')<jupyter_output><empty_output><jupyter_text>Step 4. Get author and paper details  Get authors for the affiliation<jupyter_code>#Load data from previous output
orgPaperAuthorAffiliation = asu.load('Affiliation/PaperAuthorAffiliationRelationships.tsv')

orgAuthorIds = orgPaperAuthorAffiliation.select(orgPaperAuthorAffiliation.AuthorId).distinct()

#Load Authors data
authors = mag.getDataframe('Authors')

# Get all author details
orgAuthors = authors \
    .join(orgAuthorIds, authors.AuthorId == orgAuthorIds.AuthorId, 'inner') \
    .select(orgAuthorIds.AuthorId, authors.DisplayName.alias('AuthorName'))

# Peek result
display(orgAuthors.head(5))

# Save result
asu.save(orgAuthors, 'Affiliation/Authors.tsv')<jupyter_output><empty_output><jupyter_text>Get papers for the affiliation<jupyter_code>#Load Papers data
papers = mag.getDataframe('Papers')

papers = papers.withColumn('Prefix', lit('https://academic.microsoft.com/#/detail/'))

# Get all paper details
orgPaperIds = orgPaperAuthorAffiliation.select(orgPaperAuthorAffiliation.PaperId).distinct()

orgPapers = papers \
    .join(orgPaperIds, papers.PaperId == orgPaperIds.PaperId) \
    .where(papers.Year >= 1991) \
    .select(papers.PaperId, papers.PaperTitle.alias('Title'), papers.EstimatedCitation.alias('CitationCount'), \
            papers.Date, when(papers.DocType.isNull(), 'Not available').otherwise(papers.DocType).alias('PublicationType'), \
            log(papers.Rank).alias('LogProb'), concat(papers.Prefix, papers.PaperId).alias('Url'), \
            when(papers.ConferenceSeriesId.isNull(), papers.JournalId).otherwise(papers.ConferenceSeriesId).alias('VId'), \
            papers.Year)

# Optional: Count number of rows in result
print('Number of rows in orgPapers: {}'.format(orgPapers.count()))

# Peek result
display(orgPapers.head(5))

# Save result
asu.save(orgPapers, 'Affiliation/Papers.tsv')<jupyter_output><empty_output><jupyter_text>Step 5. Get fields of study for the affiliation  Get PaperFieldsOfStudy<jupyter_code>#Load data from previous output
orgPapers = asu.load('Affiliation/Papers.tsv')

# Load FieldsOfStudy data
fieldsOfStudy = mag.getDataframe('FieldsOfStudy')

# Load PaperFieldsOfStudy data
paperFieldsOfStudy = mag.getDataframe('PaperFieldsOfStudy')

# Get Paper-Field-of-Study relationships for the input organization
orgPaperFieldsOfStudy = paperFieldsOfStudy \
    .join(orgPapers, paperFieldsOfStudy.PaperId == orgPapers.PaperId, 'inner') \
    .select(orgPapers.PaperId, paperFieldsOfStudy.FieldOfStudyId)

# Optional: peek result
display(orgPaperFieldsOfStudy.head(5))

# Save result
asu.save(orgPaperFieldsOfStudy, 'Affiliation/PaperFieldOfStudyRelationships.tsv')<jupyter_output><empty_output><jupyter_text>Get FieldsOfStudy<jupyter_code># Get all field-of-study Ids for the input organization
orgFieldsOfStudyIds = orgPaperFieldsOfStudy.select(orgPaperFieldsOfStudy.FieldOfStudyId).distinct()

# Get all field-of-study details for the input organization
orgFieldsOfStudy = fieldsOfStudy \
    .join(orgFieldsOfStudyIds, fieldsOfStudy.FieldOfStudyId == orgFieldsOfStudyIds.FieldOfStudyId, 'inner') \
    .select(orgFieldsOfStudyIds.FieldOfStudyId, fieldsOfStudy.Level.alias('FieldLevel'), fieldsOfStudy.DisplayName.alias('FieldName'))

# Optional: peek result
display(orgFieldsOfStudy.head(5))

# Save result
asu.save(orgFieldsOfStudy, 'Affiliation/FieldsOfStudy.tsv')<jupyter_output><empty_output><jupyter_text>Step 6. Get partner data  Load data previousely generated<jupyter_code># Get all paper details for the input organization from previous output
orgPapers = asu.load('Affiliation/Papers.tsv')

# Get all Paper-Author-Affiliation relationships for the input organization from previous output
orgPaperAuthorAffiliation = asu.load('Affiliation/PaperAuthorAffiliationRelationships.tsv')

# Get all paper-author-affiliation relationships
paperAuthorAffiliations = mag.getDataframe('PaperAuthorAffiliations')

# Get all affiliation details
affiliations = mag.getDataframe('Affiliations')

# Get all author details
authors = mag.getDataframe('Authors')<jupyter_output><empty_output><jupyter_text>Get partner paper-author-affiliation relationships<jupyter_code># Get all Paper-Author-Affiliation relationships for papers published by the input organization
orgAllPaperAuthorAffiliations = paperAuthorAffiliations \
    .join(orgPapers, paperAuthorAffiliations.PaperId == orgPapers.PaperId, 'inner') \
    .select(orgPapers.PaperId, paperAuthorAffiliations.AuthorId, \
            paperAuthorAffiliations.AffiliationId, paperAuthorAffiliations.AuthorSequenceNumber)

# Get partner Paper-Author-Affiliation relationships by excluding those relationships of the input organization
partnerPaperAuthorAffiliation = orgAllPaperAuthorAffiliations.subtract(orgPaperAuthorAffiliation)
display(partnerPaperAuthorAffiliation.head(5))

# Save result
asu.save(partnerPaperAuthorAffiliation, 'Affiliation/PartnerPaperAuthorAffiliationRelationships.tsv')<jupyter_output><empty_output><jupyter_text>Get partner affiliations<jupyter_code># Get all partner affiliation Ids
partnerAffiliationIds = partnerPaperAuthorAffiliation \
    .where(partnerPaperAuthorAffiliation.AffiliationId.isNotNull()) \
    .select(partnerPaperAuthorAffiliation.AffiliationId) \
    .distinct()

# Get all partner affiliation details
partnerAffiliations = affiliations \
    .join(partnerAffiliationIds, affiliations.AffiliationId == partnerAffiliationIds.AffiliationId, 'inner') \
    .select(partnerAffiliationIds.AffiliationId, affiliations.DisplayName.alias('AffiliationName'))

display(partnerAffiliations.head(5))

# Save result
asu.save(partnerAffiliations, 'Affiliation/PartnerAffiliations.tsv')<jupyter_output><empty_output><jupyter_text>Get partner authors<jupyter_code># Get all partner author Ids
partnerAuthorIds = partnerPaperAuthorAffiliation.select(partnerPaperAuthorAffiliation.AuthorId).distinct()
partnerAuthors = authors \
    .join(partnerAuthorIds, partnerAuthorIds.AuthorId == authors.AuthorId) \
    .select(partnerAuthorIds.AuthorId, authors.DisplayName.alias('AuthorName'))

display(partnerAuthors.head(5))

# Save result
asu.save(partnerAuthors, 'Affiliation/PartnerAuthors.tsv')<jupyter_output><empty_output><jupyter_text>Step 7. Display paper counts by year<jupyter_code>#Load data from previous output
orgPapers = asu.load('Affiliation/Papers.tsv')

# Get paper count and citation sum for each year
orgPaperGroupByYear = orgPapers \
    .groupBy(orgPapers.Year) \
    .agg(count(orgPapers.PaperId).alias('PaperCount'), sum(orgPapers.CitationCount).alias('CitationSum')) \
    .orderBy(orgPapers.Year)

# Display result
display(orgPaperGroupByYear)

# Save result
asu.save(orgPaperGroupByYear, 'Affiliation/PaperCountByYear.tsv', coalesce=True)<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.10008574323797646, 'fraction_numerical': 0.0018707615558500273, 'mean_word_length': 7.079345088161209, 'pattern_counts': {'":': 0, '<': 85, '<?xml version=': 0, '>': 86, 'https://': 7, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '20', 'dir': 'jupyter-structured-clean-dedup', 'id': '603262', 'license': 'MIT', 'n_tokens_mistral': 3919, 'n_tokens_neox': 3700, 'n_words': 777, 'path': 'src/AffiliationSample.ipynb', 'repo_name': 'BoyaXie/microsoft-academic-graph-pyspark-samples', 'size': '12829'}