FarmHill - Naufal Hawari

pip install kagglehub

import kagglehub
import pandas as pd
import sqlite3

path = kagglehub.dataset_download("naufalhawarinor/farmhill")

csv_file = f"{path}/farmhill_cleann.csv"

data = pd.read_csv(csv_file)

# Create SQLite in-memory database and load data into SQL
conn = sqlite3.connect(':memory:')  # Create in-memory SQLite database
data.to_sql('farmhill', conn, index=False, if_exists='replace')

print("Data loaded into SQLite database.")

query = """
SELECT 
    Customer,
    SUM("Jumlah Real (Kg)") AS Total_Serapan
FROM 
    farmhill
GROUP BY 
    Customer
HAVING 
    Total_Serapan > 500
ORDER BY 
    Total_Serapan DESC;
"""
result = pd.read_sql_query(query, conn)
print(result)

query = """
SELECT 
    "Kategori Item",
    "Nama Item",
    ROUND(AVG(Harga), 0) AS Rata_Rata_Harga
FROM 
    farmhill
GROUP BY 
    "Kategori Item", 
    "Nama Item";
"""
result = pd.read_sql_query(query, conn)
pd.set_option('display.max_rows', None)  # Menampilkan semua baris
pd.set_option('display.max_columns', None)  # Menampilkan semua kolom
pd.set_option('display.width', None)  # Menyesuaikan lebar tampilan
print(result)

query = """
SELECT 
    Customer,
    COUNT(DISTINCT "No. Invoice") AS Total_Invoice,
    SUM("Total (Net)") AS Total_Penjualan_Net
FROM 
    farmhill
GROUP BY 
    Customer
ORDER BY
	Total_Penjualan_Net DESC;
"""
result = pd.read_sql_query(query, conn)
pd.set_option('display.max_rows', None)  # Menampilkan semua baris
pd.set_option('display.max_columns', None)  # Menampilkan semua kolom
pd.set_option('display.width', None)  # Menyesuaikan lebar tampilan
print(result)