<jupyter_start><jupyter_text>Defining Station<jupyter_code>station_location = "Roban"<jupyter_output><empty_output><jupyter_text>Defining Functions<jupyter_code>def format_data(filename, water_name, time_name):
    '''
    - read in the timestamp and waterlevel;
    - select those waterlevel!=nan
    - drop duplicates timestamps & waterlevel and keep the last
    - set the 'timestamp' column into DatetimeIndex and set as index and sort it (timestamp must be monotronic)
    '''
    df = pd.read_csv(filename,usecols=[time_name, water_name])
    df_new = df[df[water_name].notna()]
    print("after dropping na" + str(df_new.shape))
    
    '''
    - there are duplicates in timestamps. keep the last
    '''
    df_new = df_new.drop_duplicates(subset=time_name, keep='last', ignore_index=True)
    print("after dropping dupes" + str(df_new.shape))
    
    df_new[time_name] = pd.DatetimeIndex(df_new[time_name], dayfirst=True)
    df_new = df_new.set_index(time_name)
    df_new = df_new.sort_index()
    print("original size: ", str(df.shape))
    print("after sort index: ", str(df_new.shape))
    
    '''
    - change timestamp from "date" format to "string format" 
    '''
    timestamp = df_new[water_name].index.strftime("%D-M%-Y%")
    waterlevel = df_new[water_name].values
    print(timestamp.shape)
    #plotOriGraph(df_new,timestamp,waterlevel,None,"Original")
    return df_new
def saveToExcelFile(df, station_location, time_name, water_name, filename):
    directory = 'cleaned/' + station_location + "/"
    filename = directory+filename+"_result.csv"
    if not os.path.exists( directory):
        os.makedirs( directory)
    df = df.rename_axis("timestamp")
    df=df.rename(columns={time_name:"timestamp",water_name:"waterlevel"})
    df.to_csv(filename)
def plotOriGraph(df_new,timestamp,waterlevel,waterlevel_flat,title):
    fig = (px.scatter(x=timestamp, y=waterlevel).update_traces(mode='markers+lines'))
    fig.update_xaxes(rangeslider_visible=True)
    fig.update_layout(
        {
            "title":title,
            "xaxis":{
                "title":"timestamp"
            },
            "yaxis":{
                "title":"waterlevel"
            }
        })
    fig.show()
def spikeWithThreshold(df_waterlevel, TP='T'):
    if TP == "T" or TP == "t":
        threshold = 0.6
    elif TP == "NT" or TP == "Nt" or TP == "nt":
        threshold = 0.3
        
    value = np.array(df_waterlevel)
    diff_list = []
    anolist = []
    threshold1 = threshold
    threshold2 =threshold*-1
    anoboo = abs(value) > abs(value) + threshold1 # default all entities in the array to false
    
    for i in range (1, len(value)):         
        diff_list.append(value[i] - value[i-1])

    for i in range (0, len(diff_list)):                      
        if diff_list[i] >= threshold1 or diff_list[i] <= threshold2:
            anolist.append(df_waterlevel.index[i+1])
            anoboo[i+1] = True # set to true if spike detected (difference > threshold)

    anono = anoboo.copy()
    # note : index of anoboo[i] = diff_list[i-1]
    for i in range (0, len(anoboo)):
        if (i != 0) and (i+1 < len(anoboo)):
            if anoboo[i] == True and anoboo[i-1] == True:
                # if i spike up and i+1 spike down, then i+1 is not a spike
                # eg : i-1 = 0.5, i = 2.3, i+1 = 0.6, i is spike, i+1 is not a spike
                if (diff_list[i-1] > 0 and diff_list[i-2] < 0) or (diff_list[i-1] < 0 and diff_list[i-2] > 0):
                    anoboo[i] = False

                # if i spike up and i+1 spike another up (difference between [(i and i+1) > 0.6] and [(i-1 and i+1 > 1.2)])
                # eg: i-1 = 0.1, i = 0.73 (>0.6), i+1 = 1.5 (>0.6), so i is not a spike, i+1 is spike
                elif (diff_list[i-1] > 0 and diff_list[i-2] > 0) or (diff_list[i-1] < 0 and diff_list[i-2] < 0):
                    anoboo[i-1] = False

            # if i is spike and i+1 is within the range of 0.59 with i (i+1 = i +- threshold), i is not a spike
            # eg : i-1 = 0.6, i = 4.5, i+1 = 4.6, i is not a spike, i and i+1 is a trend (detect only 1 sharp point spike as spike, else is trend)
            # can write as (abs(diff_list[i-1]) > 0) and (abs(diff_list[i-1]) < threshold1) and ***anoboo[i] == True***:
            elif (abs(diff_list[i-1]) > 0) and (abs(diff_list[i-1]) < threshold1) and (abs(diff_list[i-2]) > threshold1):
                anoboo[i-1] = False

    return anoboo
def get_median_filtered(signal, threshold=3.5):
    signal = signal.copy()
    peak = signal.copy()
    difference = np.abs(signal - np.median(signal))
    median_difference = np.median(difference)
    if median_difference == 0:
        s = 0
    else:
        s = 0.675*difference / float(median_difference)
    # find the whr the peak
    mask = s > threshold

    # replace the outliers with median of the graph
    signal[mask] = np.median(signal)
    return signal,mask
def plotGraph(df_new, timestamp, waterlevel, waterlevel_flat, title):
    fig = go.Figure()
    fig.add_trace(go.Scattergl(x=timestamp, y=waterlevel,
                    mode='lines+markers',
                    name='Original'))
    fig.add_trace(go.Scattergl(x=timestamp, y=waterlevel_flat,
                    mode='lines+markers',
                    name=title))
#     fig = px.add_line(x=timestamp,y=waterlevel_flat)
    fig.update_xaxes(rangeslider_visible=True)
    fig.update_layout(
        {
            "title":title,
            "xaxis":{
                "title":"timestamp"
            },
            "yaxis":{
                "title":"waterlevel"
            }
        })
    fig.show()
def fillwithline(y, spiketnt, timestamp, waterlevel):
    df_temp = pd.DataFrame()
    df_temp['timestamp'] = timestamp
    df_temp['waterlevel'] = waterlevel

    df_raw = df_temp['waterlevel']
    df_keep = df_raw.loc[np.where(spiketnt!=1)[0]] #find those who are normal

    df_out = pd.merge(df_keep,df_raw,how='outer',left_index=True,right_index=True)

    # Keep only the first column
    s = df_out.iloc[:, 0]#.to_frame()
    # df_temp['waterlevel'] = df_out.iloc[:, 0]

    # 8. Fill missing values
    df_complete = s.fillna(axis=0, method='ffill').fillna(axis=0,method="bfill")
    df_temp['waterlevel'] = df_complete.values
    df_interpolate = s.interpolate()
    df_temp['inter_waterlevel'] = df_interpolate
    return df_temp['waterlevel'].values,df_temp['inter_waterlevel'].values

def data(test_filename, station_location, timestamp_name, waterlevel_name, scaler_filename=None, model_name=None, csv_name=None):
    test_data = format_data(test_filename, waterlevel_name, timestamp_name)  # the one mad cannot detect
    
    test_data['cleaned'] = scipy.signal.medfilt(test_data[waterlevel_name], 11)
    anomalies = spikeWithThreshold(test_data[waterlevel_name],TP="NT")
    test_data['after_checking'],test_data['inter_checking'] = fillwithline(test_data, anomalies,test_data[waterlevel_name].index, test_data[waterlevel_name].values)
    test_data['u_medf'], anomalies = get_median_filtered(test_data[waterlevel_name].values, threshold=3)
    anomalies_med = spikeWithThreshold(test_data['u_medf'],TP="NT")
    test_data['med_check'],test_data['med_inter_checking'] = fillwithline(test_data, anomalies_med,test_data['u_medf'].index, test_data['u_medf'].values)
    
    saveToExcelFile(test_data, station_location, timestamp_name, waterlevel_name, csv_name)
    test_data.head()
    # plotOriGraph(test_data, test_data.index, test_data['rectified'].values, None, title="Rectified")
    plotGraph(test_data,test_data.index,test_data[waterlevel_name].values,test_data['cleaned'].values,title="Rectified")
    plotGraph(test_data,test_data.index,test_data[waterlevel_name].values,test_data['after_checking'].values,title="Rectified")
    plotGraph(test_data,test_data.index,test_data[waterlevel_name].values,test_data['med_check'].values,title="Rectified")<jupyter_output><empty_output><jupyter_text>Loop for cleaning<jupyter_code>directory = '../src/dataset/' + station_location

for i in range(2016,2022):
    year = str(i)
    csv_name = station_location + "_" + year
    filename = directory + "/" + csv_name + ".csv"
    data(filename, station_location, 
         timestamp_name="timestamp",
         waterlevel_name="actual_reading",
         csv_name=csv_name
        )<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.13345302214242968, 'fraction_numerical': 0.014602034709754639, 'mean_word_length': 3.1530815109343937, 'pattern_counts': {'":': 12, '<': 21, '<?xml version=': 0, '>': 28, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '3', 'dir': 'jupyter-structured-clean-dedup', 'id': '529152', 'license': 'MIT', 'n_tokens_mistral': 2951, 'n_tokens_neox': 2802, 'n_words': 587, 'path': 'src/csv-clean.ipynb', 'repo_name': 'haja-k/hydro-telemetry-anomaly-detection-lstm', 'size': '8355'}