Checking a CSV for the existence of a similar value in Python

Related searches

Consider the following CSV:

date,description,amount
14/02/2020,march contract,-99.00
15/02/2020,april contract,340.00
16/02/2020,march contract,150.00
17/02/2020,april contract,-100.00

What I'd like to do is:

  • Iterate through all of the rows
  • Total the amounts of lines which have the same description
  • Return the last line which has that newly-calculated amount

Applied to the above example, the CSV would look like this:

16/02/2020,march contract,51.00
17/02/2020,april contract,240.00

So far, I've tried nesting csv.reader()s inside of each other and I'm not getting the result I am wanting.

I'd like to achieve this without any libraries and/or modules.

Here is the code I have so far, where first_row is each row in the CSV and second_row is the iteration of looking for matching descriptions:

csv_reader = csv.reader(report_file)
        for first_row in csv_reader:
            description_index = 5
            amount_index = 13
            print(first_row)
            for second_row in csv_reader:
                if second_row is not first_row:
                    print(first_row[description_index] == second_row[description_index])
                        if first_row[description_index] == second_row[description_index]:
                            first_row[amount_index] = float(first_row[amount_index]) + float(second_row[amount_index])

This will work:

import csv
uniques = {}  # dictionary to store key/value pairs


with open(report_file, newline='') as f:
    reader = csv.reader(f, delimiter=',')
    next(reader, None)  # skip header row
    for data in reader:
        date = data[0]
        description = data[1]
        if description in uniques:
            cumulative_total = uniques[description][0]
            uniques[description] = [cumulative_total+float(data[2]), date]
        else:
            uniques[description] = [float(data[2]), date]

# print output
for desc, val in uniques.items():
    print(f'{val[0]}, {desc}, {val[1]}')

I know that you've asked for a solution without pandas, but you'll save yourself a lot of time if you use it:

df = pd.read_csv(report_file)

totals = df.groupby(df['description']).sum()
print(totals)

Check if specific column in csv file contains data, You don't need pandas for this. csv module would be fine import csv with open(" file.csv", "rb") as f: csvreader = csv.reader(f, delimiter=",") for row in csvreader: if� When we execute this program in Python, the output will look like this: $ python csv1.py A : 1 B : 2 C D : 3 4 A : 5 B : 6 C D : 7 Writing to CSV Files. When you have a set of data that you would like to store inside a CSV file, it’s time to do the opposite and use the write function.


I suggest you should use pandas, it'll be efficient.

or if you still want to go with your way then this will help.

import csv

with open('mycsv.csv') as csv_file:
    csv_reader = csv.reader(csv_file, delimiter=',')
    value_dict = {}
    line_no = 0
    for row in csv_reader:
        if line_no == 0:
            line_no += 1
            continue
        cur_date = row[0]
        cur_mon = row[1]
        cur_val = float(row[2])
        if row[1] not in value_dict.keys():
            value_dict[cur_mon] = [cur_date, cur_val]
        else:
            old_date, old_val = value_dict[cur_mon]
            value_dict[cur_mon] = [cur_date, (old_val + cur_val)]
        line_no += 1

for key, val_list in value_dict.items():
    print(f"{val_list[0]},{key},{val_list[1]}")

Output:

16/02/2020,march contract,51.0
17/02/2020,april contract,240.0

Mark this as answer if it helps you.

14.1. csv — CSV File Reading and Writing — Python 3.4.10 , The so-called CSV (Comma Separated Values) format is the most common The lack of a well-defined standard means that subtle differences often exist in the the overall format is similar enough that it is possible to write a single module� I am writing a script that takes one CSV file searches for a value in another CSV file then writes an output depending on the result it finds. I have been using Python's CSV Distreader and writer. I have it working, but it is very inefficient because it is looping through the 2 sets of data until it finds a result.


working with dictionary makes it easy to access values

import csv
from datetime import datetime

_dict = {}
with open("test.csv", "r") as f:
    reader = csv.reader(f, delimiter=",")

    for i, line in enumerate(reader):
        if i==0:
            headings = [line]
        else:
            if _dict.get(line[1],None) is None:
                _dict[line[1]] = {
                                    'date':line[0], 
                                    'amount':float(line[2])
                                 }
            else:
                if datetime.strptime(_dict.get(line[1]).get('date'),'%d/%m/%Y') < datetime.strptime(line[0],'%d/%m/%Y'):
                    _dict[line[1]]['date'] = line[0]

                _dict[line[1]]['amount'] = _dict[line[1]]['amount'] + float(line[2])

Here your _dict will contain unique description and values

>>> print(_dict)
{'march contract': {'date': '16/02/2020', 'amount': 51.0},  
'april contract': {'date': '17/02/2020', 'amount': 240.0}}

convert to list and add headings

headings.extend([[value['date'],key,value['amount']] for key,value in _dict.items()])

>>>print(headings)
[['date', 'description', 'amount'],['16/02/2020', 'march contract', 51.0], ['17/02/2020', 'april contract', 240.0]]

save list to csv

with open("out.csv", "w", newline="") as f:
    writer = csv.writer(f)
    writer.writerows(headings)

Import a CSV File Into an SQLite Table, Summary: in this tutorial, you will learn various ways to import CSV data into In the first scenario, you want to import data from CSV file into a table that does not exist in the SQLite To verify the import, you use the command .schema to display the structure of SQLite PHP � SQLite Node.js � SQLite Java � SQLite Python� Prerequisites: Working with csv files in Python CSV (Comma Separated Values) is a simple file format used to store tabular data, such as a spreadsheet or database. CSV file stores tabular data (numbers and text) in plain text.


You can also use itertools.groupby and sum() for this if you don't mind outputting in sorted form.

from datetime import datetime
from itertools import groupby
import csv

with open(report_file, 'r') as f:
    reader = csv.reader(f)
    lst = list(reader)[1:]

    sorted_input = sorted(lst, key=lambda x : (x[1], datetime.strptime(x[0],'%d/%m/%Y')))  #sort by description and date
    groups = groupby(sorted_input, key=lambda x : x[1])
    for k,g in groups:
        rows = list(g) 
        total = sum(float(row[2]) for row in rows)
        print(f'{rows[-1][0]},{k},{total}')  #print last date, description, total

Output:

17/02/2020,april contract,240.0
16/02/2020,march contract,51.0

Reading and Writing CSV Files in Python using CSV Module , CSV is a common format for data interchange as it's compact, simple and general . CSV (comma separated values) - literally "comma-separated values. This is an example of how a CSV file looks like. If File or Directory Exists � Python COPY File � Python Rename File Software Testing as a Career� Contents of the dataframe : Name Age City Marks 0 jack 34 Sydney 155 1 Riti 31 Delhi 177 2 Aadi 16 Mumbai 81 3 Mohit 31 Delhi 167 4 Veena 81 Delhi 144 5 Shaunak 35 Mumbai 135 6 Shaun 35 Colombo 111 7 Riti 32 Colombo 111 **** Check if an element exists in DataFrame using in & not in operators **** ** Use in operator to check if an element exists


Interacting with CSV files using Python, In this article, we will be using Python's CSV module to illustrate how to do some of the most common operations on CSV files like read, write and append. CSV (Comma Separated Values) files are one of the most used forms of data storage. This is due to If the file exists, it clears it and starts writing to it from the first row. Check if a value exists in a DataFrame using in & not in operator in Python-Pandas Last Updated: 02-07-2020 In this article, Let’s discuss how to check if a given value exists in the dataframe or not.


Python: check if key exists in dictionary (6 Ways) Python : Get number of elements in a list, lists of lists or nested list; How to check if a file or directory or link exists in Python ? 6 Ways to check if all values in Numpy Array are zero (in both 1D & 2D arrays) - Python; Python : Convert list of lists or nested list to flat list


In Python, there are many different ways to check whether a file exists and determine the type of the file. This tutorial shows three different techniques about how to check for a file’s existence. Check if File Exists # The simplest way to check whether a file exists is to try to open the file.