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.