Member Avatar for felix001

Im pretty new to Python, so i apologise in advance.
My main goal is to parse a csv file (as per below) and to create a list named based on column 5 which includes values from column 7 as the elements.

01/11/2010,DEB,12345,12345,CREDIT CARD,,44
01/11/2010,DEB,12345,12345,TAKE AWAY,,20
01/11/2010,DEB,12345,12345,TAKE AWAY,,22
01/11/2010,DEB,12345,12345,TAKE AWAY,,24

The code I have so far is :

#!/usr/bin/python

import csv
import string

itemlist = []

reader = csv.reader(open("statement.csv", "rb"))
for row in reader:
        itemlist.append(row[4])
        itemlist = list(set(itemlist))

for x in itemlist[:]:
        item = x
        item = []

The bit im getting a bit lost on is how to create a list from each element of my itemlist list.

Thanks in advance...

  • 3 Contributors
  • 7 Replies
  • 127 Views
  • 1 Day Discussion Span
  • Latest Post Latest Post by TrustyTony

Dani AI

Generated

The original goal was to group rows by the category in column 5 and collect the numeric values from column 7 so they can be summed (or otherwise analysed). ended up using a dict to accumulate totals, and and offered useful simplifications. For readers who want per‑category lists of individual transactions (not just running totals) and robust handling of money, the approach below is easier to extend and safer for real data.

A practical pattern: use csv.DictReader so columns are referred to by name, collections.defaultdict(list) to collect each category's amounts, and decimal.Decimal for monetary accuracy. Trim whitespace, strip currency characters, skip malformed rows, and delay rounding until output. Example:

from collections import defaultdict
from decimal import Decimal, InvalidOperation
import csv

categories = defaultdict(list)

with open('statement.csv', newline='') as fh:
    reader = csv.DictReader(fh,
                            fieldnames=['date','type','a','b','category','empty','amount'],
                            skipinitialspace=True)
    for row in reader:
        cat = (row.get('category') or '').strip()
        amt = (row.get('amount') or '').strip()
        if not cat or not amt:
            continue
        amt = amt.replace('$','').replace('£','').replace(',','')
        try:
            d = Decimal(amt)
        except InvalidOperation:
            continue
        categories[cat].append(d)

totals = {k: sum(v) for k, v in categories.items()}

With categories you have per‑category lists you can sum, average, sort, or serialize. totals gives quick sums; format for display only (e.g., format(total, '.2f')) so internal values remain exact.

Troubleshooting notes: handle empty or malformed rows and stray commas by checking field existence; remove has_key() (deprecated) and use key in dict if you need membership checks; prefer a context manager (with open(...)) to ensure files close; and consider using integer cents or Decimal rather than float for money. For large datasets or heavier analysis, import the CSV into pandas.DataFrame for grouping and aggregation. For Decimal details see the Python docs: decimal module.

Recommended Answers

All 7 Replies

Member Avatar for TrustyTony

column 5 has words like TAKE AWAY and CREDIT CARD.

I have not used csv lot but would you not need to set separator to comma. If the reader part works, you end up with unique items in column 5 of file in itemlist, but you need to do line 11 only once after loop not inside loop. This is how it looks to me by only reading the code, I may be wrong...

Line 15 undos line 14.,

Edited by TrustyTony because: n/a
Member Avatar for felix001

thanks for your quick response, the reade section seems to work fine but it is the assign the itemlist elements to new lists and then appending further elements from the csv to it that I am having issues with ......

Member Avatar for TrustyTony

You have saved only column 5, you have no further elements.

#!/usr/bin/python

import csv
itemlist = []

reader = csv.reader(open("statement.csv", "rb"))
for row in reader:
    itemlist.append(row[4])

# only one time!
itemlist = list(set(itemlist))

print itemlist

""" Output:
['CREDIT CARD', 'TAKE AWAY']
"""
Edited by TrustyTony because: n/a
Member Avatar for felix001

ok thanks. The itemlist is fine but ideally i wanted to
** create additonal lists based on each of the itemlist elements.
** With these new lists assign the column 7 vaules from the csv to the relevant line.

My overall plan is to have a list named "TAKE AWAY" with a number of vaules as elements that I can add together...

Thanks for all your help....

Member Avatar for felix001

Ive managed get this working in the end using a dictonary....

#!/usr/bin/python

import csv
import string

inputFile = open(str("statement.csv"),  'r')

itemlist = []
outputDic = {}
keyIndex = 0

fileReader = csv.reader(inputFile)
for line in fileReader:
        keyIndex=line[4]
        if len(line[6]) == 0:
                pass
        elif outputDic.has_key(keyIndex) == True:
                oldKey = outputDic[keyIndex]
                newKey = line[6]
                newKey=float(newKey)
                oldKey=float(oldKey)
                keySum = newKey+oldKey
                keySum2 = round(keySum, 2)
                outputDic[keyIndex] = str(keySum2)
        else:
                outputDic[keyIndex] = line[6]

print outputDic
Member Avatar for richieking 
elif outputDic.has_key(keyIndex) == True:

No need for True keyword.

You can do this without the keyword True as If statement defaults to true.
simply...

elif outputDic.has_key(keyIndex):

Also helps ;)

Member Avatar for TrustyTony

Ive managed get this working in the end using a dictonary....

#!/usr/bin/python

import csv
import string

inputFile = open(str("statement.csv"),  'r')

itemlist = []
outputDic = {}
keyIndex = 0

fileReader = csv.reader(inputFile)
for line in fileReader:
        keyIndex=line[4]
        if len(line[6]) == 0:
                pass
        elif outputDic.has_key(keyIndex) == True:
                oldKey = outputDic[keyIndex]
                newKey = line[6]
                newKey=float(newKey)
                oldKey=float(oldKey)
                keySum = newKey+oldKey
                keySum2 = round(keySum, 2)
                outputDic[keyIndex] = str(keySum2)
        else:
                outputDic[keyIndex] = line[6]

print outputDic

I simplified your code little for you, if you want to print rounded figures later, use print("%.2f" % value) in printing them.

#!/usr/bin/python
import csv

inputFile = open(str("statement.csv"),  'r')
outputDic = {}

fileReader = csv.reader(inputFile)
for line in fileReader:
        keyIndex=line[4]
        if line[6]:
            if outputDic.has_key(keyIndex):
                    outputDic[keyIndex] = outputDic[keyIndex] + float(line[6])
            else:
                    outputDic[keyIndex] = float(line[6])

print outputDic
Edited by TrustyTony because: n/a
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.