How to sort .csv file test log which item first fail and paint color - Printable Version +- Python Forum (https://python-forum.io) +-- Forum: Python Coding (https://python-forum.io/forum-7.html) +--- Forum: General Coding Help (https://python-forum.io/forum-8.html) +--- Thread: How to sort .csv file test log which item first fail and paint color (/thread-37903.html) |
How to sort .csv file test log which item first fail and paint color - SamLiu - Aug-06-2022 Hi all, I want to sort csv file find which item was failed(if this column have limit)and paint color for the cell, delete afterward item such as final data.xlsx format. I only know how to sort failed and don't know how to compared limit, kindly help me for the matter, thanks!! import csv array=[] failed=[] count=0 with open('Log.csv','r') as fp: csv_reader=csv.reader(fp) header = next(csv_reader) listreport=list(csv_reader) for line in listreport: if count<3: array.append(line) if line[5]=='Failed': failed.append(line) count+=1 with open('new_name.csv','w',encoding='utf-8',newline='') as new_line: csv_writer=csv.writer(new_line) csv_writer.writerow(header) csv_writer.writerows(array) csv_writer.writerows(failed) RE: How to sort .csv file test log which item first fail and paint color - rob101 - Aug-06-2022 You are working with a large amount of data, which is creating huge lists (len = 385), much of the data is duplicated, within the lists, which is very confusing. Also, I'm unsure why you are creating two objects: header and listreport . You could do the exact same thing with one object (the iterator in the for loop), which I've named row .with open('Log.csv') as log: csv_reader = csv.reader(log) for row in csv_reader: if count<3: array.append(row) if row[5]=='Failed': failed.append(row) count+=1I'm still unclear about your objective, as your English is a little hard for me to follow; I can see that you are doing your best, so the fail here is more on my side than yours. I'll have a think about this and try to offer better help, but if you can find a way to help me better understand your objective, then please do. edit to add: I think that the major issue here is that you're trying to do too much at once: the task needs to be broken down into smaller steps (sub-tasks, if you will), solving one thing at a time, each step moving ever closer to the solution. RE: How to sort .csv file test log which item first fail and paint color - SamLiu - Aug-06-2022 Thank you for your prompt reply, this is test log of product measured. For the file I want to two step. 1)Sort failed item.-->Already done. 2)Check column data whether in limit if assign limit such in Log.csv Column M(VDD Continuity) limit is -0.8 to 0.25 and then select that column compare value if in range and then check next(LR Continuity) have limit column does same check, if out range paint the cell color and skip this row, thanks! RE: How to sort .csv file test log which item first fail and paint color - rob101 - Aug-06-2022 You are very welcome. I have worked with Excel for many years and (from my experience) I see no reason for using Python to solve this problem. All the tools you need (the functions) are already there, in Excel, such as Conditional Formatting, Logical, Search, etc, which can be used to reference any cell in any worksheet. By trying to use Python, you now need to solve two problems, rather than just the one. RE: How to sort .csv file test log which item first fail and paint color - SamLiu - Aug-06-2022 Thanks in advance! Yes ,I have try use excel conditional formatting between function, many column need to compared by manually. It is a large workload, so want to try use python easy handle this matter. RE: How to sort .csv file test log which item first fail and paint color - DeaD_EyE - Aug-06-2022 It's not so easy because you need to know how csv.reader works and you need to convert str into float for example. Otherwise you can't do the comparison. Here an example with:
Code: import csv from operator import itemgetter class Reader: def __init__(self, csv_file, sort_fields): self.fd = open(csv_file, newline="") self.sort_fields = itemgetter(*sort_fields) # self.sort_fields(row) -> return a list # with the wanted data from the mapping # skipping first line next(self.fd) # DictReader reads automatically the first line # to get the column names self.csv = csv.DictReader(self.fd, delimiter=",") # getting minium and maximum values self.minimum_values = next(self.csv) self.maximum_values = next(self.csv) # getting the limit values from the dict and converting them to a float # that they could used for comparison self.vdd_continuity_min = float(self.minimum_values["VDD Continuity"]) self.vdd_continuity_max = float(self.maximum_values["VDD Continuity"]) self.lr_continuity_min = float(self.minimum_values["LR Continuity"]) self.lr_continuity_max = float(self.maximum_values["LR Continuity"]) @staticmethod def sort_by_float(row): # calling self.sort_fields with row -> data # map takes each element from data and call it with float() # tuple consumes the map # a tuple with floats is returned return tuple(map(float, self.sort_fields(row))) def __iter__(self): yield from self.csv def __enter__(self): return self def __exit__(self, exc_typ, exc_obj, exc_tb): # closes files automatically when leaving # the context manager self.fd.close() @staticmethod def test_pass(row): # no instance (self) is needed to do the test return row.get("Pass/Fail", "").lower() == "passed" def in_valid_range(self, row): # here self is needed, because attributes from this # instance are read vdd = float(row["VDD Continuity"]) lr = float(row["LR Continuity"]) return (self.vdd_continuity_min <= lr <= self.vdd_continuity_max) and ( self.lr_continuity_min <= lr <= self.lr_continuity_max ) if __name__ == "__main__": with Reader("Log.csv", sort_fields=["VDD Continuity", "Temperature"]) as reader: # reader instance has now some attributes print(f"{reader.vdd_continuity_min=}") print(f"{reader.vdd_continuity_max=}") print(f"{reader.lr_continuity_min=}") print(f"{reader.lr_continuity_max=}") # calls implicity the __iter__ method of the instance of Reader for row in reader: if reader.test_pass(row) and reader.in_valid_range(row): part_id = row.get("Part ID") if part_id: print(part_id)The output could be made with: https://www.geeksforgeeks.org/working-with-excel-spreadsheets-in-python/ But working directly with Excel will solve your task faster (currently). Later if you more experienced, this code is nothing difficult to write. EDIT: Added the sorting. It's not possible to use this method, if the file is the half of your RAM. RE: How to sort .csv file test log which item first fail and paint color - Larz60+ - Aug-06-2022 FYI: you can convert to pandas dataframe and manipulate that. This may ( or may not be useful in this instance ) import pandas as pd import os def display_log(filename): df = pd.read_csv(filename) print(df) def main(): # this code changes CWD to same directory as python script os.chdir(os.path.abspath(os.path.dirname(__file__))) display_log('Log.csv') if __name__ == '__main__': main()which produces (partial):
RE: How to sort .csv file test log which item first fail and paint color - DeaD_EyE - Aug-06-2022 Pandas feels for me like numpy + black magic .Pandas is powerful, but what I see is, that people without knowledge about the Core Language Python itself trying to work with this framework.
RE: How to sort .csv file test log which item first fail and paint color - SamLiu - Aug-06-2022 It appreciated for your reply, I just a newbie in Python. RE: How to sort .csv file test log which item first fail and paint color - SamLiu - Aug-24-2022 import csv up_limit=[] low_limit=[] dt=[] index_number=[12,13,14,15,22,26,32,41,42,44,45,47,48,49,50,51,52,53,54,55,56,57,58,60,62,64,66,74,80,85,86,91,92,97,98,102, 104,108,110,114,116,120,122,126,128,132,134,138,140,144,146,152,173,177,204,205,206,207,208,210,212,214,215,216, 218,239,240,241,242,243,247,248,263,264,270,271,275,277,281,283,292,299,301,304,306,309,310,311,312,313,314,315, 316,317,318,319,320,321,322,323,324,325,326,327,328,329,330,331,332,333,334,335,336,337,338,339,340,341,342,343, 344,345,346,347,348,349,350,351,352,353,354,355,356,357,358,359,360,361,362,363,364,365,366,367,368,369,370,371, 372,373,374,375,376,377,378,379,380,381,382,383,384] with open('Log.csv','r') as file: readerfile=csv.reader(file) for i, rows in enumerate(readerfile): if i==2 : for j in range(len(index_number)): col=rows[index_number[j]] up_limit.append(float(col)) elif i==3: for j in range(len(index_number)): col = rows[index_number[j]] low_limit.append(float(col)) file.close() with open('Log.csv', 'r') as fp: readerfile = csv.reader(fp) header0=next(readerfile) header1=next(readerfile) header2=next(readerfile) header3=next(readerfile) data=list(readerfile) data.sort(key=lambda row:(int(row[2]))) for row in data: if row[5]=='Failed': array.append(row) for i,rows in enumerate(array): for j in range(len(index_number)): col_value = rows[index_number[j]] if num<len(index_number): if float(col_value)>=up_limit[num] and float(col_value)<=low_limit[num]: dt.append(col_value) num += 1 else: num=0 break with open('sorted_data.csv','w',encoding='utf-8',newline='') as new: csvwriter=csv.writer(new) csvwriter.writerow(header0) csvwriter.writerow(header1) csvwriter.writerow(header2) csvwriter.writerow(header3) csvwriter.writerows(dt)I have updated the code which can read up&low limit compare by row.Why final generated file format is wrong?The sorted_data.csv is what I want. |