<commit_before>'''
MALT Dashboard Data Manipulation module

DataFrame column names expected:
 - df['Account Name']
 - df['City']
  - df['State']
 - df['Country']
 - df['Date']
 - df['Time']
 - df['IP Address']
 - df['Latitude']
 - df['Longitude']
 '''

import pandas as pd
import MySQLdb   #pip3 install mysqlclient
import sys

def get_emails():
    # #before we get the data variable from Hanzhao, we insert the following records into database
    #two data records
    email_list = [['jinzh','34.7464809','92.2895947', '2017-01-08 20:52:47','192.168.3.11','P-town','Oregon','USA'],['Dummy Data', '92.2895947', '34.7464809','2016-01-08 20:52:47', 'fdf8:f53e:61e4::18', 'San Jose', 'California', 'USA']]
    return email_list


#### MySQL Connection
def store_emails(email_list, hostname='localhost', user='', password='', database=''):
    """here we have the MySQL configured as an example. Running on localhost, its username is root,
    password is '<PASSWORD>', and we are accessing the specific database named 'malt'"""

    try:
        conn = MySQLdb.connect(hostname, user, password, database)
    except Exception as e:
        sys.exit("We can't get into the database")
    c = conn.cursor()

    #create a table if it doesn't exist named emailRecords
    c.execute("CREATE TABLE IF NOT EXISTS emailRecords(`Account Name` varchar(15), Latitude float(20), Longitude float(20), Datetime datetime, `IP Address` varchar(40), `City` varchar(20), `State` varchar(20), `Country` varchar(25))")
    #c.execute("Delete from emailRecords WHERE Datetime < NOW() - INTERVAL 90 DAY")
    #here is the insertion and commit row by row of data records
    for col in email_list:
        if col[1] != '':
              c.execute("INSERT INTO emailRecords(`Account Name`, Latitude, Longitude, Datetime, `IP Address`, City, State, Country) VALUES (%s, %s, %s, %s, %s,%s, %s, %s)", (col[0], col[1], col[2], col[3], col[4], col[5], col[6], col[7]))
              conn.commit()


### Pandas DataFrame Generation
def create_df(hostname='localhost', user='', password='', database=''):
    ''' Creates pandas dataframe in memory from the data source'''
    ## JSON file data source (for development)
    # df = pd.read_json(data)
    # df.index += 1
    # df['Time'] = df['Time'] = pd.to_datetime(df['Time Stamp'])

    ## MySQL data source
    try:
        conn = MySQLdb.connect(hostname, user, password, database)
    except Exception as e:
        sys.exit("We can't get into the database")
    c = conn.cursor()

    df = pd.read_sql('SELECT * from emailRecords', con = conn)
    print(df.dtypes)

    c.close()
    conn.close()

    return df


# Pandas DataFrame Query
## Correct df['Date'] df['Time'] to correct column.
def filter_df(df, radius='', latitude='', longitude='',<commit_msg>Update df_process.py<commit_after>'''
MALT Dashboard Data Manipulation module

DataFrame column names expected:
 - df['Account Name']
 - df['City']
  - df['State']
 - df['Country']
 - df['Date']
 - df['Time']
 - df['IP Address']
 - df['Latitude']
 - df['Longitude']
 '''

import pandas as pd
import MySQLdb   #pip3 install mysqlclient
import sys

def get_emails():
    # #before we get the data variable from Hanzhao, we insert the following records into database
    #two data records
    email_list = [['jinzh','34.7464809','92.2895947', '2017-01-08 20:52:47','192.168.3.11','P-town','Oregon','USA'],['Dummy Data', '92.2895947', '34.7464809','2016-01-08 20:52:47', 'fdf8:f53e:61e4::18', 'San Jose', 'California', 'USA']]
    return email_list


#### MySQL Connection
def store_emails(email_list, hostname='localhost', user='', password='', database=''):
    """here we have the MySQL configured as an example. Running on localhost, its username is root,
    password is '<PASSWORD>', and we are accessing the specific database named 'malt'"""

    try:
        conn = MySQLdb.connect(hostname, user, password, database)
    except Exception as e:
        sys.exit("We can't get into the database")
    c = conn.cursor()

    #create a table if it doesn't exist named emailRecords
    c.execute("CREATE TABLE IF NOT EXISTS emailRecords(`Account Name` varchar(15), Latitude float(20), Longitude float(20), Datetime datetime, `IP Address` varchar(40), `City` varchar(20), `State` varchar(20), `Country` varchar(25))")
    c.execute("Delete from emailRecords WHERE Datetime < NOW() - INTERVAL 90 DAY")
    #here is the insertion and commit row by row of data records
    for col in email_list:
        if col[1] != '':
              c.execute("INSERT INTO emailRecords(`Account Name`, Latitude, Longitude, Datetime, `IP Address`, City, State, Country) VALUES (%s, %s, %s, %s, %s,%s, %s, %s)", (col[0], col[1], col[2], col[3], col[4], col[5], col[6], col[7]))
              conn.commit()
    conn.close()

### Pandas DataFrame Generation
def create_df(hostname='localhost', user='', password='', database=''):
    ''' Creates pandas dataframe in memory from the data source'''
    ## JSON file data source (for development)
    # df = pd.read_json(data)
    # df.index += 1
    # df['Time'] = df['Time'] = pd.to_datetime(df['Time Stamp'])

    ## MySQL data source
    try:
        conn = MySQLdb.connect(hostname, user, password, database)
    except Exception as e:
        sys.exit("We can't get into the database")
    c = conn.cursor()

    df = pd.read_sql('SELECT * from emailRecords', con = conn)
    print(df.dtypes)

    c.close()
    conn.close()

    return df


# Pandas DataFrame Query
## Correct df['Date'] df['Time'] to correct column.
def filter_df(df, radius='', latitude='', longitude='',
{'fraction_non_alphanumeric': 0.15843179377013963, 'fraction_numerical': 0.03866809881847476, 'mean_word_length': 3.858260869565217, 'pattern_counts': {'":': 0, '<': 7, '<?xml version=': 0, '>': 5, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 2, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 2, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'dir': 'git-commits-cleaned', 'id': '4074968', 'n_tokens_mistral': 1918, 'n_tokens_neox': 1728, 'n_words': 573}