<jupyter_start><jupyter_text>Introduction to your own data<jupyter_code># read in exemplary data




# display a few rows of the data that we just read in



# get a description of the data



# Save the description of the data as an Excel file<jupyter_output><empty_output><jupyter_text>Concatenating DataFrames  Two DataFrames can be concatenated like this:<jupyter_code>df_cof_dctest_01 = pd.read_excel("data/original_data/insite.dce_COF_DCTest_main (01).xlsx")
df_cof_dctest_02 = pd.read_excel("data/original_data/insite.dce_COF_DCTest_main (02).xlsx")

df_concatenated = pd.concat([df_cof_dctest_01, df_cof_dctest_02])<jupyter_output><empty_output><jupyter_text>Let's check the length of the two original DataFrames and the new combined one<jupyter_code>print("Length of the first DataFrame: " + str( len(df_cof_dctest_01) ))
print("Length of the first DataFrame: " + str( len(df_cof_dctest_02) ))
print("Length of the concatenated DataFrame: " + str( len(df_concatenated) ))<jupyter_output><empty_output><jupyter_text>Try to concatenate the first two insite.dce_final_main Excel files to one DataFrame!<jupyter_code># Enter your code below this comment






# Enter code to check if your concatenation was successful<jupyter_output><empty_output><jupyter_text>Here is a solution to concatenate all Excel files that belong together<jupyter_code>import os

# os.listdir() generates a list of all elements in a certain directory. The directory has to be specified inside the 
# parantheses of the listdir() function.

for file in os.listdir("data/original_data/"):   # this for loop prints all files inside the "original_data" folder
    print(file)<jupyter_output><empty_output><jupyter_text>The dc_final_main-files and the dce_COF_DCTest_main-files need to concatenated!<jupyter_code>def concat_excel_tables(distinct_name):
    """
    This function can be used to read in multiple Excel files and return a concatenated DataFrame containing the data
    from the Excel files.
    -------------
    ...distinct_name: a string that is included in the names of the files that are to be concatenated 
    """
    # get a list of all data inside the original_data folder
    files = os.listdir("data/original_data")
    
    # create a list that only contain the distinct_name in their file name (i.e. a list with the target files)
    target_files = [f for f in files if distinct_name in f]

    # create an empty list "data" and a counter "i"
    data = []
    i = 0

    # iterate through the list with all target file names
    for file in target_files:
        i += 1
        # read in the current file from an Excel file and append it to the data list
        data.append(pd.read_excel("data/original_data/"+file))
        # print the progress of the read-in process
        print("%s of %s files are read in!" % (i, len(target_files)))
    
    # concatenate all DataFrames that were just added to the data list
    df = pd.concat(data, axis=0, sort=False, ignore_index=True)

    return df
df_final_main = concat_excel_tables("insite.dce_final_main")<jupyter_output><empty_output><jupyter_text>Try to use the function to concatenate all the dce_COF_DCTest_main Excel files to one DataFrame. Then save the DataFrame to a CSV file<jupyter_code># use the function defined above to concat the dce_COF_DCTest_main Excel files  


# save the concatenated data to a CSV file<jupyter_output><empty_output><jupyter_text>Merging DataFrames The data/preprocessed_data/csv_original_data folder contains CSV files for all data provided. Data that had to be concatenated are already concatenated! ![grafik.png](attachment:grafik.png)  Merging the COF_TOSA_Aligner data The CSV files contain the indexes of the respective DataFrames that were saved to the files. If we just read them in again as DataFrames, pandas would create new indexes and the old index would be treated as a normal column. To avoid this we specify "index_col=0". Now the old index (which is in column 0 of the CSV files) will be used as index.<jupyter_code>df_tosa_main = pd.read_csv("data/preprocessed_data/csv_original_data/insite.dce_COF_TOSA_Aligner_main.csv", index_col=0)
df_tosa = pd.read_csv("data/preprocessed_data/csv_original_data/insite.dc_COF_TOSA_Aligner.csv", index_col=0)
# Check the shapes (i.e. the number of rows and columns) of the DataFrames
print(df_tosa_main.shape)
print(df_tosa.shape)
df_tosa_merged = df_tosa_main.merge(df_tosa,
                                    left_on="ParentHistoryID",
                                    right_on="dc_COF_TOSA_AlignerHistoryId",
                                    suffixes=("_tosa", "_tosa_main"))
# Check the shape of the merged DataFrame
print(df_tosa_merged.shape)
df_tosa_merged.to_csv("df_tosa_merged.csv")<jupyter_output><empty_output><jupyter_text>Try to merge the COF_DCTest data and save it in a file!<jupyter_code># Enter your code here. Also check if the merge was successful (utilizing the .shape() method, which was also used above)



# Check if the shape of your merged DataFrame looks reasonable like we did in the example



# save the merged DataFrame to a file<jupyter_output><empty_output><jupyter_text>Try to merge the (previously merged) tosa aligner data with the (also previously merged) dctest data  Basic Preprocessing  Drop all columns that only contain missing values (using for instance your merged DataFrame)<jupyter_code># Enter your code here<jupyter_output><empty_output><jupyter_text>Drop all columns with 0 variance (i.e. there is only one unique value in the column) In PartNum there seems to be only one unique value. However there are missing values as well:<jupyter_code>df_tosa_merged["PartNum"].unique()<jupyter_output><empty_output><jupyter_text>The following line of code allows us to get an array (similar to a list) of all unique (non-missing) values:<jupyter_code>df_tosa_merged["PartNum"].dropna().unique()<jupyter_output><empty_output><jupyter_text>Using the len() function we can check the number of unique values.<jupyter_code>len(df_tosa_merged["PartNum"].dropna().unique())<jupyter_output><empty_output><jupyter_text>We can get all columns of a DataFrame as follows:<jupyter_code>df_tosa_merged.columns
# Let's iterate through all columns and drop all that only contain one unique value.
# (using DataFrame.drop(columns=column_name))<jupyter_output><empty_output><jupyter_text>Let's read in the all_data.csv file from data/preprocessed_data/csv_without_empty_cols_and_mergedFor this data- all empty columns were already removed- also all columns without any variance were removed- moreover, all columns that were used as key to merge the data and all remaining columns having "ID" or "Id" in their name were dropped- additionally I dropped all columns from final, that were not declared as relevant output by you(Having no domain expertise regarding your data, I decided to neglect the Ids, as they provide no Information to me. If they contain valuable information to you, you should later create your own merged data file which still contains the Ids.)<jupyter_code>df_all = pd.read_csv("data/preprocessed_data/csv_without_empty_cols_and_merged/all_data.csv", index_col=0)
df_all.head()<jupyter_output><empty_output><jupyter_text>Let's handle all those -99999 and -9999 values<jupyter_code>df_all.min()
# Enter code to treat -99999 and -9999 as missing values



df_all.min()<jupyter_output><empty_output><jupyter_text>Dropping duplicates<jupyter_code># Check how many rows our data currently has
print(df_all.shape)
# Enter code here




# Check if duplicates were removed
df_all.shape<jupyter_output><empty_output><jupyter_text>Taking care of the almost empty rows (from the final_main data) display() is only available for Jupyter Notebooks. It will displayer DataFrames in a nicer form than print() does.<jupyter_code>display(df_all.head(10))<jupyter_output><empty_output><jupyter_text>Lets for example check how many missing values we find in the second row (index = 1) of the DataFrame<jupyter_code>df_all.loc[1,].isna().sum()<jupyter_output><empty_output><jupyter_text>(compared to the final_main Excel file, we see only very few missing values. That is because all final columns that were not specified as relevant output in your use case description have been dropped by me)  Let's drop all rows that contain at least 14 missing valuesUse "df_all.dropna?" to find the correct parameters for the dropping process<jupyter_code>df_all.dropna?
# Enter code here



df_all.dropna(axis=0, thresh=75, inplace=True)<jupyter_output><empty_output><jupyter_text>Fill missing values with the median<jupyter_code>df_all.isna().sum()
# Enter code here





df_all.isna().sum()<jupyter_output><empty_output><jupyter_text>Save your preprocessed data!<jupyter_code># Enter code here<jupyter_output><empty_output><jupyter_text>Creating a scatter plot<jupyter_code>% matplotlib qt

# import the required library (matplotlib.pyplot). It is usally imported as the alias "plt"



def plot_scatter(x, y):
    # Enter your code here to create a scatter plot
    
    # first create a new figure
    
    # now create the scatter plot
    
    # Add an x-axis and a y-axis label to your plot
    
    # show the scatter plot<jupyter_output><empty_output><jupyter_text>Explore the following combinations with your scatter plot function:- WIGGLE_pMAX_W vs. TxOma_mW- T_PO_W vs. TxOma_mW- VI_KINK vs. Ldi_mA- T_PO_dBm vs. TxPower_dBm- SLOPE_WperA vs. TxPower_dBm- R_LD_ohm vs TxCrossing_pctYou can get the corresponding data from the DataFrame that we previously preprocessed<jupyter_code># Use your scatter function here<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.07329244204179229, 'fraction_numerical': 0.005405967356274041, 'mean_word_length': 5.096324461343473, 'pattern_counts': {'":': 0, '<': 97, '<?xml version=': 0, '>': 97, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, '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': '24', 'dir': 'jupyter-structured-clean-dedup', 'id': '401750', 'license': 'MIT', 'n_tokens_mistral': 3139, 'n_tokens_neox': 2989, 'n_words': 1039, 'path': 'resources/Day3/Apply_Python_to_your_data.ipynb', 'repo_name': 'MaxAttax/maxattax.github.io', 'size': '9619'}