How to show data by week with weeknumber?

Question

I have a huge list of dates and numbers like this:

 1.1.2018 0:00;2590
 3.1.2018 1:00;2530
 4.2.2018 2:00;1700
 6.2.2018 3:00;2340
 18.3.2018 4:00;1800
 15.4.2018 5:00;2850
 ...

And I need to add all numbers together that have the same week number and return total of numbers in a week like this:

This is my code so far, I've separated the dates and numbers in their own lists but not sure how to go on from here.

import datetime

def main():
    file = open("2018Electricity.txt", "r")
    line = file.readline()
    time_list = []
    electricity_list = []
    total = []

    for i in file:
        time = i.strip().split(';')[0]
        electricity = i.strip().split(';')[1]
        time_list.append(datetime.strptime(time, '%d.%m.%Y %H:%M'))
        electricity_list.append(electricity)
        
    file.close()

main()

The task requires me to have weeks 0-53 and to use lists and strftime %W.

Use a dictionary like in [this answer](https://stackoverflow.com/a/64312540/355230) to your earlier question on make the keys the week-number and the associated value that's total of the numbers seen for the corresponding week. You could use a [`collections.defaultdict(int)`](https://docs.python.org/3/library/collections.html#collections.defaultdict) instead of regular dictionary because it would make doing this easier. — martineau, Oct 20 '20 at 07:34

Sushil · Accepted Answer · 2020-10-20T09:25:32.337

Here is the full code (explanations provided in the code as comments):

from datetime import datetime #You messed up with the import statement. It should be from datetime import datetime instead of import datetime

def main():
    file = open("2018Electricity.txt", "r")
    line = file.readline()
    time_list = []
    electricity_list = []
    total = []

    for i in file:
        time = i.strip().split(';')[0]
        electricity = i.strip().split(';')[1]
        datee = datetime.strptime(time, '%d.%m.%Y %H:%M')
        
        if  datee.month != 12:
            time_list.append(datee.isocalendar()[1])
        else:
            if datee.isocalendar()[1] == 1:
                time_list.append(53)
            else:
                time_list.append(datee.isocalendar()[1])

        electricity_list.append(int(electricity)) #Converts electricity to an integer and appends it to electricity_list

    week_numbers = list(set(time_list)) #Removes all repeated week numbers

    for week_number in week_numbers: #Iterates over the week_numbers
        curr_elec = 0
        for week,elec in zip(time_list,electricity_list): #Creates an iterable out of time_list and electricty_list
            if week == week_number:
                curr_elec += elec #Running total of the electricity for the current week
        print(f"{week_number};{curr_elec}")

    file.close()

main()

Output:

This gives only weeks 1-52 and week 53 data is added to 1. Is there a way to print weeks 0-53? — Hueqen, Oct 20 '20 at 08:43

score 1 · Answer 2 · answered Oct 20 '20 at 07:58

To me, a pandas DataFrame seems to be the right tool for this job. Read the csv to a df, parse the date/time column to datetime, groupby week number and use sum as aggfunc:

from io import StringIO # for demo only
import pandas as pd

data = """datetime;values
1.1.2018 0:00;2590
3.1.2018 1:00;2530
4.2.2018 2:00;1700
6.2.2018 3:00;2340
18.3.2018 4:00;1800
15.4.2018 5:00;2850"""
 
 
df = pd.read_csv(StringIO(data), sep=';', parse_dates=['datetime'], dayfirst=True)

df.groupby(df.datetime.dt.isocalendar().week)['values'].sum()

Out[8]: 
week
1     5120
5     1700
6     2340
11    1800
15    2850
Name: values, dtype: int64

You can conveniently write this data to a csv, see pd.to_csv.

Is there a way to do this without pandas? This is for a simple course task and we're supposed to use simple lists and loops, very beginner type of stuff. — Hueqen, Oct 20 '20 at 08:45
@Hueqen: sure, have a look at defaultdict, see martineau's comment on your question. — FObersteiner, Oct 20 '20 at 10:11

How to show data by week with weeknumber?

2 Answers2