<jupyter_start><jupyter_text>Data Cleaning with Pandas and Jupyter Notebooks<jupyter_code>import pandas as pd
from pathlib import Path 

import sys
sys.path.append(str(Path.cwd().parent))

from scripts.normalize_data import (
    normalize_columns, 
    normalize_expedition_section_cols,
    remove_bracket_text,
    remove_whitespace,
    print_df
)

# from scripts.my_normalize_data import (
#     normalize_columns, 
#     normalize_expedition_section_cols,
#     remove_bracket_text,
#     remove_whitespace,
# )
paths = list(Path('..', 'processed_data', 'clean_data').rglob('*.csv'))

normalized_nontaxa_path = Path('..', 'processed_data', 'normalized_nontaxa_list.csv')
len(paths)<jupyter_output><empty_output><jupyter_text>Basic file cleanup Use pandas built in functions to do these steps- delete dataframe column if all values are NA   dropna(axis='columns', how='all', inplace=True)  - delete dataframe row if all values are NA   dropna(axis='index', how='all', inplace=True) - remove duplicate rows in dataframe   drop_duplicates(inplace=True)<jupyter_code>for path in paths:
    df = pd.read_csv(path, dtype=str)
    
    # code to change file   
    
    df.to_csv(path, index=False)<jupyter_output><empty_output><jupyter_text>remove leading and trailing white spaces created  `remove_whitespace` to remove all leading and trailing white spaces from a dataframe. Since we wanted to remove white spaces from the headers, we used `read_csv(header=None)` and `to_csv(header=False)` so that pandas treat the first row like any other row.<jupyter_code>for path in paths:
    df = pd.read_csv(path, dtype=str, header=None)
    
    remove_whitespace(df)
    
    df.to_csv(path, index=False, header=False)<jupyter_output><empty_output><jupyter_text>Normalizing columns names  get all unique column names Use `pandas.DataFrame.columns()` and python `set` to get all the unique columns for all the files.<jupyter_code>all_columns = set()

for path in paths:
    df = pd.read_csv(path, dtype=str, nrows=0)
    
    # code to get all column names  
    

all_columns<jupyter_output><empty_output><jupyter_text>Manually separate taxa names from other headers so that we could do some more processing on the taxa.<jupyter_code>taxa_columns = set()
len(taxa_columns)<jupyter_output><empty_output><jupyter_text>Given `all_columns` and `taxa_columns`, find the `nontaxa_columns`.<jupyter_code># code to get nontaxa_columns
nontaxa_columns = None
len(nontaxa_columns)<jupyter_output><empty_output><jupyter_text>create taxa and non-taxa file Save the taxa and nontaxa headers to csv.<jupyter_code>path = Path('..', 'processed_data', 'drafts', 'taxa_list.csv')

# create taxa dataframe an save it as a csv
path = Path('..', 'processed_data', 'drafts', 'nontaxa_list.csv')

# create nontaxa dataframe an save it as a csv<jupyter_output><empty_output><jupyter_text>normalize headers create `normalize_columns` to update the column names for a data frame.<jupyter_code>nontaxa_df = pd.read_csv(normalized_nontaxa_path, dtype=str)
print_df(nontaxa_df)<jupyter_output><empty_output><jupyter_text>create a dictionary that lists the original field name and normalized field name.<jupyter_code>nontaxa_mapping = nontaxa_df.set_index('field').to_dict()['normalized_field']<jupyter_output><empty_output><jupyter_text>normalize columns for all files<jupyter_code>for path in paths:
    df = pd.read_csv(path, dtype=str)    
    
    normalize_columns(df, nontaxa_mapping)
    
    df.to_csv(path, index=False)<jupyter_output><empty_output><jupyter_text>Clean up row values create `remove_bracket_text` to remove the [text] values at the end of some taxa columns.<jupyter_code>for path in paths:
    df = pd.read_csv(path, dtype=str)
    
    df = remove_bracket_text(df)
    
    df.to_csv(path, index=False)<jupyter_output><empty_output><jupyter_text>Turn one column into multiple columns  For some files, `Sample` column was given, but `Exp, Site, Hole, Core, Type, Section, A/W` columns where not given. `normalize_expedition_section_cols` converts `Sample` into separate `Exp, Site, Hole, Core, Type, Section, A/W` columns.<jupyter_code>for path in paths:
    df = pd.read_csv(path, dtype=str)   
    
    df = normalize_expedition_section_cols(df)
    
    df.to_csv(path, index=False)<jupyter_output><empty_output><jupyter_text>check if mandatory columns exists<jupyter_code>required_columns = {
 'A/W',
 'Bottom [cm]',
 'Bottom Depth [m]',
 'Core',
 'Exp',
 'Hole',
 'Sample',
 'Section',
 'Site',
 'Top [cm]',
 'Top Depth [m]',
 'Type'
}
for path in paths:
    df = pd.read_csv(path, dtype=str)    
    cols = set(df.columns)
    diff = required_columns - cols
    
    if(len(diff) > 0):
        print(path)
        print(required_columns - cols)<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.10482180293501048, 'fraction_numerical': 0.0004192872117400419, 'mean_word_length': 5.277631578947369, 'pattern_counts': {'":': 0, '<': 53, '<?xml version=': 0, '>': 54, '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': '13', 'dir': 'jupyter-structured-clean-dedup', 'id': '144353', 'license': 'MIT', 'n_tokens_mistral': 1670, 'n_tokens_neox': 1622, 'n_words': 411, 'path': 'data_cleaning/notebooks/1.2_data_cleaning.ipynb', 'repo_name': 'wykhuh/ec_workshops_py', 'size': '4770'}