Processing csv file with more than 700K rows of data
I think the main time-consumer will be the fact that you are iterating over 700,000 rows, one-by-one.

You could perhaps do a few of your checks/comparisons (used in the line with bit-wise stuff: &amp;, | etc) to begin with, for all rows. So for that row, it looks like this now:

data.loc[
    (data['Category'] != category) &amp; 
    (pd.Series(list1).isin(data["List1"]).any()) &amp; 
    (pd.Series(list2).isin(data["List2"]).any()) |
    (data["List2"][0] == 'NULL') |
    (list2[0] == 'NULL'))]


Before the loop, you could pre-compute each of those rows, adding the results for each comparison as a new columns:

data["check1"] = data['Category'] != category
data["check2"] = ...
data["check3"] = ...
data["check4"] = ...
data["check5"] = data.List2 == "NULL"


Now use those columns' values in your line with all comparisons.



To gain a better understanding of what it taking up all the time, you could profile the code, e.g. using the CProfile module. You could just run over the first 100 rows of your data for that purpose - the profiling tool will give summary statistics.



If you really want to get stuck into it, check out tha Pandas performance improvement methods, which get into using Numba and Cython. There can be huge gains here, but you need to maybe invest a little more effort. Numba would be the easiest to begin with; unless you are experienced with C-languages.