def zoomable_sunburst():
mydb, mycursor = connectDB()
#main_indicator
mind_list = []
data1 = mycursor.execute("SELECT * FROM `main_indicator`")
records1 = mycursor.fetchall()
for row1 in records1:
id1 = tuple([row1[0]])
#indicator
ind_list = []
data2 = mycursor.execute(
"SELECT * FROM `indicator` WHERE `mind_id` = (%s)", id1)
records2 = mycursor.fetchall()
for row2 in records2:
id2 = tuple([row2[0]])
#sub indicator
sind_list = []
data3 = mycursor.execute(
"SELECT * FROM `sub_indicator` WHERE `ind_id` = (%s)", id2)
records3 = mycursor.fetchall()
for row3 in records3:
sind = OrderedDict()
sind['name'] = row3[1]
sind['size'] = row3[4]
sind_list.append(sind)
ind = OrderedDict()
ind['name'] = row2[1]
ind['children'] = sind_list
ind_list.append(ind)
mind = OrderedDict()
mind['name'] = row1[1]
mind['children'] = ind_list
mind_list.append(mind)
# List menggabungkan semua dictionaries
indikator_list = []
indikator = OrderedDict()
indikator['name'] = 'indikator'
indikator['children'] = mind_list
indikator_list.append(indikator)
# Mengubah list dari dict menjadi JSON
j = json.dumps(indikator_list[0])
# Menyimpan file dengan format .json
with open('C:/xampp/htdocs/SKRIPSI/assets/json/indikator.json', 'w') as f:
f.write(j)
def tokoh(sen):
mydb, mycursor = connectDB()
mycursor.execute("DELETE FROM `tokoh_indikator` WHERE 1")
mydb.commit()
sql = "SELECT `indicator` FROM `indicator`"
mycursor.execute(sql)
result = mycursor.fetchall()
for row in tqdm(result):
ind = row[0]
mycursor.execute(
"""SELECT `nama_tokoh` FROM `output` WHERE `indikator` LIKE '%""" +
ind + """%' AND `sentimen_kutipan` = '""" + sen + """'""")
result = mycursor.fetchall()
if ind == 'PDB/PDRB':
ind = 'PDB'
total_tokoh = []
list_tokoh = []
for row in result:
tokoh = row[0]
x = ast.literal_eval(tokoh)
x = [n.strip() for n in x]
for y in x:
list_tokoh.append(y)
if y not in total_tokoh:
total_tokoh.append(y)
for row in total_tokoh:
jumlah = list_tokoh.count(row)
sql = """INSERT INTO `tokoh_indikator`(`indikator`, `sentimen`, `nama_tokoh`, `jumlah`) VALUES (%s,%s,%s,%s)"""
insert_tuple = (ind, sen, row, jumlah)
mycursor.execute(sql, insert_tuple)
# menghilangkan tokoh BPS
del_tokoh = [
'Suhariyanto', 'Suharyanto', 'Kecuk', 'Suryamin', 'Sasmito'
]
for r in del_tokoh:
mycursor.execute(
"""DELETE FROM `tokoh_indikator` WHERE `nama_tokoh` LIKE '%"""
+ r + """%'""")
mydb.commit()
def hitung_sub_indikator():
mydb, mycursor = connectDB()
sql = "SELECT `sub_indicator` FROM `sub_indicator`"
mycursor.execute(sql)
result = mycursor.fetchall()
for row in tqdm(result):
sind = row[0]
mycursor.execute(
"""SELECT COUNT(*) FROM `output` WHERE `sub_indikator` LIKE '%""" +
sind + """%'""")
result = mycursor.fetchall()
for row in result:
value = row[0]
sql_update = """UPDATE `sub_indicator` SET `value` = (%s) WHERE `sub_indicator` LIKE '%""" + sind + """%'"""
mycursor.execute(sql_update, (value, ))
mydb.commit()
def organisasi(sen):
mydb, mycursor = connectDB()
mycursor.execute("DELETE FROM `org_indikator` WHERE 1")
mydb.commit()
sql = "SELECT `indicator` FROM `indicator`"
mycursor.execute(sql)
result = mycursor.fetchall()
for row in tqdm(result):
ind = row[0]
mycursor.execute(
"""SELECT `organisasi` FROM `output` WHERE `indikator` LIKE '%""" +
ind + """%' AND `sentimen_kutipan` = '""" + sen + """'""")
result = mycursor.fetchall()
if ind == 'PDB/PDRB':
ind = 'PDB'
total_org = []
list_org = []
for row in result:
org = row[0]
x = ast.literal_eval(org)
x = [n.strip() for n in x]
for y in x:
list_org.append(y)
if y not in total_org:
total_org.append(y)
for row in total_org:
jumlah = list_org.count(row)
sql = """INSERT INTO `org_indikator`(`indikator`, `sentimen`, `organisasi`, `jumlah`) VALUES (%s,%s,%s,%s)"""
insert_tuple = (ind, sen, row, jumlah)
mycursor.execute(sql, insert_tuple)
# menghilangkan organisasi BPS
del_org = ['BPS', 'Badan Pusat Statistik']
for r in del_org:
mycursor.execute(
"""DELETE FROM `org_indikator` WHERE `organisasi` LIKE '%"""
+ r + """%'""")
mydb.commit()
def donutchart():
mydb, mycursor = connectDB()
# select indicator dari database
mycursor.execute("SELECT `indicator` FROM `indicator`")
result = mycursor.fetchall()
sentimen_list = []
for row in result:
ind = row[0]
sql_pos = mycursor.execute(
"""SELECT count(*) FROM `output` WHERE `indikator` LIKE '%""" +
ind + """%' AND `sentimen_berita` = 'Positif'""")
positif = mycursor.fetchone()
sql_neg = mycursor.execute(
"""SELECT count(*) FROM `output` WHERE `indikator` LIKE '%""" +
ind + """%' AND `sentimen_berita` = 'Negatif'""")
negatif = mycursor.fetchone()
sql_net = mycursor.execute(
"""SELECT count(*) FROM `output` WHERE `indikator` LIKE '%""" +
ind + """%' AND `sentimen_berita` = 'Netral'""")
netral = mycursor.fetchone()
sentimen = OrderedDict()
sentimen['id'] = ind
sentimen['Negatif'] = negatif[0]
sentimen['Netral'] = netral[0]
sentimen['Positif'] = positif[0]
sentimen_list.append(sentimen)
# menyimpan file dengan format .csv
with open('C:/xampp/htdocs/SKRIPSI/assets/data/data.csv',
mode='w',
newline='') as f:
fieldnames = ['id', 'Negatif', 'Netral', 'Positif']
writer = csv.DictWriter(f, fieldnames=fieldnames)
writer.writeheader()
for r in sentimen_list:
j = json.dumps(r)
j = json.loads(j)
writer.writerow(j)
def klasifikasi_indikator():
mydb, mycursor = connectDB()
sql_alias_id = """SELECT `alias`, `id` FROM `temp_output`"""
mycursor.execute(sql_alias_id)
rows = mycursor.fetchall()
sql = "SELECT `sub_indicator`, `ind_id` FROM `sub_indicator` WHERE `alias` LIKE lower(%s)"
sql2 = "SELECT `indicator` FROM `indicator` WHERE `ind_id` = (%s)"
for row in tqdm(rows):
sub_ind = []
ind_id = []
ind = []
alias = row[0]
id = row[1]
x = ast.literal_eval(alias)
x = [n.strip() for n in x]
for r in x:
var = "%'" + r + "'%"
mycursor.execute(sql, (var, ))
y = mycursor.fetchone()
if y is not None:
if y[0] not in sub_ind:
sub_ind.append(y[0])
if y[1] not in ind_id:
ind_id.append(y[1])
sub_ind = str(sub_ind)
set_var('sub_indikator', sub_ind, id)
for id1 in ind_id:
mycursor.execute(sql2, (id1, ))
y = mycursor.fetchone()
if y is not None:
if y[0] not in ind:
ind.append(y[0])
ind = str(ind)
set_var('indikator', ind, id)
def predict_NER():
mydb, mycursor = connectDB()
sql_insert1 = "INSERT INTO `berita_crawling` SELECT * FROM `temp`"
mycursor.execute(sql_insert1)
mydb.commit()
sql_query = """SELECT * FROM `temp`"""
mycursor.execute(sql_query)
berita = mycursor.fetchall()
#Load model NER masing-masing entitas
nlp1 = spacy.load('Person')
nlp2 = spacy.load('Position')
nlp3 = spacy.load('Organization')
nlp4 = spacy.load('Location')
nlp5 = spacy.load('Indicator')
nlp6 = spacy.load('Quote')
for row in berita:
id = row[0]
sumber = row[1]
tanggal = row[6]
judul = row[4]
konten = row[7]
konten = re.sub(r"[#/?]", "", konten)
doc1 = nlp1(konten)
doc2 = nlp2(konten)
doc3 = nlp3(konten)
doc4 = nlp4(konten)
doc5 = nlp5(konten)
doc6 = nlp6(konten)
# mengambil teks hasil prediksi dari label
person_temp = [(e.text) for e in doc1.ents if e.label_ == 'person']
position_temp = [(e.text) for e in doc2.ents if e.label_ == 'position']
organization_temp = [(e.text) for e in doc3.ents
if e.label_ == 'organization']
location_temp = [(e.text) for e in doc4.ents if e.label_ == 'location']
indicator_temp = [(e.text) for e in doc5.ents
if e.label_ == 'indicator']
quote_temp = [(e.text) for e in doc6.ents if e.label_ == 'quote']
# memasukkkan hasil prediksi kedalam list
person = []
position = []
organization = []
location = []
indicator = []
quote = []
for row in person_temp:
if row not in person:
person.append(row)
for row in position_temp:
if row not in position:
position.append(row)
for row in organization_temp:
if row not in organization:
organization.append(row)
for row in location_temp:
if row not in location:
location.append(row)
for row in indicator_temp:
if row not in indicator:
indicator.append(row)
for row in quote_temp:
if row not in quote:
quote.append(row)
# konversi list to str
person = str(person)
position = str(position)
organization = str(organization)
location = str(location)
indicator = str(indicator)
quote = str(quote)
# insert ke DB
mydb, mycursor = connectDB()
sql_insert_query = """INSERT INTO `temp_output`(`id`, `sumber`, `tanggal`, `judul_berita`, `konten_berita`,
`nama_tokoh`, `jabatan`, `organisasi`, `lokasi`, `alias`, `kutipan`)
VALUES (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)"""
insert_tuple = (id, sumber, tanggal, judul, konten, person, position,
organization, location, indicator, quote)
mycursor.execute(sql_insert_query, insert_tuple)
# menghapus DB sementara
mycursor.execute("DELETE FROM `temp` WHERE 1")
mydb.commit()
# memanggil fungsi lainnya
print("Mengklasifikasi alias ke indikator BPS")
klasifikasi_indikator() # mengklasifikasikan alias ke indikator BPS
print("Mengelompokkan Sentimen")
sentiment() # mengelompokkan sentimen berita dan sentimen kutipan
mycursor.execute("INSERT INTO `output` SELECT * FROM `temp_output`")
mycursor.execute("DELETE FROM `temp_output` WHERE 1")
mydb.commit()
print("Menghitung ulang sub_indikator")
hitung_sub_indikator(
) # menghitung value dari setiap sub indikator BPS untuk zoomable sunburst
zoomable_sunburst() # membangun data JSON untuk zoomable sunburst
donutchart() # membangun data csv untuk donut chart
def sentiment():
mydb, mycursor = connectDB()
# config
config = dict()
config["negation"] = True
config["booster"] = True
config["ungkapan"] = True
config["consecutive"] = True
config["repeated"] = True
config["emoticon"] = True
config["question"] = True
config["exclamation"] = True
config["punctuation"] = True
senti = sentistrength(config)
senti_negasi = sentistrength_negasi(config)
skor = 0
# select konten berita hasil prediksi dari database
sql_berita = """SELECT `id`, `konten_berita`, `kutipan`, `sub_indikator` FROM `temp_output`"""
mycursor.execute(sql_berita)
berita = mycursor.fetchall()
for row in tqdm(berita):
id = row[0]
konten = row[1]
kutipan = row[2]
indikator = row[3]
# mendefinisikan indikator negasi
negasi = [
line.replace('\n', '')
for line in open("indikator_negasi.txt").read().splitlines()
]
x = ast.literal_eval(indikator)
x = [n.strip() for n in x]
if len(x) > 0:
for r in x:
# jika indikator merupakan indikator negasi
if r in negasi:
senti_konten = senti_negasi.main(konten)
sk = senti_konten['kelas']
senti_quote = senti_negasi.main(kutipan)
sq = senti_quote['kelas']
# input to db
set_var('sentimen_berita', sk, id)
set_var('sentimen_kutipan', sq, id)
# jika bukan indikator negasi
else:
senti_konten = senti.main(konten)
sk = senti_konten['kelas']
senti_quote = senti.main(kutipan)
sq = senti_quote['kelas']
# input to db
set_var('sentimen_berita', sk, id)
set_var('sentimen_kutipan', sq, id)
else:
senti_konten = senti.main(konten)
sk = senti_konten['kelas']
senti_quote = senti.main(kutipan)
sq = senti_quote['kelas']
# input to db
set_var('sentimen_berita', sk, id)
set_var('sentimen_kutipan', sq, id)
def indikator_json(indikator, tahun):
mydb, mycursor = connectDB()
nodes_list = []
links_list = []
# triwulan I 2018
mycursor.execute("""SELECT `nama_tokoh` FROM `output` WHERE `indikator` LIKE '%""" + indikator + """%'
AND `tanggal` BETWEEN '""" + tahun + """/1/1' AND '""" + tahun + """/3/31' ORDER BY `tanggal` ASC""")
records = mycursor.fetchall()
for row in records:
nama = row[0]
x = ast.literal_eval(nama)
x = [n.strip() for n in x]
for r in x:
nodes = OrderedDict()
nodes['id'] = r
nodes['group'] = 1
if nodes not in nodes_list:
nodes_list.append(nodes)
for row1 in records:
tokoh_list = []
tokoh = row1[0]
x = ast.literal_eval(tokoh)
x = [n.strip() for n in x]
for t in x:
if t not in tokoh_list:
tokoh_list.append(t)
if len(tokoh_list) > 1:
comb = combinations(tokoh_list, 2)
for i in list(comb):
links = OrderedDict()
links['source'] = i[0]
links['target'] = i[1]
links['value'] = 1
links_list.append(links)
# triwulan II 2018
mycursor.execute("""SELECT `nama_tokoh` FROM `output` WHERE `indikator` LIKE '%""" + indikator + """%'
AND `tanggal` BETWEEN '""" + tahun + """/4/1' AND '""" + tahun + """/6/30' ORDER BY `tanggal` ASC""")
records1 = mycursor.fetchall()
for row in records1:
nama = row[0]
x = ast.literal_eval(nama)
x = [n.strip() for n in x]
for r in x:
nodes = OrderedDict()
nodes['id'] = r
nodes['group'] = 2
if nodes not in nodes_list:
nodes_list.append(nodes)
for row1 in records1:
tokoh_list = []
tokoh = row1[0]
x = ast.literal_eval(tokoh)
x = [n.strip() for n in x]
for t in x:
if t not in tokoh_list:
tokoh_list.append(t)
if len(tokoh_list) > 1:
comb = combinations(tokoh_list, 2)
for i in list(comb):
links = OrderedDict()
links['source'] = i[0]
links['target'] = i[1]
links['value'] = 1
links_list.append(links)
# triwulan III 2018
mycursor.execute("""SELECT `nama_tokoh` FROM `output` WHERE `indikator` LIKE '%""" + indikator + """%'
AND `tanggal` BETWEEN '""" + tahun + """/7/1' AND '""" + tahun + """/9/30' ORDER BY `tanggal` ASC""")
records1 = mycursor.fetchall()
for row in records1:
nama = row[0]
x = ast.literal_eval(nama)
x = [n.strip() for n in x]
for r in x:
nodes = OrderedDict()
nodes['id'] = r
nodes['group'] = 3
if nodes not in nodes_list:
nodes_list.append(nodes)
for row1 in records1:
tokoh_list = []
tokoh = row1[0]
x = ast.literal_eval(tokoh)
x = [n.strip() for n in x]
for t in x:
if t not in tokoh_list:
tokoh_list.append(t)
if len(tokoh_list) > 1:
comb = combinations(tokoh_list, 2)
for i in list(comb):
links = OrderedDict()
links['source'] = i[0]
links['target'] = i[1]
links['value'] = 1
links_list.append(links)
# triwulan IV 2018
mycursor.execute("""SELECT `nama_tokoh` FROM `output` WHERE `indikator` LIKE '%""" + indikator + """%'
AND `tanggal` BETWEEN '""" + tahun + """/10/1' AND '""" + tahun + """/12/31' ORDER BY `tanggal` ASC""")
records1 = mycursor.fetchall()
for row in records1:
nama = row[0]
x = ast.literal_eval(nama)
x = [n.strip() for n in x]
for r in x:
nodes = OrderedDict()
nodes['id'] = r
nodes['group'] = 4
if nodes not in nodes_list:
nodes_list.append(nodes)
for row1 in records1:
tokoh_list = []
tokoh = row1[0]
x = ast.literal_eval(tokoh)
x = [n.strip() for n in x]
for t in x:
if t not in tokoh_list:
tokoh_list.append(t)
if len(tokoh_list) > 1:
comb = combinations(tokoh_list, 2)
for i in list(comb):
links = OrderedDict()
links['source'] = i[0]
links['target'] = i[1]
links['value'] = 1
links_list.append(links)
# List menggabungkan dictionaries
graph_list = []
graph = OrderedDict()
graph['nodes'] = nodes_list
graph['links'] = links_list
graph_list.append(graph)
# Mengubah list dict ke JSON
j = js.dumps(graph_list[0])
# Menyimpan file ke format .json
with open("C:/xampp/htdocs/SKRIPSI/assets/json/graph_sna.json", 'w') as f:
f.write(j)
return j, 200, {'Access-Control-Allow-Origin': 'http://localhost',
'Access-Control-Allow-Methods': '*'}