The function removes rows from a pandas df if that row doesn't have the value of
important_1 inside of
important_2. For example if
"M" then that row would be removed, but if
"redbluegreen" then the row would be kept.
On my ~125mb files this code runs really slow. Only about 3 iterations a second. In this example it runs faster maybe because it is a much smaller csv. How can I speed this up?
import pandas as pd import numpy as np from io import StringIO df = """ valid,important_1,important_2,unrelated_1,unrelated_2 2015-07-10 01:47:00,blue,,blabla,foobar56 2015-07-10 01:51:00,blue,M,blabla,foobar32 2015-07-10 02:37:00,blue,M,blab004la,foobar 2015-07-10 02:51:00,blue,M,blabla,foobar343 2015-07-10 03:19:00,blue,blue green,blabla,foobar 2015-07-10 03:51:00,blue,,blabla1,foobar6543 2015-07-10 04:11:00,blue,green red,blabla,foobar 2015-07-10 04:51:00,blue,red,blabla,foobar2466 2015-07-10 05:51:00,blue,blue,blabla,foobar 2015-07-10 06:27:00,blue,,blabla4,foobar 2015-07-10 06:51:00,blue,,blab605la3,foobar6543 2015-07-10 07:27:00,blue,M,blabla,foobar 2015-07-10 07:51:00,blue,M,blab445la2,foobar2334 2015-07-10 08:51:00,blue,blue green red,blabla,foobar7666 2015-07-10 09:51:00,blue,blue green,blabla,foobar """ df = StringIO(df) df = pd.read_csv(df) def remove_rows_that_dont_have_important_1_in_important_2(df): """ Removes all rows of the dataframe that do not have important_1 inside of important_2. Note: All rows have the same value for important_1. Note: Every row can have a different value for important_2. Note: There are unrelated columns in the df that cannot be dropped from the df. But rows still can. """ important_1 = df.at[0, 'important_1'] # get the icao fro the df from the first ob important_2_column_index = df.columns.get_loc('important_2') # column order won't always be the same, so find the right column first iter_index = len(df) # the start point of the loop while iter_index > 0: # start from the end of the df and work forwards print(iter_index) # DEBUG printing iter_index -= 1 # every loop move forward one item. This is at the top of the loop because that way every loop we ensure to move down one (or up a row depending on how you look at it). value_of_important_2_for_this_row = df.iat[iter_index, important_2_column_index] # the value of important_2 for the row we are currently on # check for np.NaN first because it is cheaper then checking str in str if value_of_important_2_for_this_row is np.NaN: # if the value of important_2 is np.NaN then there is now way important_1 can be in it pass # str in str check elif important_1 not in value_of_important_2_for_this_row: # safe to assume the important_2 is off type str pass else: # safe to assume that important_1 is indeed in important_2 continue # skip deletion because important_1 is in important_2 Yay! df.drop([iter_index], inplace=True) # delete the row return df # reset the index before returning the df df = remove_rows_that_dont_have_important_1_in_important_2(df=df) print(df)
valid important_1 important_2 unrelated_1 unrelated_2 4 2015-07-10 03:19:00 blue blue green blabla foobar 8 2015-07-10 05:51:00 blue blue blabla foobar 13 2015-07-10 08:51:00 blue blue green red blabla foobar7666 14 2015-07-10 09:51:00 blue blue green blabla foobar