Friday, January 19, 2024

Customer Segmentation with RFM Using K-Means Clustering

 Abstract:

In the dynamic landscape of business, understanding customer behavior is crucial for effective marketing strategies. Customer segmentation offers a methodical approach to categorize customers based on their interactions with a business. In this white paper, we explore the integration of Recency, Frequency, Monetary (RFM) analysis with K-Means clustering to achieve a comprehensive and insightful customer segmentation.

 


1. Introduction:

Customer segmentation is the process of dividing a customer base into groups that share similar characteristics. RFM analysis is a widely used method for customer segmentation that leverages three key metrics:

Recency (R): How recently a customer made a purchase.

Frequency (F): How often a customer makes a purchase.

Monetary (M): How much money a customer spends.

K-Means clustering, a popular machine learning algorithm, complements RFM analysis by automating the grouping of customers based on these metrics.

2. RFM Analysis:

2.1 Recency:

Recency measures the time since a customer's last purchase. It provides insights into customer engagement and helps identify the most recent and active customers.

2.2 Frequency:

Frequency represents how often a customer makes a purchase. Customers with high frequency are more likely to be loyal.

2.3 Monetary:

Monetary reflects the total amount of money a customer has spent. High monetary value indicates a valuable customer.

3. K-Means Clustering:

K-Means clustering is an unsupervised machine learning algorithm that groups data points into clusters based on their similarities. It minimizes the within-cluster sum of squares to form distinct clusters.

4. Integration of RFM and K-Means Clustering:

4.1 Data Preprocessing:

Before applying K-Means clustering, RFM metrics are standardized to ensure equal importance. Standardization involves transforming the metrics into a common scale.

4.2 Applying K-Means Clustering:

K-Means clustering is applied to the standardized RFM metrics. The algorithm groups customers into clusters based on their recency, frequency, and monetary values.

4.3 Mapping Clusters to Segments:

Cluster labels are mapped to human-readable segments. This step adds interpretability to the results and facilitates targeted marketing.

5. Visualizing Segments:

A scatter plot is a powerful tool to visualize the segments created by RFM and K-Means clustering. It provides a clear representation of customer groups based on recency and frequency.

6. Benefits of Customer Segmentation with RFM and K-Means Clustering:

  • Personalized Marketing: Tailor marketing strategies for each segment to maximize effectiveness.
  • Customer Retention: Identify and address issues with high-value customers to enhance retention.
  • Resource Optimization: Allocate resources efficiently by focusing on segments with the highest potential.

7. Conclusion:

Integrating RFM analysis with K-Means clustering yields a robust approach to customer segmentation. This combined methodology empowers businesses to understand customer behavior, target specific groups effectively, and optimize marketing efforts. The insights gained enable businesses to build stronger customer relationships, drive loyalty, and ultimately improve the bottom line.


Here is a sample Python program for this article:

  1
  2
  3
  4
  5
  6
  7
  8
  9
 10
 11
 12
 13
 14
 15
 16
 17
 18
 19
 20
 21
 22
 23
 24
 25
 26
 27
 28
 29
 30
 31
 32
 33
 34
 35
 36
 37
 38
 39
 40
 41
 42
 43
 44
 45
 46
 47
 48
 49
 50
 51
 52
 53
 54
 55
 56
 57
 58
 59
 60
 61
 62
 63
 64
 65
 66
 67
 68
 69
 70
 71
 72
 73
 74
 75
 76
 77
 78
 79
 80
 81
 82
 83
 84
 85
 86
 87
 88
 89
 90
 91
 92
 93
 94
 95
 96
 97
 98
 99
100
101
102
103
104
105
106
107
108
109
110
from PyQt6.QtWidgets import QApplication, QMainWindow, QWidget, QVBoxLayout, QTableWidget, QTableWidgetItem
from PyQt6.QtCore import Qt
from matplotlib.backends.backend_qt5agg import FigureCanvasQTAgg as FigureCanvas
import matplotlib.pyplot as plt
import pandas as pd
import sys
import numpy as np
from sklearn.cluster import KMeans
from sklearn.preprocessing import StandardScaler
df1 =[]
class PlotCanvas(FigureCanvas):
    def __init__(self, parent=None, width=8, height=2.1, dpi=90):
        self.figure, self.ax = plt.subplots(figsize=(width, height), dpi=dpi)
        self.figure.suptitle('Customer Segmentation with RFM', fontsize=12, color='black')        
        FigureCanvas.__init__(self, self.figure)        
        self.setParent(parent)        
        FigureCanvas.updateGeometry(self)        
        self.plot()

    def plot(self):
        # Create a sample dataset
        global df1
        np.random.seed(42)
        data = {'CustomerID': range(1, 101),
                'Recency': np.random.randint(1, 365, 100),
                'Frequency': np.random.randint(1, 11, 100),
                'Monetary': np.random.randint(100, 1000, 100)}
        df1 = pd.DataFrame(data)
        # Standardize the RFM values
        rfm_cols = ['Recency', 'Frequency', 'Monetary']
        scaler = StandardScaler()
        df1[rfm_cols] = scaler.fit_transform(df1[rfm_cols])

        # Applying K-Means clustering with 10 segments
        kmeans = KMeans(n_clusters=10, random_state=42, n_init=10)
        df1['Segment'] = kmeans.fit_predict(df1[rfm_cols])

        # Mapping segments to human-readable names
        segment_names = {
            0: 'Hibernating',
            1: 'At Risk',
            2: "Cant Lose Them",
            3: 'About to Sleep',
            4: 'Need Attention',
            5: 'Loyal Customers',
            6: 'Promising',
            7: 'Potential Loyalists',
            8: 'New Customers',
            9: 'Champions'
        }

        df1['Segment'] = df1['Segment'].map(segment_names)

        # Visualize the segments
        for segment, color in zip(segment_names.values(), [
            '#1f77b4', '#aec7e8', '#ffbb78', '#98df8a', '#ff9896',
            '#c5b0d5', '#c49c94', '#e377c2', '#f7b6d2', '#7f7f7f'
        ]):
            self.ax.scatter(df1[df1['Segment'] == segment]['Recency'],
                            df1[df1['Segment'] == segment]['Frequency'],
                            color=color, label=segment, alpha=0.7, s=100)

        #self.ax.set_title('Customer Segmentation with RFM')
        self.ax.set_xlabel('Recency (Scaled)')
        self.ax.set_ylabel('Frequency (Scaled)')
        self.ax.legend()

        self.draw()

class Window(QWidget):
    def __init__(self):
        super(Window, self).__init__()
        self.initUI()

    def initUI(self):
        
        self.m8 = PlotCanvas(self, width=6.7, height=5.10)
        self.m8.move(264, 125)
        self.m8.show()                
        #layout = QVBoxLayout(self)
        #s#elf.m8 = PlotCanvas(self, width=8.2, height=5.10)
        #layout.addWidget(self.m8)

        # Create a table view
        self.table_widget = QTableWidget(self)
        self.table_widget.setColumnCount(2)
        self.table_widget.setHorizontalHeaderLabels(['Segment', 'Number of Customers'])
        self.populate_table()
        self.table_widget.setGeometry(930, 125, 230, 350)

    def populate_table(self):
        global df1
        # Count the number of customers in each segment
        segment_counts = df1['Segment'].value_counts()

        # Populate the table
        for segment, count in segment_counts.items():
            row_position = self.table_widget.rowCount()
            self.table_widget.insertRow(row_position)
            self.table_widget.setItem(row_position, 0, QTableWidgetItem(segment))
            self.table_widget.setItem(row_position, 1, QTableWidgetItem(str(count)))

if __name__ == '__main__':
    app = QApplication(sys.argv)
    window = QMainWindow()
    central_widget = Window()
    window.setCentralWidget(central_widget)
    window.setGeometry(100, 100, 1200, 800)
    window.show()
    sys.exit(app.exec())

 Customer Segmentation with RFM Using K-Means Clustering

In the provided program, the weights on different segments are not explicitly specified in the code. The weights are implicitly determined by the K-Means clustering algorithm. K-Means is an unsupervised machine learning algorithm that partitions the data into k clusters based on similarity. In this case, the number of clusters (n_clusters) is set to 10.

The algorithm assigns each data point to one of the 10 clusters, and the centroids of these clusters are calculated to minimize the sum of squared distances between data points and their respective cluster centroids.

So, the weights on different segments are derived from the centroid locations in the feature space. Each segment (cluster) is represented by a centroid, and the position of this centroid determines the characteristics of the segment. The data points within a segment are closer to the centroid of that segment compared to centroids of other segments.

In the code, the colors and labels for each segment are defined in the loop that follows the K-Means clustering. The loop iterates over the segment names and their corresponding colors, and for each segment, it plots the data points with the specified color, label, and other visual properties.

In summary, the weights on different segments are not explicitly set in the code; they emerge from the clustering process based on the characteristics of the data and the K-Means algorithm's centroid calculations. Each segment represents a group of customers with similar Recency, Frequency, and Monetary values.

Sunday, January 7, 2024

Moving Image Regions with Python, Tkinter and OpenCV

 Overview

Imagine having a tool that allows you to interactively delete specific regions of an image with just a few clicks. This Python and Tkinter application empowers users to open an image, draw rectangles around unwanted regions, and seamlessly delete or relocate them. This technical documentation provides an insight into the functionality of each method, shedding light on how this program achieves its remarkable image manipulation capabilities.



Feature Highlights

Open Image: The application starts by prompting the user to open an image file. The selected image is loaded using OpenCV, ensuring compatibility with various image formats.


Draw Rectangles: Users can draw rectangles on the image canvas by clicking and dragging. These rectangles define the regions to be manipulated.


Move and Delete: The application introduces an innovative approach to both moving and deleting image regions. When the user holds the Shift key and clicks, the selected region becomes interactive, allowing effortless relocation. Additionally, the program enables the deletion of unwanted regions, providing a dynamic and user-friendly experience.


Method Breakdown

open_image

This method is responsible for opening a file dialog, allowing users to select an image file. Once an image is selected, it is loaded using OpenCV, and the display_image method is called to present it on the Tkinter canvas.


display_image

The display_image method converts the image from BGR to RGB format and creates a Tkinter PhotoImage object. This image is then displayed on the canvas, ensuring correct dimensions and preventing garbage collection.


select_rect

This method is triggered when the user holds the Shift key and clicks, activating the cursor for moving rectangles or images. It adds the 'selected' tag to the current canvas item.


activate_cursor

The activate_cursor method sets the cursor in motion, binding it to the movement of rectangles or images. When the user releases the mouse button, the deselect method is called.


deselect

The deselect method removes the 'selected' tag, deactivating the cursor. It rebinds the canvas to the initial event handlers, allowing the user to draw rectangles or select regions.


move_rect

The move_rect method handles the movement of rectangles or images. If a rectangle is selected, it is moved using the coords method. If an image is selected, the program calculates the displacement and moves the canvas item accordingly.


on_button_press, on_move_press, on_button_release

These methods respond to mouse events. on_button_press initializes the drawing of rectangles, on_move_press captures the drawn region, and on_button_release finalizes the process.


Conclusion

In essence, this Python and Tkinter application serves as an interactive image manipulation tool, allowing users to draw, move, and delete specific regions effortlessly. By leveraging OpenCV and Tkinter, it provides a robust platform for customizing and extending image processing projects. Whether you're a developer exploring image manipulation or an enthusiast seeking a powerful yet accessible tool, this program opens the door to a world of possibilities.

The source code can be downloaded at my patreon shop.

Sunday, December 31, 2023

A Python Tkinter-Based Image Editor

 This program is a simple image editing application built using the Tkinter library in Python. Here's a description of its basic features:




  1. Toolbar with Shape Buttons:

    • The left side of the canvas contains a toolbar with buttons for various shapes, including circle, rectangle, rounded rectangle, triangle, line, curve, star, text, and an eraser.
    • Each button is associated with a specific drawing action. When a shape button is clicked, it activates the corresponding drawing mode.
  2. Canvas for Drawing:

    • The main canvas occupies the majority of the application window and serves as the drawing area.
    • The canvas is scrollable, allowing users to work on larger drawings that extend beyond the initial view.
  3. File Menu:

    • The application includes a File menu with options to open, save, save as, and view properties of the drawing.
  4. Draw Menu:

    • The Draw menu provides options for drawing basic shapes like a circle, rectangle, and rounded rectangle.
  5. View Menu:

    • The View menu includes options for zooming in, zooming out, and resetting the view to 100%.
  6. Window Menu:

    • The Window menu provides options such as settings and an about dialog, though the actual implementations are currently placeholders.
  7. Color Selection:

    • The program allows users to select a color for drawing using a color picker dialog.
  8. Eraser:

    • The eraser button activates an eraser mode, allowing users to erase parts of their drawings.
  9. Zooming and Panning:

    • Placeholder buttons for zooming in, zooming out, moving the image, panning, rotating, changing font, and adjusting font size are present in the toolbar.
  10. Tooltips:

    • Tooltips are implemented for each button on the toolbar, providing users with information about the functionality of each button when they hover over it.
  11. Text Drawing:

    • There is a button for drawing text on the canvas, though the actual implementation is a placeholder.
  12. Cursor Lines:

    • During drawing or other operations, vertical and horizontal lines are displayed as a visual aid to indicate the current cursor position.
  13. Saving and Opening Images:

    • Users can open existing images for editing or save their drawings as image files.
  14. Properties Window:

    • There is a placeholder properties window that displays information such as width and height.
  15. Resizable Canvas:

    • The canvas adjusts its size based on the loaded image or drawing, and users can view and modify properties like width and height.
  16. Mouse Interaction:

    • The application captures mouse events to facilitate drawing and other interactive actions on the canvas.

It's worth noting that some functionalities like zooming, panning, rotating, changing font, and adjusting font size are currently implemented as placeholders and may require further development.

The source code is available at my patreon shop. It is just a demo program ideal if you are interested to improve the software further on your own where anything is possible. Currently, implemented features are draw circles, open, edit and save images and of course the screen layout. I will upload the near finished version at my patreon shop as a separate item.

Friday, December 22, 2023

Tips on How to Use cx_Freeze and pyArmor

 Let me share the journey of refining my program, "Monitoring Data Usage with Python," and the challenges I encountered along the way. As the program evolved to meet my specific requirements—running WAMP64, saving captured data to a database, and incorporating a user-friendly pyQt6 GUI—I encountered some hitches during testing that needed creative solutions.



One of the issues arose when executing WAMP64 from within the script. As the execution of Python code is asynchronous, a timing misalignment led to errors since the script attempted to save data to the database before WAMP64 fully loaded. To address this, I employed a workaround by running WAMP64 on a separate thread, pausing the script's execution for 30 seconds to ensure synchronization. This solution proved effective and resolved the problem at hand.

However, the real challenge surfaced when attempting to convert the Python script into an executable (.exe) file. The motivation behind this conversion was to streamline program execution, replacing the manual process with a simple double-click on the desktop icon.

Initially, I turned to pyInstaller, but it fell short of expectations. Subsequently, I switched to cx_Freeze, which showed promise but introduced an unexpected behavior—re-running the program every 30 seconds. This behavior was unacceptable, so I devised a workaround by introducing a global variable and saving it in an INI file. By initializing this variable to zero during the first load, the program executed entirely. Afterward, I set it to one, ensuring that subsequent re-executions would exit immediately. This clever solution resolved the recurring execution problem, leaving me content with the setup.


By the way, I did all developments in virtual environment using pipenv.

The command used to generate the exe file:

python setup.py build

And here is the setup.py I used:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
import sys
from cx_Freeze import setup, Executable

build_exe_options = {
    "packages": ["PyQt6"],
    "includes": ["PyQt6.QtWidgets", "PyQt6.QtGui", "PyQt6.QtCore"],
    "include_files": ["data_usage.ini"],
}

base = None
if sys.platform == "win32":
    base = "Win32GUI"  # Use "Win32GUI" for a GUI application

setup(
    name="DataUsageApp",
    version="1.0",
    description="Data Usage App",
    options={"build_exe": build_exe_options},
    executables=[Executable("pyqt_hourly2.py", base=base)],
)

Considering the desire to share my program with others while keeping my code private, I decided to obfuscate the script using pyArmor. Unfortunately, this decision led to numerous errors, rendering the .exe file nonfunctional. In a stroke of insight, I copied the content of the lib folder from a previous build, pre-obfuscation, and success! The .exe file functioned as intended.

The following command line is what I used to obfuscate the python script:

pyarmor obfuscate pyqt_hourly2.py

This journey taught me valuable lessons in problem-solving and adapting to unforeseen challenges, ultimately resulting in an efficient and user-friendly program ready to benefit others.


Here is how to create the database in wam64(I used phpMyAdmin):

CREATE DATABASE IF NOT EXISTS data_usage;


And to create its table:

DROP TABLE IF EXISTS `data_usage`;

CREATE TABLE IF NOT EXISTS `data_usage` (

  `date_time` datetime NOT NULL,

  `data_usage` double NOT NULL,

  `incoming` double NOT NULL,

  `outgoing` double NOT NULL

) ENGINE=MyISAM DEFAULT CHARSET=latin1;

COMMIT; 

You may download the app here Data Usage App.

Saturday, December 9, 2023

Monitoring Data Usage with Python: A Two-Part Program

Are you frequently puzzled by the rapid depletion of your data, wondering if there's an explanation for its swift consumption? Perhaps you've had moments where it feels like your data plan is slipping away faster than anticipated, leaving you with a sense of being shortchanged. If you find yourself yearning for the ability to keep a close eye on your data usage and establish a cutoff point based on a specific megabyte threshold, you're not alone. The desire to gain better control over your data consumption is a common sentiment, and fortunately, there are solutions available to help you achieve just that.

In this article, we'll explore how to monitor and display data usage using a Python program. The solution consists of two programs: a data usage monitoring program responsible for capturing real-time data usage and saving it to a MySQL database, and a PyQt6 program that displays the data usage per hour.

Data Usage Monitoring Program

The data usage monitoring program utilizes the psutil library to collect real-time network statistics, and it interacts with the WorldTimeAPI to obtain the current time with a specified timezone. The collected data is then stored in a MySQL database.


 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
import psutil
import time
import requests
import mysql.connector as mysql
from datetime import datetime, timedelta

incoming = 0
outgoing = 0
data_usage = 0
t = 0

def get_current_time_with_offset(timezone="Asia/Hong_Kong"):
    try:
        response = requests.get(f"http://worldtimeapi.org/api/timezone/{timezone}")

        if response.status_code == 200:
            data = response.json()
            current_time_str = data['datetime']
            current_time = datetime.strptime(current_time_str, "%Y-%m-%dT%H:%M:%S.%f%z")
            new_time = current_time + timedelta(seconds=1)
            return new_time
        else:
            print(f"Error: Unable to fetch current time. Status code: {response.status_code}")

    except Exception as e:
        print(f"An error occurred: {e}")

# Example usage:
new_time = get_current_time_with_offset()

def get_realtime_data_usage(interval=1):
    global new_time, data_usage, outgoing, incoming, t

    prev_net_stats = psutil.net_io_counters()

    while True:
        if t / 10 == 1:
            t = 0
            new_time = get_current_time_with_offset()
        t += 1
        new_time = new_time + timedelta(seconds=1)
        current_net_stats = psutil.net_io_counters()

        data_transferred = (
            (current_net_stats.bytes_sent - prev_net_stats.bytes_sent) +
            (current_net_stats.bytes_recv - prev_net_stats.bytes_recv)
        ) / 1000
        data_usage = data_usage + round(data_transferred / 1000, 3)
        outgoing = outgoing + round((current_net_stats.bytes_sent - prev_net_stats.bytes_sent) / 1000, 3)
        incoming = incoming + round((current_net_stats.bytes_recv - prev_net_stats.bytes_recv) / 1000, 3)

        data_usage1 = round(data_transferred, 3)
        outgoing1 = round((current_net_stats.bytes_sent - prev_net_stats.bytes_sent) / 1000, 3)
        incoming1 = round((current_net_stats.bytes_recv - prev_net_stats.bytes_recv) / 1000, 3)

        print(
            str(new_time.strftime("%Y-%m-%d %H:%M:%S")) +
            '-' + str(round(data_usage, 3)) +
            '-' + str(round(outgoing, 3)) +
            '-' + str(round(incoming, 3))
        )

        db = mysql.connect(
            host="localhost",
            user="root",
            passwd="",
            database="data_usage"
        )
        cursor = db.cursor()
        sql = "INSERT INTO data_usage (date_time, data_usage, incoming, outgoing) VALUES (%s, %s, %s, %s)"
        val = (new_time.strftime("%Y-%m-%d %H:%M:%S"), round(data_usage1, 3), round(incoming1, 3), round(outgoing1, 3))
        cursor.execute(sql, val)
        db.commit()

        prev_net_stats = current_net_stats
        time.sleep(interval)

# Example usage:
get_realtime_data_usage()

This program constantly monitors data usage and inserts the collected information into a MySQL database. The data includes the timestamp, total data usage, incoming data, and outgoing data.


PyQt6 Program for Display

The PyQt6 program provides a graphical user interface to display data usage per hour. It offers features like auto-refresh, specifying the refresh interval, and selecting a date for display.

  1
  2
  3
  4
  5
  6
  7
  8
  9
 10
 11
 12
 13
 14
 15
 16
 17
 18
 19
 20
 21
 22
 23
 24
 25
 26
 27
 28
 29
 30
 31
 32
 33
 34
 35
 36
 37
 38
 39
 40
 41
 42
 43
 44
 45
 46
 47
 48
 49
 50
 51
 52
 53
 54
 55
 56
 57
 58
 59
 60
 61
 62
 63
 64
 65
 66
 67
 68
 69
 70
 71
 72
 73
 74
 75
 76
 77
 78
 79
 80
 81
 82
 83
 84
 85
 86
 87
 88
 89
 90
 91
 92
 93
 94
 95
 96
 97
 98
 99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
import sys
from PyQt6.QtWidgets import QApplication, QMainWindow, QMessageBox, QVBoxLayout, QWidget, QTableWidget, QTableWidgetItem, QPushButton, QDateEdit, QCheckBox, QLabel, QLineEdit
from PyQt6.QtGui import QFont, QColor
from PyQt6.QtCore import Qt, QDate, QTimer, QDateTime
import mysql.connector

class DataUsageApp(QMainWindow):
    def __init__(self):
        super().__init__()
        self.refresh_timer = QTimer()
        self.refresh_timer.timeout.connect(self.refresh_data)

        self.init_ui()

    def init_ui(self):
        self.setWindowTitle('Data Usage App')
        central_widget = QWidget(self)
        self.setCentralWidget(central_widget)

        layout = QVBoxLayout(central_widget)

        self.label_date_time = QLabel(self)
        self.label_date_time.setAlignment(Qt.AlignmentFlag.AlignCenter)
        font = self.label_date_time.font()
        font.setPointSize(30)
        self.label_date_time.setFont(font)
        layout.addWidget(self.label_date_time)

        current_date_time = QDateTime.currentDateTime()
        date_time_str = current_date_time.toString("yyyy-MM-dd hh:mm:ss")
        self.label_date_time.setText(date_time_str)

        qdate = QDate.currentDate()
        self.date_edit = QDateEdit(qdate, self)
        self.date_edit.setCalendarPopup(True)
        layout.addWidget(self.date_edit)

        self.label_refresh_interval = QLabel('Refresh Interval (seconds):', self)
        layout.addWidget(self.label_refresh_interval)

        self.line_edit_refresh_interval = QLineEdit(self)
        layout.addWidget(self.line_edit_refresh_interval)

        self.checkbox_auto_refresh = QCheckBox('Auto Refresh', self)
        self.checkbox_auto_refresh.stateChanged.connect(self.auto_refresh_changed)
        layout.addWidget(self.checkbox_auto_refresh)

        btn_refresh = QPushButton('Refresh Data', self)
        btn_refresh.clicked.connect(self.refresh_data)
        layout.addWidget(btn_refresh)

        self.table_widget = QTableWidget()
        layout.addWidget(self.table_widget)
        self.setGeometry(50, 55, 445, 800)

    def auto_refresh_changed(self, checked):
        if checked:
            self.start_auto_refresh()
        else:
            self.stop_auto_refresh()

    def start_auto_refresh(self):
        try:
            interval = int(self.line_edit_refresh_interval.text())
            if interval > 0:
                self.refresh_timer.start(interval * 1000)  # Convert seconds to milliseconds
            else:
                self.show_error_message("Please enter a valid positive refresh interval.")
        except ValueError:
            self.show_error_message("Please enter a valid number for the refresh interval.")

    def stop_auto_refresh(self):
        self.refresh_timer.stop()

    def show_error_message(self, message):
        error_box = QMessageBox(self)
        error_box.setIcon(QMessageBox.Icon.Critical)
        error_box.setWindowTitle("Error")
        error_box.setText(message)
        error_box.exec()

    def refresh_data(self):
        self.table_widget.clear()

        current_date_time = QDateTime.currentDateTime()
        date_time_str = current_date_time.toString("yyyy-MM-dd hh:mm:ss")
        self.label_date_time.setText(date_time_str)

        selected_date = self.date_edit.date().toString('yyyy-MM-dd')
        result = self.execute_query(selected_date)
        self.populate_table(result)

        if self.checkbox_auto_refresh.isChecked():
            try:
                interval = int(self.line_edit_refresh_interval.text())
                if interval > 0:
                    self.refresh_timer.start(interval * 1000)  # Convert seconds to milliseconds
                else:
                    self.show_error_message("Please enter a valid positive refresh interval.")
                    self.refresh_timer.stop()
            except ValueError:
                self.show_error_message("Please enter a valid positive refresh interval.")
                self.refresh_timer.stop()
        else:
            self.refresh_timer.stop()

    def execute_query(self, selected_date):
        try:
            connection = mysql.connector.connect(
                user='root',
                password='',
                host='localhost',
                database='data_usage'
            )

            cursor = connection.cursor()

            select_query = f"""
            SELECT
                DATE_FORMAT(date_time, '%H:00:00') AS hour,
                ROUND(SUM(data_usage), 3) AS total_data_usage,
                ROUND(SUM(incoming), 3) AS total_incoming,
                ROUND(SUM(outgoing), 3) AS total_outgoing
            FROM
                data_usage
            WHERE
                DATE(date_time) = STR_TO_DATE('{selected_date}', '%Y-%m-%d')
            GROUP BY
                hour
            ORDER BY
                hour;
            """

            cursor.execute(select_query)
            result = cursor.fetchall()
            return result

        except mysql.connector.Error as err:
            print(f"Error: {err}")

        finally:
            if 'connection' in locals() and connection.is_connected():
                cursor.close()
                connection.close()

    def populate_table(self, result):
        self.table_widget.setRowCount(len(result))
        self.table_widget.setColumnCount(len(result[0]) if result else 0)

        header_labels = ['Hour', 'Total Data Usage', 'Total Incoming', 'Total Outgoing']
        self.table_widget.setHorizontalHeaderLabels(header_labels)

        for row_index, row_data in enumerate(result):
            for col_index, col_data in enumerate(row_data):
                item = QTableWidgetItem(str(col_data))
                self.table_widget.setItem(row_index, col_index, item)

                if col_index in [1, 2, 3]:
                    item.setTextAlignment(Qt.AlignmentFlag.AlignRight | Qt.AlignmentFlag.AlignVCenter)

        self.add_totals_row(result)

    def add_totals_row(self, result):
        self.table_widget.insertRow(self.table_widget.rowCount())

        totals = [sum(float(column) if column and str(column).replace('.', '', 1).isdigit() else 0.0 for column in col) for col in zip(*result)]

        for col_index, total in enumerate(totals):
            item = QTableWidgetItem(str(round(total, 3)))
            item.setFlags(item.flags() & ~Qt.ItemFlag.ItemIsEditable)
            self.table_widget.setItem(self.table_widget.rowCount() - 1, col_index, item)

            if col_index in [1, 2, 3]:
                item.setTextAlignment(Qt.AlignmentFlag.AlignRight | Qt.AlignmentFlag.AlignVCenter)

            if col_index == 0:
                item.setText('TOTAL')
                font = item.font()
                font.setBold(True)
                item.setFont(font)

            item.setBackground(QColor(211, 211, 211))

def main():
    app = QApplication(sys.argv)
    ex = DataUsageApp()
    ex.show()
    sys.exit(app.exec())

if __name__ == '__main__':
    main()

The screenshot:


This PyQt6 program provides a user-friendly interface for visualizing data usage. It includes features such as auto-refresh, specifying refresh intervals, and selecting dates for data display. The data is fetched from the MySQL database and presented in a tabular format.

Feel free to customize and enhance these programs based on your specific requirements.