Skip to content
TheChatArchiveThe Chat Archive
gpt-4-0125-preview· openaiHugging Face Suzhen/CodeChat

i have following code which runs really slow can you improve it to run faster?: import…

  1. User

    i have following code which runs really slow can you improve it to run faster?: import pandas as pd import os import numpy as np from datetime import timedelta

    The path where your CSV files are stored

    daily_data_path = r"E:\01_calculate_talib\day_spot" hourly_data_path = r"H:\1h_spot" four_data_path = r"E:\01_calculate_talib\4h_spot" week_data_path = r"E:\01_calculate_talib\week_spot" month_data_path = r"E:\01_calculate_talib\month_spot"

    valid_extras_directory = r"H:\trade\crypto data\day\to merge corrected(replace dot values)"

    def find_first_matching_1h(filename): for root, , files in os.walk(hourly_data_path): for file in files: if file.split("")[-2] == filename: return os.path.join(root, file)

    return None
    

    def find_first_matching_4h(filename): for root, , files in os.walk(four_data_path): for file in files: if file.split("")[-2] == filename: return os.path.join(root, file)

    return None
    

    def find_first_matching_week(filename): for root, , files in os.walk(week_data_path): for file in files: if file.split("")[-2] == filename: return os.path.join(root, file)

    return None
    

    def find_first_matching_month(filename): for root, , files in os.walk(month_data_path): for file in files: if file.split("")[-2] == filename: return os.path.join(root, file)

    return None
    

    Iterate through each file in the csv_folder_path

    def add_all_data(daily_df, hourly_df, four_df, week_df, month_df): # Generate the hourly column names dynamically based on available columns minus 'Date' hourly_column_names = [f"c{i}h{col}" for i in range(1, 25) for col in hourly_df.columns if col != 'Date'] four_column_names = [f"c{i}4h{col}" for i in range(1, 7) for col in four_df.columns if col != 'Date'] week_column_names = [f"c{i}w{col}" for i in range(1, 2) for col in week_df.columns if col != 'Date'] month_column_names = [f"c{i}m{col}" for i in range(1, 2) for col in month_df.columns if col != 'Date']

    # Combined DataFrame with adjusted columns
    combined_columns = list(
        daily_df.columns) + hourly_column_names + four_column_names + week_column_names + month_column_names
    combined_df = pd.DataFrame(columns=combined_columns)
    
    # Function to generate hourly data for a given day
    def get_hourly_data_for_day(day):
        filtered_hourly_df = hourly_df[hourly_df['Date'] == day]
        hourly_data = []
        for _, row in filtered_hourly_df.iterrows():
            for col in filtered_hourly_df.columns:
                if col != 'Date':
                    hourly_data.append(row[col])
        while len(hourly_data) < len(hourly_column_names):
            hourly_data.append(None)  # Append None for missing data
        return hourly_data
    
    def get_four_data_for_day(day):
        filtered_four_df = four_df[four_df['Date'] == day]
        four_data = []
        for _, row in filtered_four_df.iterrows():
            for col in filtered_four_df.columns:
                if col != 'Date':
                    four_data.append(row[col])
        while len(four_data) < len(four_column_names):
            four_data.append(None)  # Append None for missing data
        return four_data
    
    def get_week_data_for_day(day):
    
        week_start = week_df['Date'] - pd.to_timedelta(6, unit='d')
        filtered_week_df = week_df[(week_start <= day) & (week_df['Date'] >= day)]
        week_data = []
        for _, row in filtered_week_df.iterrows():
            for col in filtered_week_df.columns:
                if col != 'Date':
                    week_data.append(row[col])
        while len(week_data) < len(week_column_names):
            week_data.append(None)  # Append None for missing data
        return week_data
    
    def get_month_data_for_day(day):
        month_start = month_df['Date'].values.astype('datetime64[M]')
        filtered_month_df = month_df[(month_start <= day) & (month_df['Date'] >= day)]
        month_data = []
        for _, row in filtered_month_df.iterrows():
            for col in filtered_month_df.columns:
                if col != 'Date':
                    month_data.append(row[col])
        while len(month_data) < len(month_column_names):
            month_data.append(None)  # Append None for missing data
        return month_data
    
    for _, daily_row in daily_df.iterrows():
        daily_data = daily_row.tolist()
        day = daily_row['Date'].date()
        hourly_data = get_hourly_data_for_day(day)
        four_data = get_four_data_for_day(day)
        week_data = get_week_data_for_day(day)
        month_data = get_month_data_for_day(day)
        combined_row = daily_data + hourly_data + four_data + week_data + month_data
        combined_df = pd.concat([combined_df, pd.DataFrame([combined_row], columns=combined_columns)],
                                ignore_index=True)
    
    return combined_df
    

    def add_y_with_next_2d(combined_df): # Precisely identify columns high_columns = [col for col in combined_df.columns if col.startswith('c') and col.endswith('_h_High')] low_columns = [col for col in combined_df.columns if col.startswith('c') and col.endswith('_h_Low')]

    # Initial placeholders for new columns
    combined_df['y_High_2d'] = np.nan
    combined_df['y_Low_2d'] = np.nan
    combined_df['y_Priority_2d'] = np.nan
    
    # Iterating through DataFrame rows except the last two
    for index in range(len(combined_df) - 2):
        next_two_high_values = combined_df.loc[index + 1:index + 2, high_columns].to_numpy().flatten()
        next_two_low_values = combined_df.loc[index + 1:index + 2, low_columns].to_numpy().flatten()
    
        # Find max and min values for y_High and y_Low
        y_High_value = np.max(next_two_high_values)
        y_Low_value = np.min(next_two_low_values)
    
        # Assign y_High and y_Low to the current row
        combined_df.at[index, 'y_High_2d'] = y_High_value
        combined_df.at[index, 'y_Low_2d'] = y_Low_value
    
        # Determine the positions (row, column index) of the max and min values
        highest_pos = np.argmax(next_two_high_values)
        lowest_pos = np.argmin(next_two_low_values)
    
        highest_row = highest_pos // len(high_columns)
        lowest_row = lowest_pos // len(low_columns)
    
        # Calculate y_Priority based on the rules provided
        if lowest_row is None:
            print(f'---------------------------------------------- None at {index}')
            combined_df.at[index, 'y_Priority_2d'] =0
        elif highest_row is None:
            print(f'---------------------------------------------- None at {index}')
            combined_df.at[index, 'y_Priority_2d'] = 0
        elif highest_row < lowest_row:
            combined_df.at[index, 'y_Priority_2d'] = 1
        elif highest_row >= lowest_row:
            combined_df.at[index, 'y_Priority_2d'] = 0
        else:  # High and Low are in the same row in respect to the next two rows
            highest_col_index = highest_pos % len(high_columns)
            lowest_col_index = lowest_pos % len(low_columns)
    
            if highest_col_index < lowest_col_index:
                combined_df.at[index, 'y_Priority_2d'] = 1
            else:
                combined_df.at[index, 'y_Priority_2d'] = 0
    
    return combined_df
    

    def add_y_with_next_3d(combined_df): # Precisely identify columns high_columns = [col for col in combined_df.columns if col.startswith('c') and col.endswith('_h_High')] low_columns = [col for col in combined_df.columns if col.startswith('c') and col.endswith('_h_Low')]

    # Initial placeholders for new columns
    combined_df['y_High_3d'] = np.nan
    combined_df['y_Low_3d'] = np.nan
    combined_df['y_Priority_3d'] = np.nan
    
    # Iterating through DataFrame rows except the last two
    for index in range(len(combined_df) - 3):
        next_3_high_values = combined_df.loc[index + 1:index + 3, high_columns].to_numpy().flatten()
        next_3_low_values = combined_df.loc[index + 1:index + 3, low_columns].to_numpy().flatten()
    
        # Find max and min values for y_High and y_Low
        y_High_value = np.max(next_3_high_values)
        y_Low_value = np.min(next_3_low_values)
    
        # Assign y_High and y_Low to the current row
        combined_df.at[index, 'y_High_3d'] = y_High_value
        combined_df.at[index, 'y_Low_3d'] = y_Low_value
    
        # Determine the positions (row, column index) of the max and min values
        highest_pos = np.argmax(next_3_high_values)
        lowest_pos = np.argmin(next_3_low_values)
    
        highest_row = highest_pos // len(high_columns)
        lowest_row = lowest_pos // len(low_columns)
    
        # Calculate y_Priority based on the rules provided
        if lowest_row is None:
            print(f'---------------------------------------------- None at {index}')
            combined_df.at[index, 'y_Priority_3d'] =0
        elif highest_row is None:
            print(f'---------------------------------------------- None at {index}')
            combined_df.at[index, 'y_Priority_3d'] = 0
        elif highest_row < lowest_row:
            combined_df.at[index, 'y_Priority_3d'] = 1
        elif highest_row >= lowest_row:
            combined_df.at[index, 'y_Priority_3d'] = 0
        else:  # High and Low are in the same row in respect to the next two rows
            highest_col_index = highest_pos % len(high_columns)
            lowest_col_index = lowest_pos % len(low_columns)
    
            if highest_col_index < lowest_col_index:
                combined_df.at[index, 'y_Priority_3d'] = 1
            else:
                combined_df.at[index, 'y_Priority_3d'] = 0
    
    return combined_df
    

    def add_y_with_next_5d(combined_df): # Precisely identify columns high_columns = [col for col in combined_df.columns if col.startswith('c') and col.endswith('_h_High')] low_columns = [col for col in combined_df.columns if col.startswith('c') and col.endswith('_h_Low')]

    # Initial placeholders for new columns
    combined_df['y_High_5d'] = np.nan
    combined_df['y_Low_5d'] = np.nan
    combined_df['y_Priority_5d'] = np.nan
    
    # Iterating through DataFrame rows except the last two
    for index in range(len(combined_df) - 5):
        next_5_high_values = combined_df.loc[index + 1:index + 5, high_columns].to_numpy().flatten()
        next_5_low_values = combined_df.loc[index + 1:index + 5, low_columns].to_numpy().flatten()
    
        # Find max and min values for y_High and y_Low
        y_High_value = np.max(next_5_high_values)
        y_Low_value = np.min(next_5_low_values)
    
        # Assign y_High and y_Low to the current row
        combined_df.at[index, 'y_High_5d'] = y_High_value
        combined_df.at[index, 'y_Low_5d'] = y_Low_value
    
        # Determine the positions (row, column index) of the max and min values
        highest_pos = np.argmax(next_5_high_values)
        lowest_pos = np.argmin(next_5_low_values)
    
        highest_row = highest_pos // len(high_columns)
        lowest_row = lowest_pos // len(low_columns)
    
        # Calculate y_Priority based on the rules provided
        if lowest_row is None:
            print(f'---------------------------------------------- None at {index}')
            combined_df.at[index, 'y_Priority_5d'] = 0
        elif highest_row is None:
            print(f'---------------------------------------------- None at {index}')
            combined_df.at[index, 'y_Priority_5d'] = 0
        if highest_row < lowest_row:
            combined_df.at[index, 'y_Priority_5d'] = 1
        elif highest_row >= lowest_row:
            combined_df.at[index, 'y_Priority_5d'] = 0
        else:  # High and Low are in the same row in respect to the next two rows
            highest_col_index = highest_pos % len(high_columns)
            lowest_col_index = lowest_pos % len(low_columns)
    
            if highest_col_index < lowest_col_index:
                combined_df.at[index, 'y_Priority_5d'] = 1
            else:
                combined_df.at[index, 'y_Priority_5d'] = 0
    
    return combined_df
    

    def merge_valid_files(combined_df): global df_merged first = True

    for valid_extra in os.listdir(valid_extras_directory):
        if valid_extra.endswith(".csv"):
            valid_extra_path = os.path.join(valid_extras_directory, valid_extra)
            extra_data = pd.read_csv(valid_extra_path)
            extra_data['Date'] = pd.to_datetime(extra_data['Date'], format="ISO8601", utc=True)
            if first:
                df_merged = combined_df.merge(extra_data, how='left', on='Date')
                first = False
            else:
                df_merged = df_merged.merge(extra_data, how='left', on='Date')
    
    return df_merged
    

    for daily_csv_file in os.listdir(daily_data_path):

    try:
    
        daily_file_path = os.path.join(daily_data_path, daily_csv_file)
        hourly_file_path = find_first_matching_1h(daily_csv_file.split('_')[-2])
        four_file_path = find_first_matching_4h(daily_csv_file.split('_')[-2])
        week_file_path = find_first_matching_week(daily_csv_file.split('_')[-2])
        month_file_path = find_first_matching_month(daily_csv_file.split('_')[-2])
        print(f'processig {daily_csv_file}'
              f' with {hourly_file_path}'
              f' with {four_file_path}'
              f' with {week_file_path}'
              f' with {month_file_path}')
        # Load the daily and hourly data from CSV files
        daily_df = pd.read_csv(daily_file_path)
        hourly_df = pd.read_csv(hourly_file_path)
        four_df = pd.read_csv(four_file_path)
        week_df = pd.read_csv(week_file_path)
        month_df = pd.read_csv(month_file_path)
    
        daily_df['Date'] = pd.to_datetime(daily_df['Date'], format="ISO8601", utc=True)
        hourly_df['Date'] = pd.to_datetime(hourly_df['Date'], format="ISO8601", utc=True)
        four_df['Date'] = pd.to_datetime(four_df['Date'], format="ISO8601", utc=True)
        week_df['Date'] = pd.to_datetime(week_df['Date'], format="ISO8601", utc=True)
        month_df['Date'] = pd.to_datetime(month_df['Date'], format="ISO8601", utc=True)
    
        # Strip time part from hourly_df 'Date' for alignment
        hourly_df['Date'] = hourly_df['Date'].dt.date
        four_df['Date'] = four_df['Date'].dt.date
        week_df['Date'] = week_df['Date'].dt.date
        month_df['Date'] = month_df['Date'].dt.date
    
        combined_df = add_all_data(daily_df, hourly_df, four_df, week_df, month_df)
    
        columns_to_remove = combined_df.filter(like='_Date').columns
        combined_df.drop(columns=columns_to_remove, inplace=True)
    
        columns_to_remove = combined_df.filter(like='_Symbol').columns
        combined_df.drop(columns=columns_to_remove, inplace=True)
    
        high_cols = [f'c{i}_h_High' for i in range(1, 25)]
        low_cols = [f'c{i}_h_Low' for i in range(1, 25)]
    
        # Shift the DataFrame by one row
        df_shifted = combined_df.shift(-1)
    
        # Calculate y_High and y_Low
        combined_df['y_High_1d'] = df_shifted[high_cols].max(axis=1)
        combined_df['y_Low_1d'] = df_shifted[low_cols].min(axis=1)
    
    
        # Calculate y_Priority
        def calculate_priority(row):
            high_index = np.argmax(row[high_cols].values)
            low_index = np.argmin(row[low_cols].values)
            return int(high_index < low_index)
    
    
        combined_df['y_Priority_1d'] = df_shifted.apply(calculate_priority, axis=1)
    
        combined_df = add_y_with_next_2d(combined_df)
        combined_df = add_y_with_next_3d(combined_df)
        combined_df = add_y_with_next_5d(combined_df)
    
        combined_df = combined_df.iloc[2:-6]
    
        combined_df = merge_valid_files(combined_df)
        # Save the combined DataFrame
        combined_df.to_csv(daily_file_path, index=False)
        print('Combined CSV has been saved.')
    
    except Exception as e:
        print(
            f"----------------------------------> An error occurred while processing {daily_file_path}: {e}")
    
  2. Assistant

    Your code involves a lot of heavy data processing, and I see multiple areas for improvement to make it run faster. Here are several suggestions:

    1. Avoid Iterating Over DataFrame Rows: Iterating over DataFrame rows using .iterrows() is very slow especially for large data sets. Instead, using vectorized operations or pandas functions like .apply() is much faster in most cases, although .apply() itself can be slower compared to fully vectorized operations.

    2. Reduce the Number of File Reads: If your hourly, 4hr, weekly, and monthly data files do not change frequently, consider merging them into a single file or database after preprocessing. This way, you only need to read the combined data, which will significantly reduce I/O time.

    3. Optimize Data Searching: The functions find_first_matching_* repetitively walk through directories to find matching files. It's more efficient to map these correspondences once and reuse the map.

    4. Pre-compute Column Names: You can compute the dynamic column names outside the function that generates data for days, as they don't change for each call.

    5. Vectorize Conditions and Calculations: In add_y_with_next_* functions, replacing loops with vectorized operations or using .shift() for comparisons should improve performance.

    6. Concatenation Efficiency: Instead of concatenating rows one by one to the DataFrame (combined_df = pd.concat([...]) inside a loop), it's more efficient to append them to a list and then concatenate the list to the DataFrame once outside the loop.

    Below is an optimization approach on a key part of your code focusing on these aspects, which you can extend to other parts accordingly:

    import pandas as pd
    import numpy as np
    import os
    
    # Assuming datetime conversion and other preprocessing is done
    
    def get_combined_data(daily_df, hourly_df, four_df, week_df, month_df):
        # Example of optimized vectorized approach for a single daily_df row match
        # Remember to replicate and modify for your specific case as this example might not cover everything
        
        # Assuming 'Date' columns have been aligned and are already datetime64 types where necessary
        # Ensure all 'Date' columns are of datetime type and aligned to the same format for easy comparison/manipulation
        
        # Example: For hourly data - Vectorizing this entirely would depend on the specifics of how your data aligns
        # Here's a simplification where we assume each day in daily_df should match 24 rows in hourly_df
        
        # Example of concatenation efficiency for one dataset
        rows_list = []
        for day in daily_df['Date']:
            matching_rows = hourly_df[hourly_df['Date'] == day]
            hourly_data = matching_rows.drop(columns=['Date']).values.flatten()
            row = [day] + list(hourly_data)
            rows_list.append(row)
        hourly_extended_df = pd.DataFrame(rows_list, columns=['Date'] + [f'Hour_{x}' for x in range(1, 25)])
        
        # Complete the approach for 4h, weekly, and monthly in a similar vein
    
        # Your logic for merging all these data sets would come here
        # combined_df = ...
    
        return combined_df
    
    # Reducing file reads: Build a map once if possible then use it
    # Assuming your file names have a unique part that directly maps to the daily file's unique part
    # such as a timestamp or a special code. Precompute this mapping and use it to avoid os.walk() calls.
    
    # Your other function optimizations would follow similar patterns:
    
    # Using shift and vectorized operations for y calculations
    # combined_df['y_High_1d'] = combined_df[high_columns].shift(-1).max(axis=1)
    # and so forth for other columns like y_Low_1d...
    
    # Finally merging and any additional operations
    
    # Please note, given the complexity of your script, and without having access to the actual structure of your data,
    # the above recommendations are meant to illustrate the direction for potential optimization rather than provide a complete solution.
    

    Remember, the key to optimization is often restructuring the problem and reducing repetitive operations, especially disk IO and row-by-row processing. Always profile your code to identify bottlenecks efficiently.

  3. User

    perform this advice : Instead of concatenating rows one by one to the DataFrame (combined_df = pd.concat([...]) inside a loop), it’s more efficient to append them to a list and then concatenate the list to the DataFrame once outside the loop. and send me updated code of mentioned part

  4. Assistant

    Sure, I'll apply the advised optimization technique to the relevant part of your code. Let's focus on the add_all_data function where you're appending rows to combined_df within a loop. I'll adjust this part to append rows to a list and then perform a single concatenation after the loop.

    Here's how you can update the add_all_data part of your code:

    def add_all_data(daily_df, hourly_df, four_df, week_df, month_df):
        hourly_column_names = [f"c{i}_h_{col}" for i in range(1, 25) for col in hourly_df.columns if col != 'Date']
        four_column_names = [f"c{i}_4h_{col}" for i in range(1, 7) for col in four_df.columns if col != 'Date']
        week_column_names = [f"c{i}_w_{col}" for i in range(1, 2) for col in week_df.columns if col != 'Date']
        month_column_names = [f"c{i}_m_{col}" for i in range(1, 2) for col in month_df.columns if col != 'Date']
    
        combined_columns = daily_df.columns.tolist() + hourly_column_names + four_column_names + week_column_names + month_column_names
        combined_rows = []  # List to hold row data before concatenating
    
        for _, daily_row in daily_df.iterrows():
            daily_data = daily_row.tolist()
            day = daily_row['Date'].date()
    
            # Assuming get_hourly_data_for_day, get_four_data_for_day, get_week_data_for_day, and get_month_data_for_day
            # are defined similarly to their descriptions in the initial code
            hourly_data = get_hourly_data_for_day(day)
            four_data = get_four_data_for_day(day)
            week_data = get_week_data_for_day(day)
            month_data = get_month_data_for_day(day)
    
            combined_row = daily_data + hourly_data + four_data + week_data + month_data
            combined_rows.append(combined_row)  # Append the combined_row into combined_rows list
    
        # Once all rows are processed, concatenate them all at once
        combined_df = pd.DataFrame(combined_rows, columns=combined_columns)
        
        return combined_df
    

    In this amended version, rather than concatenating each row to the DataFrame within the loop (which is known to be inefficient due to DataFrame's immutable nature), a list combined_rows is used to collect all row data. After the loop has processed all rows, this list is turned into a DataFrame in a single operation, which is substantially faster.

    Please make sure you adapt the functions like get_hourly_data_for_day(day), get_four_data_for_day(day), get_week_data_for_day(day), and get_month_data_for_day(day) as needed, since the original code snippet was not provided for these functions. This approach assumes these functions return lists of data corresponding to the provided day.

  5. User

    should i change for _, daily_row in daily_df.iterrows(): part fo better performance?

  6. Assistant

    Yes, using .iterrows() to iterate over a DataFrame in pandas is generally less efficient compared to vectorized operations or other pandas methods like .apply(). However, in your specific case of constructing a new DataFrame from multiple other DataFrames row-by-row, the alternatives to .iterrows() might not offer a straightforward performance improvement without significantly refactoring your logic, mainly because your operation seems inherently row-based due to the need to construct complex combined rows from several sources.

    For operations that truly require row-wise manipulation and cannot be easily vectorized or replaced by pandas built-in functions, .iterrows() is an acceptable approach albeit not the most performant one. However, if your primary goal is to build a combined DataFrame from multiple sources with complex row-wise logic, then focusing on optimizing other parts of your code (e.g., file I/O, reducing the number of DataFrame mutations, etc.) might yield more significant performance gains.

    That said, if there's a portion of your logic that can be expressed in a vectorized form or through the use of more efficient pandas operations (e.g., using .apply(), though it's also not as fast as true vectorized operations), then it's advisable to do so. For instance, calculations that can be applied across columns or the use of boolean masks to filter rows are often areas where performance can be improved without resorting to .iterrows().

    Here's a simplified example of using .apply() in some context, but remember, .apply() is more beneficial when you can't perform direct vectorized operations:

    # Hypothetical example where you need to calculate a column based on complex row-wise logic
    # This method still uses a loop internally but can be cleaner for some transformations.
    def complex_logic(row):
        # Hypothetical complex logic on a row
        return row['A'] + row['B'] - row['C']
    
    daily_df['New_Column'] = daily_df.apply(lambda row: complex_logic(row), axis=1)
    

    For the specific logic in your add_all_data function, the main performance concern is not the iteration itself but how the rows are being combined and added to the DataFrame. As mentioned earlier, collecting the rows in a list and then creating a DataFrame in one go (as you've updated) is a significant improvement.

    In summary, unless you can refactor your logic to be more vectorized, which might be challenging given the complexity of combining multiple DataFrames row by row with specific logic, optimizing the .iterrows() part might not be the most impactful change. Focus on optimizing data access and minimizing DataFrame mutations where possible.