<jupyter_start><jupyter_text>We used three datasets in this paper (one for each growth cycle -2017/18/19). The code described below was applied to each dataset separetely, but here we show just one to avoid repetition.<jupyter_code>#Import libraries for tabular data processing
import pandas as pd
import tabula as tab
import numpy as np
import os<jupyter_output><empty_output><jupyter_text>Convert pdf to csvThe datasets made available by Genomes to Fields initiative (G2F) contained some tables in pdf instead of csv format. So to use the tables we are converting it from pdf to csv using tabula library, the conversion was performed page by page so it would be easier to check the values accuracy.<jupyter_code># For a document with 18 pages
pages = [] 
for i in range(18):
    each_page = tabula.read_pdf('/data/fieldnotes.pdf', pages=i+1, output_format='dataframe')
    print(f"Doing page {i+1}") # print the current page to notice if the function got stuck
    pages.append(each_page)
# Clean and tidy each page
for i in range(18):
    pages[i] = pages[i][0]

# Export this to csv and check if the values between pdf and csv version are matching
for i in range(18):
    pages[i].to_csv(f"/data/fieldnotes_page{i}.csv", index=False)<jupyter_output><empty_output><jupyter_text>Check the if the csv file matches the pdf version manually, them merge the pages together in a file called "/data/fieldnotes_mergedpages.csv".  Clean csvThe tabular dataset contains missing values, and other inconsistencies that must be resolved before using it as input for a machine learning model. ```Check for NAs```<jupyter_code>def na_stats(df):
    '''
    Function to count the NAs per column
    and count the amount of unique values
    '''
    list_of_columns = df.columns
    print(f"This dataframe has {len(df)} rows")
    
    # Check each column for na values
    for column in list_of_columns:
        df_clean = df[df[column].notna()]
        nans = len(df[df[column].isna()])
        unique_values = df_clean[column].unique()
        
        print(f"{column} has {len(unique_values)} unique values, and {nans} NAs ")
# Read the csv file, each dataframe is separated by Year already
df = pd.read_csv("/data/fieldnotes_mergedpages.csv")
na_stats(df)
# Fill Yield NAs in the dataframe using its replicate values
df['Yield'] = df['Yield'].fillna(
                df.groupby(by=['Test', 'Pedigree'])['Yield'].transform(
                lambda s:s.loc[s.first_valid_index()]))
                #takes the first valid number in the group and fill in the NA
# Drop samples that didn't have a replicate Yield value
df = df.dropna(axis=0)
# Replace NaN in Stock column with 'unknow'
df['Stock'] = df['Stock'].fillna('unknown')<jupyter_output><empty_output><jupyter_text>```Clean Typos```Converting pdf to csv may have created some typos like extra blank spaces in the end of the word<jupyter_code># Clean typos in the string columns
columns = ['Barcode', 'Test', 'Stock', 'Pedigree']

for col in columns:
    df[col] = df[col].str.strip('!? \n\t"')
    df[col] = df[col].str.strip("'")<jupyter_output><empty_output><jupyter_text>```Separate Hybrids into parental lines```<jupyter_code># Repeat this for each df used
df['Parental 1'] = df['Pedigree'].str.split('/').str.get(0)
df['Parental 2'] = df['Pedigree'].str.split('/').str.get(1)<jupyter_output><empty_output><jupyter_text>```Add columns to describe treatment```<jupyter_code># G2LA, G2FE and DG2F are the original treatment names as used in the Genomes to Field Initiative.
# In the paper they are replaced by more intuitive names P2F1, P1F1 and P1F2 
g2la = df.loc[df['Test'] == 'G2LA']
g2la['Planting'] ='late'
g2la['Fertilizer'] = 'optimal'

g2fe = df.loc[df['Test'] == 'G2FE']
g2fe['Planting'] = 'optimal'
g2fe['Fertilizer'] = 'optimal'

dg2f = df.loc[df['Test'] == 'DG2F']
dg2f['Planting'] = 'optimal'
dg2f['Fertilizer'] ='reduced'

df = g2la.append(g2fe).append(dg2f)
# save csv
df.to_csv('/data/fielddata/df_2017.csv')<jupyter_output><empty_output><jupyter_text>Prepare input data<jupyter_code># Import libraries for preparing input data
from fastai.tabular.all import *
# Load the cleaned dataframes
df_2017 = pd.read_csv('/data/fielddata/df_2017.csv')
df_2018 = pd.read_csv('/data/fielddata/df_2018.csv')
df_2019 = pd.read_csv('/data/fielddata/df_2019.csv')

# Blend the datasets together
mixed = df_2017.append(df_2018)
mixed_df = mixed.append(df_2019)
# Divide between train/validation and test (90:10%)
# the sample function uses a equal probability of getting any row in the dataset
df_test = mixed_df.sample(frac=0.1, random_state=32)
df_train_val = mixed_df.drop(df_test.index)

df_test.to_csv('/data/fielddata/df_test.csv', index=False)
df_train_val.to_csv('/data/fielddata/df_train_val.csv', index=False)
# Random splitter function from fastai
splitter = RandomSplitter(seed=42)
splits = splitter(range_of(df_train_validation))
splits
procs = [Categorify, Normalize, FillMissing]
cat_names = [ 'Parental 1', 'Parental 2', 'Planting', 'Stock', 'Fertilizer']
cont_names =['Days_after_sowing']

dls = TabularDataLoaders.from_df(df_train_val,
                                        y_names="Yield",
                                        cat_names=cat_names,
                                        cont_names=cont_names,
                                        procs = procs,
                                        splits = splits)

# Prepare the train/val data for XGBoost and Random Forest
X_train, y_train = dls.train.xs, dls.train.ys.values.ravel()
X_val, y_val = dls.valid.xs, dls.valid.ys.values.ravel()

X = X_train.append(X_val)
Y = np.append(y_train, y_val)

X.to_csv('/data/fielddata/X_ordinal.csv')
Y.to_csv('/data/fielddata/Y.csv')

# Prepare the holdout data for XGBoost and Random Forest
dl = dls.test_dl(df_test)
Xtest = dl.xs

Ytest = Xtest.pop('Yield')
Xtest.to_csv('/data/fielddata/Xtest_ordinal.csv')
Ytest.to_csv('/data/fielddata/Ytest.csv')
# Prepare One-hot encoded dataset
# One hot encode
categorical_cols = ['Stock', 'Parental 1','Parental 2', 'Planting', 'Fertilizer', 'Days_after_sowing']

# Ad the test set to the training dataset to dummy then together, so they match
superX = X.append(Xtest)
superX = pd.get_dummies(superX, columns=categorical_cols)

X_ohe = superX[:3878]
Xtest_ohe = superX[3878:]

# Save the one-hot encoded Xs
# No need to export the target values as they are the same as above
X_ohe.to_csv('/data/fielddata/X_ohe.csv')
Xtest_ohe.to_csv('/data/fielddata/Xtest_ohe.csv')<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.11855036855036855, 'fraction_numerical': 0.017045454545454544, 'mean_word_length': 4.269417475728155, 'pattern_counts': {'":': 0, '<': 29, '<?xml version=': 0, '>': 29, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 5, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '7', 'dir': 'jupyter-structured-clean-dedup', 'id': '501312', 'license': 'MIT', 'n_tokens_mistral': 2285, 'n_tokens_neox': 2157, 'n_words': 632, 'path': 'tab_processing.ipynb', 'repo_name': 'mdanilevicz/maize_early_yield_prediction', 'size': '6512'}