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['pbl']
koleksi = db['dataGYM']
path_csv = 'megaGymDataset.csv'
try:
with open(path_csv, 'r', newline='', encoding='utf-8') as csvfile:
csv_reader = csv.DictReader(csvfile, delimiter=';')
for row in csv_reader:
# Create a new dictionary with non-empty string keys
clean_row = {}
for key, value in row.items():
if key and value is not None and len(key.strip()) > 0:
clean_row[str(key)] = value
koleksi.insert_one(clean_row)
print("Data from CSV successfully imported into 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['dataGYM']
# 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 = ['number','title','desc','type','bodypart','equipment','level','rating','ratingdesc']
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': [
{'number': {'$regex': query, '$options': 'i'}},
{'title': {'$regex': query, '$options': 'i'}},
{'desc': {'$regex': query, '$options': 'i'}},
{'type': {'$regex': query, '$options': 'i'}},
{'bodypart': {'$regex': query, '$options': 'i'}},
{'equipment': {'$regex': query, '$options': 'i'}},
{'level': {'$regex': query, '$options': 'i'}},
{'rating': {'$regex': query, '$options': 'i'}},
{'ratingdesc': {'$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():
# Menghitung jumlah perusahaan berdasarkan type
type_counts = koleksi.aggregate([
{"$group": {"_id": "$type", "count": {"$sum": 1}}}
])
# Menghitung jumlah perusahaan berdasarkan bodypart
bodypart_counts = koleksi.aggregate([
{"$group": {"_id": "$bodypart", "count": {"$sum": 1}}}
])
equipment_counts = koleksi.aggregate([
{"$group": {"_id": "$equipment", "count": {"$sum": 1}}}
])
level_counts = koleksi.aggregate([
{"$group": {"_id": "$level", "count": {"$sum": 1}}}
])
# Menghitung jumlah perusahaan berdasarkan rating
rating_counts = koleksi.aggregate([
{"$group": {"_id": "$rating", "count": {"$sum": 1}}}
])
# Membuat dictionary untuk variabel title, year_founded, dan bodypart
type = {item['_id']: item['count'] for item in type_counts}
bodypart = {item['_id']: item['count'] for item in bodypart_counts}
equipment = {item['_id']: item['count'] for item in equipment_counts}
level = {item['_id']: item['count'] for item in level_counts}
rating = {item['_id']: item['count'] for item in rating_counts}
# Membuat grafik batang untuk type
fig_type = go.Figure()
fig_type.add_trace(go.Bar(x=list(type.keys()), y=list(type.values()), marker=dict(color='coral', opacity=0.7)))
fig_type.update_layout(title='Tipe Exercise', xaxis_title='type', yaxis_title='Jumlah')
# Membuat grafik batang untuk bodypart
fig_bodypart = go.Figure()
fig_bodypart.add_trace(go.Bar(x=list(bodypart.keys()), y=list(bodypart.values()), marker=dict(color='darkseagreen', opacity=0.7)))
fig_bodypart.update_layout(title='Bagian Tubuh', xaxis_title='bodypart', yaxis_title='Jumlah')
fig_equipment = go.Figure()
fig_equipment.add_trace(go.Bar(x=list(equipment.keys()), y=list(equipment.values()), marker=dict(color='peachpuff', opacity=0.7)))
fig_equipment.update_layout(title='Alat yang digunakan', xaxis_title='equipment', yaxis_title='Jumlah')
fig_level = go.Figure()
fig_level.add_trace(go.Bar(x=list(level.keys()), y=list(level.values()), marker=dict(color='lightblue', opacity=0.7)))
fig_level.update_layout(title='level', xaxis_title='level', yaxis_title='Jumlah')
# Membuat grafik batang untuk rating
fig_rating = go.Figure()
fig_rating.add_trace(go.Bar(x=list(rating.keys()), y=list(rating.values()), marker=dict(color='lightgreen', opacity=0.7)))
fig_rating.update_layout(title='Deskripsi Penilaian', xaxis_title='rating', yaxis_title='Jumlah')
# Konversi grafik ke HTML
graph_html_type = fig_type.to_html(full_html=False)
graph_html_bodypart = fig_bodypart.to_html(full_html=False)
graph_html_equipment = fig_equipment.to_html(full_html=False)
graph_html_level = fig_level.to_html(full_html=False)
graph_html_rating = fig_rating.to_html(full_html=False)
return render_template('graph.html', graph_html_type=graph_html_type, graph_html_bodypart=graph_html_bodypart, graph_html_equipment=graph_html_equipment, graph_html_level=graph_html_level, graph_html_rating=graph_html_rating)
# Rute untuk menambah data baru
@app.route('/add', methods=['POST'])
def add():
new_data = {
'number': request.form['number'],
'title': request.form['title'],
'desc': request.form['desc'],
'type': request.form['type'],
'bodypart': request.form['bodypart'],
'equipment': request.form['equipment'],
'level': request.form['level'],
'rating': request.form['rating'],
'ratingdesc': request.form['ratingdesc'],
}
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 = {
'number': request.form['number'],
'title': request.form['title'],
'desc': request.form['desc'],
'type': request.form['type'],
'bodypart': request.form['bodypart'],
'equipment': request.form['equipment'],
'level': request.form['level'],
'rating': request.form['rating'],
'ratingdesc': request.form['ratingdesc'],
}
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>DATA GYM</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>Data GYM</h1>
<p>Data Length: {{ 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="number">Number:</label>
<input type="number" id="number" name="number" /></div>
<div><label for="title">Title:</label>
<input type="text" id="title" name="title" /></div>
<div><label for="desc">Description:</label>
<input type="text" id="desc" name="desc" /></div>
<div><label for="type">Type:</label>
<input type="text" id="type" name="type" /></div>
<div><label for="bodypart">Body Part:</label>
<input type="text" id="bodypart" name="bodypart" /></div>
<div><label for="equipment">Equipment:</label>
<input type="text" id="equipment" name="equipment" /></div>
<div><label for="level">Level:</label>
<input type="text" id="level" name="level" /></div>
<div><label for="rating">Rating:</label>
<input type="text" id="rating" name="rating" /></div>
<div><label for="ratingdesc">RatingDesc:</label>
<input type="text" id="ratingdesc" name="ratingdesc" /></div>
<button type="submit"
style="width: 150px; height: 35px; border: 2px solid #000000; margin-top: 20px">Tambah Data</button>
</form>
<table>
<thead>
<tr>
<th>Number</th>
<th>Title</th>
<th>Description</th>
<th>Type</th>
<th>BodyPart</th>
<th>Equipment</th>
<th>Level</th>
<th>Rating</th>
<th>RatingDesc</th>
<th>Aksi</th>
</tr>
</thead>
<tbody>
{% for row in data %}
<tr>
<td>{{ row.number }}</td>
<td>{{ row.title }}</td>
<td>{{ row.desc }}</td>
<td>{{ row.type }}</td>
<td>{{ row.bodypart }}</td>
<td>{{ row.equipment }}</td>
<td>{{ row.level }}</td>
<td>{{ row.rating }}</td>
<td>{{ row.ratingdesc }}</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 GYM</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="title">Title:</label>
<input
type="text"
id="title"
name="title"
value="{{ data.title }}"
/>
<!-- Isi formulir dengan data yang ada untuk diedit -->
<label for="desc">Description:</label>
<input
type="text"
id="desc"
name="desc"
value="{{ data.desc }}"
/>
<label for="type">Type:</label>
<input
type="text"
id="type"
name="type"
value="{{ data.type }}"
/>
<label for="bodypart">Body Part:</label>
<input
type="text"
id="bodypart"
name="bodypart"
value="{{ data.bodypart }}"
/>
<label for="equipment">Equipment:</label>
<input
type="text"
id="equipment"
name="equipment"
value="{{ data.equipment }}"
/>
<label for="level">Level:</label>
<input
type="text"
id="level"
name="level"
value="{{ data.level }}"
/>
<label for="rating">Rating:</label>
<input
type="text"
id="rating"
name="rating"
value="{{ data.rating }}"
/>
<label for="ratingdesc">Rating Desc:</label>
<input
type="text"
id="ratingdesc"
name="ratingdesc"
value="{{ data.ratingdesc}}"
/>
<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 DATA GYM</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 title */
/* Gaya CSS untuk warna batang pada grafik type */
.bar-type {
fill: coral; /* Warna batang */
opacity: 1; /* Tingkat transparansi */
}
/* Gaya CSS untuk warna batang pada grafik bodypart*/
.bar-bodypart {
fill: darkseagreen; /* Warna batang */
opacity: 1; /* Tingkat transparansi */
}
/* Gaya CSS untuk warna batang pada grafik equipment*/
.bar-equipment {
fill: peachpuff; /* Warna batang */
opacity: 1; /* Tingkat transparansi */
}
.bar-level {
fill: lightgreen; /* Warna batang */
opacity: 1; /* Tingkat transparansi */
}
.bar-rating {
fill: salmon; /* Warna batang */
opacity: 1; /* Tingkat transparansi */
}
</style>
</head>
<body>
<!-- Grafik type-->
<div class="graph-container bar-type">{{ graph_html_type|safe }}</div>
<!-- Grafik bodypart -->
<div class="graph-container bar-bodypart">
{{ graph_html_bodypart|safe }}</div>
<!-- Grafik equipment -->
<div class="graph-container bar-equipment">
{{ graph_html_equipment|safe }}
</div>
<div class="graph-container bar-level">
{{ graph_html_level|safe }}
</div>
<div class="graph-container bar-rating">
{{ graph_html_rating|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,
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 exercise dengan alat Dumbbell, maka ketik kata Dumbbell pada pencarian :
hasilnya :
muncul pengguna user premium dan jumlah datanya
4. Grafik berdasarkan setiap kategori :
Comments
Post a Comment