<SYSTEM_TASK:>
Given the following text description, write Python code to implement the functionality described below step by step
<END_TASK>
<USER_TASK:>
Description:
 DB Analysis
With a few hundred papers in the DB, some experiments can be done on the data.
1. Connecting to the DB
Step1:  2. Counting publications.
Step2:  3. Distinct Affiliations
Step3:  3. TF-IDF and K-Means?
3. Distinct Affiliations
<ASSISTANT_TASK:>
Python Code:
import mariadb
import json
with open('../credentials.json', 'r') as crd_json_fd:
    json_text = crd_json_fd.read()
    json_obj = json.loads(json_text)
credentials = json_obj["Credentials"]
username = credentials["username"]
password = credentials["password"]
table_name = "publications"
db_name = "ubbcluj"
mariadb_connection = mariadb.connect(user=username, password=password, database=db_name)
mariadb_cursor = mariadb_connection.cursor()
Explanation: DB Analysis
With a few hundred papers in the DB, some experiments can be done on the data.
1. Connecting to the DB
End of explanation
queryString = "SELECT COUNT(*) FROM "
queryString += table_name
try:
    mariadb_cursor.execute(queryString)
except Exception as ex:
    print(ex)
for item in mariadb_cursor:
    count = item[0]
    print("Number of publications: {0}".format(count))
    
Explanation: 2. Counting publications.
End of explanation
queryString = "SELECT Affiliations, COUNT(*) AS c FROM publications GROUP BY Affiliations ORDER BY c DESC"
try:
    mariadb_cursor.execute(queryString)
except Exception as ex:
    print(ex)
affiliations = []
for item in mariadb_cursor:
    Affiliation = item[0]
    affiliations.append(item[0])
    c = item[1]
    print("{0} : {1} occurences".format(Affiliation, c))
for i in affiliations:
    if "conference" in [k.lower() for k in i.split()]:
        print(i)
for i in affiliations:
    if "journal" in [k.lower() for k in i.split()]:
        print(i)
tokens = []
for i in affiliations:
    words = i.split()
    for word in words:
        tokens.append(word)
tokens
from nltk.corpus import stopwords
import nltk
nltk.download('stopwords')
sr= stopwords.words('english')
clean_tokens = tokens[:]
for token in tokens:
    if token in stopwords.words('english'):
        
        clean_tokens.remove(token)
freq = nltk.FreqDist(clean_tokens)
for key,val in freq.items():
    #print(str(key) + ':' + str(val))
    pass
freq.plot(20, cumulative=False)
# Histogram of professors publication number
queryString = "SELECT (Select FullName from humanoid_entities where id = ProfessorId), ProfessorId, COUNT(Title) FROM publications GROUP BY ProfessorId ORDER BY COUNT(Title) desc"
try:
    mariadb_cursor.execute(queryString)
except Exception as ex:
    print(ex)
name_dict = {}
tup_list = []
max_id = 0
for item in mariadb_cursor:
    ProfName = item[0]
    ProfId = item[1]
    max_id = max(max_id, ProfId)
    Count = item[2]
    tup_list.append((ProfName, ProfId, Count))
    name_dict[ProfId] = ProfName
    
    
import pandas as pd
print(tup_list)
final_list = []
for i in range(max_id):
    found_id = False
    found_value = 0
    for k in tup_list:
        if i == k[1]:
            found_id = True
            found_value = k[2]
            break
    if not found_id:
        name_dict[i] = "NONE"
    final_list.append((i, found_value))
    
fa = pd.DataFrame([k[1] for k in final_list])
import numpy as np
import matplotlib.pyplot as plt
import matplotlib.ticker as plticker
fig = plt.figure()
ax = fig.add_axes([0, 0, 1, 1])
ax.bar([name_dict[k[0]] for k in final_list], [k[1] for k in final_list])
loc = plticker.MultipleLocator(base=1.0)
ax.xaxis.set_major_locator(loc)
plt.xticks(rotation=90)
plt.show()
Explanation: 3. Distinct Affiliations
End of explanation
queryString = "SELECT Title FROM publications"
try:
    mariadb_cursor.execute(queryString)
except Exception as ex:
    print(ex)
titles = []
for item in mariadb_cursor:
    Title = item[0]
    titles.append(item[0])
from sklearn.feature_extraction.text import CountVectorizer
corpus = titles[:]
corpus
vectorizer = CountVectorizer()
X = vectorizer.fit_transform(corpus)
print(vectorizer.get_feature_names())
print(X.shape)
print(X.toarray())
for k in X.toarray():
    for j in k:
        if j > 1:
            print(j)
import pandas as pd
from sklearn.feature_extraction.text import TfidfVectorizer
vec = TfidfVectorizer(use_idf=False, norm='l1')
matrix = vec.fit_transform(corpus)
pd.DataFrame(matrix.toarray(), columns=vec.get_feature_names())
from textblob import TextBlob
import nltk
nltk.download('punkt')
def textblob_tokenizer(str_input):
    blob = TextBlob(str_input.lower())
    tokens = blob.words
    words = [token.stem() for token in tokens]
    return words
vec = CountVectorizer(tokenizer=textblob_tokenizer)
matrix = vec.fit_transform(corpus)
pd.DataFrame(matrix.toarray(), columns=vec.get_feature_names())
vec = TfidfVectorizer(tokenizer=textblob_tokenizer,
                      stop_words='english',
                      use_idf=True)
matrix = vec.fit_transform(corpus)
df = pd.DataFrame(matrix.toarray(), columns=vec.get_feature_names())
for word in df.columns:
    for row in df[word]:
        if row != 0.0:
            print(word, row)
from sklearn.cluster import KMeans
number_of_clusters = 10
km = KMeans(n_clusters=number_of_clusters)
km.fit(matrix)
km.fit
print("Top terms per cluster:")
order_centroids = km.cluster_centers_.argsort()[:, ::-1]
terms = vec.get_feature_names()
for i in range(number_of_clusters):
    top_ten_words = [terms[ind] for ind in order_centroids[i, :5]]
    print("Cluster {}: {}".format(i, ' '.join(top_ten_words)))
results = pd.DataFrame({
    'corpus': corpus,
    'category': km.labels_
})
results.sort_values('category')
for k in results.sort_values('category').values:
    print(k[1], " --- ", k[0])
### GENSIM
from gensim.models import word2vec
from gensim.test.utils import common_texts, get_tmpfile
tokenized_sentences = [[j.lower() for j in st.split() if j not in stopwords.words('english')] for st in corpus]
model = word2vec.Word2Vec(tokenized_sentences, min_count=1)
model.save("word2vec.model")
#model = word2vec.load("word2vec.model")
model
model.wv["study"]
words = list(model.wv.vocab)
print(words)
X = model[model.wv.vocab]
df = pd.DataFrame(df)
df.shape
df.head()
import numpy as np
import io
out_v = io.open('vecs.tsv', 'w', encoding='utf-8')
out_m = io.open('meta.tsv', 'w', encoding='utf-8')
for word in model.wv.vocab:
    out_m.write(word + "\n")
    out_v.write('\t'.join([str(x) for x in model[word]]) + "\n")
    
out_v.close()
out_m.close()
Explanation: 3. TF-IDF and K-Means?
3. Distinct Affiliations
End of explanation
<END_TASK>