2
\$\begingroup\$

I have completed a project I was working on using the methods I know how, but it is very inefficient. I am a beginner trying to figure out how I can improve my work by using software solutions.

I have a large dataset and I put it into smaller dataframes (grouped by part numbers) which I will loop through to perform the same task on all of them. The following is a small example of one of the individual dataframes:

import pandas as pd
from pandas import Timestamp
import numpy as np
data = {'Part': {0: 'QOL2',1: 'QOL2',2: 'QOL2',3: 'QOL2',4: 'QOL2',5: 'QOL2',6: 'QOL2',7: 'QOL2',8: 'QOL2',9: 'QOL2'},
        'Start': {0: Timestamp('2021-09-25 00:00:00'),1: Timestamp('2021-09-30 00:00:00'),2: Timestamp('2021-10-04 00:00:00'),3: Timestamp('2021-10-04 00:00:00'),4: Timestamp('2021-11-03 00:00:00'),5: Timestamp('2021-11-03 00:00:00'),6: Timestamp('2021-12-22 00:00:00'),7: Timestamp('2021-12-22 00:00:00'),8: Timestamp('2021-12-24 00:00:00'),9: Timestamp('2022-03-21 00:00:00')},
        'Finish': {0: Timestamp('2021-09-25 00:00:00'),1: Timestamp('2021-09-30 00:00:00'),2: Timestamp('2021-10-04 00:00:00'),3: Timestamp('2021-10-04 00:00:00'),4: Timestamp('2021-11-03 00:00:00'),5: Timestamp('2021-11-03 00:00:00'),6: Timestamp('2021-12-24 00:00:00'),7: Timestamp('2021-12-22 00:00:00'),8: Timestamp('2021-12-24 00:00:00'),9: Timestamp('2022-03-21 00:00:00')},
        'Code': {0: 'SR2F',1: 'LS4DQ',2: 'DP2L',3: 'Testing Broke',4: 'SR2F',5: 'JKO2',6: 'Light Off',7: 'QFD3',8: 'A3SA',9: 'LA52'},
        'Fix': {0: 'na',1: 'na',2: 'na',3: 'Testing Procedure Fixed',4: 'na',5: 'na',6: 'Light Repair',7: 'na',8: 'na',9: 'na'},
        'Fixed': {0: 'No',1: 'No',2: 'No',3: 'Yes',4: 'No',5: 'No',6: 'Yes',7: 'No',8: 'No',9: 'No'},
        'DateColumnTests': {0: 'None',1: 'None',2: 'None',3: 'None',4: 'None',5: 'None',6: 'None',7: 'None',8: 'None',9: 'None'},
        'DuringTest': {0: False,1: False,2: False,3: False,4: False,5: False,6: False,7: False,8: False,9: False}}
df = pd.DataFrame.from_dict(data)
Part Start Finish Code Fix Fixed DateColumnTests DuringTest
0 QOL2 2021-09-25 00:00:00 2021-09-25 00:00:00 SR2F na No None False
1 QOL2 2021-09-30 00:00:00 2021-09-30 00:00:00 LS4DQ na No None False
2 QOL2 2021-10-04 00:00:00 2021-10-04 00:00:00 DP2L na No None False
3 QOL2 2021-10-04 00:00:00 2021-10-04 00:00:00 Testing Broke Testing Procedure Fixed Yes None False
4 QOL2 2021-11-03 00:00:00 2021-11-03 00:00:00 SR2F na No None False
5 QOL2 2021-11-03 00:00:00 2021-11-03 00:00:00 JKO2 na No None False
6 QOL2 2021-12-22 00:00:00 2021-12-24 00:00:00 Light Off Light Repair Yes None False
7 QOL2 2021-12-22 00:00:00 2021-12-22 00:00:00 QFD3 na No None False
8 QOL2 2021-12-24 00:00:00 2021-12-24 00:00:00 A3SA na No None False
9 QOL2 2022-03-21 00:00:00 2022-03-21 00:00:00 LA52 na No None False

I added the last 3 columns above for the analysis I do later so they are not necessarily needed. In the dataframe I have:

  • one part number in the first column
  • one code or fix in the fourth column
    • if there is a fix in fourth column, the fix name will be in fifth column
  • start and finish dates of the code/fix in the second and third columns
    • codes always have the same start/finish date
    • fixes can have the same start/finish date or last multiple days

The idea is to have an output dataframe that has the same number of rows as the number of fixes (in this case 2 fixes in indexes 3 and 6). For each row there is a fix, I want to see which codes happen before the current fix and the last one; the codes that happen during the current fix; and the codes that happen after the current fix and before the next one. The output would look like:

Part FixName BeforeFix DuringFix AfterFix
0 QOL2 Testing Broke ['SR2F', 'LS4DQ'] ['DP2L'] ['SR2F', 'JKO2']
1 QOL2 Light Off ['SR2F', 'JKO2'] ['QFD3', 'A3SA'] ['LA52']

I was able to get this desired output first using iterrows(). I understood from some research that iterrows() was not very optimal. I then tried changing to itertuples() as it was a reasonable transition. Although it ran faster, it still took a very long time (total number of rows could be anywhere from 2,000 to 1,000,000). My code to get this output is:

uniqueFixes = df['Fixed'].value_counts() # Counting Yes/No
uniqueFixesNum = uniqueFixes[1] # Number of Yes

new_df = pd.DataFrame(
        columns = ['Part', 'FixName', 'BeforeFix', 'DuringFix', 'AfterFix'],
        index = range(uniqueFixesNum)) # New df
PartCol = [] # Used for number and names of Parts
FixCol = [] # Used for number and names of Fix Names

for rowQ in df.itertuples():
    if rowQ.Fixed == 'Yes':
        PartCol.append(rowQ.Part) # Appending Part Names
        FixCol.append(rowQ.Code) # Appending Fix Names

new_df['Part'] = PartCol # Populating new df with Parts
new_df['FixName'] = FixCol # Populating new df with Fixes

# Find dates where codes occur during a fix:
for daterow1 in df.itertuples():
    for daterow2 in df.itertuples():
        # Comparing start date of row to start and finish date of every other row, to see if it is in a range of any Fix
        # If it does fall in range of a Fix, change DateColumnTests row from None to Index of Fix
        if daterow1.Start >= daterow2.Start and daterow1.Start < daterow2.Finish and daterow1.Fixed == 'No':
            df.loc[daterow1.Index, 'DateColumnTests'] = daterow2.Index
  
        elif daterow1.Start > daterow2.Start and daterow1.Start <= daterow2.Finish and daterow1.Fixed == 'No':
            df.loc[daterow1.Index, 'DateColumnTests'] = daterow2.Index
        
        elif daterow1.Start >= daterow2.Start and daterow1.Start <= daterow2.Finish and daterow2.Fixed == 'Yes':
            df.loc[daterow1.Index, 'DateColumnTests'] = daterow2.Index    
#display(df)

BeforeFix = [] # Column to append to
DuringFix = [] # Column to append to
AfterFix = [] # Column to append to
FixCount = 0 # Increases by 1 for every Fix

for row1 in df.itertuples():
    if row1.Fixed == 'No' and row1.DateColumnTests == 'None': # If row has No Fix and 'None' in column, will be in BeforeFix
        BeforeFix.append(row1.Code)

    if row1.Fixed == 'Yes':
        new_df.iat[FixCount, 2] = str(BeforeFix) # If row has Yes, BeforeFix column will become entry in new df at the FixCount

        for row2 in df.itertuples(): # When row1 has a yes, will compare it against all rows in row2
            
            # row2 finish is less than row1 finish, has false, has 'None', and row2 is not a fix
            if row2.Finish < row1.Finish and row2.DuringTest == False and row2.DateColumnTests == 'None' and row2.Fixed != 'Yes':
                df.loc[row2.Index, 'DuringTest'] = 'Before' # False becomes Before
            
            # row2 finish is less than or equal to row1 finish, and row2 is not a fix
            elif row2.Finish <= row1.Finish and row2.DuringTest == False and row2.Fixed != 'Yes':
                df.loc[row2.Index, 'DuringTest'] = 'During' # False becomes during
                DuringFix.append(row2.Code) # Appending these values to during column

        new_df.iat[FixCount, 3] = str(DuringFix) # During Column added to new df
        FixCount = FixCount + 1 # Adding 1 to Fix Count
        BeforeFix.clear() # Clearing
        DuringFix.clear() # Clearing
        
for row3 in df.itertuples(): # The last rows with a False and no Fix must be in the After Column
    if row3.DuringTest == False and row3.Fixed == 'No':
        AfterFix.append(row3.Code)
        
for row4 in new_df.itertuples(): # For After column, can duplicate the before column in the next row if the 
    if row4.Index != 0:
        new_df.loc[row4.Index - 1, 'AfterFix'] = row4.BeforeFix
    if row4.Index == len(new_df) - 1: # After the last fix, if there are codes after, will be in the last After Row
        new_df.loc[row4.Index, 'AfterFix'] = str(AfterFix)
        
display(new_df)

This code does get me to the desired output, but from researching, it sounds like itertuples() is not often the best practice. In my case above, I used it 7 times to complete my task. I had used a nested for loop two different types that used itertuples() in each loop which I believe took the most amount of time (I compared each row to every other row in the dataframe). I also achieved my goal by changing the values in the final columns (during iteration) and using those values in conditional statements (which I believe is also bad practice).

After trying to think of a different way to do it instead of iterating through the rows I could not think of a different approach without having some way of keeping track of the index like I could in itertuples() (or iterrows()). I was wondering if I am missing something obvious (or if it is very complex) or if this is an example where you need to use iteration in a dataframe. I see examples of things like vectorization but they usually seem aimed at numerical operations and I'm not sure if it would work in my case.

One thing that I thought might work would be to use groupby to group the dates for when they occur before a Fix and during a Fix for each Fix. Then I might be able to get away without keeping track of the Index and would not have to iterate and just use the new "groupby" dataframe to fill the values in the output dataframe. Although, the only way I thought of being able to use groupby would be to iterate through the rows and classify each row as Before# or During# so then I would be back to square one. I apologize for the long question, I am new and although I solved my task, I have a feeling I'm going to run into this type of problem again in the future.

Improve this question
\$\endgroup\$

1 Answer 1

Reset to default
1
\$\begingroup\$

I put it into smaller dataframes (grouped by part numbers) which I will loop through to perform the same task on all of them

That's not usually how Pandas is supposed to work. You should have one dataframe and call df.groupby('Part'). However, I did not demonstrate this in my suggested code because your sample data only show one part.

Don't represent Fixed as a yes/no string; represent it as a boolean (and probably don't include it in the dataframe at all; keep it as a free series).

It's generally not a very good idea to construct series of lists in Pandas. Instead, if you're at all able, just have one row per code, and only one code column (not separate columns for before, during and after). You could distinguish these categories by

  • holding separate dataframes (what I did); or
  • adding a categorical variable equal to 'before', 'during' or 'after'; or
  • inferring the before/during/after status based on the relationship of the code date to the fix date.

In general you should avoid itertuples (especially in multiple nested loops). But this is a nasty problem so I don't blame you for reaching for that. It's made nastier by the fact that you have an "after" category. I don't know what your use case is, but it seems strange to me to duplicate codes and have them "owned" by multiple fix events. Could you not reduce this to a "before" and a "during", and assume that the "before" of fix i is the same as the "after" of fix i - 1 ?

Anyway, due to all of the above, your code is a mess but so is mine. There are lots of edge cases and I'm not convinced that I've taken care of them all. Unit test thoroughly.

Suggested

import pandas as pd
from pandas import Timestamp
import numpy as np

data = {
    'Part': {0: 'QOL2',1: 'QOL2',2: 'QOL2',3: 'QOL2',4: 'QOL2',5: 'QOL2',6: 'QOL2',7: 'QOL2',8: 'QOL2',9: 'QOL2'},
    'Start': {0: Timestamp('2021-09-25 00:00:00'),1: Timestamp('2021-09-30 00:00:00'),2: Timestamp('2021-10-04 00:00:00'),3: Timestamp('2021-10-04 00:00:00'),4: Timestamp('2021-11-03 00:00:00'),5: Timestamp('2021-11-03 00:00:00'),6: Timestamp('2021-12-22 00:00:00'),7: Timestamp('2021-12-22 00:00:00'),8: Timestamp('2021-12-24 00:00:00'),9: Timestamp('2022-03-21 00:00:00')},
    'Finish': {0: Timestamp('2021-09-25 00:00:00'),1: Timestamp('2021-09-30 00:00:00'),2: Timestamp('2021-10-04 00:00:00'),3: Timestamp('2021-10-04 00:00:00'),4: Timestamp('2021-11-03 00:00:00'),5: Timestamp('2021-11-03 00:00:00'),6: Timestamp('2021-12-24 00:00:00'),7: Timestamp('2021-12-22 00:00:00'),8: Timestamp('2021-12-24 00:00:00'),9: Timestamp('2022-03-21 00:00:00')},
    'Code': {0: 'SR2F',1: 'LS4DQ',2: 'DP2L',3: 'Testing Broke',4: 'SR2F',5: 'JKO2',6: 'Light Off',7: 'QFD3',8: 'A3SA',9: 'LA52'},
    'Fix': {0: 'na',1: 'na',2: 'na',3: 'Testing Procedure Fixed',4: 'na',5: 'na',6: 'Light Repair',7: 'na',8: 'na',9: 'na'},
    'Fixed': {0: 'No',1: 'No',2: 'No',3: 'Yes',4: 'No',5: 'No',6: 'Yes',7: 'No',8: 'No',9: 'No'},
    'DateColumnTests': {0: 'None',1: 'None',2: 'None',3: 'None',4: 'None',5: 'None',6: 'None',7: 'None',8: 'None',9: 'None'},
    'DuringTest': {0: False,1: False,2: False,3: False,4: False,5: False,6: False,7: False,8: False,9: False}}
df = pd.DataFrame.from_dict(data)

# Start "from scratch"
df = df.drop(['Fixed', 'DateColumnTests', 'DuringTest'], axis='columns')

# The string 'na' should be an actual NaN
df['Fix'] = df.Fix.replace('na', np.nan)

# Fix-type rows
is_fix = df.Fix.notna()
fixes = df[is_fix]

# Back-fill fix event start and finish dates
# Ensure 'fix' events occur at the end of a date group
by_start = df.sort_values(by=['Start', 'Fix'], na_position='first')
by_finish = by_start.sort_values(by=['Finish', 'Fix'], na_position='first')
nat = np.datetime64('NaT')
df['StartFix'] = pd.Series(
    np.where(by_start.Fix.isna(), nat, by_start.Start),
    index=by_start.index,
).bfill().ffill()
df['FinishFix'] = pd.Series(
    np.where(by_finish.Fix.isna(), nat, by_finish.Finish),
    index=by_finish.index,
).bfill().ffill()

is_during = (
    ~is_fix
    & ((df.Start >= df.StartFix) | df.StartFix.isna())
    & (df.Finish <= df.FinishFix)
)
left_during = df[is_during]
during = (
    pd.merge(
        left=left_during, right=fixes,
        suffixes=('', '_Fix'),
        left_on=('Part', 'StartFix', 'FinishFix'),
        right_on=('Part', 'Start', 'Finish')
    )
    .drop(columns=['Start_Fix', 'Finish_Fix', 'Fix'])
    .set_index(left_during.index)
    .rename({'Fix_Fix': 'Fix'}, axis='columns')
)

not_during = df[~(is_fix | is_during)].drop(columns='Fix')

before_left = not_during[not_during.Start < not_during.StartFix]
before = (
    pd.merge(
        left=before_left, right=fixes,
        suffixes=('', '_Fix'),
        left_on=('Part', 'StartFix', 'FinishFix'),
        right_on=('Part', 'Start', 'Finish'),
    )
    .drop(columns=['Start_Fix', 'Finish_Fix'])
    .set_index(before_left.index)
)

after = (
    pd.merge_asof(
        left=not_during, right=fixes,
        suffixes=('', '_Fix'),
        by='Part', on='Start', direction='backward', #  allow_exact_matches=False,
    )
    .set_index(not_during.index)
)
after = (
    after[after.Fix.notna()]
    .drop(columns='FinishFix')
    .rename({
        'Finish_Fix': 'FinishFix'
    }, axis='columns')
)

pd.set_option('display.max_columns', None)
print('Before:')
print(before, '\n')
print('During:')
print(during, '\n')
print('After:')
print(after, '\n')

Output

Before:
   Part      Start     Finish   Code   StartFix  FinishFix       Code_Fix  \
0  QOL2 2021-09-25 2021-09-25   SR2F 2021-10-04 2021-10-04  Testing Broke   
1  QOL2 2021-09-30 2021-09-30  LS4DQ 2021-10-04 2021-10-04  Testing Broke   
4  QOL2 2021-11-03 2021-11-03   SR2F 2021-12-22 2021-12-24      Light Off   
5  QOL2 2021-11-03 2021-11-03   JKO2 2021-12-22 2021-12-24      Light Off   

                       Fix  
0  Testing Procedure Fixed  
1  Testing Procedure Fixed  
4             Light Repair  
5             Light Repair   

During:
   Part      Start     Finish  Code   StartFix  FinishFix       Code_Fix  \
2  QOL2 2021-10-04 2021-10-04  DP2L 2021-10-04 2021-10-04  Testing Broke   
7  QOL2 2021-12-22 2021-12-22  QFD3 2021-12-22 2021-12-24      Light Off   
8  QOL2 2021-12-24 2021-12-24  A3SA 2021-12-22 2021-12-24      Light Off   

                       Fix  
2  Testing Procedure Fixed  
7             Light Repair  
8             Light Repair   

After:
   Part      Start     Finish  Code   StartFix  FinishFix       Code_Fix  \
4  QOL2 2021-11-03 2021-11-03  SR2F 2021-12-22 2021-10-04  Testing Broke   
5  QOL2 2021-11-03 2021-11-03  JKO2 2021-12-22 2021-10-04  Testing Broke   
9  QOL2 2022-03-21 2022-03-21  LA52 2021-12-22 2021-12-24      Light Off   

                       Fix  
4  Testing Procedure Fixed  
5  Testing Procedure Fixed  
9             Light Repair
Improve this answer
\$\endgroup\$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.