Python - a single csv file sort using multiple fields and delete rows that are sequential within that file how do I do this using sqlite

user_15252513 0
Viewed 34

Hi I have the code below that work on sorting a csv file that contains multiple rows for each user and has to be sorted by a few columns and date, where if column 2, 3 and 5 are the same in the current row compared to the previous row we delete the current row (duplicate). the outputs are written to two files on for not duplicate and the other for duplicates.

Here are some of the entries from the file:

shift1,2021-02-14 06:35:00,J,P2,***USER16-J-P2,USER16
shift1,2021-02-15 07:35:00,J9,P2,***USER16-J9-P2,USER16
shift1,2021-02-17 06:35:00,J,P3,***USER16-J-P3,USER16
shift1,2021-02-18 07:35:00,J9,P2,***USER16-J9-P2,USER16
shift1,2021-02-19 06:35:00,J,P1,***USER16-J-P1,USER16
shift1,2021-02-22 07:35:00,J9,P2,***USER16-J9-P2,USER16
shift1,2021-02-23 07:35:00,J9,P2,***USER16-J9-P2,USER16
shift1,2021-02-25 06:35:00,J,P3,***USER16-J-P3,USER16
shift1,2021-02-26 06:35:00,J,P3,***USER16-J-P3,USER16
shift1,2021-02-27 06:35:00,J,P2,***USER16-J-P2,USER16
...
shift1,2021-02-17 07:35:00,J9,P3,***USER23-J9-P3,USER23
shift1,2021-02-18 07:35:00,J9,P3,***USER23-J9-P3,USER23
shift1,2021-02-19 06:35:00,J,P1,***USER23-J-P1,USER23
shift1,2021-02-19 22:55:00,N,P1,***USER23-N-P1,USER23
shift1,2021-02-21 06:35:00,J,P3,***USER23-J-P3,USER23
shift1,2021-02-22 22:55:00,N,P2,***USER23-N-P2,USER23
shift1,2021-02-23 22:55:00,N,P2,***USER23-N-P2,USER23
shift1,2021-02-24 22:55:00,N,P2,***USER23-N-P2,USER23
shift1,2021-02-26 07:35:00,J9,P2,***USER23-J9-P2,USER23

Here are the results:

shift1,2021-02-14 06:35:00,J,P2,***USER16-J-P2,USER16
shift1,2021-02-15 07:35:00,J9,P2,***USER16-J9-P2,USER16
shift1,2021-02-17 06:35:00,J,P3,***USER16-J-P3,USER16
shift1,2021-02-18 07:35:00,J9,P2,***USER16-J9-P2,USER16
shift1,2021-02-19 06:35:00,J,P1,***USER16-J-P1,USER16
shift1,2021-02-22 07:35:00,J9,P2,***USER16-J9-P2,USER16
shift1,2021-02-25 06:35:00,J,P3,***USER16-J-P3,USER16
shift1,2021-02-27 06:35:00,J,P2,***USER16-J-P2,USER16
...
shift1,2021-02-15 14:35:00,S,P1,***USER23-S-P1,USER23
shift1,2021-02-17 07:35:00,J9,P3,***USER23-J9-P3,USER23
shift1,2021-02-19 06:35:00,J,P1,***USER23-J-P1,USER23
shift1,2021-02-19 22:55:00,N,P1,***USER23-N-P1,USER23
shift1,2021-02-21 06:35:00,J,P3,***USER23-J-P3,USER23
shift1,2021-02-22 22:55:00,N,P2,***USER23-N-P2,USER23
shift1,2021-02-26 07:35:00,J9,P2,***USER23-J9-P2,USER23

Here is the Code:

import csv

entries = []
last_entry = [None, None, None]
check = [None, None, None]
duplicate_entries = []
with open('test.txt', 'r') as my_file:
    for line in my_file:
        columns = line.strip().split(',')
        check[0] = columns[2]
        check[1] = columns[3]
        check[2] = columns[5]
        if check != last_entry:
            if columns[2] not in entries:
                last_entry[0] = columns[2]
                if columns[3] not in entries:
                    last_entry[1] = columns[3]
                    if columns[5] not in entries:
                        last_entry[2] = columns[5]
                        if columns[1] not in entries:
                            entries.append(columns)

        else:
            duplicate_entries.append(columns)

# writes entries to
with open('test_out.txt', 'w') as out_csv_file:
    text_out = csv.writer(out_csv_file, delimiter=",")
    for result in entries:
        text_out.writerow(result)


# writing out duplicates from duplicate_entries 
with open('test_dups.txt', 'w') as out_dups_file:
    text_out = csv.writer(out_dups_file, delimiter=",")
    for result in duplicate_entries:
        text_out.writerow(result)

Since I am very new to python and programming I would like to know how I can improve this and also how could I do this in sqlite3 for python.

delete-row csv sorting sqlite python
1 Answers

If you just need to check if the previous row has the same values in certain columns, the following approach might be sufficient. You can use Python's itemgetter() to extract just the values you need to compare with. Also you can use a csv.reader() instead of using split(',') when reading your existing file in:

from operator import itemgetter
import csv

entries = []
duplicate_entries = []
last_entry = [None, None, None]
req_cols = itemgetter(2, 3, 5)

with open('v1.txt', 'r') as f_input:
    csv_input = csv.reader(f_input)
    
    for row in csv_input:
        cur_entry = req_cols(row)
        
        if cur_entry != last_entry:
            last_entry = cur_entry
            entries.append(row)
        else:
            duplicate_entries.append(row)

# writes entries to
with open('test_v2.txt', 'w', newline='') as out_csv_file:
    text_out = csv.writer(out_csv_file)
    text_out.writerows(entries)

# writing out duplicates from duplicate_entries 
with open('test_dups.txt', 'w', newline='') as out_dups_file:
    text_out = csv.writer(out_dups_file)
    text_out.writerows(duplicate_entries)

You also should add the newline='' parameter to avoid problems with extra newlines in your output (see the csv.reader() documentation). Also, csv.writerows() can be used to write your list of rows in a single call.

user_49857330
Related