Group per week

2

I have a table like this I am trying to separate by week, I do not have much knowledge in which functions I can use to check how many weeks have between those dates, to later group. The data is in a csv table, I am using pandas to open the csv file.

         Nome completo  count
Hora                                                      

2017-03-06  Talita       4
2017-03-07  Filho        8
2017-03-07  Jao         10
2017-03-07  Otavio       6
2017-03-08  Gabriel      2
2017-03-13  Guilherme    1

Final table that I intend to mount

Nome         Semana_1  Semana_2   
Talita          4         0
Filho           8         0
Jao             10        0
Otavio          6         0
Gabriel         2         0
Guilherme       0         1
    
asked by anonymous 19.08.2017 / 14:05

2 answers

1

Since you are using pandas to read csv , I suggest using your own functions to resolve this issue. Of course there must be several ways to do this with the pandas, I will introduce what occurred to me.

  

Edited
  See that I will repeat Talita , in the same week, to show and effectiveness of the solution. : -)

Simulating csv :

import io 
import pandas as pd

s = '''
"Data","Nome","Count"
2017-03-06,"Talita",4
2017-03-07,"Filho",8
2017-03-07,"Jao",10
2017-03-07,"Talita",5
2017-03-07,"Otavio",6
2017-03-08,"Gabriel",2
2017-03-13,"Guilherme",1
'''

Reading csv :

df = pd.read_csv(io.StringIO(s), parse_dates=True)

Grouping:

df['Data']=pd.to_datetime(df['Data'])
df['Semana'] = df['Data'].dt.to_period('W-THU')
df = df.groupby(by=['Semana', 'Nome'])['Nome', 'Count', 'Data'].sum()

Final result:

                                 Count
Semana                Nome            
2017-03-03/2017-03-09 Filho          8
                      Gabriel        2
                      Jao           10
                      Otavio         6
                      Talita         9
2017-03-10/2017-03-16 Guilherme      1

From this table you can make a iterrows mount in the format you want, besides being able to export to various formats with .to , for example: df.to_csv , df.to_json , to_latex, to_pickle, to_records, to_string, to_xarray e "There you go ...."

    
19.08.2017 / 22:44
1

Matheus, I put a code where I create a dictionary with the data that you passed it, and I create 2 methods: one to add the exact week of the year in each object of the user list, another to organize the output table , to be as close as possible to what you want to put there in your example.

To identify the week, I used the .isocalendar () attribute, which returns a tuple containing the calendar of the week.

import datetime

user_list = [
    {
        "name": "Talita",
        "count": 4,
        "date": datetime.date(year=2017, month=3, day=6),
    },
    {
        "name": "Filho",
        "count": 8,
        "date": datetime.date(year=2017, month=3, day=7),
    },
    {
        "name": "Jao",
        "count": 10,
        "date": datetime.date(year=2017, month=3, day=7),
    },
    {
        "name": "Otavio",
        "count": 6,
        "date": datetime.date(year=2017, month=3, day=7),
    },
    {
        "name": "Gabriel",
        "count": 2,
        "date": datetime.date(year=2017, month=3, day=8),
    },
    {
        "name": "Guilherme",
        "count": 1,
        "date": datetime.date(year=2017, month=3, day=13),
    },
]

def get_week(user_list):
    for u in user_list:
        week = u['date'].isocalendar()[1]
        u['week'] = week
    return user_list

def group_by_week(user_list):
    actual_week = 0
    week_group = {}
    for u in user_list:
        if actual_week != u['week']:
            week_group[u['week']] = []
            week_group[u['week']].append(u)
            actual_week = u['week']
        else:
            week_group[actual_week].append(u)
    return week_group


# Adicionando semana em cada objeto
print("~> Adicionando semana em cada objeto e printando")
user_list = get_week(user_list)
for t in user_list:
    print("Name: %s, week: %s" % (t['name'], t['week']))

print("\n~> Organizando tabela de saida")
# Organizando tabela
week_group = group_by_week(user_list)
count_week = 0
for key in week_group:
    if count_week < key:
        count_week += 1
    print("# Semana %s" % count_week)
    for user in week_group[key]:
        print("Name: %s, count: %s, date: %s" % (user['name'], user['count'], user['date']))

Result

~> Adicionando semana em cada objeto e printando
Name: Talita, week: 10
Name: Filho, week: 10
Name: Jao, week: 10
Name: Otavio, week: 10
Name: Gabriel, week: 10
Name: Guilherme, week: 11

~> Organizando tabela de saida
# Semana 1
Name: Talita, count: 4, date: 2017-03-06
Name: Filho, count: 8, date: 2017-03-07
Name: Jao, count: 10, date: 2017-03-07
Name: Otavio, count: 6, date: 2017-03-07
Name: Gabriel, count: 2, date: 2017-03-08
# Semana 2
Name: Guilherme, count: 1, date: 2017-03-13
    
19.08.2017 / 17:12