|
|
import pandas as pd |
|
|
|
|
|
|
|
|
def process_Kit_Composition_and_relation(output_csv_path: str = 'data/real_data_excel/converted_csv/Kit_Composition_and_relation_cleaned_with_line_type_and_id.csv') -> pd.DataFrame: |
|
|
""" |
|
|
Process the Kit_Composition_and_relation.csv file to clean the data and add line type and id. |
|
|
|
|
|
Returns: |
|
|
saves to csv path |
|
|
cleaned_df: pd.DataFrame |
|
|
""" |
|
|
df = pd.read_csv('data/real_data_excel/converted_csv/Kit_Composition_and_relation.csv') |
|
|
|
|
|
master = df[["Master Kit", "Master Kit Description"]] |
|
|
master["kit_type"] = "master" |
|
|
master.rename(columns={"Master Kit": "kit_name", "Master Kit Description": "kit_description"}, inplace=True) |
|
|
|
|
|
subkit = df[["Sub kit", "Sub kit description"]] |
|
|
subkit["kit_type"] = "subkit" |
|
|
subkit.rename(columns={"Sub kit": "kit_name", "Sub kit Description": "kit_description"}, inplace=True) |
|
|
subkit.columns = ["kit_name", "kit_description", "kit_type"] |
|
|
|
|
|
prepack = df[["Prepack", "Prepack Description"]] |
|
|
prepack["kit_type"] = "prepack" |
|
|
prepack.rename(columns={"Prepack": "kit_name", "Prepack Description": "kit_description"}, inplace=True) |
|
|
|
|
|
|
|
|
cleaned_df = pd.concat([master, subkit, prepack]) |
|
|
cleaned_df[['kit_name','kit_description','kit_type']].drop_duplicates() |
|
|
tmp = cleaned_df.groupby('kit_name').count()['kit_type'].reset_index() |
|
|
standalone_masterkit_list = tmp.loc[tmp['kit_type']==1,'kit_name'] |
|
|
|
|
|
cleaned_df.loc[cleaned_df['kit_name'].isin(standalone_masterkit_list),'line_type'] = 'long line' |
|
|
cleaned_df.loc[cleaned_df['kit_type']=='prepack','line_type'] = 'mini load' |
|
|
cleaned_df.loc[cleaned_df['kit_type']=='subkit','line_type'] = 'long line' |
|
|
cleaned_df.loc[cleaned_df['line_type']=='mini load', 'line_id'] = 7 |
|
|
cleaned_df.loc[cleaned_df['line_type']=='long line', 'line_id'] = 6 |
|
|
cleaned_df.to_csv(output_csv_path, index=False) |
|
|
return cleaned_df |
