How to group csv in python without using pandas

Question

I have a CSV file with 3 rows: "Username", "Date", "Energy saved" and I would like to sum the "Energy saved" of a specific user by date.

For example, if username = 'merrytan', how can I print all the rows with "merrytan" such that the total energy saved is aggregated by date? (Date: 24/2/2022 Total Energy saved = 1001 , Date: 24/2/2022 Total Energy saved = 700)

I am a beginner at python and typically, I would use pandas to resolve this issue but it is not allowed for this project so I am at a complete loss on where to even begin. I would appreciate any help and guidance. Thank you.

If you are not allowed to use pandas in this project, how do you read the data? Have you tried a specific approach to share with us? — TheFaultInOurStars
– TheFaultInOurStars, Commented Feb 24, 2022 at 3:51

hteza · Accepted Answer · 2022-02-24 07:52:56Z

2

My alternative to opening csv files is to use csv module of native python. You read them as a "file" and just extract the values that you need. I filter using the first column and keep only keep the equal index values from the concerned column. (which is thrid and index 2.)

import csv

energy_saved = []
with open(r"D:\test_stack.csv", newline="") as csvfile:
    file = csv.reader(csvfile)
    for row in file:
        if row[0]=="merrytan":
           energy_saved.append(row[2])
    energy_saved = sum(map(int, energy_saved))

Now you have a list of just concerned values, and you can sum them afterwards.

Edit - So, I just realized that I left out the time part of your request completely lol. Here's the update.

import csv
my_dict = {}
with open(r"D:\test_stack.csv", newline="") as file:
    for row in csv.reader(file):
        if row[0]=="merrytan":
             my_dict[row[1]] = my_dict.get(row[1], 0) + int(row[2])

So, we need to get the date column of the file as well. We need to make a presentation of two "rows" but when Pandas has been prohibited, we will go to dictionary with date as keys and energy as values.

But your date column has repeated values (regardless intended or else) and Dictionaries require keys to be unique. So, we use a loop. You add one date value after another as key and corresponding energy as value to the new dictionary, but when it is already present, you will sum with the existing value instead.

edited Feb 24, 2022 at 7:52

answered Feb 24, 2022 at 4:12

hteza

3211 silver badge8 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Zach Young Over a year ago

Good start. You need to go back and read the request, I see OP asking for sums binned by date… and there’s a typo in the question… 700 should be for 2/25 (not 24).

hteza Over a year ago

Yep. I just noticed how I have left out a step of the request. Thanks lol

James McPherson · Accepted Answer · 2022-02-24 03:53:56Z

0

I would turn your CSV file into a two-level dictionary, with username and then date as the keys

infile = open("data.csv", "r").readlines()
savings = dict()

# Skip the first line of the CSV, since that has the column names
# not data
for row in infile[1:]:
    username, date_col, saved = row.strip().split(",")
    saved = int(saved)
    if username in savings:
        if date_col in savings[username]:
            savings[username][date_col] = savings[username][date_col] + saved
        else:
            savings[username][date_col] = saved
    else:
        savings[username] = {date_col: saved}

answered Feb 24, 2022 at 3:53

James McPherson

2,5861 gold badge14 silver badges17 bronze badges

2 Comments

Zach Young Over a year ago

Why recommend hand-parsing the CSV, instead of using the well-tested and spec-compliant CSV module in the standard library?

James McPherson Over a year ago

Laziness on my part, and I believe there's value in knowing how to write a solution when you really don't have useful modules available to you.

Collectives™ on Stack Overflow

How to group csv in python without using pandas

2 Answers 2

2 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related