UJIAN AKHIR SEMESTER 5
BISNIS DIGITAL
DATABASE ADVANCED & BIG DATA

Selamat datang para pembaca setia blog ini! Kali ini, kita akan memasuki dunia menarik pengaplikasian database NoSQL, khususnya MongoDB, dengan menggunakan alat yang sangat bermanfaat, yaitu Jupyter Notebook.

Apa itu MongoDB?

MongoDB merupakan salah satu jenis database NoSQL yang berbasis dokumen. Berbeda dengan database relasional tradisional, MongoDB menggunakan format BSON (Binary JSON) untuk menyimpan data. Keunggulan MongoDB terletak pada kemampuannya menangani data semi-struktural atau tidak terstruktur dengan lebih efisien.

Mengapa MongoDB?

Pertanyaan ini wajar muncul. MongoDB menjadi pilihan populer karena kemudahannya dalam menyimpan data yang beragam, skalabilitas horizontal yang baik, dan fleksibilitas dalam memodelkan data. Penerapan MongoDB seringkali memberikan solusi yang optimal untuk aplikasi yang membutuhkan penanganan data yang cepat dan terdistribusi.

Penerapan

Sebelum masuk ke materi prakter, tentunya kita perlu data untuk diolah, data dapat di cari pada laman web kaggle.com.

Visualisasi & Filter Data, serta fungsi CRUD data.

Langkah - Langkah :

1. Import file datasheet yang akan dipakai ke MongoDB menggunakkan VS Code. pastikan file sudah csv dan nama sesuai dengan nama file. buat file pada VS Code dengan nama film.cvs

berikut kode untuk impor csv :

import csv
from pymongo import MongoClient

client = MongoClient('mongodb://localhost:27017/')
db = client['pbl']
koleksi = db['OCD']

# Impor data dari file CSV ke MongoDB
path_csv = 'ocd_patient_dataset1.csv'

with open(path_csv, 'r') as csvfile:
    csv_reader = csv.DictReader(csvfile)
    for row in csv_reader:
        koleksi.insert_one(row)
    print("Data dari CSV berhasil diimpor ke MongoDB.")

2. Untuk menampilkan data dan fungsi CRUD, buat file app.py lalu isi dengan kode seperti dibawah ini :

# import csv
from flask import Flask, render_template, request, redirect
from pymongo import MongoClient
from bson import ObjectId
import plotly.graph_objects as go

app = Flask(__name__)
client = MongoClient('mongodb://localhost:27017')
db = client['pbl']
koleksi = db['OCD']

# Membaca file CSV dan memasukkan data ke MongoDB jika belum ada
# path_csv = 'ds_salaries.csv'

# with open(path_csv, 'r') as csvfile:
    # csv_reader = csv.DictReader(csvfile)
    # for row in csv_reader:
        # koleksi.insert_one(row)

# Rute untuk menampilkan data dari koleksi MongoDB
# @app.route('/')
def index():
    data_cursor = koleksi.find()
    formatted_data = []
    header = ['id', 'age', 'gender', 'ethnicity', 'marital_status', 'education_level', 'ocd_diagnosis_date', 'duration_of_symptoms', 'previous_diagnoses', 'family_history_of_ocd', 'obsession_type', 'compulsion_type', 'ybocs_score_obsessions', 'ybocs_score_compulsions', 'depression_diagnosis', 'anxiety_diagnosis', 'medications']

    for row in data_cursor:
        formatted_row = {key: row[key] for key in header}
        formatted_data.append(formatted_row)

    return render_template('index.html', data=formatted_data)

@app.route('/graph')
def charts():
    # Ambil data dari MongoDB
    data_cursor = koleksi.find()
    data_list = list(data_cursor)

    # Siapkan data untuk grafik lingkaran
    ybocs_scores = [float(data['ybocs_score_obsessions']) for data in data_list]

    # Ambil 10 kategori pertama untuk grafik lingkaran (gunakan seluruh data jika kurang dari 10)
    top_10_scores = sorted(data_list, key=lambda x: float(x['ybocs_score_obsessions']), reverse=True)[:10]
    scores = [float(data['ybocs_score_obsessions']) for data in top_10_scores]
    labels = [data['id'] for data in top_10_scores]

    # Buat grafik lingkaran untuk menampilkan distribusi skor Y-BOCS
    fig_pie = go.Figure(data=[go.Pie(labels=labels, values=scores)])
    fig_pie.update_layout(title="Top 10 Y-BOCS Scores")

    # Grafik batang untuk menampilkan rata-rata skor Y-BOCS per tingkat pendidikan (education level)
    education_levels = [data['education_level'] for data in data_list if 'education_level' in data]
    unique_education_levels = list(set(education_levels))
    avg_scores_education = [sum([float(data['ybocs_score_obsessions']) for data in data_list if 'education_level' in data and data['education_level'] == level])
                            / len([float(data['ybocs_score_obsessions']) for data in data_list if 'education_level' in data and data['education_level'] == level])
                            for level in unique_education_levels]

    fig_bar = go.Figure(data=go.Bar(x=unique_education_levels, y=avg_scores_education))
    fig_bar.update_layout(title="Average Y-BOCS Score per Education Level")

    # Grafik garis untuk menampilkan perbandingan skor Y-BOCS berdasarkan status pernikahan (marital status)
    marital_statuses = [data['marital_status'] for data in data_list if 'marital_status' in data]
    unique_marital_statuses = list(set(marital_statuses))
    avg_scores_marital = [sum([float(data['ybocs_score_obsessions']) for data in data_list if 'marital_status' in data and data['marital_status'] == status])
                          / len([float(data['ybocs_score_obsessions']) for data in data_list if 'marital_status' in data and data['marital_status'] == status])
                          for status in unique_marital_statuses]

    fig_line = go.Figure(data=go.Scatter(x=unique_marital_statuses, y=avg_scores_marital, mode='lines+markers'))
    fig_line.update_layout(title="Average Y-BOCS Score per Marital Status")

    # Konversi setiap figure ke dalam representasi HTML agar bisa dimasukkan ke dalam template
    graph_html_pie = fig_pie.to_html(full_html=False)
    graph_html_bar = fig_bar.to_html(full_html=False)
    graph_html_line = fig_line.to_html(full_html=False)

    return render_template('graph.html', graph_pie=graph_html_pie, graph_bar=graph_html_bar, graph_line=graph_html_line)



# Rute lainnya untuk tambah, edit, dan hapus data

# Rute untuk menampilkan data dan melakukan pencarian
@app.route('/', methods=['GET', 'POST'])
def index():
    if request.method == 'POST':
        query = request.form.get('query')
        if query:
            data_cursor = koleksi.find({
                '$or': [
                    {'id': {'$regex': query, '$options': 'i'}},
                    {'age': {'$regex': query, '$options': 'i'}},
                    {'gender': {'$regex': query, '$options': 'i'}},
                    {'ethnicity': {'$regex': query, '$options': 'i'}},
                    {'marital_status': {'$regex': query, '$options': 'i'}},
                    {'education_level': {'$regex': query, '$options': 'i'}},
                    {'ocd_diagnosis_date': {'$regex': query, '$options': 'i'}},
                    {'duration_of_symptoms': {'$regex': query, '$options': 'i'}},
                    {'previous_diagnoses': {'$regex': query, '$options': 'i'}},
                    {'family_history_of_ocd': {'$regex': query, '$options': 'i'}},
                    {'obsession_type': {'$regex': query, '$options': 'i'}},
                    {'compulsion_type': {'$regex': query, '$options': 'i'}},
                    {'ybocs_score_obsessions': {'$regex': query, '$options': 'i'}},
                    {'ybocs_score_compulsions': {'$regex': query, '$options': 'i'}},
                    {'depression_diagnosis': {'$regex': query, '$options': 'i'}},
                    {'anxiety_diagnosis': {'$regex': query, '$options': 'i'}},
                    {'medications': {'$regex': query, '$options': 'i'}}
                ]
            })
            data_list = list(data_cursor)
            return render_template('index.html', data=data_list)
        else:
            return redirect('/')
    else:
        data_cursor = koleksi.find()
        data_list = list(data_cursor)
        return render_template('index.html', data=data_list)

# Rute untuk menambah data baru
@app.route('/add', methods=['POST'])
def add():
    new_data = {
        'id': request.form['id'],
        'age': request.form['age'],
        'gender': request.form['gender'],
        'ethnicity': request.form['ethnicity'],
        'marital_status': request.form['marital_status'],
        'education_level': request.form['education_level'],
        'ocd_diagnosis_date': request.form['ocd_diagnosis_date'],
        'duration_of_symptoms': request.form['duration_of_symptoms'],
        'previous_diagnoses': request.form['previous_diagnoses'],
        'family_history_of_ocd': request.form['family_history_of_ocd'],
        'obsession_type': request.form['obsession_type'],
        'compulsion_type': request.form['compulsion_type'],
        'ybocs_score_obsessions': request.form['ybocs_score_obsessions'],
        'ybocs_score_compulsions': request.form['ybocs_score_compulsions'],
        'depression_diagnosis': request.form['depression_diagnosis'],
        'anxiety_diagnosis': request.form['anxiety_diagnosis'],
        'medications': request.form['medications']
    }
    koleksi.insert_one(new_data)
    return redirect('/')

# Rute untuk menghapus data
@app.route('/delete/<id>', methods=['GET'])
def delete(id):
    koleksi.delete_one({'_id': ObjectId(id)})
    return redirect('/')

# Rute untuk menampilkan form edit
@app.route('/edit/<id>', methods=['GET'])
def edit(id):
    data = koleksi.find_one({'_id': ObjectId(id)})
    return render_template('edit.html', data=data)

# Rute untuk menyimpan perubahan dari form edit
@app.route('/update/<id>', methods=['POST'])
def update(id):
    updated_data = {
       'id': request.form['id'],
        'age': request.form['age'],
        'gender': request.form['gender'],
        'ethnicity': request.form['ethnicity'],
        'marital_status': request.form['marital_status'],
        'education_level': request.form['education_level'],
        'ocd_diagnosis_date': request.form['ocd_diagnosis_date'],
        'duration_of_symptoms': request.form['duration_of_symptoms'],
        'previous_diagnoses': request.form['previous_diagnoses'],
        'family_history_of_ocd': request.form['family_history_of_ocd'],
        'obsession_type': request.form['obsession_type'],
        'compulsion_type': request.form['compulsion_type'],
        'ybocs_score_obsessions': request.form['ybocs_score_obsessions'],
        'ybocs_score_compulsions': request.form['ybocs_score_compulsions'],
        'depression_diagnosis': request.form['depression_diagnosis'],
        'anxiety_diagnosis': request.form['anxiety_diagnosis'],
        'medications': request.form['medications']
    }
    koleksi.update_one({'_id': ObjectId(id)}, {'$set': updated_data})
    return redirect('/')

if __name__ == '__main__':
    app.run(debug=True)

3. Untuk menampilkan grafik, buat Buatlah folder baru "templates" untuk menyimpan script html, terdapat 3 file yaitu index.html untuk mengatur tampilan halaman awal, edit.html untuk mengatur tampilan halaman edit, dan graph.html untuk mengatur tampilan halaman grafik.

Kode file index.html

<!DOCTYPE html>
<html lang="en">
  <head>
    <meta charset="UTF-8" />
    <meta http-equiv="X-UA-Compatible" content="IE=edge" />
    <meta name="viewport" content="width=device-width, initial-scale=1.0" />
    <title>Data Pasien OCD</title>
    <style>
      table {
        border-collapse: collapse;
        width: 80%;
      }

      th, td {
        border: 1px solid black;
        padding: 8px;
        text-align: left;
        max-width: 200px;
        overflow: hidden;
        white-space: nowrap;
        text-overflow: ellipsis;
        margin: 4px;
      }

      #search {
        width: 10%; /* Sesuaikan lebar input pencarian */
        padding: 5px;
        margin-top: 5px;
        margin-right: 10px; /* Tambahkan margin ke kanan */
    }

    button {
      width: 5%; /* Sesuaikan lebar tombol pencarian */
      padding: 5px;
      margin-top: 5px;
  }

      th:nth-child(1),
      td:nth-child(1) {
        width: 5%;
      }

      th:nth-child(2),
      td:nth-child(2) {
        width: 8%;
      }

      th:nth-child(3),
      td:nth-child(3) {
        width: 8%;
      }

      th:nth-child(4),
      td:nth-child(4) {
        width: 16%;
      }

      th:nth-child(5),
      td:nth-child(5) {
        width: 8%;
      }

      th:nth-child(6),
      td:nth-child(6) {
        width: 12%;
      }

      th:nth-child(7),
      td:nth-child(7) {
        width: 12%;
      }

      th:nth-child(8),
      td:nth-child(8) {
        width: 12%;
      }

      th:nth-child(9),
      td:nth-child(9) {
        width: 8%;
      }

      th:nth-child(10),
      td:nth-child(10) {
        width: 12%;
      }

      th:nth-child(11),
      td:nth-child(11) {
        width: 8%;
      }

      form {
        margin-bottom: 20px;
      }
    </style>
  </head>
  <body>
      <h1>Data Pasien OCD</h1>
      <p>Data Length: {{ data|length }}</p>
      <form action="/" method="POST">
        <!-- <label for="search">Cari:</label> -->
        <input type="text" id="search" name="query" />
        <button type="submit">Cari</button>
      </form>
  
      <button
          type="button"
          onclick="window.location.href='/graph'"
          style="width: 150px; height: 25px; margin-top: 10px; margin-bottom: 10px"
      >
      Tampilkan Grafik
    </button>
      </div>
    </form>
    <style>
      form input {
        border: 1px solid #000000; /* Ganti dengan warna yang diinginkan */
        border-radius: 2px;
        padding: 8px;
        margin-top: 5px;
        width: 100%;
      }
      form {
        display: flex;
        flex-wrap: wrap;
      }

      div {
        flex: 0 0 20%; /* Setiap div akan menempati 30% dari lebar container */
        margin-right: 5%; /* Menambahkan jarak antara setiap div */
        margin-bottom: 15px; /* Menambahkan jarak antar baris */
      }
    </style>
   
    <form action="/add" method="POST">
      <div><label for="id">ID:</label>
          <input type="text" id="id" name="id" /></div>
  
      <div><label for="age">Age:</label>
          <input type="text" id="age" name="age" /></div>
  
      <div><label for="gender">Gender:</label>
          <input type="text" id="gender" name="gender" /></div>
  
      <div><label for="ethnicity">Ethnicity:</label>
          <input type="text" id="ethnicity" name="ethnicity" /></div>
  
      <div><label for="marital_status">Marital Status:</label>
          <input type="text" id="marital_status" name="marital_status" /></div>
  
      <div><label for="education_level">Education Level:</label>
          <input type="text" id="education_level" name="education_level" /></div>
  
      <div><label for="ocd_diagnosis_date">OCD Diagnosis Date:</label>
          <input type="text" id="ocd_diagnosis_date" name="ocd_diagnosis_date" /></div>
  
      <div><label for="duration_of_symptoms">Duration of Symptoms (months):</label>
          <input type="text" id="duration_of_symptoms" name="duration_of_symptoms" /></div>
  
      <div><label for="previous_diagnoses">Previous Diagnoses:</label>
          <input type="text" id="previous_diagnoses" name="previous_diagnoses" /></div>
  
      <div><label for="family_history_of_ocd">Family History of OCD:</label>
          <input type="text" id="family_history_of_ocd" name="family_history_of_ocd" /></div>
  
      <div><label for="obsession_type">Obsession Type:</label>
          <input type="text" id="obsession_type" name="obsession_type" /></div>
  
      <div><label for="compulsion_type">Compulsion Type:</label>
          <input type="text" id="compulsion_type" name="compulsion_type" /></div>
  
      <div><label for="ybocs_score_obsessions">Y-BOCS Score (Obsessions):</label>
          <input type="text" id="ybocs_score_obsessions" name="ybocs_score_obsessions" /></div>
  
      <div><label for="ybocs_score_compulsions">Y-BOCS Score (Compulsions):</label>
          <input type="text" id="ybocs_score_compulsions" name="ybocs_score_compulsions" /></div>
  
      <div><label for="depression_diagnosis">Depression Diagnosis:</label>
          <input type="text" id="depression_diagnosis" name="depression_diagnosis" /></div>
  
      <div><label for="anxiety_diagnosis">Anxiety Diagnosis:</label>
          <input type="text" id="anxiety_diagnosis" name="anxiety_diagnosis" /></div>
  
      <div><label for="medications">Medications:</label>
          <input type="text" id="medications" name="medications" /></div>
  
      <button type="submit" style="width: 150px; height: 35px; border: 2px solid #000000; margin-top: 20px">Tambah Data</button>
  </form>
  

    <table>
      <thead>
        <tr>
          <th>ID</th>
          <th>Age</th>
          <th>Gender</th>
          <th>Ethnicity</th>
          <th>Marital Status</th>
          <th>Education Level</th>
          <th>OCD Diagnosis Date</th>
          <th>Duration of Symptoms (months)</th>
          <th>Previous Diagnoses</th>
          <th>Family History of OCD</th>
          <th>Obsession Type</th>
          <th>Compulsion Type</th>
          <th>Y-BOCS Score (Obsessions)</th>
          <th>Y-BOCS Score (Compulsions)</th>
          <th>Depression Diagnosis</th>
          <th>Anxiety Diagnosis</th>
          <th>Medications</th>
          <th>Aksi</th>
        </tr>
      </thead>
      <tbody>
        {% for row in data %}
          <tr>
            <td>{{ row.id }}</td>
            <td>{{ row.age }}</td>
            <td>{{ row.gender }}</td>
            <td>{{ row.ethnicity }}</td>
            <td>{{ row.marital_status }}</td>
            <td>{{ row.education_level }}</td>
            <td>{{ row.ocd_diagnosis_date }}</td>
            <td>{{ row.duration_of_symptoms }}</td>
            <td>{{ row.previous_diagnoses }}</td>
            <td>{{ row.family_history_of_ocd }}</td>
            <td>{{ row.obsession_type }}</td>
            <td>{{ row.compulsion_type }}</td>
            <td>{{ row.ybocs_score_obsessions }}</td>
            <td>{{ row.ybocs_score_compulsions }}</td>
            <td>{{ row.depression_diagnosis }}</td>
            <td>{{ row.anxiety_diagnosis }}</td>
            <td>{{ row.medications }}</td>
            <td>
              <form action="/edit/{{ row._id }}" method="GET">
                <button type="submit">Edit Data</button>
              </form>
              <form action="/delete/{{ row._id }}" method="GET">
                <button type="submit">Hapus</button>
              </form>
            </td>
          </tr>
        {% endfor %}
      </tbody>
    </table>
  </body>
</html>

Kode file edit.html

<!DOCTYPE html>
<html lang="en">
  <head>
    <meta charset="UTF-8" />
    <meta http-equiv="X-UA-Compatible" content="IE=edge" />
    <meta name="viewport" content="width=device-width, initial-scale=1.0" />
    <title>Edit Data</title>
    <style>
      /* Atur gaya sesuai kebutuhan Anda */
      body {
        text-align: center; /* Center text horizontally */
        }
      form {
        display: flex;
        flex-direction: column;
        max-width: 300px; /* Sesuaikan lebar form jika diperlukan */
        margin: auto; /* Pusatkan form di tengah halaman */
      }

      label {
        margin-bottom: 8px;
      }

      input {
        margin-bottom: 16px;
        padding: 8px;
        border: 1px solid #000000;
        border-radius: 6px;
      }

      button {
        padding: 8px;
        border: 2px solid #000000;
        border-radius: 6px;
        /* background-color: #2197db; */
      }
    </style>
  </head>
  <body>
    <h1>Edit Data</h1>
    <form action="/update/{{ data._id }}" method="POST">
      <label for="id">ID:</label>
      <input type="text" 
              id="id" 
              name="id" 
              value="{{ data.id }}" />
      <label for="age">Age:</label>
      <input type="text" 
              id="age" 
              name="age" 
              value="{{ data.age }}" />
      <label for="gender">Gender:</label>
      <input type="text" 
              id="gender" 
              name="gender" 
              value="{{ data.gender }}" />
      <!-- Tambahkan label dan input sesuai dengan kolom yang diinginkan -->
      <label for="ethnicity">Ethnicity:</label>
      <input type="text" 
              id="ethnicity" 
              name="ethnicity" 
              value="{{ data.ethnicity }}" />
      <label for="marital_status">Marital Status:</label>
      <input type="text" 
              id="marital_status" 
              name="marital_status" 
              value="{{ data.marital_status }}" />
      <label for="education_level">Education Level:</label>
      <input type="text" 
              id="education_level" 
              name="education_level" 
              value="{{ data.education_level }}" />
      <label for="ocd_diagnosis_date">OCD Diagnosis Date:</label>
      <input type="text" 
              id="ocd_diagnosis_date" 
              name="ocd_diagnosis_date" 
              value="{{ data.ocd_diagnosis_date }}" />
      <label for="duration_of_symptoms">Duration of Symptoms (months):</label>
      <input type="text" 
              id="duration_of_symptoms" 
              name="duration_of_symptoms" 
              value="{{ data.duration_of_symptoms }}" />
      <label for="previous_diagnoses">Previous Diagnoses:</label>
      <input type="text" 
              id="previous_diagnoses" 
              name="previous_diagnoses" 
              value="{{ data.previous_diagnoses }}" />      
      <label for="family_history_of_ocd">Family History of OCD:</label>
      <input type="text" 
              id="family_history_of_ocd" 
              name="family_history_of_ocd" 
              value="{{ data.family_history_of_ocd }}" />
      <label for="obsession_type">Obsession Type:</label>
      <input type="text" 
              id="obsession_type" 
              name="obsession_type" 
              value="{{ data.obsession_type }}" />   
      <label for="compulsion_type">Compulsion Type:</label>
      <input type="text" 
              id="compulsion_type" 
              name="compulsion_type" 
              value="{{ data.compulsion_type }}" />
      <label for="ybocs_score_obsessions">Y-BOCS Score (Obsessions):</label>
      <input type="text" 
              id="ybocs_score_obsessions)" 
              name="ybocs_score_obsessions" 
              value="{{ data.ybocs_score_obsessions }}" />
      <label for="ybocs_score_compulsions">Y-BOCS Score (Compulsions):</label>
      <input type="text" 
              id="ybocs_score_compulsions" 
              name="ybocs_score_compulsions"
              value="{{ data.ybocs_score_compulsions }}" />
      <label for="depression_diagnosis">Depression Diagnosis:</label>
      <input type="text" 
              id="depression_diagnosis" 
              name="depression_diagnosis" 
              value="{{ data.depression_diagnosis }}" />
      <label for="anxiety_diagnosis">Anxiety Diagnosis:</label>
      <input type="text" 
              id="anxiety_diagnosis" 
              name="anxiety_diagnosis" 
              value="{{ data.anxiety_diagnosis }}" />
      <label for="medications">Medications:</label>
      <input type="text" 
              id="medications" 
              name="medications" 
              value="{{ data.medications }}" />
      <button type="submit">Update Data</button>
    </form>
  </body>
</html>

Kode file graph.html

<!DOCTYPE html>
<html lang="en">
  <head>
    <meta charset="UTF-8" />
    <title>Grafik OCD Patient</title>
    <!-- Plotly.js -->
    <script src="https://cdn.plot.ly/plotly-latest.min.js"></script>
  </head>
  <body>
    <div id="chart1">
        <!-- Ini adalah tempat untuk grafik lingkaran -->
        {{ graph_pie|safe }}
      </div>
      <div id="chart2">
        <!-- Ini adalah tempat untuk grafik batang -->
        {{ graph_bar|safe }}
      </div>
      <div id="chart3">
        <!-- Ini adalah tempat untuk grafik garis -->
        {{ graph_line|safe }}
      </div>
      
  </body>
</html>

HASIL :

1. Tampilan Awal

Pada halaman awal akan muncul data yang sudah diimport dan jumlah datanya. Di sini juga terdapat tools untuk melakukan pencarian berdasarkan kategori tertentu, tampilkan grafik, tambah data, hapus data, dan edit data pada pojok kanan tabel (aksi).

2. Tampilan edit data

apabila tombol aksi diklik pada edit, maka akan muncul halaman edit data

3. Hasil tampilan cari berdasarkan kategori

apabila ingin melihat data tertentu saja, kita hanya perlu ketikkan sesuai dengan kategorinya, misal ingin melihat data gaji kategori , maka ketik pada pencarian :

- Data Gender "Female"