| 1. seprate attributes and data | |
| 2. remove the datatypes from the attributes | |
| C:\Users\Niall Dcunha\DatasetCreator\house price prediction\21754539_dataset | |
| # import os | |
| # import glob | |
| # import pandas as pd | |
| # import openai | |
| # from openai import OpenAI | |
| # from dotenv import load_dotenv | |
| # import ast | |
| # import re | |
| # def extract_dict_from_response(response: str) -> dict: | |
| # # Try extracting code block content containing the dictionary | |
| # match = re.search(r"```(?:python)?\s*(\{.*?\})\s*```", response, re.DOTALL) | |
| # if match: | |
| # mapping_str = match.group(1) | |
| # else: | |
| # # Try extracting dictionary directly if it's not in code block | |
| # match = re.search(r"(\{.*\})", response, re.DOTALL) | |
| # if not match: | |
| # raise ValueError("❌ Could not find a Python dictionary in the response.") | |
| # mapping_str = match.group(1) | |
| # try: | |
| # return ast.literal_eval(mapping_str) | |
| # except Exception as e: | |
| # print("⚠️ Failed to evaluate extracted dictionary string.") | |
| # print("String:", mapping_str) | |
| # raise e | |
| # # Load environment variables | |
| # load_dotenv() | |
| # client = OpenAI( | |
| # api_key=os.getenv("OPENAI_API_KEY"), | |
| # base_url=os.getenv("OPENAI_API_BASE") # Optional: for Azure or self-hosted | |
| # ) | |
| # def load_csv_files(folder_path): | |
| # csv_files = glob.glob(os.path.join(folder_path, "*.csv")) | |
| # dataframes = [] | |
| # column_sets = [] | |
| # valid_paths = [] | |
| # print("📥 Reading CSV files...") | |
| # for file in csv_files: | |
| # try: | |
| # df = pd.read_csv(file) | |
| # dataframes.append(df) | |
| # column_sets.append(list(df.columns)) | |
| # valid_paths.append(file) | |
| # print(f"✅ Loaded: {os.path.basename(file)}") | |
| # except pd.errors.ParserError as e: | |
| # print(f"❌ Skipping file due to parsing error: {os.path.basename(file)}") | |
| # print(f" ↳ {e}") | |
| # except Exception as e: | |
| # print(f"⚠️ Unexpected error with file {os.path.basename(file)}: {e}") | |
| # return dataframes, column_sets, valid_paths | |
| # def generate_mapping_prompt(column_sets): | |
| # prompt = ( | |
| # "You are a data scientist helping to merge multiple ML prediction datasets. " | |
| # "Each CSV may have different or similar column names. I need a unified mapping to standardize these datasets. " | |
| # "Also, please identify likely prediction label columns (e.g., price, quality, outcome).\n\n" | |
| # "Here are the column headers from each CSV:\n" | |
| # ) | |
| # for i, columns in enumerate(column_sets): | |
| # prompt += f"CSV {i+1}: {columns}\n" | |
| # prompt += ( | |
| # "\nPlease provide:\n" | |
| # "1. A Python dictionary mapping similar columns across these CSVs.\n" | |
| # "2. A list of columns most likely to represent prediction labels.\n\n" | |
| # "Format your response as:\n" | |
| # "```python\n" | |
| # "column_mapping = { ... }\n" | |
| # "label_columns = [ ... ]\n" | |
| # "```" | |
| # ) | |
| # return prompt | |
| # def get_column_mapping_from_openai(column_sets): | |
| # prompt = generate_mapping_prompt(column_sets) | |
| # response = client.chat.completions.create( | |
| # model="gpt-4", | |
| # messages=[ | |
| # {"role": "system", "content": "You are a helpful data scientist."}, | |
| # {"role": "user", "content": prompt} | |
| # ], | |
| # temperature=0.3 | |
| # ) | |
| # content = response.choices[0].message.content | |
| # print("\n📩 Received response from OpenAI.") | |
| # try: | |
| # # Try parsing both dictionary and label list from the response | |
| # column_mapping_match = re.search(r"column_mapping\s*=\s*(\{.*?\})", content, re.DOTALL) | |
| # label_columns_match = re.search(r"label_columns\s*=\s*(\[.*?\])", content, re.DOTALL) | |
| # if column_mapping_match: | |
| # mapping = ast.literal_eval(column_mapping_match.group(1)) | |
| # else: | |
| # raise ValueError("❌ Could not find `column_mapping` in the response.") | |
| # if label_columns_match: | |
| # label_columns = ast.literal_eval(label_columns_match.group(1)) | |
| # else: | |
| # label_columns = [] | |
| # except Exception as e: | |
| # print("⚠️ Error parsing OpenAI response:") | |
| # print(content) | |
| # raise e | |
| # return mapping, label_columns | |
| # def standardize_columns(df, mapping): | |
| # new_columns = {col: mapping.get(col, col) for col in df.columns} | |
| # return df.rename(columns=new_columns) | |
| # def merge_csvs(folder_path, output_file="merged_dataset.csv"): | |
| # dfs, column_sets, csv_paths = load_csv_files(folder_path) | |
| # if not dfs: | |
| # print("❌ No valid CSVs found to merge.") | |
| # return | |
| # print("\n🧠 Requesting column mapping from OpenAI...") | |
| # mapping, label_columns = get_column_mapping_from_openai(column_sets) | |
| # print("\n📌 Column Mapping:") | |
| # for k, v in mapping.items(): | |
| # print(f" '{k}' -> '{v}'") | |
| # print("\n🏷️ Suggested Label Columns:") | |
| # for label in label_columns: | |
| # print(f" - {label}") | |
| # standardized_dfs = [standardize_columns(df, mapping) for df in dfs] | |
| # merged_df = pd.concat(standardized_dfs, ignore_index=True, sort=False) | |
| # merged_df.to_csv(output_file, index=False) | |
| # print(f"\n✅ Merged dataset saved as '{output_file}'") | |
| # if __name__ == "__main__": | |
| # folder_path = "house" | |
| import os | |
| import glob | |
| import pandas as pd | |
| import ast | |
| import re | |
| from itertools import combinations | |
| from rapidfuzz import fuzz, process | |
| from dotenv import load_dotenv | |
| from openai import OpenAI | |
| # Manual rename map to standardize some known variations | |
| manual_rename_map = { | |
| "review": "text", | |
| "text": "text", | |
| "NumBedrooms": "bedrooms", | |
| "HousePrice": "price", | |
| "TARGET(PRICE_IN_LACS)": "price", | |
| "SquareFootage": "area", | |
| "SQUARE_FT": "area", | |
| "sentiment": "label", | |
| "target": "label", | |
| "type": "label", | |
| "variety": "label", | |
| "class": "label", | |
| "HeartDisease": "label", | |
| "Heart Attack Risk (Binary)": "label", | |
| "Heart Attack Risk": "label" | |
| } | |
| def normalize(col): | |
| return re.sub(r'[^a-z0-9]', '', col.lower()) | |
| def apply_manual_renaming(df, rename_map): | |
| renamed = {} | |
| for col in df.columns: | |
| if col in rename_map: | |
| renamed[col] = rename_map[col] | |
| return df.rename(columns=renamed) | |
| def get_fuzzy_common_columns(cols_list, threshold=75): | |
| base = cols_list[0] | |
| common = set() | |
| for col in base: | |
| match_all = True | |
| for other in cols_list[1:]: | |
| match, score, _ = process.extractOne(col, other, scorer=fuzz.token_sort_ratio) | |
| if score < threshold: | |
| match_all = False | |
| break | |
| if match_all: | |
| common.add(col) | |
| return common | |
| def sortFiles(dfs): | |
| unique_dfs = [] | |
| seen = [] | |
| for i, df1 in enumerate(dfs): | |
| duplicate = False | |
| for j in seen: | |
| df2 = dfs[j] | |
| if df1.shape != df2.shape: | |
| continue | |
| if df1.reset_index(drop=True).equals(df2.reset_index(drop=True)): | |
| duplicate = True | |
| break | |
| if not duplicate: | |
| unique_dfs.append(df1) | |
| seen.append(i) | |
| return unique_dfs | |
| def load_csv_files(folder_path): | |
| csv_files = glob.glob(os.path.join(folder_path, "*.csv")) | |
| dfs = [] | |
| column_sets = [] | |
| paths = [] | |
| for file in csv_files: | |
| try: | |
| df = pd.read_csv(file) | |
| dfs.append(df) | |
| column_sets.append(list(df.columns)) | |
| paths.append(file) | |
| print(f"✅ Loaded: {os.path.basename(file)}") | |
| except Exception as e: | |
| print(f"❌ Failed to load {file}: {e}") | |
| return dfs, column_sets, paths | |
| def generate_mapping_prompt(column_sets): | |
| prompt = ( | |
| "You are a data scientist helping to merge multiple machine learning prediction datasets. " | |
| "Each CSV file may have different column names, even if they represent similar types of data. " | |
| "Your task is to identify and map these similar columns across datasets to a common, unified name. " | |
| "Columns with clearly similar features (e.g., 'Bedrooms' and 'BedroomsAbvGr') should be merged into one column with a relevant name like 'bedrooms'.\n\n" | |
| "Avoid keeping redundant or unique columns that do not have any logical counterpart in other datasets unless they are essential. " | |
| "The goal is not to maximize the number of columns or rows, but to create a clean, consistent dataset for training ML models.\n\n" | |
| "Examples:\n" | |
| "- Dataset1: 'Locality' -> Mumbai, Delhi\n" | |
| "- Dataset2: 'Places' -> Goa, Singapore\n" | |
| "→ Merge both into a common column like 'location'.\n\n" | |
| "Please also identify likely label or target columns that are typically used for prediction (e.g., price, sentiment, outcome, quality).\n\n" | |
| ) | |
| for i, cols in enumerate(column_sets): | |
| prompt += f"CSV {i+1}: {cols}\n" | |
| prompt += "\nPlease return:\n```python\ncolumn_mapping = { ... }\nlabel_columns = [ ... ]\n```" | |
| return prompt | |
| def get_column_mapping_from_openai(column_sets): | |
| load_dotenv() | |
| client = OpenAI( | |
| api_key=os.getenv("OPENAI_API_KEY"), | |
| base_url=os.getenv("OPENAI_API_BASE", "") | |
| ) | |
| prompt = generate_mapping_prompt(column_sets) | |
| response = client.chat.completions.create( | |
| model="gpt-4", | |
| messages=[ | |
| {"role": "system", "content": "You are a helpful data scientist."}, | |
| {"role": "user", "content": prompt} | |
| ], | |
| temperature=0.3 | |
| ) | |
| content = response.choices[0].message.content | |
| try: | |
| column_mapping_match = re.search(r"column_mapping\s*=\s*(\{.*?\})", content, re.DOTALL) | |
| label_columns_match = re.search(r"label_columns\s*=\s*(\[.*?\])", content, re.DOTALL) | |
| column_mapping = ast.literal_eval(column_mapping_match.group(1)) if column_mapping_match else {} | |
| label_columns = ast.literal_eval(label_columns_match.group(1)) if label_columns_match else [] | |
| except Exception as e: | |
| print("⚠️ Error parsing OpenAI response:") | |
| print(content) | |
| raise e | |
| return column_mapping, label_columns | |
| def clean_and_merge(folder, query=None, use_ai=True): | |
| os.makedirs("./final", exist_ok=True) | |
| dfs, column_sets, csv_paths = load_csv_files(folder) | |
| if not dfs: | |
| print("No valid CSVs found.") | |
| return | |
| dfs = sortFiles(dfs) | |
| dfs = [apply_manual_renaming(df, manual_rename_map) for df in dfs] | |
| if use_ai: | |
| try: | |
| column_mapping, label_columns = get_column_mapping_from_openai(column_sets) | |
| dfs = [df.rename(columns={col: column_mapping.get(col, col) for col in df.columns}) for df in dfs] | |
| except Exception as e: | |
| print("Falling back to fuzzy matching due to OpenAI error:", e) | |
| use_ai = False | |
| if not use_ai: | |
| # Normalize columns for fuzzy match fallback | |
| normalized_cols = [] | |
| for df in dfs: | |
| normalized_cols.append({normalize(col) for col in df.columns}) | |
| # Get best combination with fuzzy common columns | |
| max_common = set() | |
| best_combo = [] | |
| for i in range(2, len(dfs)+1): | |
| for combo in combinations(range(len(dfs)), i): | |
| selected = [normalized_cols[j] for j in combo] | |
| fuzzy_common = get_fuzzy_common_columns(selected) | |
| if len(fuzzy_common) >= len(max_common): | |
| max_common = fuzzy_common | |
| best_combo = combo | |
| # Harmonize and align | |
| aligned_dfs = [] | |
| for idx in best_combo: | |
| df = dfs[idx] | |
| col_map = {} | |
| for std_col in max_common: | |
| match, _, _ = process.extractOne(std_col, [normalize(col) for col in df.columns]) | |
| for col in df.columns: | |
| if normalize(col) == match: | |
| col_map[col] = std_col | |
| break | |
| df_subset = df[list(col_map.keys())].rename(columns=col_map) | |
| aligned_dfs.append(df_subset) | |
| combined_df = pd.concat(aligned_dfs, ignore_index=True) | |
| else: | |
| combined_df = pd.concat(dfs, ignore_index=True) | |
| # Label assignment fallback | |
| for i, df in enumerate(dfs): | |
| if 'label' not in df.columns: | |
| name = os.path.basename(csv_paths[i]).split(".")[0].lower() | |
| name_cleaned = name | |
| if query: | |
| words = set(re.sub(r'[^a-z]', ' ', query.lower()).split()) | |
| for word in words: | |
| name_cleaned = name_cleaned.replace(word, "") | |
| df['label'] = name_cleaned | |
| # Decide best final file | |
| largest_df = max(dfs, key=lambda df: len(df)) | |
| flag = False | |
| if len(largest_df) > len(combined_df) and len(largest_df.columns) > 2: | |
| flag = True | |
| elif len(combined_df) > len(largest_df) and (len(largest_df.columns) - len(combined_df.columns)) > 3 and len(largest_df.columns) < 7: | |
| flag = True | |
| output_file = f"./final/{query or os.path.basename(folder)}.csv" | |
| if flag: | |
| largest_df.to_csv(output_file, index=False) | |
| print(f"⚠️ Saved fallback single file due to poor merge: {output_file}") | |
| else: | |
| combined_df.to_csv(output_file, index=False) | |
| print(f"✅ Saved merged file: {output_file}") | |
| # Example usage: | |
| clean_and_merge("house", query="house", use_ai=True) | |
| # merge_csvs(folder_path) | |