Project Based Learning

PBL Database Advanced & Big Data

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 import_csv.py

berikut kode untuk impor csv :

import csv
from pymongo import MongoClient

client = MongoClient('mongodb://localhost:27017/')
db = client['netflix']
koleksi = db['user']

# Impor data dari file CSV ke MongoDB
path_csv = 'netflix.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 :

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['netflix']
koleksi = db['user']

# 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 = ['user_id', 'subscription_type', 'monthly_revenue', 'join_date', 'last_payment_date', 'country', 'age', 'gender', 'device', 'plan_duration']

    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)

# 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': [
                    {'user_id': {'$regex': query, '$options': 'i'}},
                    {'subscription_type': {'$regex': query, '$options': 'i'}},
                    {'monthly_revenue': {'$regex': query, '$options': 'i'}},
                    {'join_date': {'$regex': query, '$options': 'i'}},
                    {'last_payment_date': {'$regex': query, '$options': 'i'}},
                    {'country': {'$regex': query, '$options': 'i'}},
                    {'age': {'$regex': query, '$options': 'i'}},
                    {'gender': {'$regex': query, '$options': 'i'}},
                    {'device': {'$regex': query, '$options': 'i'}},
                    {'plan_duration': {'$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)
    
@app.route('/graph')
def show_graph():

          
            subscription_type_counts = koleksi.aggregate([
                {"$group": {"_id": "$subscription_type", "count": {"$sum": 1}}}
            ])

            
            monthly_revenue_counts = koleksi.aggregate([
                {"$group": {"_id": "$monthly_revenue", "count": {"$sum": 1}}}
            ])

       
            country_counts = koleksi.aggregate([
                {"$group": {"_id": "$country", "count": {"$sum": 1}}}
            ])

            age_counts = koleksi.aggregate([
                {"$group": {"_id": "$age", "count": {"$sum": 1}}}
            ])

            gender_counts = koleksi.aggregate([
                {"$group": {"_id": "$gender", "count": {"$sum": 1}}}
            ])

           
            device_counts = koleksi.aggregate([
                {"$group": {"_id": "$device", "count": {"$sum": 1}}}
            ])

            plan_duration_counts = koleksi.aggregate([
                {"$group": {"_id": "$plan_duration", "count": {"$sum": 1}}}
            ])

            # Membuat dictionary untuk variabel subscription_type, year_founded, dan country
            subscription_type = {item['_id']: item['count'] for item in subscription_type_counts}
            monthly_revenue = {item['_id']: item['count'] for item in monthly_revenue_counts}
            country = {item['_id']: item['count'] for item in country_counts}
            age = {item['_id']: item['count'] for item in age_counts}
            gender = {item['_id']: item['count'] for item in gender_counts}
            device = {item['_id']: item['count'] for item in device_counts}
            plan_duration = {item['_id']: item['count'] for item in plan_duration_counts}

            # Membuat grafik batang untuk industri
            fig_subscription_type = go.Figure()
            fig_subscription_type.add_trace(go.Bar(x=list(subscription_type.keys()), y=list(subscription_type.values()), marker=dict(color='steelblue', opacity=0.7)))
            fig_subscription_type.update_layout(title='Subcription Type', xaxis_title='Subscription Type', yaxis_title='Jumlah')

            # Membuat grafik batang untuk monthly_revenue
            fig_monthly_revenue = go.Figure()
            fig_monthly_revenue.add_trace(go.Bar(x=list(monthly_revenue.keys()), y=list(monthly_revenue.values()), marker=dict(color='coral', opacity=0.7)))
            fig_monthly_revenue.update_layout(title='Pendapatan Bulanan', xaxis_title='monthly revenue', yaxis_title='Jumlah')

            # Membuat grafik batang untuk country
            fig_country = go.Figure()
            fig_country.add_trace(go.Bar(x=list(country.keys()), y=list(country.values()), marker=dict(color='darkseagreen', opacity=0.7)))
            fig_country.update_layout(title='Negara Pengguna Netflix', xaxis_title='country', yaxis_title='Jumlah')
            
            fig_age = go.Figure()
            fig_age.add_trace(go.Bar(x=list(age.keys()), y=list(age.values()), marker=dict(color='peachpuff', opacity=0.7)))
            fig_age.update_layout(title='Range Umur Pengguna Netflix', xaxis_title='age', yaxis_title='Jumlah')

            fig_gender = go.Figure()
            fig_gender.add_trace(go.Bar(x=list(gender.keys()), y=list(gender.values()), marker=dict(color='lightblue', opacity=0.7)))
            fig_gender.update_layout(title='Gender', xaxis_title='Gender', yaxis_title='Jumlah')

            # Membuat grafik batang untuk device
            fig_device = go.Figure()
            fig_device.add_trace(go.Bar(x=list(device.keys()), y=list(device.values()), marker=dict(color='lightgreen', opacity=0.7)))
            fig_device.update_layout(title='Device yang digunakan', xaxis_title='Device', yaxis_title='Jumlah')
            
            fig_plan_duration = go.Figure()
            fig_plan_duration.add_trace(go.Bar(x=list(plan_duration.keys()), y=list(plan_duration.values()), marker=dict(color='salmon', opacity=0.7)))
            fig_plan_duration.update_layout(title='Plan Duration', xaxis_title='Plan Duration', yaxis_title='Jumlah')

            # Konversi grafik ke HTML
            graph_html_subscription_type = fig_subscription_type.to_html(full_html=False)
            graph_html_monthly_revenue = fig_monthly_revenue.to_html(full_html=False)
            graph_html_country = fig_country.to_html(full_html=False)
            graph_html_age = fig_age.to_html(full_html=False)
            graph_html_gender = fig_gender.to_html(full_html=False)
            graph_html_device = fig_device.to_html(full_html=False)
            graph_html_plan_duration = fig_plan_duration.to_html(full_html=False)

            return render_template('graph.html', graph_html_subscription_type=graph_html_subscription_type, graph_html_monthly_revenue=graph_html_monthly_revenue, graph_html_country=graph_html_country, graph_html_age=graph_html_age, graph_html_gender=graph_html_gender, graph_html_device=graph_html_device, graph_html_plan_duration=graph_html_plan_duration)

# Rute untuk menambah data baru
@app.route('/add', methods=['POST'])
def add():
    new_data = {
        'user_id': request.form['user_id'],
        'subscription_type': request.form['subscription_type'],
        'monthly_revenue': request.form['monthly_revenue'],
        'join_date': request.form['join_date'],
        'last_payment_date': request.form['last_payment_date'],
        'country': request.form['country'],
        'age': request.form['age'],
        'gender': request.form['gender'],
        'device': request.form['device'],
        'plan_duration': request.form['plan_duration'],
        'company_size': request.form['company_size']
    }
    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 = {
        'user_id': request.form['user_id'],
        'subscription_type': request.form['subscription_type'],
        'monthly_revenue': request.form['monthly_revenue'],
        'join_date': request.form['join_date'],
        'last_payment_date': request.form['last_payment_date'],
        'country': request.form['country'],
        'age': request.form['age'],
        'gender': request.form['gender'],
        'device': request.form['device'],
        'plan_duration': request.form['plan_duration'],
        'company_size': request.form['company_size']
    }
    koleksi.update_one({'_id': ObjectId(id)}, {'$set': updated_data})
    return redirect('/')

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

3. Untuk menampilkan grafik, buat Buatlah file baru 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>Netflix User</title>
    <style>
      table {
        border-collapse: collapse;
        width: 100%;
      }

      th,
      td {
        border: 1px solid black;
        padding: 8px;
        text-align: left;
        max-width: 200px; /* Atur lebar maksimum untuk kolom */
        overflow: hidden;
        white-space: nowrap;
        text-overflow: ellipsis;
        margin: 4px; /* Menambahkan jarak pada setiap sisi */
      }

      th:nth-child(1),
      td:nth-child(1) {
        width: 5%; /* Atur lebar kolom user_id di sini */
      }

      th:nth-child(2),
      td:nth-child(2) {
        width: 8%; /* Atur lebar kolom subscription_type di sini */
      }

      th:nth-child(3),
      td:nth-child(3) {
        width: 8%; /* Atur lebar kolom monthly_revenue di sini */
      }

      th:nth-child(4),
      td:nth-child(4) {
        width: 8%; /* Atur lebar kolom join_date di sini */
      }

      th:nth-child(5),
      td:nth-child(5) {
        width: 8%; /* Atur lebar kolom last_payment_date di sini */
      }

      th:nth-child(6),
      td:nth-child(6) {
        width: 8%; /* Atur lebar kolom country di sini */
      }

      th:nth-child(7),
      td:nth-child(7) {
        width: 5%; /* Atur lebar kolom age di sini */
      }

      th:nth-child(8),
      td:nth-child(8) {
        width: 8%; /* Atur lebar kolom gender di sini */
      }

      th:nth-child(9),
      td:nth-child(9) {
        width: 8%; /* Atur lebar kolom device di sini */
      }

      th:nth-child(10),
      td:nth-child(10) {
        width: 12%; /* Atur lebar kolom plan_duration di sini */
      }

      /* Tambahkan margin-bottom untuk memberikan ruang antara form dan tabel */
      form {
        margin-bottom: 20px;
      }
    </style>
  </head>
  <body>
    <h1>Netflix User</h1>
    <p>Jumlah data: {{ data|length }}</p>
    <button
      type="button"
      onclick="window.location.href='/graph'"
      style="width: 150px; height: 25px; margin-top: 10px; margin-bottom: 10px"
    >
      Tampilkan Grafik
    </button>
    <form action="/" method="POST">
      <div>
        <label for="search">Cari berdasarkan kategori:</label>
        <input type="text" id="search" name="query" />
        <button type="submit"
        style="width: 150px; height: 25px; border: 2px solid #000000; margin-top: 15px">Cari</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="user_id">User ID:</label>
        <input type="text" id="user_id" name="user_id" /></div>

      <div><label for="subscription_type">Subscription Type:</label>
        <input type="text" id="subscription_type" name="subscription_type" /></div>

      <div><label for="monthly_revenue">Monthly Revenue:</label>
        <input type="text" id="monthly_revenue" name="monthly_revenue" /></div>

      <div><label for="join_date">Join Date:</label>
        <input type="text" id="join_date" name="join_date" /></div>

      <div><label for="last_payment_date">Last Payment Date:</label>
        <input type="text" id="last_payment_date" name="last_payment_date" /></div>

      <div><label for="country">Country:</label>
        <input type="text" id="country" name="country" /></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="device">Device:</label>
        <input type="text" id="device" name="device" /></div>

      <div><label for="plan_duration">Plan Duration:</label>
        <input type="text" id="plan_duration" name="plan_duration" /></div>

      <div><label for="company_size">Company Size:</label>
        <input type="text" id="company_size" name="company_size" /></div>

      <button type="submit"
      style="width: 150px; height: 35px; border: 2px solid #000000; margin-top: 20px">Tambah Data</button>
    </form>

    <table>
      <thead>
        <tr>
          <th>User ID</th>
          <th>Subscription Type</th>
          <th>Monthly Revenue</th>
          <th>Join Date</th>
          <th>Last Payment Date</th>
          <th>Country</th>
          <th>Age</th>
          <th>Gender</th>
          <th>Device</th>
          <th>Plan Duration</th>
          <th>Aksi</th>
        </tr>
      </thead>
      <tbody>
        {% for row in data %}
        <tr>
          <td>{{ row.user_id }}</td>
          <td>{{ row.subscription_type }}</td>
          <td>{{ row.monthly_revenue }}</td>
          <td>{{ row.join_date }}</td>
          <td>{{ row.last_payment_date }}</td>
          <td>{{ row.country }}</td>
          <td>{{ row.age }}</td>
          <td>{{ row.gender }}</td>
          <td>{{ row.device }}</td>
          <td>{{ row.plan_duration }}</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 User</title>
    <style>
      /* Atur gaya sesuai kebutuhan Anda */
      form {
        display: flex;
        flex-direction: column;
        max-width: 400px; /* 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="user_id">User ID:</label>
      <input
        type="text"
        id="user_id"
        name="user_id"
        value="{{ data.user_id }}"
      />
      <!-- Isi formulir dengan data yang ada untuk diedit -->
      <label for="subscription_type">Subscription Type:</label>
      <input
        type="text"
        id="subscription_type"
        name="subscription_type"
        value="{{ data.subscription_type }}"
      />
      <label for="monthly_revenue">Monthly Revenue:</label>
      <input
        type="text"
        id="monthly_revenue"
        name="monthly_revenue"
        value="{{ data.monthly_revenue }}"
      />
      <label for="join_date">Join Date:</label>
      <input
        type="text"
        id="join_date"
        name="join_date"
        value="{{ data.join_date }}"
      />
      <label for="last_payment_date">Last Payment Date:</label>
      <input type="text" 
      id="last_payment_date" 
      name="last_payment_date" 
      value="{{ data.last_payment_date }}" 
      />
      <label for="country">Country:</label>
      <input
        type="text"
        id="country"
        name="country"
        value="{{ data.country }}"
      />
      <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 }}"
      />
      <label for="device">Device:</label>
      <input
        type="text"
        id="device"
        name="device"
        value="{{ data.device }}"
      />
      <label for="plan_duration">Plan Duration:</label>
      <input
        type="text"
        id="plan_duration"
        name="plan_duration"
        value="{{ data.plan_duration }}"
      />
      <button type="submit">Update Data</button>
    </form>
  </body>
</html>

Kode file graph.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>Grafik Netflix User</title>
    <!-- Sertakan dependensi Plotly dan CSS lainnya jika diperlukan -->
    <!-- Plotly CDN -->
    <script src="https://cdn.plot.ly/plotly-latest.min.js"></script>
    <style>
      /* Gaya CSS untuk kontainer grafik */
      .graph-container {
        margin: 20px; /* Atur margin sesuai kebutuhan Anda */
      }

      /* Gaya CSS untuk warna batang pada grafik subscription_type */
      .bar-subscription_type {
        fill: steelblue; /* Warna batang */
        opacity: 1; /* Tingkat transparansi */
      }

      /* Gaya CSS untuk warna batang pada grafik monthly_revenue */
      .bar-monthly_revenue {
        fill: coral; /* Warna batang */
        opacity: 1; /* Tingkat transparansi */
      }

      /* Gaya CSS untuk warna batang pada grafik country*/
      .bar-country {
        fill: darkseagreen; /* Warna batang */
        opacity: 1; /* Tingkat transparansi */
      }

      /* Gaya CSS untuk warna batang pada grafik age*/
      .bar-age {
        fill: peachpuff; /* Warna batang */
        opacity: 1; /* Tingkat transparansi */
      }

      .bar-gender {
        fill: lightblue; /* Warna batang */
        opacity: 1; /* Tingkat transparansi */
      }

      .bar-device {
        fill: lightgreen; /* Warna batang */
        opacity: 1; /* Tingkat transparansi */
      }

      .bar-plan_duration {
        fill: salmon; /* Warna batang */
        opacity: 1; /* Tingkat transparansi */
      }

    </style>
  </head>
  <body>
    <!-- Grafik subscription_type -->
    <div class="graph-container bar-subscription_type">
      {{ graph_html_subscription_type|safe }}
    </div>

    <!-- Grafik monthly_revenue-->
    <div class="graph-container bar-monthly_revenue">{{ graph_html_monthly_revenue|safe }}</div>

    <!-- Grafik country -->
    <div class="graph-container bar-country">
      {{ graph_html_country|safe }}</div>

      <!-- Grafik age -->
    <div class="graph-container bar-age">
        {{ graph_html_age|safe }}
    </div>

    <div class="graph-container bar-gender">
        {{ graph_html_gender|safe }}
    </div>
    <div class="graph-container bar-device">
        {{ graph_html_device|safe }}
    </div>
    <div class="graph-container bar-plan_duration">
        {{ graph_html_plan_duration|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 dikil 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 inginj melihat netflix user dengan subscription type premium, maka ketik kata premium pada pencarian :