How to filter a row in a csv file based on multiple indexes?

user_14453519 0
Viewed 95

I have a file which looks like this:

#This is TEST-data
2020-09-07T00:00:03.230+02:00,ID-10,3,London,Manchester,London,1,1,1
2020-09-07T00:00:03.230+02:00,ID-10,3,London,London,Manchester,1,1
2020-09-07T00:00:03.230+02:00,ID-20,2,London,London,1,1
2020-09-07T00:00:03.230+02:00,ID-20,2,London,London1,1
2020-09-07T00:00:03.230+02:00,ID-30,3,Madrid,Sevila,Sevilla,1,1,1
2020-09-07T00:00:03.230+02:00,ID-30,3,Madrid,Sevilla,Madrid,1
2020-09-07T00:00:03.230+02:00,ID-40,2,Madrid,Barcelona,1,1,1,1

Index[2] in each row shows how much cities are present in that specific row. So the first row has value 3 for index[2], which are London, Manchester, London.

I am trying to do the following:

For every row I need to check if any of row [3] + the cities mentioned after it (based on the number of cities) are present in cities_to_filter.

This is my current code:

path = r'c:\data\ELK\Desktop\test_data_countries.txt'

cities_to_filter = ['Sevilla', 'Manchester']

def filter_row(row):
    # amount_of_cities = row[2]    
    condition_1 = any(city in row for city in cities_to_filter)
    
    return condition_1

with open (path, 'r') as output_file:
    reader = csv.reader(output_file, delimiter = ',')
    next(reader)
    for row in reader:
        if filter_row(row):
            print(row)

The code I have works okay for this dataset but its quiet risky as its looking at every column, even the ones which I know aren't cities. I need my code to check only the columns which are cities based on the amount of cities each row contains.

filter indexing list python
1 Answers

The cities "list" always starts at the same offset and the length is known from row[2]. So just slice it out and use your any() expression to check for cites to filter, or you could use set operations, but that's probably overkill for this:

import csv

path = r'c:\data\ELK\Desktop\test_data_countries.txt'

cities_to_filter = ['Sevilla', 'Manchester']

def filter_row(row):
    count = int(row[2])
    cities = row[3:3+count]
    return any(city in cities for city in cities_to_filter)

with open (path, 'r') as input_file:
    reader = csv.reader(input_file, delimiter = ',')
    next(reader)
    for row in reader:
        if filter_row(row):
            print(row)

Also, renamed output_file to input_file as the file is being read, not written.

Output

['2020-09-07T00:00:03.230+02:00', 'ID-10', '3', 'London', 'Manchester', 'London', '1', '1', '1']
['2020-09-07T00:00:03.230+02:00', 'ID-10', '3', 'London', 'London', 'Manchester', '1', '1']
['2020-09-07T00:00:03.230+02:00', 'ID-30', '3', 'Madrid', 'Sevila', 'Sevilla', '1', '1', '1']
['2020-09-07T00:00:03.230+02:00', 'ID-30', '3', 'Madrid', 'Sevilla', 'Madrid', '1']
mhawke_219450
Related