# -*- coding: utf-8 -*-
"""
Created on Fri Aug 29 11:07:26 2025

@author: bh
"""
import numpy as np
import pandas as pd
import warnings

# Connecting Google Drive
#from google.colab import drive
#drive.mount("/content/drive/")



#Vise alle kolonnerne i stor datafram:
pd.set_option('display.max_columns', None)  

# Set display options to show all rows
pd.set_option('display.max_rows', None)

# Set the width of the display in characters
# This can sometimes influence wrapping, but the terminal width is still the primary limit
pd.set_option('display.width', 100) # Set to a value wider than your expected output

df=None

def fejlanalyse( df)  :
 
    print(f'\nNumber of problems in each column, out of {len(df)} values, rows' )
    missing_values_series = df.isna().sum()  # Calculate #missing values per column, to a dataserie
    #print(  missing_values_series  )
    missing_values_df = missing_values_series.reset_index() # Convert the Series to a DataFrame, Resetting the index turns the column names into a regular column
    
    percentages = []
    for count in missing_values_series:
        percentage = round(count / len(df) * 100,1)
        percentages.append(percentage)
   
    missing_values_df['%rows with Error'] = percentages
    missing_values_df.columns = ['Column Name', 'Missing Count','%rows with Error']
    print(missing_values_df)
    
    

    
    """
  
   
    print('\nFejl% for hver kolonne:' )   
    for col, percentage in (df.isna().sum() / df.shape[0] * 100).round(1).items():
       print(f'{col}: {percentage:.1f}')
  
    """
    
    # Identify rows with any missing values
    rows_with_nan = df[df.isnull().any(axis=1)]
    
    """
    rows_with_nan = df[df.isnull().any(axis=1)]
    df.isnull(): This creates a DataFrame of the same shape as your original DataFrame df, but with True where a value is missing (NaN) and False otherwise.
    .any(axis=1): This is applied to the boolean DataFrame from the previous step. axis=1 means it operates row by row. It returns a boolean Series where True indicates that there is at least one True (missing value) in that row, and False means there are no missing values in that row.
    """
    
    # Display the rows with missing values
    #display(rows_with_nan)
    #df.iloc[0].index[[0,0,1]].tolist() #giver listen ['InvoiceNo', 'InvoiceNo', 'StockCode'] for kolonnenavne i rækken=df.iloc[0].
    # Iterate through the identified rows and print the row index and columns with NaN values
    if verbose==True:
        print('\nList of row nr and field with None, no value(NaN)\nRow nr : [column name(s)]')
        for index, row in rows_with_nan.iterrows():
            nan_columns = row.index[row.isnull()].tolist()
            #print(f"Row {index:5d}: Missing values in columns: {nan_columns}")
            print(f" {index:6d}: {nan_columns}") 
    
    """.iterrows(): This is a method used to iterate over the rows of a Pandas DataFrame.
     For each row, it yields two values: the index of the row and the row data as a Series.
     row.isnull(): For the current row (which is a Series), this creates a boolean Series
     indicating which values in that row are missing, altså liste med kolonne-nr for isnull
    row.index[...]: This uses the boolean Series from the previous step to select the index labels (column names in this case) from the row.index. It essentially gives you the names of the columns that have missing values in the current row.
    .tolist(): This converts the resulting index of column names into a Python list and stores; [True, False, True] når rækken indeholder NaN i 1. og 3. kolonne 
    """
    print('\nNo of rows in the dataset: ',len(df))       #1000  for invoice_dirty.csv datasæt
    print('No of problems-rows: ',len(rows_with_nan))
    print('List of problem-rows, only first 20 shown:\n ',rows_with_nan.head(20))   
    return df, rows_with_nan 


def how_values_are_read():
    pass

"""
ren info:

  InvoiceNo StockCode  ... CustomerID         Country
0    536365     71053  ...    17850.0  United Kingdom
1       NaN       NaN  ...    17850.0  United Kingdom
2    536365    84406B  ...    17850.0  United Kingdom
3    536365    84029G  ...    17850.0  United Kingdom
4    536365    84029E  ...    17850.0  United Kingdom   
CustomerID  er heltalsværdier i filen, hvorfor fortolkes som  float ?
NaN is a float: In NumPy and pandas, NaN (Not a Number), which is used to represent missing values, is treated as a floating-point value.
Pandas needs a single dtype: When pandas reads a column from a file, it tries to determine a single data type that can accommodate all the values in that column.
Promotion to float: If a column is mostly integers but contains even one NaN or a value that cannot be parsed as an integer, pandas will read the entire column as a float64 to avoid losing the information about the missing or non-integer values.

Here's how the type inference, type-genkendelsen, generally works:

Scanning the column: Pandas reads through all the values in each column.
Finding a common type: It tries to find the most appropriate data type (like integer, float, boolean, datetime, or object) that can represent all the values in that 
column without losing information.
Promoting types: If a column contains mixed data types, pandas will often "promote" the column to a broader, super, type that can 
accommodate all the values. For example, if a column has both integers and strings, it will likely be inferred as object. 
If it has integers and NaNs, it will likely be inferred as float64.

  
You've hit a common point of confusion when working with 
pandas and integer types! Even though you specified 'int' or 'int64' for 'InvoiceNo', 
it's showing up as float64 because the column likely contains missing values (NaNs), og NaN opfattes som float
 
"""

    
def parse_dates_with_formats(date_series, formats):
    """
    Attempts to parse a pandas Series of date strings using a list of formats.
    Returns a Series of datetime objects, with errors coerced to NaT.
    """
    parsed_series = pd.Series(index=date_series.index, dtype='datetime64[ns]')
    print('formats:',formats)
    #print(parsed_series ) # i start alle NaT
    for fmt in formats:
        # Attempt to parse the remaining unparsed dates, som ikke er Nan, with the current format
        unparsed_mask = parsed_series.isna() #alle elementer har NaT første gang, så True for alle; for fmt1  afprøves alle 
        #dem der parses af fmt1 har ikke mere NaT, har værdi=False, de behandles ikke i fmt2 forsøget
        #print( unparsed_mask )
        try:
            parsed_series[unparsed_mask] = pd.to_datetime(   #kun dem der stadig har NaT forsøges at parses
                date_series[unparsed_mask],
                format=fmt,
                errors='coerce'
            )
        except Exception as e:
            # Handle potential errors during parsing with a specific format
            print(f"Warning: Could not parse with format '{fmt}'. Error: {e}")
            pass # Continue to the next format

    return parsed_series

    # Example usage:
    # formats_to_try = ['%d.%m.%Y', '%d.%m.%y'] # Your list of formats
    # df['YourDateColumn'] = parse_dates_with_formats(df['YourDateColumn'], formats_to_try)
    # første fmt give NaT for dem der ikke kan parses
    

 
    
def field_types_registration( df, coltypes  )   :
    """
    forsøger registere colonnerne efter typerne specifikt angivet i coltypes; strings registreres som ´object´
    
    If df.info() shows a column with a dtype of int64 and the non-null count is equal to the total number of entries in the DataFrame, 
    you can be confident that pandas has successfully recognized all values in that column as integers of the int64 type.
    
    """
    fmt1=datetimeformat
    fmt2=fmt1.replace('Y', 'y')
    #formats=['%Y-%d-%m', '%y-%d-%m']
    formats=[fmt1,fmt2]
    #print(formats)
    #coltypes ={'InvoiceDate':'datetime','InvoiceNo':'int','Quantity':'int32','UnitPrice':'float64','CustomerID':'int'}
    print('\nConverting column types to specified types:')
    
    for index, key in enumerate(coltypes):
        #print(index, key, coltypes[key] )  2 InvoiceDate datetime
        if coltypes[key] == 'datetime':
            try:
                #df[key] = pd.to_datetime(df[key], errors='coerce')
                #df[key] = pd.to_datetime(df['datetime'], format=datetimeformat, errors='coerce')  #year=YYYY
                df[key] = parse_dates_with_formats(df[key], formats)
                 
                #df['YourDateColumn'] = pd.to_datetime(df['datetime'], format='%Y-%m-%d', errors='coerce')  #year=YYYY
                #df['YourDateColumn'] = pd.to_datetime(df['datetime'], format='%y-%m-%d', errors='coerce')  #year=YY
                print(f"Converted column '{key}' to datetime" )
            except Exception as e:
                print(f"#########   Error converting column '{key}' to datetime: {e}")
    
        elif coltypes[key] in ['int', 'int32', 'int64', 'Int64']:
            try:
                # Attempt to convert to numeric first, then to integer
                df[key] = pd.to_numeric(df[key], errors='coerce')
                df[key] = df[key].astype(coltypes[key], errors='ignore') # Use errors='ignore' to keep NaNs
                df[key] = df[key].astype('Int64', errors='ignore') # Note the capital 'I'
                print(f"Converted column '{key}' to {coltypes[key]}.")
            except Exception as e:
                 print(f"#########   Error converting column '{key}' to numeric/int: {e}")
    
        elif coltypes[key] in ['float', 'float32', 'float64']:
             try:
                #df[key] = pd.to_numeric(df[key], errors='coerce')
                #df[key] = df[key].astype(coltypes[key], errors='ignore')
                df[key] = pd.to_numeric(df[key], errors='coerce')
                df[key] = df[key].astype(coltypes[key])
                print(f"Converted column '{key}' to {coltypes[key]}.")
             except Exception as e:
                print(f"#########   Error converting column '{key}' to numeric/float: {e}")
   
    # After the loop, check the dtypes again
    #print("\nData types after column type conversion (strings and unindtified are object):")
    #print(df.dtypes)
    return df

"""
df.head(3)
fejlanalyse()
df.iloc[141]  
InvoiceNo,StockCode,Description,Quantity,InvoiceDate,UnitPrice,CustomerID,Country
C536379,  D,Discount,            -1,01.12.2010,"27,5",14527,United Kingdom   

df.iloc[154]   # test af rækken med index 154
""" 


 

def automatic_type_determination():
    # virker ikke særligt godt, not used
    # Iterate through columns and attempt automatic type conversion
    for col in df.columns:
        print(f"Processing column: {col}")
        if df[col].dtype == 'object':
            # Attempt to convert to datetime first
            try:
                #df[col] = pd.to_datetime(df[col], errors='coerce')  #convert errors to NaT  not a timestamp
                df[col] = pd.to_datetime(df[col]) 
                print(f"Converted column '{col}' to datetime.")
            except:
                # If not datetime, check if it can be numeric
                try:
                    df[col] = pd.to_numeric(df[col])
                    print(f"Converted column '{col}' to numeric.")
                except:
                    # If still object, it's likely a string or mixed types
                    print(f"Column '{col}' remains as object (likely string or mixed types).")
        elif df[col].dtype == 'float64':
            # You can add logic here if you want to handle float columns specifically
            pass # Or do nothing if you want to keep them as float
    
        elif df[col].dtype == 'int64':
            # You can add logic here if you want to handle int columns specifically
            pass # Or do nothing if you want to keep them as int
    
    # After the loop, check the dtypes again
    print("\nData types after automatic conversion attempt:")
    print(df.dtypes)
    
    df.head(10)
    return df




def change_column_names( df)     :
    new_columns = {}
    for col in df.columns:
        new_col = col.lower().replace(' ', '_').replace('-', '_')
        new_columns[col] = new_col
    #print(new_columns)  #udskriver dette dictionary, bruger denne kopi nedenforlav kopi
    df=df.rename(columns=new_columns)
    
   
    # I want to set indx-column  id as the first column: 
    desired_column_order = list(new_columns.values())
    primary_field_lower =primary_field.lower()
    # Ensure 'id' is in the list before attempting to move it
    if  primary_field_lower in desired_column_order:
        # Remove 'id' from its current position
        desired_column_order.remove(primary_field_lower)
        if remove_primary_field==True:
            df = df.drop(columns=primary_field_lower)
            print(f"column with primary field '{primary_field}' has been removed\n" )
        else:
            # Insert primary_field at the beginning of the list
            desired_column_order.insert(0,primary_field_lower)  #fjerner id-feltet permanent: 
    else:
        print(f"Warning: '{primary_field}' not found in the list of column values.")
    # Now you can use this reordered list for selecting and ordering columns in your DataFrame:
    df = df[desired_column_order]
      
    
    """
    Index(['invoiceno', 'stockcode', 'description', 'quantity', 'invoicedate',
           'unitprice', 'customerid', 'country', 'id', 'notes'],
          dtype='object')
 
    # Corrected renaming dictionary with keys matching the actual column names
    new_columns={'ID': 'id', # Assuming 'ID' is the column you added earlier
               'InvoiceNo': 'invoice_no',
               'StockCode': 'stock_code',
               'Description': 'description',
               'Quantity': 'quantity',
               'InvoiceDate': 'invoice_date',
               'UnitPrice': 'unit_price',
               'CustomerID': 'customer_id',
               'Country': 'country',
               'notes': 'notes'} # Assuming 'notes' column was added with this name
    """
 
    """
    # Now, get the list of new column names in your desired order from the dictionary keys
    desired_column_order = list(new_columns.values())  #ok
    
    print("Content of desired_column_order BEFORE reindexing:")
    print(desired_column_order)
    
    # Reindex the DataFrame with the desired column order
    df = df[desired_column_order]
    print("\nColumns in the DataFrame after reindexing:")
    print(df.columns.tolist())  #not in desired_column_order 
    
    # Now, display the head again
    display(df.head())
    df.head()  #not  in desired_column_order ?
    df[df['id']==45] # a check
    """
    
    return df
    
 
    


def correct_other_problems(df, strings_columns,  num_columns )  :
    #strings_columns =['description']
    #strings_columns =['text_column']
       
    # rens string kolonner:
    # Choose your replacement character (e.g., a space or an underscore)
    replacement_char = '_' # Or ' ' or ''
    
    # Replace semicolons in the 'text_column'
    # Use .str accessor for string operations
    # Use fillna('') first to treat potential NaNs as empty strings during replacement
    #test_df['text_column'] = test_df['text_column'].fillna('').str.replace(';', replacement_char)
    for col in strings_columns:
        df[col] = remove_spaces( df[col])   
        #df[col] = df[col].fillna('').str.replace(';', replacement_char)
        df[col] = df[col].str.replace(';', replacement_char)
       
    # rens numeriske kolonner, erstatter , med . i talværdierne:
    #num_columns =['price']
    for col in  num_columns:
        df[col] =  df[col].astype(str).str.replace(',', '.', regex=False)
        
    return df
    
    """testing:
    
    for col in strings_columns:
        df[col] = remove_spaces(df[col])  
    
    # Replace the comma with a period in the 'UnitPrice' column
    # Ensure the column is treated as strings first, in case there are already numbers
    df['UnitPrice'] = df['UnitPrice'].astype(str).str.replace(',', '.', regex=False)

    # Convert the 'UnitPrice' column to numeric (float), coercing errors to NaN
    df['UnitPrice'] = pd.to_numeric(df['UnitPrice'], errors='coerce')
    
    InvoiceNo,StockCode,Description,Quantity,InvoiceDate,UnitPrice,CustomerID,Country
    536365,71053,WHITE METAL LANTERN,6,01.12.2010,"3,39",17850,United Kingdom
    ,,,6,,"2,55",17850,United Kingdom
    536365,84406B,CREAM CUPID HEARTS COAT HANGER,8,01.12.2010,"2,75",17850,United Kingdom  
   
    # Replace NaN values in 'Country Code' with 'Unknown'
    df['Country'].fillna('Unknown', inplace=True)
    #df['sub-region'].fillna('Unknown', inplace=True)
    
    # Check the number of missing values in 'Country Code' after filling
    print("\nNaN count in 'Country Code' after filling:")
    print(df['Country'].isna().sum())
    """


def guess_column_types(df, num_rows_to_examine=5) :
    """
    Attempts to guess the data type of each column based on the first few rows.

    Args:
        df: The pandas DataFrame to analyze.
        num_rows_to_examine: The number of rows to examine for type guessing.
    """
    global strings_columns, num_columns, datetime_columns, coltypes 
    strings_columns =[]
    num_columns =[]
    datetime_columns =[]
    coltypes ={}
    print(f"\nAttempting to guess column types based on the first {num_rows_to_examine} rows:")
    for col in df.columns:
      # Get the data from the first few rows of the column
      sample_data = df[col].head(num_rows_to_examine)
      
      # Check for numeric first
      try:
          sample_data_str = sample_data.astype(str).str.replace(',', '.', regex=False) 
          # Attempt to convert to numeric, errors='coerce' will turn unparseable values into NaN
          numeric_sample = pd.to_numeric(sample_data_str, errors='coerce')
          #print(numeric_sample, type( numeric_sample ) )
          #print(numeric_sample.notna().sum() / num_rows_to_examine)
          # Check if a significant portion were successfully converted (not NaN)
          if numeric_sample.notna().sum() / num_rows_to_examine > 0.7: 
               # Further check if it looks like an integer column (handle NaNs in check)
               # Check if the non-NaN values are equal to their integer conversion
               is_integer_like = numeric_sample[numeric_sample.notna()].apply(lambda x: x == int(x) if pd.notna(x) else False) #for non NaN sums( antal rækker med x=int(x))
               if is_integer_like.mean() > 0.7: # Check proportion of integer-like values among non-NaNs
                    print(f"Column '{col}': Guessed as integer")
                    coltypes[col]='int64'  
               else:
                    print(f"Column '{col}': Guessed as numeric (float/mixed numeric)")
                    coltypes[col]='float64' 
                    
               num_columns.append(col)
              
               continue # Move to the next column if guessed as numeric

    
             
              

      except:
          pass # Not a purely numeric column
          
      # Store the current warning filters
      original_filters = warnings.filters[:]
      #print(  'original_filters ', original_filters )
      
      # Check for datetime, kræver format string angives
      try:
          # Temporarily ignore UserWarnings
          warnings.filterwarnings('ignore', category=UserWarning)
          # Attempt to convert to datetime, errors='coerce' will turn unparseable dates into NaT
          datetime_sample = pd.to_datetime(sample_data, errors='coerce')
          # Check if a significant portion were successfully converted (not NaT)
          if datetime_sample.notna().sum() / num_rows_to_examine > 0.7:
               print(f"Column '{col}': Guessed as datetime")
               coltypes[col]='datetime' 
               datetime_columns.append(col)
               continue # Move to the next column if guessed as datetime
      except:
          pass # Not a datetime column
      
      finally:
          # Restore the original warning filters
          warnings.filters = original_filters

      # If not numeric or datetime, guess as string (object in pandas)
      print(f"Column '{col}': Guessed as string (object)")
      strings_columns.append(col)



# Example usage:
# Assuming 'df' is your DataFrame
# guess_column_types(df)

 
   
def read_data(fn='dirty_data.csv', primary_field_ ='',sep=';')  :
    global  primary_field, datetimeformat
    
 
    sourcefile = PATH+'lasvegasnevada-gov-web-site-analytics.csv'
    sourcefile = PATH+'invoices_dirty.csv'
    sourcefile = PATH+fn
    #sourcedir ='url'
    sourcedir ='local'
    
    loop=True
    quotechar="'"
    while(loop):
      if  sourcedir=='url':
          url = 'http://bjarnephys.com/Python/dirty_data.csv'  # Replace with the actual URL of your file
          try:
              # Removed on_bad_lines='skip' to see parsing errors
              # Changed to on_bad_lines='warn' to see warnings
              # Added quotechar='"' to handle quoted fields
              df = pd.read_csv(url, quotechar=quotechar, sep=sep, on_bad_lines='warn')
              print("DataFrame successfully read from URL:")
              display(df.head())
          except Exception as e:
              print(f"Error reading data from URL: {e}")
              # Handle read error: maybe set df to None or print a message and continue
              df = None # Set df to None if reading fails
      else:
          try:
              # Use the current 'sep' value for reading
              # Removed on_bad_lines='skip' to see parsing errors
              # Changed to on_bad_lines='warn' to see warnings
              # Added quotechar='"' to handle quoted fields
              df = pd.read_csv(sourcefile, engine='python', quotechar=quotechar, sep=sep, on_bad_lines='warn')
              
          except Exception as e:
              print(f"Error reading CSV with separator '{sep}': {e}")
              # Handle read error
              df = None # Set df to None if reading fails

      # Check if DataFrame was read successfully before printing info
      if df is not None:
          print('\nInfo about input dataset\nNo of rows/data-lines:',len(df))
          if len(df)<30:
              print(df)
          else:
              print('\nFirst 5 rows:\n',df.head(),'\n')

          print(f"\nCurrently using value-separator: '{sep}'")
      else:
          print(f"\nCould not read file with separator '{sep}'. Please try a different separator.")


      sep_ = input("Choose another separator (e.g., ',' or ';') - press <Enter> to accept current data read: ")

      # If user presses Enter (empty string), break the loop
      if sep_ == '':
          loop = False
      # If user enters a new separator, update 'sep' and continue the loop
      elif sep_ != sep:
          sep = sep_
          print(f"Trying with new separator: '{sep}'")
      # If user enters the same separator, do nothing and the loop will continue to re-read with the same separator
      else:
           print(f"Separator remains: '{sep}'")

    # After the loop, df will hold the successfully read DataFrame (or None if all attempts failed)
    if df is None:
        print("\nFailed to read data after multiple attempts.")

  
    # Get input from the user for columns to discard
    columns_to_discard_str = input("\nPlease write the names of columns to be discarded in data input, comma separated, <Enter> for no column to discard: ")
    #columns_to_discard_str='notes'
    # Split the input string by comma and strip whitespace from each column name
    columns_to_discard = [col.strip() for col in columns_to_discard_str.split(',')]
    
    # Check if the columns exist in the DataFrame before dropping
    columns_to_drop = [col for col in columns_to_discard if col in df.columns]
    columns_not_found = [col for col in columns_to_discard if col not in df.columns]
    
    if columns_not_found:
        print(f"\nWarning: The following columns were not found in the DataFrame and could not be dropped: {columns_not_found}")
    
    # Drop the specified columns from the DataFrame
    if columns_to_drop:
        df = df.drop(columns=columns_to_drop)
        print(f"Dropped columns: {columns_to_drop}")
    else:
        print("No valid columns were specified for dropping.")
    


    guess_column_types(df, num_rows_to_examine=min(10,len(df)))
    print('String-columns:', strings_columns)
    print('Nummerical-columns:', num_columns)
    print('Coltypes:', coltypes)
  
    datetimeformat = input("\nPlease write the datetime format. Examples:\n"
                   "- For dates like 25.12.2023: %d.%m.%Y\n"
                   "- For dates like 2023-12-25: %Y-%m-%d  -this is the default choice, press <Enter> for this\n"
                   "- For timestamps like 25/Dec/2023 14:30:00: %d/%b/%Y %H:%M:%S\n"
                   "Enter format: ")
    
    if len(datetimeformat)<5:
            datetimeformat='%Y-%m-%d'
    fmt1=datetimeformat
    fmt2=fmt1.replace('Y', 'y')
    #formats=['%Y-%d-%m', '%y-%d-%m']
    formats=[fmt1,fmt2]
    print('\nDatetimeformat to be used:',formats)
    print('( %Y stands for year with 4 ciffers, %y for year with 2 )')
    
    
    primary_field= input("\nPlease write a column name, which can be used as primary key, with unique values; press <Enter> for use a automatic generated column: ")
    
    if primary_field=='':
        df['ID'] = np.arange(len(df))  #indfører indx kolonne, explicit, findes nemlig ikke i datafiler, ellers brug den i filen:
        primary_field='ID'  
    print('\nColumn-name for primary key values, with unique values:',primary_field )
    # df['ID'] = df[indx]    #hvor indx er navnet på kolonne, der kan bruges som primary index i datasættet

    """
    print('Column names and type:')
    for col in df.columns:
        print(f"Column: {col}, Data Type: {df.dtypes[col]}")
        if df.dtypes[col] in ['float64','float32']:
            print(f"  Column {col} has a float data type.")
        if df.dtypes[col] in ['int64','int32','Int64']:
            print(f"  Column {col} has a int data type.")
        print(f"3 først rækker:'{df[col].iloc[0]}', '{df[col].iloc[1]}', '{df[col].iloc[2]}'\n")
     
    df2=df.copy()  # sikkerhedskopi
    
    """        

  
    return df


def remove_spaces(ds)  :
    # Trim leading and trailing whitespace from the 'text_column', 1 eller flere ' ' erstates af 1 '' [for interne spaces, strip() tager de 'ydre']
    ds = ds.str.replace(' +', ' ', regex=True).str.strip() 
    return ds


def delete_duplicated_rows(df,columns_to_consider):
    """ see explaination in delete_duplicated_rows2()
    Checks for and removes duplicate rows from a DataFrame, comparing only values in columns_to_consider column, 
    we have to exclude a unique row index column, otherwise no rows are inditical!
    
    Returns the DataFrame with duplicates removed.
    """
    
    # Checking for duplicates in data, considering only specified columns
    duplicate_rows_mask = df.duplicated(subset=columns_to_consider)
    dupl_rows_count = duplicate_rows_mask.sum()
    print(f'\nNumber of duplicate rows found (excluding the first occurrence: {dupl_rows_count}')
    print('( Only columns in subset',columns_to_consider,'are considered )')
    # Get the number of rows before removing duplicates
    rows_before = len(df)
    print(f"Number of rows before removing duplicates: {rows_before}")

    # Remove duplicate rows, keeping the first occurrence
    df_cleaned = df.drop_duplicates(subset=columns_to_consider)

    # Get the number of rows after removing duplicates
    rows_after = len(df_cleaned)
    print(f"Number of rows after removing duplicates: {rows_after}")

    return df_cleaned





def drop_problem_rows(df, KeepID=[]):
    """
    Drops rows with any NaN values unless their 'original_id' is in the KeepID list.
    Assumes 'original_id' column exists in the DataFrame.
    Returns the DataFrame with problematic rows (not in KeepID) dropped.
    """
    #print( 'KeepID=',KeepID )
    df['notes'] = ''
    # Assuming df is your DataFrame and 'ID' is the primary key column
    # Identify rows with any missing values using a boolean mask
    rows_with_nan_mask = df.isna().any(axis=1)
    #print(  'rows_with_nan_mask\n',rows_with_nan_mask) #series of True,False, True if the row contians NaN
    
    # Filter the DataFrame using the mask and select the 'ID' column
    # Then convert the resulting Series of IDs to a Python list
    ids_of_problem_rows = df[rows_with_nan_mask]['ID'].tolist()
    
    # Now you have a list of IDs for rows with NaNs
    #print("IDs of rows with missing values:", ids_of_problem_rows)
    
    # You can then use this list to mark the 'notes' column
    df.loc[df['ID'].isin(ids_of_problem_rows), 'notes'] = 'Check manually'
    
    #rows_to_drop_mask = rows_with_nan & (~rows_to_keep_mask)
    # Create a boolean mask for rows to keep based on the KeepID list and 'original_id' column
    #rows_to_keep_mask = df['ID'].isin(KeepID)
    
    # This means we drop rows that have NaNs AND are NOT in the KeepID list
    #rows_to_drop_mask = rows_with_nan & (~rows_to_keep_mask)
    rows_to_drop_mask = rows_with_nan_mask
    if len(KeepID)==len(df):
          df_cleaned_with_keep =df  #all er kept
    else:
        df_cleaned_with_keep = df[~rows_to_drop_mask].copy()
    """
    
    # Create a boolean mask for rows to keep based on the KeepID list and 'original_id' column
    rows_to_keep_mask = df[primary_field].isin(KeepID)
   
    #rows_to_keep_mask[rows_to_keep_mask==True]
    # Combine the masks: keep rows that are NOT in rows_with_nan OR are in rows_to_keep_mask
    # This means we drop rows that have NaNs AND are NOT in the KeepID list
    rows_to_drop_mask = rows_with_nan & (~rows_to_keep_mask)
    """
    # Drop the identified rows
   

    # The code after the return statement will not be executed.
    # print("Original DataFrame info:")
    # df.info()
    #
    # print("\nCleaned DataFrame info (keeping specified rows with NaNs):")
    # df_cleaned_with_keep.info()
    #
    # print("\nRows that would have been dropped but were kept:")
    # display(df_cleaned_with_keep[df_cleaned_with_keep['original_id'].isin(KeepID) & rows_with_nan]) # Use 'original_id' here

    return df_cleaned_with_keep


   
    
 
def save_file(df, destination_file='') :  
    import shutil
    import os
    global fn
    print('\n\n-----------------------------------Saving file: -----------------------------------------\n')
    if len(destination_file)==0: 
        #print('Destination_file not defined' )
        destination_file=PATH+fn[0:-4]+'_cleaned.csv'  #fjerner  
    ################################### Save the merged_df DataFrame to a CSV file
    # index=False prevents pandas from writing the DataFrame index as a column in the CSV
    # først gemmes kopi af oprindelige fil
    
    #print('Destination_file: ',destination_file )
    
    # Define the source and destination file paths
    # Replace 'source_file.txt' and 'destination_file.txt' with your actual file names and paths
    #source_file = '/content/source_file.txt'

    bakup = fn+'.bak'
    source_file = PATH+fn
    print('\nStatus of problem(s) in the dataset, to be saved:\n')
    print(df.info())
    df , rows_with_nan = fejlanalyse(df) 
    if len(df)<30:
        print('\nThe complete dataset:\n',df)
    else:
        print('\nFirst 5 rows:\n',df.head(),'\n')
 
    print('\nNotice, a notes-column has been added, to be used in a manual data-inspection in Excel\n')
    """
    try:
        # Copy the file
        shutil.copyfile(source_file,bakup )
        print(f"Bakup file created: '{bakup}'")
    except FileNotFoundError:
        print(f"Error: The source file '{source_file}' was not found.")
    except Exception as e:
        print(f"An error occurred while copying the file: {e}")
    """    
    #destination_file=PATH+'test.csv'
    df.to_csv(destination_file, index=False, decimal=',', sep=';') #kan indlæses i Excel, evt. problemer med decimaltegn kan måske løses med ',' ->  '.'
    print(f"\nDataFrame successfully saved to: {destination_file}")
    """
    ####################################   Download file from Google-drive
    file_to_download = fn
    print( fn  )
    try:
      files.download(file_to_download)
      print(f"Downloading {file_to_download}...")
    except Exception as e:
      print(f"Error downloading file: {e}")
    """

df=None
fn=None
df2=None
primary_field =None
verbose = False
remove_primary_field = True
datetimeformat =''
def clean_data(fn_='dirty_data.csv',sep=',')  :
    pass
    global fn, PATH,strings_columns,num_columns,coltypes
    fn=fn_
    # Connecting Google Drive
    #from google.colab import drive
    #drive.mount("/content/drive/");  PATH =r'/content/drive/MyDrive/dataset'
    PATH =r'C:\Users\bh\Documents\efteruddannelse, kurser\Kodree-data\\'
    #skal ikke kaldes externt, kopierer linier ind i cmd prompt
  

    #fn='dirty_data_cleaned.csv'
    df = read_data( fn=fn, primary_field_ ='',sep=sep ) #læser fra datafilen
    
  
    df , rows_with_nan =fejlanalyse(df) 

    
    df.columns
    
    #manual angivlse af column typer:
    #strings_columns =['Text_column']
    #num_columns =['price','CustomerID']
    
    # fjerner overflødige spaces, ';'  fra strings_columns, vil nemlig bruge ';' som felt-seperator i datafilen
    # erstatter ',' med '.' i num_columns
    df=correct_other_problems(df, strings_columns,   num_columns ) 
    #print('\nFirst 5 rows after correct_other_problems()')
    #print(df.head() )
    #coltypes ={'price':'float64','CustomerID':'int64'}    
    df = field_types_registration(df,  coltypes) #giver hurtig besked om problemer, kan introducere fejl 
 
    #split datatime kolonner op i date og time kolonner
    for col in  datetime_columns:
        col_name=col.lower().split('date')[0]
        df[col_name+'_date'] = df[col].dt.date
        df[col_name+'_time'] = df[col].dt.time  #begge vises som oject i typeoversigt
        #df = df.drop(columns=[col])
        
    print('\nEach datetime-columns are splitup into date and time columns, both are marked as object-type' )
    print(df.info())  # none , how ?
    
    print('\n\nAnalysis of problems after correction of redundant spaces in text fields, errors in numerical fields:' )
    df , rows_with_nan = fejlanalyse(df) 
   
    
    
   
    # Get all column names except indx -kolonnen 
    #primary_field ='ID'
   
    #print( 'primary_field ',primary_field )
    columns_to_consider = df.columns.tolist()
    if primary_field in columns_to_consider:
        columns_to_consider.remove(primary_field)
    else:
        print("Warning: '",primary_field,"' column not found. Checking for duplicates across all columns.")
       

    #rows with all the same values in columns_to_consider are deleted:
        
    df = delete_duplicated_rows(df,columns_to_consider)
    print('\nProblem analysis after removing duplicated rows:' )
    df , rows_with_nan = fejlanalyse(df) 
 
   
    """If you want a clean, sequential 0-based integer index for your final DataFrame after all the cleaning, then resetting the index is a good step,
    df = df.reset_index(drop=False)
    
    If you are comfortable with the existing index (which might have gaps after dropping rows), you can skip this step.
    You are correct that when you use df.reset_index(drop=True), it discards the original index. Since your 'ID' column was created from the original index before dropping rows, and the index was updated after dropping duplicates, the 'ID' column effectively became the index (or was intended to represent the original index). Using drop=True removes this index.
    """
    
    #list of rows to be kept, despite NaN values
    KeepID = [45,939]    
    KeepID = [1]
    #print(f"\nDropping problem rows, keeps rows with primary field '{primary_field}':", KeepID)
    print(f"\nDropping problem rows. You can choose to keep these for later manual inspection in Excel,\n(these rows will have a mark in a notes-column)")
          
    #Excelkeep rows with primary field '{primary_field}':", KeepID)
    drop_problem_rows_choice = input("\n -Press <Enter> to keep all problem rows"
                   "  \n -Write none to delete all these rows:")
    
    original_len=len(df)
    if drop_problem_rows_choice.lower()=='none':
        
        KeepID=[] 
    else: 
        KeepID =df[primary_field].tolist()   
    # TypeError: 'str' object is not callable
    
    df=drop_problem_rows(df, KeepID)  
    if drop_problem_rows_choice=='none':
        del_rows = original_len-len(df)
        print(f"\n{del_rows} rows have been deleted")
    
    """
    print(len(df) )  #963       should be 977-(16-2)=963, as 16 rows with NaN, but i dont drop 2 from the 977
    print( df[df['ID']==45  ] )  #in set
    print( df[df['ID']==939  ] )  #in set 
    print( df[df['ID']==66  ] )  #deleted, ok
    print(len(df))  #963, ok 
    """
    #df = df.dropna()  #fjerner groft rækker med fejl, hvis %fejl lille
    print('\nProblem analysis after deleting some problem rows:' )
    df , rows_with_nan = fejlanalyse(df) 

  
    print('\nFirst 5 rows after column corrections, dropping some problem rows:')
    print(df.head() )
    print('Notice, a notes-column has been added, to be used in a manual data-inspection in Excel\n')
    
    df2=df
    df=change_column_names(df) # small letters, sanake_case
    print('Column names are corrected to only small letters, - replaced by _')
    df.columns
    #rename eventually column names
    new_columns={ 'customerid':'customer_id' }  # as { 'old_name':'new_name',..}
    df=df.rename(columns=new_columns)
   
    destination_file ='' # hvis tom laves ny fil med ende _cleaned.csv 
    save_file(df,destination_file )



#%% test-area    
def remove_column_corrections_test()  :
    global PATH
    
    import time
   
    text =['','  leading; space', 'trailing space  ', '\t\tspaces   ', '   spaces  ','  many     spaces   ',' many     spaces']
    price =[]
    customerID =[]
    dates=['2022-01-01',np.nan,'22-01-02','2022-01-03','2022-01-03','2022-01-04','2022-01-05'] #genkedes af guess_type
    #dates=['01-01-2022',np.nan,'02-01-22','03-01-2022','03-01-2022','04-01-2022','05-01-2022']  #genkedes af guess_type, men dd mm er uklart 
    print( text)
    for i in range(0,len(text)):
        price.append(str(i+0.5))
        customerID.append(str(i))
        
    price[0]=0
    price[1]=np.nan 
    price[2]=2.0 
    price[3]='3,0' 
    price[4]='3,0' 
    customerID[0] =np.nan 
    customerID[3]= customerID[4]=3  
    types=['text','float','int','date=%d-%m-%Y']
    
    print(texts, price, customerID )
    
    data = {
            'Text_column':text ,
            'price':price,
            'CustomerID':customerID,
            'datetime':dates
            }
    df = pd.DataFrame(data)
    print(df)
    
    guess_column_types(df, num_rows_to_examine=min(10,len(df)))
    print('string-columns:', strings_columns)
    print('nummerical-columns:', num_columns)
    print('coltypes:', coltypes)
    print('')
    
    data2={'ID':[1,2,3]}
    df2= pd.DataFrame(data2)
    print(df2)
    
    
  
    df.to_csv(PATH+'dirty_data.csv', index=False, decimal=',', sep=';') #kan indlæses i Excel, evt. problemer med decimaltegn kan måske løses med ',' ->  '.'
 
    df['dateime'].pd
    df.info()
    df['dateime'].max_date - df['dateime'].min_date
    time_difference = df['datetime'].max() - df['datetime'].min()
    print(time_difference)
    # Assuming your CSV file has a column named 'InvoiceDate' with dates like '01.12.2010'
    df = pd.read_csv(
        'your_data.csv',
        parse_dates=['InvoiceDate'],
        date_format='%d.%m.%Y' # Specify the format: DD.MM.YYYY
    )
    df['YourDateColumn'] = pd.to_datetime(
        df['YourDateColumn'],
        format=['%d.%m.%Y', '%d.%m.%y'], # Try YYYY first, then yy
        errors='coerce'
    )
    
    df.columns[3]
    types[3]
    format_ =types[3][5:]
    format_ =types[3][5:].split('-')
    format_str =''
    for f in format_:
        format_str+='%'+f[1]
    format_str   
    df['YourDateColumn'] = pd.to_datetime(df['YourDateColumn'], format='%d.%m.%Y %H:%M:%S', errors='coerce') #errors='coerce' -> laver NaT=time None hvis fejl
    #if your timestamp looks like '01.12.2010 14:30:00', the format code would be '%d.%m.%Y %H:%M:%S'.
    #df['YourDateColumn'] = pd.to_datetime(df['datetime'], format='%d-%m-%Y %H:%M:%S', errors='coerce') #alle fejl
    df['YourDateColumn'] = pd.to_datetime(df['datetime'], format='%Y-%m-%d', errors='coerce')  #ok
    df['YourDateColumn'] = pd.to_datetime(df['datetime'], format='%y-%m-%d', errors='coerce')  #ok
    df['YourDateColumn'] = pd.to_datetime(df['datetime'], format='%Y-%m-%d %H:%M:%S', errors='coerce')  #alle med fejl
    
    t1 = time.time()
    df['YourDateColumn'] = pd.to_datetime(df['datetime'], format=format_, errors='coerce') # '01-02-22' ->NaT
    
    t2 = time.time()
    print(f"dt: {1000*(t2-t1):.0f} ms")  #28ms/7 -> 4ms/stk  , 1ms/stk -> 1E6*0.001/60=17min per 1M
    df['YourDateColumn'] #
  
    
    t1 = time.time()
    df[['day', 'month', 'year']] = df['datetime'].str.split('-', expand=True)   #vektoriseret,hurigt opsplit
    t2 = time.time()
    print(f"dt: {1000*(t2-t1):.0f} ms")  #14ms/7 -> 2ms/stk  
    df
    
    t1 = time.time()
    df[['day', 'month', 'year']] = df['datetime'].str.split('-', expand=True).astype(int) #fejler når NaN findes
    t2 = time.time()
    print(f"dt: {1000*(t2-t1):.0f} ms")  #14ms/7 -> 2ms/stk  
    df
    
    t1 = time.time()
    df[['day', 'month', 'year']] = df['datetime'].str.split('-', expand=True).astype('Int32') #ok også med NaN
    t2 = time.time()
    print(f"dt: {1000*(t2-t1):.0f} ms")  #18ms  /7 -> 2ms/stk  
    df

    
    #opsplit til int subdele
    t1 = time.time()
    df[['day', 'month', 'year']] = df['datetime'].str.split('-', expand=True)   
    df['day'] = pd.to_numeric(df['day'], errors='coerce').astype('Int32')      # tillader NaN!
    df['month'] = pd.to_numeric(df['month'], errors='coerce').astype('Int32')
    df['year'] = pd.to_numeric(df['year'] , errors='coerce').astype('Int32')

    t2 = time.time()
    print(f"dt: {1000*(t2-t1):.0f} ms")  #10ms /7 , overraskende hurtigere in inline beregningen ovenfor
    df



    df.info()
    
    df['day'] = pd.to_numeric(split_cols['day_str'], errors='coerce')
    df['month'] = pd.to_numeric(split_cols['month_str'], errors='coerce')
    df['year'] = pd.to_numeric(split_cols['year_str'], errors='coerce')
    
    """
    expand=False (default): When expand=False, str.split() returns a pandas Series where each element is a list of the split strings. It keeps the result within the structure of a single Series. This is useful if you want to work with the lists of split parts within a single Series.

    # Example with expand=False
    s = pd.Series(['a-b-c', 'd-e-f'])
    split_series = s.str.split('-', expand=False)
    print(split_series)
    # Output:
    # 0    [a, b, c]
    # 1    [d, e, f]
    # dtype: object
    expand=True: When expand=True, str.split() returns a pandas DataFrame where each split part becomes a separate column. This is specifically designed for cases like yours where you want to create new columns directly from the split parts.
    
    # Example with expand=True
    s = pd.Series(['a-b-c', 'd-e-f'])
    split_df = s.str.split('-', expand=True)
    print(split_df)
    # Output:
    #    0  1  2
    # 0  a  b  c
    # 1  d  e  f
    """
    t1 = time.time()
    #df[['day', 'month', 'year']] = df['datetime'].str.split('-', expand=True)   #vektoriseret,hurigt opsplit
    
    df[['day', 'month', 'year']] = df['datetime'].str.split('-', expand=False)   #vektoriseret,hurigt opsplit
    
    t2 = time.time()
    print(f"dt: {1000*(t2-t1):.0f} ms")  #14ms/7 -> 2ms/stk
    df
    df_test['Year_Numeric'] = pd.to_numeric(df_test['Year_Col'], errors='coerce')
    
   
    format=['%d.%m.%Y', '%d.%m.%y']
    # Create a test DataFrame with mixed year formats (as strings)
    data = {'Year_Col': ['00', '05', '10', '98', '99', '1995', '2023', 'invalid']}
    df_test = pd.DataFrame(data)
    
   
    fmt1='%Y-%d-%m'
    fmt2=fmt1.replace('Y', 'y')
    #formats=['%Y-%d-%m', '%y-%d-%m']
    formats=[fmt1,fmt2]
    print(formats)
    t1 = time.time()
    df['YourDateColumn'] = parse_dates_with_formats(df['datetime'], formats)
    t2 = time.time()
    print(f"dt: {1000*(t2-t1):.0f} ms")  #190ms/7 -> 11ms/stk

    format_=['%d-%m-%Y']  
    t1 = time.time()
    df['YourDateColumn'] = parse_dates_with_formats(df['datetime'], format_)
    t2 = time.time()
    print(f"dt: {1000*(t2-t1):.0f} ms")  #19ms/7 -> 3ms/stk
    df

    
    print("Original DataFrame:")
    display(df_test)
    t1 = time.time()
    # Convert the column to numeric, coercing errors
    df_test['Year_Numeric'] = pd.to_numeric(df_test['Year_Col'], errors='coerce')
    
    # Define the threshold for 2-digit year interpretation (e.g., 99)
    # Years > this threshold will be considered in the 1900s if they are 2-digit
    two_digit_year_threshold = 50  
    
    # Use numpy.where for vectorized conditional logic to convert 2-digit years
    df_test['Year_YYYY'] = np.where(
        # Condition: Check if the numeric year is not NaN AND
        # if it's a 2-digit year (less than 100, assuming positive years)
        df_test['Year_Numeric'].notna() & (df_test['Year_Numeric'] < 100),
        # Value if condition is True (2-digit year):
        # If 2-digit year is <= threshold, add 1900, otherwise add 2000
        np.where(
            df_test['Year_Numeric'] > two_digit_year_threshold,
            df_test['Year_Numeric'] + 1900,
            df_test['Year_Numeric'] + 2000
        ),
        # Value if condition is False (already 4-digit or NaN):
        # Keep the original numeric year (which will be NaN for invalid entries)
        df_test['Year_Numeric']
    )
        
    t2 = time.time()
    print(f"dt: {1000*(t2-t1):.0f} ms")  # 6ms for 7 -> 1ms/stk, 1e6 -> 1E6*0.001/60=17min, men skal kun kører een gang!
    print(df_test)
    
    #selvom du kan splitte strenge vektoriseret, er den mest performante metode til at håndtere blandede datoformater ved parsing i pandas typisk at bruge pd.to_datetime med parameteren format sat til en liste af mulige formatkoder.
    guess_column_types(df, num_rows_to_examine=min(10,len(df)))
    print('string-columns:', strings_columns)
    print('nummerical-columns:', num_columns)
    print('coltypes:', coltypes)
    print('')
    
    df.to_csv(PATH+'dirty_data.csv', index=False)

    
    # rens string kolonner:
    strings_columns =[df.columns[0]]
    for col in strings_columns:
        df[col] = remove_spaces( test_df[col])   
        
    for s in df[df.columns[0]]:
        print(f"'{s}'")
    
    
    # rens numeriske kolonner:
    num_columns =[df.columns[1],df.columns[2]]
    for col in  num_columns:
        df[col] =  df[col].astype(str).str.replace(',', '.', regex=False)
        
    
    df

    
    
    sample_df['Text_column'] = remove_spaces(sample_df['text_column'] )
    for s in sample_df['text_column']:
        print(f"'{s}'")
        
    """
    # Let's create a sample DataFrame to demonstrate
    data = {'text_column': ['  leading space', 'trailing space  ', '\t\tspaces   ', '  both spaces  ','  many     spaces   ']}
    sample_df = pd.DataFrame(data)
    
    print("Original DataFrame:")
    for s in sample_df['text_column']:
        print(f"'{s}'")
    
    # Use str.replace with regex=True to replace multiple spaces with a single space
    sample_df['text_column'] = sample_df['text_column'].str.replace(' +', ' ', regex=True).str.strip()
    
    
    # Trim leading and trailing whitespace from the 'text_column'
    #sample_df['text_column'] = sample_df['text_column'].str.replace(' +', ' ').replace('+ ', ' ').strip()
    
    print("\nDataFrame after trimming:")
    for s in sample_df['text_column']:
        print(f"'{s}'")
        
    """



#%% optimizing datetime calculation -note
"""
Regarding grouping and counting within time periods (like a 
month): You are correct that it is indeed most optimal and the 
recommended approach in pandas to use the datetime type directly 
for these kinds of time-based analyses.

Here's why:

Optimized Operations: Pandas has highly optimized, vectorized 
operations built specifically for its datetime64 type. Accessing 
components like the month (.dt.month), year (.dt.year), or performing 
operations like resampling or grouping by time periods 
(.groupby(pd.Grouper(...))) is extremely fast because these operations 
are implemented efficiently in C.
Correctness: Using the datetime type ensures that pandas correctly 
handles calendar-related logic, including things like leap years, the 
number of days in each month, and time zone conversions if applicable. 
Grouping by separate integer columns wouldn't automatically account 
for these complexities.
Convenience: Pandas provides easy-to-use accessors (.dt) and methods 
for extracting date and time components or grouping by various time 
frequencies (daily, monthly, yearly, etc.) when using the datetime 
type.
While having 'day', 'month', and 'year' as separate integer columns 
can be useful for certain types of analysis that only involve those 
specific components, for operations that inherently involve time 
periods or date arithmetic, working with the datetime type is the most 
efficient and robust method in pandas.

So, for tasks like counting records within each month, you would 
ideally convert your date column to datetime and then use pandas' 
datetime functionalities for grouping.

"""

    
#%% startup programme 
#clean_data(fn_='dirty_data.csv',sep=',')  
#clean_data(fn_='invoices_dirty.csv',sep=',')

import sys
if __name__ == "__main__":   #når kaldt fra cmd prompt, og når kaldt med run fra spyder
    # C:\Users\bh\AppData\Local\anaconda3\envs\openai2\python.exe data_cleaning.py dirty_data.csv sep=';'
    # go to path with data_cleaning.py and a datafile dirty_data.csv,  C:\Users\bh\AppData\Local\anaconda3\envs\openai2\python.exe is my python interpreter
    r"""
    %runfile C:/Users/bh/Documents/programmering/Python/data_cleaning.py --wdir
    Script name: c:\users\bh\documents\programmering\python\data_cleaning.py
    Number of arguments: 0
    Arguments received: 
    """

    if len(sys.argv)>1: 
        sep_arg =sys.argv[2]
       
        sep_arg =sep_arg.split('=')
        
        if len(sep_arg)>0 and sep_arg[0]=='sep':
            sep_=sep_arg[1]
        else: 
            sep_=';'
            
        clean_data(fn_=sys.argv[1],sep=sep_)
    else: 
        clean_data(fn_='dirty_data.csv',sep=';')