<SYSTEM_TASK:>
Given the following text description, write Python code to implement the functionality described below step by step
<END_TASK>
<USER_TASK:>
Description:
 Dates pivot table
Step1:  Get collection information from ArticleMeta
Step2:  Filtering valid collections and renames 'code' to 'collection'
Some collections won't be analyzed, mainly to avoid duplicates
(there are articles in more than one collection).
The spa (Public Health collection) should have part of it
kept in the result, but it's not a collection
whose journals/articles are assigned to a single country.
The collections below are linked to a single country
Step3:  ISSN selection from spa
These journals in the spa collection have the following countries
Step4:  Dates dataset
This dataset is the
Network spreadsheet/CSV pack
 which can be found in the
SciELO Analytics report
web page.
The first two rows of it are
Step5:  Simplify the column names
Step6:  Creates a new DataFrame
Step7:  Add pub_year (ate_1996)
Step8:  Convert string to int
Step9:  Get the current Year
Step10:  Insert columns for checking
Step11:  Insert columns with calcule of months
Step12:  Filter citables documents
Step13:  Pivot Table
Step14:  Renames the labels for CSV
Step15:  Check a specific document
<ASSISTANT_TASK:>
Python Code:
from datetime import datetime
start = datetime.utcnow() # For measuring the total processing time
import json
from urllib.request import urlopen
import pandas as pd
import numpy as np
Explanation: Dates pivot table
End of explanation
AMC_URL = "http://articlemeta.scielo.org/api/v1/collection/identifiers/"
amc_data = pd.DataFrame(json.load(urlopen(AMC_URL)))
print("Number of collections: " + str(amc_data.shape[0]+1))
amc_data.head(2)
Explanation: Get collection information from ArticleMeta
End of explanation
dont_evaluate = ["bio", "cci", "cic", "ecu", "psi", "pry", "rve", "rvo", "rvt", "sss", "spa", "wid"]
amc_names_map = {"code": "collection"}
amc_pairs = amc_data[(amc_data["acron2"].str.len() == 2) & 
                     ~amc_data["code"].isin(dont_evaluate)]\
                    .rename(columns=amc_names_map)\
#                    "~amc_data["code"].isin(dont_evaluate)]" is denying the list "dont_evaluate"
print("Number of collections: " + str(amc_pairs.shape[0]+1))
collections = amc_pairs[['collection']].copy()
collections
Explanation: Filtering valid collections and renames 'code' to 'collection'
Some collections won't be analyzed, mainly to avoid duplicates
(there are articles in more than one collection).
The spa (Public Health collection) should have part of it
kept in the result, but it's not a collection
whose journals/articles are assigned to a single country.
The collections below are linked to a single country:
End of explanation
spa_issn_country = pd.DataFrame([
    ("0021-2571"),
    ("0042-9686"),
    ("1020-4989"),
    ("1555-7960"),
], columns=["issn"])
spa_issn_country # For collection = "spa", only!
Explanation: ISSN selection from spa
These journals in the spa collection have the following countries:
End of explanation
import zipfile
# Use the Zip file in jcatalog/data/scielo
with zipfile.ZipFile( "../../data/scielo/tabs_network_190210.zip", 'r') as zip_ref:
    zip_ref.extract('documents_dates.csv', 'csv_files')
df0 = pd.read_csv('csv_files/documents_dates.csv', keep_default_na=False, low_memory=False)
df0.shape
Explanation: Dates dataset
This dataset is the
Network spreadsheet/CSV pack
 which can be found in the
SciELO Analytics report
web page.
The first two rows of it are:
Unzip the CSV file
End of explanation
names_map = {
    "ISSN SciELO": "issn",
    "title at SciELO":"title",
    "document publishing ID (PID SciELO)": "docs",
    "document type":"type",
    "document is citable": "is_citable",
    "document publishing year": "year"
}
#  df[list(names_map.keys())].rename(columns=names_map, inplace=True)
df0.rename(columns=names_map, inplace=True)
df0.head(2)
Explanation: Simplify the column names
End of explanation
df = pd.concat([
    pd.merge(df0[df0["collection"] != "spa"], collections,      how="inner", on="collection"),
    pd.merge(df0[df0["collection"] == "spa"], spa_issn_country, how="inner", on="issn"),
])
df.head(2)
# compare
df0.shape
df.shape
set(df.collection)
Explanation: Creates a new DataFrame: filtering SPA and discarding those collections that are not analyzable
End of explanation
df["pub_year"] = np.where(df['year'] <= 1996, 'ate_1996', df["year"])
Explanation: Add pub_year (ate_1996)
End of explanation
df['document published at year'] = pd.to_numeric(df['document published at year'], errors='coerce')
df['document published at month'] = pd.to_numeric(df['document published at month'], errors='coerce')
df['document accepted at year'] = pd.to_numeric(df['document accepted at year'], errors='coerce')
df['document accepted at month'] = pd.to_numeric(df['document accepted at month'], errors='coerce')
df['document submitted at year'] = pd.to_numeric(df['document submitted at year'], errors='coerce')
df['document submitted at month'] = pd.to_numeric(df['document submitted at month'], errors='coerce')
Explanation: Convert string to int
End of explanation
current_year = datetime.now().year
print(current_year)
Explanation: Get the current Year
End of explanation
df['check_doc_pub_scielo'] = np.where(
    (df['document published in SciELO at year'] >= 1997) & 
    (df['document published in SciELO at year'] <= current_year) & 
    (df['document published in SciELO at month'] >= 1) & 
    (df['document published in SciELO at month'] <= 12) &
    (df['document published in SciELO at day'] >= 1) & 
    (df['document published in SciELO at day'] <= 31), 0,1)
df['check_doc_pub'] = np.where(
    (df['document published at year'] >= 1997) & 
    (df['document published at year'] <= current_year) & 
    (df['document published at month'] >= 1) & 
    (df['document published at month'] <= 12), 0, 1)
df['check_doc_accepted'] = np.where(
    (df['document accepted at year'] >= 1997) & 
    (df['document accepted at year'] <= current_year) & 
    (df['document accepted at month'] >= 1) & 
    (df['document accepted at month'] <= 12), 0, 1)
df['check_doc_submitted'] = np.where(
    (df['document submitted at year'] >= 1997) & 
    (df['document submitted at year'] <= current_year) & 
    (df['document submitted at month'] >= 1) & 
    (df['document submitted at month'] <= 12), 0, 1)
Explanation: Insert columns for checking
End of explanation
df['meses_sub_aprov'] = np.where(
    (df.check_doc_submitted == 0) & (df.check_doc_accepted == 0),
    (df['document accepted at year'] * 12 + df['document accepted at month']) - 
    (df['document submitted at year'] * 12 + df['document submitted at month']), np.nan)
df['meses_aprov_pub'] = np.where(
    (df.check_doc_accepted == 0) & (df.check_doc_pub == 0),
    (df['document published at year'] * 12 + df['document published at month']) - 
    (df['document accepted at year'] * 12 + df['document accepted at month']), np.nan)
df['meses_sub_pub'] = np.where(
    (df.check_doc_submitted == 0) & (df.check_doc_pub == 0),
    (df['document published at year'] * 12 + df['document published at month']) - 
    (df['document submitted at year'] * 12 + df['document submitted at month']), np.nan)
df['meses_aprov_pub_scielo'] = np.where(
    (df.check_doc_accepted == 0) & (df.check_doc_pub_scielo == 0),
    (df['document published in SciELO at year'] * 12 + df['document published in SciELO at month']) - 
    (df['document accepted at year'] * 12 + df['document accepted at month']), np.nan)
df['meses_sub_pub_scielo'] = np.where(
    (df.check_doc_submitted == 0) & (df.check_doc_pub_scielo == 0),
    (df['document published in SciELO at year'] * 12 + df['document published in SciELO at month']) - 
    (df['document submitted at year'] * 12 + df['document submitted at month']), np.nan)
Explanation: Insert columns with calcule of months
End of explanation
dfcit = df[df['is_citable'] == 1]
dfcit.shape
Explanation: Filter citables documents
End of explanation
values_list = ['meses_sub_aprov', 
               'meses_aprov_pub', 
               'meses_sub_pub', 
               'meses_aprov_pub_scielo', 
               'meses_sub_pub_scielo']
td = dfcit.pivot_table(
     index=["issn"],
     values=values_list,
     columns=["pub_year"],
     aggfunc=[np.nanmean, np.nanstd],
     fill_value="")
td.head(10).T
td.columns.levels
Explanation: Pivot Table
End of explanation
td.keys()
for k in td.keys():
    print(k)
newlabel = []
for k in td.keys():
    newlabel.append(k[0]
                    .replace('nanmean', 'media')
                    .replace('nanstd', 'desvp')+'_'+k[1]+'_'+k[2])
newlabel
newlabel[0::24]
td.columns = newlabel
td.T
td.to_csv("output/td_documents_dates_network.csv")
print(f"Notebook processing duration: {datetime.utcnow() - start}")
Explanation: Renames the labels for CSV
End of explanation
b = df[df['docs'].str.contains('S0100-40421998000500015')]
b[['document submitted at year',
   'document accepted at year',
   'document published at year',
   'document published in SciELO at year']].astype(int)
Explanation: Check a specific document
End of explanation
<END_TASK>