Diffusion Open Access Compliance Check Tool (OACCT) (issn4frontend) Browse

10_oacct_terms.py
f6ac3b58106fissn4frontend

10_oacct_terms.py
View Options

			#!/usr/bin/env python
			# coding: utf-8

			# # Projet Open Access Compliance Check Tool (OACCT)
			#
			# Projet P5 de la bibliothèque de l'EPFL en collaboration avec les bibliothèques des Universités de Genève, Lausanne et Berne : https://www.swissuniversities.ch/themen/digitalisierung/p-5-wissenschaftliche-information/projekte/swiss-mooc-service-1-1-1-1
			#
			# Ce notebook permet de modifier les données extraites des differentes sources et les exporter dans les tables de l'application OACCT.
			#
			# Auteur : Pablo Iriarte, Université de Genève (pablo.iriarte@unige.ch)
			# Date de dernière mise à jour : 08.09.2021

			# In[1]:


			import pandas as pd
			import csv
			import json
			import numpy as np
			import os
			# afficher toutes les colonnes
			pd.set_option('display.max_columns', None)
			# definir le debut des ids
			id_start = 1


			# ## Import du fichier extrait de Sherpa

			# In[2]:


			sherpa = pd.read_csv('sample/sherpa_policies_brut.tsv', encoding='utf-8', header=0, sep='\t')
			sherpa


			# In[3]:


			# test des valeurs pour les versions
			sherpa['article_version'].value_counts()


			# In[4]:


			# test des valeurs pour les issns
			sherpa.loc[sherpa['issn'].isna()]


			# In[5]:


			# ajout des ISSN-L
			issns = pd.read_csv('issn/20171102.ISSN-to-ISSN-L.txt', encoding='utf-8', header=0, sep='\t')
			issns


			# In[6]:


			# renommer les colonnes
			issns = issns.rename(columns={'ISSN' : 'issn', 'ISSN-L' : 'issnl'})
			issns


			# In[7]:


			# merge avec la table sherpa
			sherpa = pd.merge(sherpa, issns, on='issn', how='left')
			sherpa


			# In[8]:


			# test des valeurs pour les issnl
			sherpa.loc[sherpa['issnl'].isna()]


			# In[9]:


			# extraction des données IR Archiving + Embargo par ISSN
			sherpa_ir = sherpa[['issnl', ]]


			# ## Import du fichier des licences Read & Publish

			# In[10]:


			rp = pd.read_csv('sample/read_publish_brut_merge.tsv', encoding='utf-8', header=0, sep='\t')
			rp


			# In[11]:


			rp['embargo_months'].value_counts()


			# In[12]:


			# ajout de l'éditeur dans un seul champ
			# rp.loc[rp['Elsevier'] == 'x', 'public_notes'] = 'Elsevier Read & Publish agreement'
			rp.loc[rp['Elsevier'] == 'x', 'rp_publisher'] = 'Elsevier'
			rp.loc[rp['Springer Nature'] == 'x', 'rp_publisher'] = 'Springer Nature'
			rp.loc[rp['Wiley'] == 'x', 'rp_publisher'] = 'Wiley'
			rp.loc[rp['TF'] == 'x', 'rp_publisher'] = 'TF'
			rp.loc[rp['CUP'] == 'x', 'rp_publisher'] = 'CUP'
			rp


			# In[13]:


			# test des valeurs pour les versions
			rp['rp_publisher'].value_counts()


			# In[14]:


			# test des valeurs pour les versions
			rp['license'].value_counts()


			# In[15]:


			# supprimer les champs inutiles et renommer les colonnes
			del rp['Elsevier']
			del rp['Springer Nature']
			del rp['Wiley']
			del rp['TF']
			del rp['CUP']
			del rp['URL']
			rp


			# In[16]:


			# renommer les colonnes
			rp = rp.rename(columns = {'Title' : 'title', 'ROR' : 'ror', 'read_publish_id' : 'rp_id'})
			rp


			# ## Table applicable_version

			# In[17]:


			# creation du DF
			col_names = ['id',
			'type',
			'description'
			]
			applicable_version = pd.DataFrame(columns = col_names)
			# 3 values : published, accepted, submitted
			new_row1 = {'id':1, 'type':'submitted', 'description' : 'Submitted version'}
			new_row2 = {'id':2, 'type':'accepted', 'description' : 'Accepted version'}
			new_row3 = {'id':3, 'type':'published', 'description' : 'Published version'}
			#append row to the dataframe
			applicable_version = applicable_version.append(new_row1, ignore_index=True)
			applicable_version = applicable_version.append(new_row2, ignore_index=True)
			applicable_version = applicable_version.append(new_row3, ignore_index=True)
			applicable_version


			# In[18]:


			# ajout de la valeur UNKNOWN
			applicable_version = applicable_version.append({'id' : 999999, 'type' : 'UNKNOWN', 'description' : 'UNKNOWN'}, ignore_index=True)
			applicable_version


			# In[19]:


			# renommage des champs finaux
			applicable_version_export = applicable_version[['id', 'description']]


			# In[20]:


			# export de la table applicable_version
			result = applicable_version_export.to_json(orient='records', force_ascii=False)
			parsed = json.loads(result)
			with open('sample/version.json', 'w', encoding='utf-8') as file:
			json.dump(parsed, file, indent=2, ensure_ascii=False)


			# In[21]:


			# export csv
			applicable_version_export.to_csv('sample/version.tsv', sep='\t', encoding='utf-8', index=False)


			# In[22]:


			# export excel
			applicable_version_export.to_excel('sample/version.xlsx', index=False)


			# In[23]:


			# merge avec la table sherpa
			sherpa = pd.merge(sherpa, applicable_version[['id', 'type']], left_on='article_version', right_on='type', how='left')
			sherpa


			# In[24]:


			sherpa = sherpa.rename(columns = {'id_x' : 'id', 'id_y' : 'version'})
			del sherpa['type']
			sherpa


			# In[25]:


			# merge avec la table read & publish
			rp = pd.merge(rp, applicable_version[['id', 'type']], left_on='article_version', right_on='type', how='left')
			rp


			# In[26]:


			rp = rp.rename(columns = {'id' : 'version'})
			del rp['type']
			rp


			# ## Table oa_licence

			# In[27]:


			# creation du DF
			# 'version' n'est pas utilisée, on dédoublonne par nom sans la version
			col_names = ['id',
			'name',
			'url'
			]
			oa_licence = pd.DataFrame(columns = col_names)
			oa_licence


			# In[28]:


			# export des licences
			sherpa['license'].value_counts()


			# In[29]:


			sherpa_licences = sherpa['license'].drop_duplicates()
			sherpa_licences = sherpa_licences.dropna()
			sherpa_licences


			# In[30]:


			oa_licence['sherpa_code'] = np.nan
			oa_licence


			# In[31]:


			for code in sherpa_licences:
			print (code)
			oa_licence = oa_licence.append({'sherpa_code' : code}, ignore_index=True)


			# In[32]:


			oa_licence


			# In[33]:


			# convertir l'index en id
			oa_licence = oa_licence.reset_index()
			# ajout de l'id avec l'index + 1
			oa_licence['id'] = oa_licence['index'] + 1
			del oa_licence['index']
			oa_licence


			# In[34]:


			# ajout du nom et des URLs
			oa_licence.loc[oa_licence['sherpa_code'] == 'cc_by', 'name'] = 'CC BY'
			oa_licence.loc[oa_licence['sherpa_code'] == 'cc_by', 'url'] = 'https://creativecommons.org/licenses/by/4.0/'
			oa_licence.loc[oa_licence['sherpa_code'] == 'cc_by_sa', 'name'] = 'CC BY-SA'
			oa_licence.loc[oa_licence['sherpa_code'] == 'cc_by_sa', 'url'] = 'https://creativecommons.org/licenses/by-sa/4.0/'
			oa_licence.loc[oa_licence['sherpa_code'] == 'cc_by_nc', 'name'] = 'CC BY-NC'
			oa_licence.loc[oa_licence['sherpa_code'] == 'cc_by_nc', 'url'] = 'https://creativecommons.org/licenses/by-nc/4.0/'
			oa_licence.loc[oa_licence['sherpa_code'] == 'cc_by_nc_sa', 'name'] = 'CC BY-NC-SA'
			oa_licence.loc[oa_licence['sherpa_code'] == 'cc_by_nc_sa', 'url'] = 'https://creativecommons.org/licenses/by-nc-sa/4.0/'
			oa_licence.loc[oa_licence['sherpa_code'] == 'cc_by_nd', 'name'] = 'CC BY-ND'
			oa_licence.loc[oa_licence['sherpa_code'] == 'cc_by_nd', 'url'] = 'https://creativecommons.org/licenses/by-nd/4.0/'
			oa_licence.loc[oa_licence['sherpa_code'] == 'cc_by_nc_nd', 'name'] = 'CC BY-NC-ND'
			oa_licence.loc[oa_licence['sherpa_code'] == 'cc_by_nc_nd', 'url'] = 'https://creativecommons.org/licenses/by-nc-nd/4.0/'
			oa_licence.loc[oa_licence['sherpa_code'] == 'cc0', 'name'] = 'CC0'
			oa_licence.loc[oa_licence['sherpa_code'] == 'cc0', 'url'] = 'https://creativecommons.org/publicdomain/zero/1.0/'
			oa_licence.loc[oa_licence['sherpa_code'] == 'bespoke_license', 'name'] = 'Specific license'
			oa_licence.loc[oa_licence['sherpa_code'] == 'bespoke_license', 'url'] = ''
			oa_licence.loc[oa_licence['sherpa_code'] == 'all_rights_reserved', 'name'] = 'All rights reserved'
			oa_licence.loc[oa_licence['sherpa_code'] == 'all_rights_reserved', 'url'] = ''
			oa_licence.loc[oa_licence['sherpa_code'] == 'cc_gnu_gpl', 'name'] = 'GNU GPL'
			oa_licence.loc[oa_licence['sherpa_code'] == 'cc_gnu_gpl', 'url'] = 'http://gnugpl.org/'
			oa_licence.loc[oa_licence['sherpa_code'] == 'cc_public_domain', 'name'] = 'Public domain'
			oa_licence.loc[oa_licence['sherpa_code'] == 'cc_public_domain', 'url'] = 'https://creativecommons.org/share-your-work/public-domain/'
			# oa_licence.loc[oa_licence['sherpa_code'] == 'bespoke_license', 'url'] = 'https://port.sas.ac.uk/mod/book/view.php?id=1340&chapterid=1003'
			oa_licence


			# In[35]:


			# ajout de la valeur UNKNOWN
			oa_licence = oa_licence.append({'id' : 999999, 'sherpa_code' : '___', 'name' : 'UNKNOWN', 'url' : ''}, ignore_index=True)
			oa_licence


			# In[36]:


			# ajout aux tables sherpa et rp
			sherpa = sherpa.rename(columns = {'license' : 'sherpa_code'})
			sherpa


			# In[37]:


			# ajout aux tables sherpa et rp
			rp = rp.rename(columns = {'license' : 'sherpa_code'})
			rp


			# In[38]:


			# merge
			sherpa = pd.merge(sherpa, oa_licence[['sherpa_code', 'id']], on='sherpa_code', how='left')
			sherpa


			# In[39]:


			sherpa = sherpa.rename(columns = {'id_x' : 'id', 'id_y' : 'licence'})
			sherpa


			# In[40]:


			# merge
			rp = pd.merge(rp, oa_licence[['sherpa_code', 'id']], on='sherpa_code', how='left')
			rp


			# In[41]:


			rp = rp.rename(columns = {'id' : 'licence'})
			rp


			# In[42]:


			# renommage des champs finaux
			oa_licence_export = oa_licence[['id', 'name', 'url']]
			oa_licence_export = oa_licence_export.rename(columns={'name' : 'name_or_abbrev', 'url' : 'website'})


			# In[43]:


			# export de la table oa_licence
			result = oa_licence_export.to_json(orient='records', force_ascii=False)
			parsed = json.loads(result)
			with open('sample/licence.json', 'w', encoding='utf-8') as file:
			json.dump(parsed, file, indent=2, ensure_ascii=False)


			# In[44]:


			# export csv
			oa_licence_export.to_csv('sample/licence.tsv', sep='\t', encoding='utf-8', index=False)


			# In[45]:


			# export excel
			oa_licence_export.to_excel('sample/licence.xlsx', index=False)


			# ## Table cost_factor_type

			# In[46]:


			# creation du DF
			col_names = ['id',
			'name'
			]
			cost_factor_type = pd.DataFrame(columns = col_names)
			cost_factor_type = cost_factor_type.append({'id' : 1, 'name' : 'APC'}, ignore_index=True)
			cost_factor_type = cost_factor_type.append({'id' : 2, 'name' : 'Discount'}, ignore_index=True)
			cost_factor_type = cost_factor_type.append({'id' : 3, 'name' : 'Refund'}, ignore_index=True)
			cost_factor_type


			# In[47]:


			# ajout de la valeur UNKNOWN
			cost_factor_type = cost_factor_type.append({'id' : 999999, 'name' : 'UNKNOWN'}, ignore_index=True)
			cost_factor_type


			# In[48]:


			# export de la table
			result = cost_factor_type.to_json(orient='records', force_ascii=False)
			parsed = json.loads(result)
			with open('sample/cost_factor_type.json', 'w', encoding='utf-8') as file:
			json.dump(parsed, file, indent=2, ensure_ascii=False)


			# In[49]:


			# export csv
			cost_factor_type.to_csv('sample/cost_factor_type.tsv', sep='\t', encoding='utf-8', index=False)


			# In[50]:


			# export excel
			cost_factor_type.to_excel('sample/cost_factor_type.xlsx', index=False)


			# ## Table cost_factor

			# ### Ajout des données des APCs depuis DOAJ

			# In[51]:


			# ajout de DOAJ info
			doaj = pd.read_csv('doaj/journalcsv__doaj_20210312_0636_utf8.csv', encoding='utf-8', header=0)
			doaj


			# In[52]:


			# garder les lignes avec APC
			doaj_apc = doaj.loc[doaj['APC'] == 'Yes'][['Journal ISSN (print version)', 'Journal EISSN (online version)', 'APC amount']]
			doaj_apc


			# In[53]:


			# garder les lignes avec APC no
			doaj_apc_no = doaj.loc[doaj['APC'] == 'No'][['Journal ISSN (print version)', 'Journal EISSN (online version)']]
			doaj_apc_no


			# In[54]:


			# attribuer la valeur 0
			doaj_apc_no['APC amount'] = 0
			doaj_apc_no


			# In[55]:


			# ajout à la table des APC
			doaj_apc = doaj_apc.append(doaj_apc_no, ignore_index=True)
			doaj_apc


			# In[56]:


			# découpage du prix en 'amount' et 'symbol'
			doaj_apc[['amount', 'symbol']] = doaj_apc['APC amount'].str.split(' ', n=1, expand=True)
			doaj_apc


			# In[57]:


			doaj_apc.loc[doaj_apc['APC amount'] == 0, 'amount'] = 0
			doaj_apc.loc[doaj_apc['APC amount'] == 0, 'symbol'] = ''
			doaj_apc


			# In[58]:


			# ajouter les champs manquants
			doaj_apc['cost_factor_type'] = 1
			doaj_apc['comment'] = 'Source: DOAJ'
			doaj_apc


			# In[59]:


			# renommer les champs
			doaj_apc = doaj_apc.rename(columns = {'Journal ISSN (print version)' : 'issn_print', 'Journal EISSN (online version)' : 'issn_electronic'})
			doaj_apc


			# In[60]:


			# ajout du issn
			doaj_apc['issn'] = doaj_apc['issn_electronic']
			doaj_apc


			# In[61]:


			doaj_apc.loc[doaj_apc['issn'].isna()]


			# In[62]:


			# ajout du issnp quand c'est vide
			doaj_apc.loc[doaj_apc['issn'].isna(), 'issn'] = doaj_apc['issn_print']
			doaj_apc.loc[doaj_apc['issn'].isna()]


			# In[63]:


			doaj_apc = pd.merge(doaj_apc, issns, on='issn', how='left')
			doaj_apc


			# In[64]:


			# renommer les colonnes
			doaj_apc = doaj_apc.rename(columns={'issnl' : 'issn_link'})
			doaj_apc


			# ### Ajout des APCs depuis la base Journal Database (Zurich Open Repository and Archive)
			#
			# https://www.jdb.uzh.ch/

			# In[65]:


			# JDB base de Zurich
			jdb = pd.read_csv('zora/jdb_apcs.tsv', encoding='utf-8', header=0, sep='\t')
			jdb


			# In[66]:


			# renommer l'id
			jdb = jdb.rename(columns = {'id' : 'jdb_id'})
			jdb


			# In[67]:


			# ajouter les champs manquants
			jdb['cost_factor_type'] = 1
			jdb['comment'] = 'Source: JDB (' + jdb['apc_date'].astype(str) + ')'
			jdb


			# In[68]:


			# renommer les champs
			jdb = jdb.rename(columns = {'apc_fee' : 'amount', 'apc_currency' : 'symbol'})
			jdb


			# In[69]:


			jdb = jdb.drop_duplicates(subset='jdb_id', keep='last')


			# In[70]:


			# import openapc avec les valeurs max
			openapc = pd.read_csv('openapc/open_apc_max.tsv', encoding='utf-8', header=0, sep='\t')
			openapc


			# In[71]:


			# renommer les champs
			openapc = openapc.rename(columns = {'period' : 'apc_date', 'issn_l' : 'issn_link', 'euro' : 'amount'})
			openapc


			# In[72]:


			# ajouter le lien avec le type et le symbole
			openapc['cost_factor_type'] = 1
			openapc['jdb_id'] = np.nan
			openapc['symbol'] = 'EUR'
			openapc['comment'] = 'Source: OpenAPC (' + openapc['apc_date'].astype(str) + ')'
			openapc


			# In[73]:


			# ajout des lignes de openapc
			jdb = jdb.append(openapc, ignore_index=True)
			jdb


			# In[74]:


			# supprimer les doublons par issnl et date
			jdb = jdb.drop_duplicates(subset=['issn_link', 'apc_date'], keep='first')
			jdb


			# In[75]:


			# ajout de DOAJ
			cost_factor = doaj_apc.append(jdb, ignore_index=True)
			cost_factor


			# In[76]:


			# test issnl
			cost_factor.loc[cost_factor['issn_link'].isna()]


			# In[77]:


			# merge avec issnl
			cost_factor = pd.merge(cost_factor, issns, on='issn', how='left')
			cost_factor


			# In[78]:


			# test issnl
			cost_factor.loc[cost_factor['issnl'].isna()]


			# In[79]:


			#ajout des issn quand ça manque
			cost_factor.loc[cost_factor['issn'].isna(), 'issn'] = cost_factor['issn_print']
			cost_factor.loc[cost_factor['issn'].isna(), 'issn'] = cost_factor['issn_electronic']
			cost_factor.loc[cost_factor['issn'].isna(), 'issn'] = cost_factor['issn_link']
			cost_factor.loc[cost_factor['issn'].isna()]


			# In[80]:


			#ajout des issnl quand ça manque
			cost_factor.loc[cost_factor['issnl'].isna(), 'issnl'] = cost_factor['issn_link']
			cost_factor.loc[cost_factor['issnl'].isna(), 'issnl'] = cost_factor['issn_print']
			cost_factor.loc[cost_factor['issnl'].isna(), 'issnl'] = cost_factor['issn_electronic']
			cost_factor.loc[cost_factor['issnl'].isna(), 'issnl'] = cost_factor['issn']
			cost_factor.loc[cost_factor['issnl'].isna()]


			# In[81]:


			# prendre les ids pour le merge
			cost_factor_ids = cost_factor[['issn', 'issnl', 'cost_factor_type', 'amount', 'symbol', 'comment']]
			# cost_factor_ids_1 = cost_factor_ids_1.rename(columns = {'issn_link' : 'issn'})
			# cost_factor_ids_2 = cost_factor.loc[cost_factor['issn_electronic'].notna()][['issn_electronic', 'cost_factor_type', 'amount', 'symbol', 'comment']]
			# cost_factor_ids_2 = cost_factor_ids_2.rename(columns = {'issn_electronic' : 'issn'})
			# cost_factor_ids_3 = cost_factor.loc[cost_factor['issn_print'].notna()][['issn_print', 'cost_factor_type', 'amount', 'symbol', 'comment']]
			# cost_factor_ids_3 = cost_factor_ids_3.rename(columns = {'issn_print' : 'issn'})
			# cost_factor_ids_4 = cost_factor.loc[cost_factor['issn'].notna()][['issn', 'cost_factor_type', 'amount', 'symbol', 'comment']]
			# cost_factor_ids = cost_factor_ids_1.append(cost_factor_ids_2)
			# cost_factor_ids = cost_factor_ids.append(cost_factor_ids_3)
			# cost_factor_ids = cost_factor_ids.append(cost_factor_ids_4)
			cost_factor_ids


			# In[82]:


			# supprimer les doublons et les vides
			cost_factor_ids = cost_factor_ids.drop_duplicates(subset=['issnl'])
			cost_factor_ids


			# In[83]:


			# merge dans l'autre sens pour garder que les lignes du fichier
			cost_factor_ids = pd.merge(cost_factor_ids, sherpa[['id', 'issnl']], on='issnl', how='left')
			cost_factor_ids


			# In[84]:


			# garder les lignes avec merge
			cost_factor_ids_all = cost_factor_ids.loc[cost_factor_ids['id'].notnull()]
			cost_factor_ids_all


			# In[85]:


			# supprimer les doublons
			cost_factor_ids_all = cost_factor_ids_all.drop_duplicates(subset=['id'])
			cost_factor_ids_all


			# In[86]:


			# supprimer les doublons par issnl
			cost_factor_ids_all = cost_factor_ids_all.drop_duplicates(subset=['issnl'])
			del cost_factor_ids_all['id']
			cost_factor_ids_all


			# In[87]:


			# convertir l'index en id
			cost_factor_ids_all = cost_factor_ids_all.reset_index()
			# ajout de l'id avec l'index + 1
			cost_factor_ids_all['cost_factor'] = cost_factor_ids_all['index'] + id_start
			del cost_factor_ids_all['index']
			# convertir l'index en id
			cost_factor_ids_all = cost_factor_ids_all.reset_index()
			# ajout de l'id avec l'index + 1
			cost_factor_ids_all['cost_factor'] = cost_factor_ids_all['index'] + id_start
			del cost_factor_ids_all['index']
			cost_factor_ids_all


			# In[88]:


			# merge avec la table sherpa
			sherpa = pd.merge(sherpa, cost_factor_ids_all[['issnl', 'cost_factor']], on='issnl', how='left')
			sherpa


			# In[89]:


			sherpa.loc[sherpa['cost_factor'].isna()]


			# In[90]:


			# garder les APCs pour la version published
			sherpa.loc[sherpa['article_version'] != 'published', 'cost_factor'] = np.nan
			sherpa.loc[sherpa['cost_factor'].notna()]


			# In[91]:


			# renommer l'id du fichier sherpa brut
			# cost_factor_ids_all = cost_factor_ids_all.rename(columns = {'id' : 'id_sherpa'})
			cost_factor_ids_all = cost_factor_ids_all.rename(columns = {'cost_factor' : 'id'})
			cost_factor_ids_all


			# In[92]:


			cost_factor_ids_all['id'] = cost_factor_ids_all['id'].astype(int)


			# In[93]:


			cost_factor_ids_all


			# In[94]:


			cost_factor_export = cost_factor_ids_all[['id', 'cost_factor_type', 'amount', 'symbol', 'comment']]
			cost_factor_export


			# In[95]:


			cost_factor_export.shape[0]


			# In[96]:


			# ajout de la valeur Rabais 100% pour les licences Read & Publish
			rpid = cost_factor_export.shape[0] + 1
			cost_factor_export = cost_factor_export.append({'id' : rpid, 'cost_factor_type' : 2, 'amount' : 100, 'symbol' : '%', 'comment' : 'Read & Publish agreement'}, ignore_index=True)
			cost_factor_export


			# In[97]:


			# ajout de l'id dans la table read & publish
			rp['cost_factor'] = rpid
			rp


			# In[98]:


			# ajout de la valeur UNKNOWN
			cost_factor_export = cost_factor_export.append({'id' : 999999, 'cost_factor_type' : 999999, 'amount' : 0, 'symbol' : '', 'comment' : 'UNKNOWN'}, ignore_index=True)
			cost_factor_export


			# In[99]:


			# export de la table
			result = cost_factor_export.to_json(orient='records', force_ascii=False)
			parsed = json.loads(result)
			with open('sample/cost_factor.json', 'w', encoding='utf-8') as file:
			json.dump(parsed, file, indent=2, ensure_ascii=False)


			# In[100]:


			# export csv
			cost_factor_export.to_csv('sample/cost_factor.tsv', index=False)


			# In[101]:


			# export excel
			cost_factor_export.to_excel('sample/cost_factor.xlsx', index=False)


			# ## Table term

			# In[102]:


			sherpa


			# In[103]:


			# col_names = ['id', 'applicable_version', 'cost_factor', 'embargo', 'archiving']
			term_sherpa = sherpa[['id', 'version', 'cost_factor', 'embargo', 'archiving', 'locations_ir', 'locations_not_ir', 'licence', 'journal', 'conditions', 'public_notes', 'prerequisite_funders', 'prerequisite_funders_ror']]
			term_sherpa


			# In[104]:


			# renommer les champs
			term_sherpa = term_sherpa.rename(columns = {'id' : 'id_sherpa', 'embargo' : 'embargo_months', 'prerequisite_funders_ror' : 'ror'})
			term_sherpa


			# In[105]:


			# merge des champs dans le comment : conditions, public_notes, locations_not_ir
			term_sherpa['conditions'] = term_sherpa['conditions'].fillna('')
			term_sherpa['public_notes'] = term_sherpa['public_notes'].fillna('')
			term_sherpa['locations_not_ir'] = term_sherpa['locations_not_ir'].fillna('')
			term_sherpa['locations_ir'] = term_sherpa['locations_ir'].fillna('')
			term_sherpa.loc[term_sherpa['locations_not_ir'] != '', 'locations_not_ir'] = 'Non institutional archiving locations: ' + term_sherpa['locations_not_ir']
			term_sherpa.loc[term_sherpa['locations_ir'] != '', 'locations_ir'] = 'Institutional archiving locations: ' + term_sherpa['locations_ir']
			term_sherpa.loc[term_sherpa['archiving'] == False, 'comment'] = term_sherpa['locations_not_ir']
			term_sherpa.loc[term_sherpa['archiving'] == True, 'comment'] = term_sherpa['locations_ir']
			term_sherpa.loc[term_sherpa['comment'] == '', 'comment'] = 'Conditions: ' + term_sherpa['conditions']
			term_sherpa.loc[(term_sherpa['comment'] != '') & (term_sherpa['conditions'] != ''), 'comment'] = term_sherpa['comment'] + ' ; Conditions: ' + term_sherpa['conditions']
			term_sherpa.loc[(term_sherpa['public_notes'] != '') & (term_sherpa['public_notes'] != term_sherpa['comment']), 'comment'] = term_sherpa['comment'] + ' ; Public notes: ' + term_sherpa['public_notes']
			term_sherpa.loc[(term_sherpa['public_notes'] != '') & (term_sherpa['comment'] == ''), 'comment'] = 'Public notes: ' + term_sherpa['public_notes']
			term_sherpa


			# In[106]:


			term_sherpa['prerequisite_funders'].value_counts()


			# In[107]:


			rp


			# In[108]:


			term_rp = rp[['rp_id', 'version', 'archiving', 'embargo_months', 'cost_factor', 'licence', 'journal', 'rp_publisher', 'ror', 'valid_from', 'valid_until']]
			term_rp


			# In[109]:


			term_rp['rp_publisher'].value_counts()


			# In[110]:


			term_rp.loc[term_rp['rp_publisher'] == 'Elsevier', 'comment'] = 'Elsevier Read & Publish agreement'
			term_rp.loc[term_rp['rp_publisher'] == 'Wiley', 'comment'] = 'Wiley Read & Publish agreement'
			term_rp.loc[term_rp['rp_publisher'] == 'TF', 'comment'] = 'Taylor and Francis Read & Publish agreement'
			term_rp.loc[term_rp['rp_publisher'] == 'Springer Nature ', 'comment'] = 'Springer Nature Read & Publish agreement'
			term_rp.loc[term_rp['rp_publisher'] == 'CUP', 'comment'] = 'Cambridge University Press (CUP) Read & Publish agreement. Article types covered: Research Articles, Review Articles, Rapid Communication, Brief Reports and Case Reports'
			del term_rp['rp_publisher']
			term_rp


			# In[111]:


			# cocnat de deux tables
			term_orig = term_sherpa[['id_sherpa', 'version', 'cost_factor', 'embargo_months', 'archiving', 'licence', 'journal', 'prerequisite_funders', 'ror', 'comment']]
			term_orig


			# In[112]:


			term_orig = term_orig.append(term_rp, ignore_index=True, sort=False)
			term_orig


			# In[113]:


			# ajout d'un hash unique pour chaque variante
			term_orig['id_content_hash'] = term_orig.apply(lambda x: hash(tuple(x[['version', 'cost_factor', 'embargo_months', 'archiving', 'comment']])), axis = 1)
			term_orig['id_content_hash_licence'] = term_orig.apply(lambda x: hash(tuple(x[['version', 'cost_factor', 'embargo_months', 'archiving', 'licence', 'comment']])), axis = 1)


			# In[114]:


			term_orig.sort_values(by='id_content_hash')


			# In[115]:


			# doublons
			term_orig.loc[term_orig.duplicated(subset='id_content_hash')].sort_values(by='id_content_hash')


			# In[116]:


			term_orig['licence'] = term_orig['licence'].fillna(999999)
			term_orig['licence'] = term_orig['licence'].astype(int)
			term_orig['cost_factor'] = term_orig['cost_factor'].fillna(999999)
			term_orig['cost_factor'] = term_orig['cost_factor'].astype(int)
			# term_orig['embargo_months'] = term_orig['embargo_months'].fillna(0)
			# term_orig['embargo_months'] = term_orig['embargo_months'].astype(int)
			term_orig.loc[term_orig['archiving'] == True, 'ir_archiving'] = 1
			term_orig.loc[term_orig['archiving'] == False, 'ir_archiving'] = 0
			term_orig['ir_archiving'] = term_orig['ir_archiving'].fillna(0)
			term_orig


			# In[117]:


			term_orig.loc[term_orig['ir_archiving'].isna()]


			# In[118]:


			term_orig['ir_archiving'].value_counts()


			# In[119]:


			term_orig['licence'] = term_orig['licence'].astype(int)
			term_orig['ir_archiving'] = term_orig['ir_archiving'].astype(int)
			term_orig['cost_factor'] = term_orig['cost_factor'].astype(int)
			term_orig


			# In[120]:


			terms_export_dates = term_orig.loc[(term_orig['valid_from'].notna()) \| (term_orig['valid_until'].notna())][['id_content_hash', 'ror', 'valid_from', 'valid_until']]
			terms_export_dates


			# In[121]:


			terms_export = term_orig[['id_sherpa', 'rp_id', 'id_content_hash', 'id_content_hash_licence', 'version', 'cost_factor', 'embargo_months', 'ir_archiving', 'licence', 'comment']]
			terms_export


			# In[122]:


			# test de doublons
			terms_export.loc[terms_export.duplicated(subset='id_content_hash')].sort_values(by='id_content_hash')


			# In[123]:


			terms_export_dedup = terms_export.drop_duplicates(subset=['id_content_hash'])
			terms_export_dedup


			# In[124]:


			terms_export_dedup_licence = terms_export.drop_duplicates(subset=['id_content_hash_licence'])
			terms_export_dedup_licence


			# In[125]:


			# test de doublons
			terms_export_dedup_licence.loc[terms_export_dedup_licence.duplicated(subset='id_content_hash')].sort_values(by='id_content_hash')


			# In[126]:


			# totaux pour les deux sources
			terms_export_dedup.loc[terms_export_dedup['id_sherpa'].notna()].shape[0]


			# In[127]:


			terms_export_dedup.loc[terms_export_dedup['rp_id'].notna()].shape[0]


			# In[128]:


			terms_export_dedup.loc[terms_export_dedup['rp_id'].notna()]


			# In[129]:


			# convertir l'index en id
			terms_export_dedup.reset_index(inplace=True)
			del terms_export_dedup['index']
			terms_export_dedup


			# In[130]:


			# ajout de l'id avec l'index + 1
			terms_export_dedup['id'] = terms_export_dedup.index + 1
			# del terms_export_dedup['index']
			terms_export_dedup


			# In[131]:


			terms_export_dedup['source'] = ''
			terms_export_dedup


			# In[132]:


			# grouper par licence
			terms_export_dedup_licences = terms_export_dedup_licence[['licence', 'id_content_hash']]
			terms_export_dedup_licences


			# In[133]:


			# concat valeurs avec même id
			terms_export_dedup_licences['licence'] = terms_export_dedup_licences['licence'].astype(str)
			terms_export_dedup_licences = terms_export_dedup_licences.groupby('id_content_hash').agg({'licence': lambda x: ', '.join(x)})
			terms_export_dedup_licences


			# In[134]:


			# test des valeur multiples
			terms_export_dedup_licences.loc[terms_export_dedup_licences['licence'].str.contains(',')]


			# In[135]:


			# ajout des licences groupées
			terms_export_dedup_fin = pd.merge(terms_export_dedup, terms_export_dedup_licences, on='id_content_hash', how='left')
			terms_export_dedup_fin


			# In[136]:


			# merge avec les dates pour avoir les terms ids
			terms_export_dates = pd.merge(terms_export_dates, terms_export_dedup_fin[['id_content_hash', 'id']], on='id_content_hash')
			terms_export_dates = terms_export_dates.rename(columns = {'id' : 'term'})
			terms_export_dates


			# In[137]:


			# renommer les champs de licence
			del terms_export_dedup_fin['licence_x']
			terms_export_dedup_fin = terms_export_dedup_fin.rename(columns = {'licence_y' : 'licence'})


			# In[138]:


			terms_export_fin = terms_export_dedup_fin[['version', 'cost_factor', 'embargo_months', 'ir_archiving', 'licence', 'comment', 'id', 'source']]
			terms_export_fin


			# In[139]:


			# export de la table
			result = terms_export_fin.to_json(orient='records', force_ascii=False)
			parsed = json.loads(result)
			with open('sample/term.json', 'w', encoding='utf-8') as file:
			json.dump(parsed, file, indent=2, ensure_ascii=False)


			# In[140]:


			# export csv
			terms_export_fin.to_csv('sample/term.tsv', index=False)


			# In[141]:


			# export excel
			terms_export_fin.to_excel('sample/term.xlsx', index=False)


			# ## Table condition_type

			# In[142]:


			# Journal-only, Organization-only, Journal-organization agreement
			col_names = ['id',
			'condition_issuer'
			]
			condition_type = pd.DataFrame(columns = col_names)
			condition_type = condition_type.append({'id' : 1, 'condition_issuer' : 'Journal-only'}, ignore_index=True)
			condition_type = condition_type.append({'id' : 2, 'condition_issuer' : 'Organization-only'}, ignore_index=True)
			condition_type = condition_type.append({'id' : 3, 'condition_issuer' : 'Journal-organization agreement'}, ignore_index=True)
			condition_type


			# In[143]:


			# export de la table
			result = condition_type.to_json(orient='records', force_ascii=False)
			parsed = json.loads(result)
			with open('sample/condition_type.json', 'w', encoding='utf-8') as file:
			json.dump(parsed, file, indent=2, ensure_ascii=False)


			# In[144]:


			# export csv
			condition_type.to_csv('sample/condition_type.tsv', index=False)


			# In[145]:


			# export excel
			condition_type.to_excel('sample/condition_type.xlsx', index=False)


			# ## Table organization

			# In[146]:


			# extraction des organizations (funders)
			sherpa


			# In[147]:


			sherpa.loc[sherpa['prerequisite_funders'].notna()]


			# In[148]:


			sherpa['prerequisite_funders'].value_counts()


			# In[149]:


			funders = sherpa.loc[sherpa['prerequisite_funders'].notna()][['prerequisite_funders_name', 'prerequisite_funders_fundref', 'prerequisite_funders_ror', 'prerequisite_funders_country', 'prerequisite_funders_url', 'prerequisite_funders_sherpa_id']]
			funders


			# In[150]:


			funders_dedup = funders.drop_duplicates(subset='prerequisite_funders_ror')
			funders_dedup


			# In[151]:


			funders_dedup.shape[0]


			# In[152]:


			# export excel
			funders_dedup.to_excel('sample/funders.xlsx', index=False)


			# In[153]:


			# export csv
			funders_dedup.to_csv('sample/funders.tsv', index=False)


			# In[154]:


			# creation du DF
			organization_funders = funders_dedup
			organization_funders = organization_funders.rename(columns = {'prerequisite_funders_name' : 'name',
			'prerequisite_funders_fundref' : 'fundref',
			'prerequisite_funders_ror' : 'ror',
			'prerequisite_funders_country' : 'iso_code',
			'prerequisite_funders_url' : 'website',
			'prerequisite_funders_sherpa_id' : 'sherpa_id'
			})
			organization_funders


			# In[155]:


			# lien avec les pays
			country = pd.read_csv('sample/country.tsv', encoding='utf-8', header=0, sep='\t')
			country


			# In[156]:


			# merge avec les pays
			organization_funders['iso_code'] = organization_funders['iso_code'].str.upper()
			organization_funders['is_funder'] = 1
			organization_funders = pd.merge(organization_funders, country[['iso_code', 'id']], how='left', on='iso_code')
			organization_funders


			# In[157]:


			organization_funders = organization_funders.rename(columns = {'id' : 'country'})
			organization_funders


			# In[158]:


			# ajout des organizations suisses
			organization = pd.read_csv('ror/ror_ch_hei_export.tsv', encoding='utf-8', header=0, sep='\t', dtype={'fundref': str, 'orgref': str}, na_filter=False)
			organization


			# In[159]:


			# tri par nom
			organization = organization.sort_values(by='name')
			organization


			# In[160]:


			organization = organization.reset_index(drop=True)
			organization


			# In[161]:


			# mettre l'EPFL en position 1 et UNIGE en 2
			target_row = 32
			# Move target row to first element of list.
			idx = [target_row] + [i for i in range(len(organization)) if i != target_row]
			organization = organization.iloc[idx]
			organization


			# In[162]:


			organization = organization.reset_index(drop=True)
			organization


			# In[163]:


			# mettre l'EPFL en position 1 et UNIGE en 2
			target_row = 45
			# Move target row to first element of list.
			idx = [target_row] + [i for i in range(len(organization)) if i != target_row]
			organization = organization.iloc[idx]
			organization


			# In[164]:


			organization = organization.reset_index(drop=True)
			organization


			# In[165]:


			# ajout des funders
			organization = organization.append(organization_funders, ignore_index=True)
			organization


			# In[166]:


			# remplacement dans le fundref id qui renvoie vers du JSON seulement
			# URL actuel : http://data.crossref.org/fundingdata/funder/10.13039/[fundref id]
			# ex : http://dx.doi.org/10.13039/501100007903
			# redirigé sur : http://data.crossref.org/fundingdata/funder/10.13039/501100007903
			# URL des publications financées : https://search.crossref.org/funding?q=[fundref id]&from_ui=yes
			# ex : https://search.crossref.org/funding?q=501100003006&from_ui=yes
			organization['fundref'] = organization['fundref'].str.replace('http://dx.doi.org/10.13039/', '')
			organization


			# In[167]:


			# df pour l'export
			organization_export = organization[['name', 'website', 'country', 'starting_year', 'is_funder', 'ror', 'fundref']]
			organization_export


			# In[168]:


			# ajout des valeurs vides
			organization_export['starting_year'] = organization_export['starting_year'].fillna(0)
			organization_export['fundref'] = organization_export['fundref'].fillna('')
			organization_export['ror'] = organization_export['ror'].fillna('')
			organization_export


			# In[169]:


			# ajout de l'id avec l'index + 1
			organization_export['id'] = organization_export.index + 1
			# del terms_export_dedup['index']
			organization_export


			# In[170]:


			# export de la table
			result = organization_export.to_json(orient='records', force_ascii=False)
			parsed = json.loads(result)
			with open('sample/organization.json', 'w', encoding='utf-8') as file:
			json.dump(parsed, file, indent=2, ensure_ascii=False)


			# In[171]:


			# export excel
			organization_export.to_excel('sample/organization.xlsx', index=False)


			# In[172]:


			# export csv
			organization_export.to_csv('sample/organization.tsv', index=False)


			# ## Table condition_set_term

			# In[173]:


			term_orig


			# In[174]:


			terms_export_dedup


			# In[175]:


			# merge des terms id
			term_orig = pd.merge(term_orig, terms_export_dedup[['id_content_hash', 'id']], on='id_content_hash', how='left')
			term_orig


			# In[176]:


			term_orig = term_orig.rename(columns = {'id' : 'term'})
			term_orig


			# In[177]:


			condition_type


			# In[178]:


			# merge des condition type
			term_orig['condition_type'] = 3
			term_orig.loc[term_orig['ror'].isna(), 'condition_type'] = 1
			term_orig


			# In[179]:


			organization_export


			# In[180]:


			# merge des organizations
			term_orig = pd.merge(term_orig, organization_export[['ror', 'id']], on='ror', how='left')
			term_orig


			# In[181]:


			term_orig = term_orig.rename(columns = {'id' : 'organization'})
			term_orig


			# In[182]:


			# concat valeurs avec même id
			condition_set_term_dedup_terms = term_orig[['term', 'id_content_hash']]
			condition_set_term_dedup_terms_dedup = condition_set_term_dedup_terms.drop_duplicates()
			condition_set_term_dedup_terms_dedup = condition_set_term_dedup_terms_dedup.loc[condition_set_term_dedup_terms_dedup['term'].notna()]
			condition_set_term_dedup_terms_dedup['term'] = condition_set_term_dedup_terms_dedup['term'].astype(int)
			condition_set_term_dedup_terms_dedup['term'] = condition_set_term_dedup_terms_dedup['term'].astype(str)
			condition_set_term_dedup_terms_dedup = condition_set_term_dedup_terms_dedup.groupby('id_content_hash').agg({'term': lambda x: ', '.join(x)})
			condition_set_term_dedup_terms_dedup


			# In[183]:


			# concat valeurs avec même id
			condition_set_term_dedup_journals = term_orig[['journal', 'id_content_hash']]
			condition_set_term_dedup_journals_dedup = condition_set_term_dedup_journals.drop_duplicates()
			condition_set_term_dedup_journals_dedup = condition_set_term_dedup_journals_dedup.loc[condition_set_term_dedup_journals_dedup['journal'].notna()]
			condition_set_term_dedup_journals_dedup['journal'] = condition_set_term_dedup_journals_dedup['journal'].astype(int)
			condition_set_term_dedup_journals_dedup['journal'] = condition_set_term_dedup_journals_dedup['journal'].astype(str)
			condition_set_term_dedup_journals_dedup = condition_set_term_dedup_journals_dedup.groupby('id_content_hash').agg({'journal': lambda x: ', '.join(x)})
			condition_set_term_dedup_journals_dedup


			# In[184]:


			# concat valeurs avec même id
			condition_set_term_dedup_organizations = term_orig[['organization', 'id_content_hash']]
			condition_set_term_dedup_organizations_dedup = condition_set_term_dedup_organizations.drop_duplicates()
			condition_set_term_dedup_organizations_dedup = condition_set_term_dedup_organizations_dedup.loc[condition_set_term_dedup_organizations_dedup['organization'].notna()]
			condition_set_term_dedup_organizations_dedup['organization'] = condition_set_term_dedup_organizations_dedup['organization'].astype(int)
			condition_set_term_dedup_organizations_dedup['organization'] = condition_set_term_dedup_organizations_dedup['organization'].astype(str)
			condition_set_term_dedup_organizations_dedup = condition_set_term_dedup_organizations_dedup.groupby('id_content_hash').agg({'organization': lambda x: ', '.join(x)})
			condition_set_term_dedup_organizations_dedup


			# In[185]:


			# concat valeurs avec même id : pas possible pour condition_type
			condition_set_term_dedup_condition_types = term_orig[['condition_type', 'id_content_hash']]
			condition_set_term_dedup_condition_types_dedup = condition_set_term_dedup_condition_types.drop_duplicates()
			condition_set_term_dedup_condition_types_dedup = condition_set_term_dedup_condition_types_dedup.loc[condition_set_term_dedup_condition_types_dedup['condition_type'].notna()]
			# condition_set_term_dedup_condition_types_dedup['condition_type'] = condition_set_term_dedup_condition_types_dedup['condition_type'].astype(int)
			# condition_set_term_dedup_condition_types_dedup['condition_type'] = condition_set_term_dedup_condition_types_dedup['condition_type'].astype(str)
			# condition_set_term_dedup_condition_types_dedup = condition_set_term_dedup_condition_types_dedup.groupby('id_content_hash').agg({'condition_type': lambda x: ', '.join(x)})
			condition_set_term_dedup_condition_types_dedup


			# In[186]:


			# recuperation des ids groupés
			terms_export_dedup = pd.merge(terms_export_dedup, condition_set_term_dedup_terms_dedup, on='id_content_hash', how='left')
			terms_export_dedup = pd.merge(terms_export_dedup, condition_set_term_dedup_journals_dedup, on='id_content_hash', how='left')
			terms_export_dedup = pd.merge(terms_export_dedup, condition_set_term_dedup_organizations_dedup, on='id_content_hash', how='left')
			terms_export_dedup = pd.merge(terms_export_dedup, condition_set_term_dedup_condition_types_dedup, on='id_content_hash', how='left')
			terms_export_dedup


			# In[187]:


			condition_sets_orig = terms_export_dedup[['term', 'condition_type', 'organization', 'journal']]
			condition_sets_orig


			# In[188]:


			# ajout d'un hash unique pour chaque variante
			condition_sets_orig['id_term_hash'] = condition_sets_orig.apply(lambda x: hash(tuple(x[['condition_type', 'organization', 'journal']])), axis = 1)
			condition_sets_orig


			# In[189]:


			# grouper les termes qui ont les mêmes valeurs pour le reste
			condition_sets_orig_terms = condition_sets_orig[['term', 'id_term_hash']]
			condition_sets_orig_terms_dedup = condition_sets_orig_terms.drop_duplicates()
			condition_sets_orig_terms_dedup = condition_sets_orig_terms_dedup.loc[condition_sets_orig_terms_dedup['term'].notna()]
			condition_sets_orig_terms_dedup['term'] = condition_sets_orig_terms_dedup['term'].astype(int)
			condition_sets_orig_terms_dedup['term'] = condition_sets_orig_terms_dedup['term'].astype(str)
			condition_sets_orig_terms_dedup = condition_sets_orig_terms_dedup.groupby('id_term_hash').agg({'term': lambda x: ', '.join(x)})
			condition_sets_orig_terms_dedup


			# In[190]:


			# ajout des ids groupées
			condition_sets_orig_terms = pd.merge(condition_sets_orig, condition_sets_orig_terms_dedup, on='id_term_hash', how='left')
			condition_sets_orig_terms


			# In[191]:


			# rename terms
			del condition_sets_orig_terms['term_x']
			condition_sets_orig_terms = condition_sets_orig_terms.rename(columns = {'term_y' : 'term'})
			condition_sets_orig_terms


			# In[192]:


			# test duplicates
			condition_sets_orig_terms.loc[condition_sets_orig_terms.duplicated()].sort_values(by='term')


			# In[193]:


			condition_sets_orig_terms.loc[condition_sets_orig_terms.duplicated()].shape[0]


			# In[194]:


			condition_sets_orig_terms_dedup = condition_sets_orig_terms.drop_duplicates()
			condition_sets_orig_terms_dedup


			# In[195]:


			# ajout des champs manquants
			condition_sets_orig_terms_dedup['comment'] = ''


			# In[196]:


			# remplacement des "nan"
			condition_sets_orig_terms_dedup.loc[condition_sets_orig_terms_dedup['journal'].isna()]


			# In[197]:


			# remplacement des "nan"
			condition_sets_orig_terms_dedup.loc[condition_sets_orig_terms_dedup['term'].isna()]


			# In[198]:


			# remplacement des "nan"
			condition_sets_orig_terms_dedup.loc[condition_sets_orig_terms_dedup['condition_type'].isna()]


			# In[199]:


			# remplacement des "nan"
			condition_sets_orig_terms_dedup.loc[condition_sets_orig_terms_dedup['organization'].isna()]


			# In[200]:


			# remplacement des "nan"
			condition_sets_orig_terms_dedup['organization'] = condition_sets_orig_terms_dedup['organization'].fillna('')
			condition_sets_orig_terms_dedup


			# In[201]:


			# convertir l'index en id
			condition_sets_orig_terms_dedup = condition_sets_orig_terms_dedup.reset_index()
			# ajout de l'id avec l'index + 1
			condition_sets_orig_terms_dedup['id'] = condition_sets_orig_terms_dedup['index'] + 1
			del condition_sets_orig_terms_dedup['index']
			condition_sets_orig_terms_dedup


			# In[202]:


			# convertir l'index en id
			condition_sets_orig_terms_dedup = condition_sets_orig_terms_dedup.reset_index()
			# ajout de l'id avec l'index + 1
			condition_sets_orig_terms_dedup['id'] = condition_sets_orig_terms_dedup['index'] + 1
			del condition_sets_orig_terms_dedup['index']
			condition_sets_orig_terms_dedup


			# In[203]:


			# export de la table
			result = condition_sets_orig_terms_dedup[['id', 'condition_type', 'organization', 'journal', 'term', 'comment']].to_json(orient='records', force_ascii=False)
			parsed = json.loads(result)
			with open('sample/condition_set.json', 'w', encoding='utf-8') as file:
			json.dump(parsed, file, indent=2, ensure_ascii=False)


			# In[204]:


			# export excel
			condition_sets_orig_terms_dedup[['id', 'condition_type', 'organization', 'journal', 'term', 'comment']].to_excel('sample/condition_set.xlsx', index=False)


			# In[205]:


			# export csv
			condition_sets_orig_terms_dedup[['id', 'condition_type', 'organization', 'journal', 'term', 'comment']].to_csv('sample/condition_set.tsv', index=False)


			# ## Table organization_condition_set

			# In[206]:


			condition_sets_orig_terms_dedup


			# In[207]:


			condition_sets_orig_terms_dedup.loc[(condition_sets_orig_terms_dedup['organization'].notna()) & (condition_sets_orig_terms_dedup['organization'] != '')]


			# In[208]:


			# creation du DF
			# col_names = ['id',
			# 'organization',
			# 'condition_set',
			# 'valid_from',
			# 'valid_until'
			# ]
			# organization_condition = pd.DataFrame(columns = col_names)
			organization_condition = condition_sets_orig_terms_dedup.loc[(condition_sets_orig_terms_dedup['organization'].notna()) & (condition_sets_orig_terms_dedup['organization'] != '')][['id', 'organization', 'term']]
			organization_condition


			# In[209]:


			# extraction des terms ids
			organization_condition_split = organization_condition.assign(term = organization_condition.term.str.split(',')).explode('term')
			organization_condition_split


			# In[210]:


			organization_condition_split.loc[organization_condition_split['organization'].isna()]


			# In[211]:


			organization_condition_split.loc[organization_condition_split['term'].isna()]


			# In[212]:


			organization_condition_split['term'] = organization_condition_split['term'].astype(int)
			organization_condition_split


			# In[213]:


			# ajout du ROR
			terms_export_dates


			# In[214]:


			# merge pour obtenir les dates
			organization_condition_split = pd.merge(organization_condition_split, terms_export_dates[['term', 'valid_from', 'valid_until']], on='term', how='left')
			organization_condition_split


			# In[215]:


			# dédoublonage
			organization_condition_split_dedup = organization_condition_split.drop_duplicates()
			organization_condition_split_dedup


			# In[216]:


			organization_condition = pd.merge(organization_condition, organization_condition_split_dedup[['id', 'valid_from', 'valid_until']], on='id', how='left')
			organization_condition


			# In[217]:


			organization_condition = organization_condition.rename(columns = {'id' : 'condition_set'})
			organization_condition['valid_from'] = organization_condition['valid_from'].fillna('')
			organization_condition['valid_until'] = organization_condition['valid_until'].fillna('')
			organization_condition


			# In[218]:


			# split final pour avoir une ligne par organization
			organization_condition_fin = organization_condition.assign(organization = organization_condition.organization.str.split(',')).explode('organization')
			organization_condition_fin


			# In[219]:


			# ajout de l'id avec l'index + 1
			organization_condition_fin = organization_condition_fin.reset_index()
			organization_condition_fin['id'] = organization_condition_fin.index + 1
			del organization_condition_fin['index']
			organization_condition_fin


			# In[220]:


			# export de la table
			result = organization_condition_fin[['id', 'condition_set', 'organization', 'valid_from', 'valid_until']].to_json(orient='records', force_ascii=False)
			parsed = json.loads(result)
			with open('sample/organization_condition.json', 'w', encoding='utf-8') as file:
			json.dump(parsed, file, indent=2, ensure_ascii=False)


			# In[221]:


			# export excel
			organization_condition_fin[['id', 'condition_set', 'organization', 'valid_from', 'valid_until']].to_excel('sample/organization_condition.xlsx', index=False)


			# In[222]:


			# export csv
			organization_condition_fin[['id', 'condition_set', 'organization', 'valid_from', 'valid_until']].to_csv('sample/organization_condition.tsv', index=False)


			# ## Table journal_condition_set

			# In[223]:


			# creation du DF
			# col_names = ['id',
			# 'journal',
			# 'condition_set',
			# 'valid_from',
			# 'valid_until'
			# ]
			# journal_condition = pd.DataFrame(columns = col_names)
			journal_condition = condition_sets_orig_terms_dedup.loc[(condition_sets_orig_terms_dedup['journal'].notna()) & (condition_sets_orig_terms_dedup['journal'] != '')][['id', 'journal']]
			journal_condition


			# In[224]:


			journal_condition = journal_condition.rename(columns = {'id' : 'condition_set'})
			journal_condition['valid_from'] = ''
			journal_condition['valid_until'] = ''
			journal_condition


			# In[225]:


			# split final pour avoir une ligne par journal
			journal_condition_fin = journal_condition.assign(journal = journal_condition.journal.str.split(',')).explode('journal')
			journal_condition_fin


			# In[226]:


			# ajout de l'id avec l'index + 1
			journal_condition_fin = journal_condition_fin.reset_index()
			journal_condition_fin['id'] = journal_condition_fin.index + 1
			del journal_condition_fin['index']
			journal_condition_fin


			# In[227]:


			# export de la table
			result = journal_condition_fin.to_json(orient='records', force_ascii=False)
			parsed = json.loads(result)
			with open('sample/journal_condition.json', 'w', encoding='utf-8') as file:
			json.dump(parsed, file, indent=2, ensure_ascii=False)


			# In[228]:


			# export excel
			journal_condition_fin.to_excel('sample/journal_condition.xlsx', index=False)


			# In[229]:


			# export csv
			journal_condition_fin.to_csv('sample/journal_condition.tsv', index=False)


			# In[ ]:

rOACCT/import_scripts/10_oacct_terms.pyf6ac3b58106fissn4frontend

10_oacct_terms.pyView Options

rOACCT/import_scripts/10_oacct_terms.py
f6ac3b58106fissn4frontend

10_oacct_terms.py
View Options