<jupyter_start><jupyter_text>Connect MySQL - Load Data with mysql.connector- Load Data in Dataframe format<jupyter_code>import mysql.connector
import pandas as pd
from pandas_profiling import ProfileReport

db_connection = mysql.connector.connect(
    host="192.168.127.12",
    user="gyan",
    password="<PASSWORD>",
    database="gyan_db"
)<jupyter_output><empty_output><jupyter_text>Simple Query + Print out<jupyter_code>mycursor = db_connection.cursor()
mycursor.execute("SELECT * FROM core_stats")

myresult = mycursor.fetchall()

for x in myresult:
    print(x)<jupyter_output>('BETAZRPDCOR001', datetime.datetime(2022, 3, 1, 14, 55, 27), 2, '0', 45751, 0, 0, 0, 0, 0, 0, 1, 2, 33.3333)
('BETAZRPDCOR001', datetime.datetime(2022, 3, 1, 15, 16, 59), 2, '0', 45901, 0, 0, 0, 0, 0, 0, 1, 2, 33.3333)
('BETAZRPDCOR001', datetime.datetime(2022, 3, 1, 15, 18, 1), 2, '0', 45901, 0, 0, 0, 0, 0, 0, 1, 2, 33.3333)
('BETAZRPDCOR001', datetime.datetime(2022, 3, 1, 15, 24, 2), 2, '0', 45921, 0, 0, 0, 0, 0, 0, 1, 2, 33.3333)
('BETAZRPDCOR001', datetime.datetime(2022, 3, 1, 15, 30, 2), 2, '0', 45940, 0, 0, 0, 0, 0, 0, 1, 2, 33.3333)
('BETAZRPDCOR001', datetime.datetime(2022, 3, 1, 15, 36, 2), 2, '0', 45980, 0, 0, 0, 0, 0, 0, 1, 2, 33.3333)
('BETAZRPDCOR001', datetime.datetime(2022, 3, 1, 15, 42, 1), 2, '0', 46003, 0, 0, 0, 0, 0, 0, 1, 2, 33.3333)
('BETAZRPDCOR001', datetime.datetime(2022, 3, 1, 15, 48, 2), 2, '0', 37907, 0, 0, 0, 0, 0, 0, 1, 2, 33.3333)
('BETAZRPDCOR001', datetime.datetime(2022, 3, 1, 15, 54, 1), 2, '0', 46049, 0, 0, 0, 0, 0, 0, 1, 2, 33.3333)
('BETAZRPDCOR001'[...]<jupyter_text>Python Dataframe ViewLoad four Tables from our Gyan Database1. rrc2. rru3. rb4. rb_per5. core_stats<jupyter_code>rrc = pd.read_sql('SELECT * FROM vlte_rrc_stats', con=db_connection)
rrc.head()
rb = pd.read_sql('SELECT * FROM vlte_rb_stats', con=db_connection)
rb.head()
rru = pd.read_sql('SELECT * FROM vlte_rru_stats', con=db_connection)
rru.head()
rb_per = pd.read_sql('SELECT * FROM vlte_rb_per_qci_stats', con=db_connection)
rb_per.head()
core_stats = pd.read_sql('SELECT * FROM core_stats', con=db_connection)
core_stats.head()
asset_location = pd.read_sql('SELECT * FROM assets_location', con=db_connection)
asset_location.head()<jupyter_output><empty_output><jupyter_text>Generate Profile Report<jupyter_code>generate_profile_report=False
output_file_location = Profile_report_folder_name+str(table)+".html"
table_list = [rrc ,rru,rb,rb_per,core_stats, asset_location ]
tablename_list = ["rrc" ,"rru","rb","rb_per","core_stats", "asset_location" ]
Profile_report_folder_name = "Profile_Reports/"



if generate_profile_report:
    for table,name in zip(table_list,tablename_list):
        

        profile_report = ProfileReport(table)
        output_file_location = Profile_report_folder_name+name+".html"
        
        
        if os.path.exists(csv_filename):
            print("File {} already generated".format(output_file_location))
            pass
        else:
            profile_report.to_file(output_file=output_file_location)
            print("Table ", name," Complete")
import os

if generate_profile_report:
    for table,name in zip(table_list,tablename_list):

        profile_report = ProfileReport(table)
        output_file_location = Profile_report_folder_name+name+".html"
        
        
        if os.path.exists(output_file_location):
            print("File {} already generated".format(output_file_location))
            pass
        else:
            profile_report.to_file(output_file=output_file_location)
            print("Table ", name," Complete")<jupyter_output>File Profile_Reports/rrc.html already generated
File Profile_Reports/rru.html already generated
File Profile_Reports/rb.html already generated
File Profile_Reports/rb_per.html already generated
File Profile_Reports/core_stats.html already generated
File Profile_Reports/asset_location.html already generated<jupyter_text>Data Cleaning  Deal with nan<jupyter_code>import math

test_data_a = 21
test_data_b = -19
test_data_c = float("nan")

print(math.isnan(test_data_a))
print(math.isnan(test_data_b))
print(math.isnan(test_data_c))
rb["drb_dl_th_capacity_avg"]= rb["drb_dl_th_capacity_avg"].apply(lambda x: float(x) if x=="nan" else x)

rb["drb_dl_th_capacity_avg"].value_counts().sort_values(ascending= False)<jupyter_output><empty_output><jupyter_text>Manualy Check  rb<jupyter_code>rb.columns
rb.groupby("bs_name")[['drb_dl_th_capacity_sum', 'drb_dl_th_capacity_avg',
       'drb_ul_th_capacity_sum', 'drb_ul_th_capacity_avg',
       'drb_max_ul_th_capacity', 'drb_pdcp_sdu_air_loss_rate_dl_sum',
       'drb_pdcp_sdu_loss_rate_ul_sum']].agg(['mean']).reset_index().round(2)<jupyter_output><empty_output><jupyter_text>Helper Functions<jupyter_code>def getSummaryTable(df):
    '''
    Input: a Dataframe you want to get some columsn
    Output: a summary table in dataframe format, with 1st column as id, 2nd column as Missing value percentage, third column as Unique value count.

    summary_table= getSummaryTable(df)

    '''

    columns = list(df.columns)
    unique_column = [df[c].nunique() for c in columns]

    missing_number = df.isnull().sum()/len(df)
    missing_number = list(missing_number)

    Columns_Name = list(df.columns)
    summary_table = pd.DataFrame({'Columns_Name': Columns_Name,
                                 'missing_rate': missing_number,
                                  'unique_count': unique_column})

    return summary_table
def getDuplicateColumns(df):
    '''
    Get a list of duplicate columns.
    It will iterate over all the columns in dataframe and find the columns whose contents are duplicate.
    :param df: Dataframe object
    :return: List of columns whose contents are duplicates.
    '''
    duplicateColumnNames = set()
    # Iterate over all the columns in dataframe
    cnt = 0
    for i in range(df.shape[1]):

        # Select column at xth index.
        col = df.iloc[:, i]

        # Iterate over all the columns in DataFrame from (x+1)th index till end
        for j in range(i + 1, df.shape[1]):
            # Select column at yth index.
            otherCol = df.iloc[:, j]

            # Check if two columns at x 7 y index are equal
            if col.equals(otherCol):
                duplicateColumnNames.add(df.columns.values[j])

                print("Pair", cnt, ": ", df.columns[i], " | ", df.columns[j])
                cnt += 1

    return list(duplicateColumnNames)


def remove_duplicate_columns(df):
    '''
    Remove duplicate columns (columns with different name but same values)

    input: a dataframe
    Output: dataframe without duplicate columns

    '''

    duplicate_list = getDuplicateColumns(df)
    col = list(df.columns)
    for i in duplicate_list:
        col.remove(i)

    return df[col]
getSummaryTable(rb_per)
# Check for Duplication Columns - Same value different col name
getDuplicateColumns(rb_per)<jupyter_output><empty_output><jupyter_text>Prepare Data for Time Series Visualization  Dataset Preparation-   Visualization  Plotly  Time Series Date time<jupyter_code># Get the range of data that needs to be processed
# input("Enter record start time (MM/DD/YYYY):")
start_time = (datetime.now() - timedelta(hours=24)
              ).strftime('%m-%d-%Y %H:%M:%S')
# input("Enter record end time (MM/DD/YYYY):")
end_time = datetime.now().strftime('%m-%d-%Y %H:%M:%S')


# print(response)
df = pd.read_csv(io.StringIO(response.text), parse_dates=['opening_time'])

# Select the data in the required range
mask = (df['opening_time'] <= end_time) & (df['opening_time'] >= start_time)
df_new = pd.DataFrame(df[mask])

# Calculate session peak values to Mbps
print(df_new.tx_peak_octets)
DFWIRV01COR001_1_peak_ul_ses = df_new.tx_peak_octets * 0.0000008
DFWIRV01COR001_1_peak_dl_ses = df_new.rx_peak_octets * 0.0000008


# Find max session peak value
DFWIRV01COR001_1_peak_ul = DFWIRV01COR001_1_peak_ul_ses.max()
DFWIRV01COR001_1_peak_dl = DFWIRV01COR001_1_peak_dl_ses.max()

# Calculate total number of packets dropped
DFWIRV01COR001_1_dropped_packets = df_new.tx_total_dropped_packets + \
    df_new.rx_total_dropped_packets
DFWIRV01COR001_1_transmitted_packets = df_new.tx_packets + df_new.rx_packets
DFWIRV01COR001_1_total_transmitted_packets = DFWIRV01COR001_1_transmitted_packets.sum()
DFWIRV01COR001_1_total_dropped_packets = DFWIRV01COR001_1_dropped_packets.sum()
if DFWIRV01COR001_1_total_dropped_packets is None:
    DFWIRV01COR001_1_total_dropped_packets = 0

# print(DFWIRV01COR001_1_total_dropped_packets)
# print(DFWIRV01COR001_1_total_transmitted_packets)

if DFWIRV01COR001_1_total_dropped_packets is not None and DFWIRV01COR001_1_total_dropped_packets != 0:
    DFWIRV01COR001_1_percentage_drop = (
        DFWIRV01COR001_1_total_dropped_packets / DFWIRV01COR001_1_total_transmitted_packets) * 100
else:
    DFWIRV01COR001_1_percentage_drop = 0

# Find total users in the Data Frame
DFWIRV01COR001_1_total_users = df_new.sub_ref_2.nunique()

# Print out the calculated values
print("Peak Upload Speed (Mbps):", DFWIRV01COR001_1_peak_ul)
print("Peak Download Speed (Mbps):", DFWIRV01COR001_1_peak_dl)
print("Percentage Packets Dropped:", DFWIRV01COR001_1_total_dropped_packets)
print("Total Users:", DFWIRV01COR001_1_total_users)<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.11484232052523947, 'fraction_numerical': 0.05779786890539231, 'mean_word_length': 4.597590361445783, 'pattern_counts': {'":': 1, '<': 33, '<?xml version=': 0, '>': 33, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 1, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 3, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '8', 'dir': 'jupyter-structured-clean-dedup', 'id': '78245', 'license': 'MIT', 'n_tokens_mistral': 3816, 'n_tokens_neox': 3332, 'n_words': 735, 'path': 'Python_MySQL.ipynb', 'repo_name': 'dujijundavid/Anomaly_Detection', 'size': '9287'}