Python Forum
Adding 2-column CSV together in base Python without using pandas
Thread Rating:
  • 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
Adding 2-column CSV together in base Python without using pandas
#1
I have a CSV file with ID column (Username) and two numeric columns. In base Python I want to get the ID and the sum of Auto and Manual Score, then generate another CSV with the result.

Example input CSV:
Username Auto Score Manual Score
1234, 1,
1234, 1,
1234, 1,
1234, , 1.5
345, 1,
345, 1,
345, , 2
133, 1,
133, 1,
133, , 2.5

Here is my code:
import csv

with open("exam_for_2019.csv") as csvfile:
    reader = csv.DictReader(csvfile)
    
    for row in reader:
        sum_row = int(row['Auto Score']) + int(row['Manual Score'])
        print(sum_row)
But I got the error as: ValueError: invalid literal for int() with base 10: '' .

How can I fix it to sum the result with one Username with one sum score each row?
Reply
#2
You need to check each value before you sum it, and if it's empty, treat it as zero. Something like:

auto_score = int(row['Auto Score']) if row['Auto Score'].strip() else 0
Craig "Ichabod" O'Brien - xenomind.com
I wish you happiness.
Recommended Tutorials: BBCode, functions, classes, text adventures
Reply
#3
(Sep-29-2019, 11:59 AM)ichabod801 Wrote: You need to check each value before you sum it, and if it's empty, treat it as zero. Something like:
 auto_score = int(row['Auto Score']) if row['Auto Score'].strip() else 0 

I do a bit changed on my code as you said, but I got error when I try to sum then
import csv
 
with open("exam_for_2019.csv") as csvfile:
    reader = csv.DictReader(csvfile)
     
    for row in reader:
        auto_score = float(row['Auto Score']) if row['Auto Score'].strip() else 0
        Manual_Score = float(row['Manual Score']) if row['Manual Score'].strip() else 0
        sum_row = float(auto_score) + float(Manual_Score)
        sums = sum(sum_row)
        print(sums)
And I get TypeError: 'float' object is not iterable

(Sep-29-2019, 11:59 AM)ichabod801 Wrote: You need to check each value before you sum it, and if it's empty, treat it as zero. Something like:

auto_score = int(row['Auto Score']) if row['Auto Score'].strip() else 0

And also is possible assign the total number with each user name?
Like
Username Total
1234, 4.5
345, 4
133, 4.5
Reply
#4
A couple of things. On line 7 you have float(row['Auto Score']). So auto_score is a float. Therefore, there is no reason for float(auto_score) on line 9. You can just use auto_score.

On line 9 you add the two scores together. So there is no need to sum them on line 10. And that is why you are getting an error. The sum function is for a list of numbers, and you are passing it one number. But like I said, you don't need that.

To get the full output, you are going to want to build a list with append in your for loop. Here's an example:

sums = []
for x in range(5):
    sums.append(x + x)
However, you are going to want to append a tuple of the username and the total. Once you have that list, you can send it through csv.writer to output the data to a file.
Craig "Ichabod" O'Brien - xenomind.com
I wish you happiness.
Recommended Tutorials: BBCode, functions, classes, text adventures
Reply


Possibly Related Threads…
Thread Author Replies Views Last Post
  Adding PD DataFrame column bsben 2 241 Mar-08-2024, 10:46 PM
Last Post: deanhystad
  Python Alteryx QS-Passing pandas dataframe column inside SQL query where condition sanky1990 0 686 Dec-04-2023, 09:48 PM
Last Post: sanky1990
  pandas : problem with conditional filling of a column Xigris 2 591 Jul-22-2023, 11:44 AM
Last Post: Xigris
  How to assign a value to pandas dataframe column rows based on a condition klllmmm 0 797 Sep-08-2022, 06:32 AM
Last Post: klllmmm
  Basic Pandas, obtaining a value from column and row JamesOzone 2 1,054 Jun-30-2022, 07:16 PM
Last Post: jefsummers
  Reshaping a single column in to multiple column using Python sahar 7 1,967 Jun-20-2022, 12:35 PM
Last Post: deanhystad
  Float Slider - Affecting Values in Column 'Pandas' planckepoch86 0 1,368 Jan-22-2022, 02:18 PM
Last Post: planckepoch86
  pandas pivot table: How to find count for each group in Index and Column JaneTan 0 3,225 Oct-23-2021, 04:35 AM
Last Post: JaneTan
  Adding another condition for df column comparison Mark17 2 1,606 Sep-30-2021, 03:54 PM
Last Post: Mark17
  Python Pandas: How do I extract all the >1000 data from a certain column? JaneTan 0 1,529 Jul-17-2021, 09:09 AM
Last Post: JaneTan

Forum Jump:

User Panel Messages

Announcements
Announcement #1 8/1/2020
Announcement #2 8/2/2020
Announcement #3 8/6/2020