Grouping by the days data into each weeks possibly using datetime. dataframe is different for each day. Have to merge

Question


import pandas as pd

# intialise data of lists. 
date1={'Indv':[1,2,3,4],'date':[1,1,1,1],'number':[5,5,5,5]}
date2={'Indv':[1,2,3,4],'date':[2,2,2,2],'number':[5,6,5,3]}
date3={'Indv':[1,2,3,4],'date':[3,3,3,3],'number':[2,3,4,5]}
date4={'Indv':[1,2,3,4],'date':[4,4,4,4],'number':[1,3,4,5]}
date5={'Indv':[1,2,3,4],'date':[5,5,5,5],'number':[2,3,7,5]}
date6={'Indv':[1,2,3,4],'date':[6,6,6,6],'number':[9,11,4,5]}
date7={'Indv':[1,2,3,4],'date':[7,7,7,7],'number':[3,3,4,5]}

date8={'Indv':[1,2,3,4],'date':[8,8,8,8],'number':[5,5,5,5]}
date9={'Indv':[1,2,3,4],'date':[9,9,9,9],'number':[5,6,5,3]}
date10={'Indv':[1,2,3,4],'date':[10,10,10,10],'number':[2,3,4,5]}
date11={'Indv':[1,2,3,4],'date':[11,11,11,11],'number':[1,3,4,5]}
date12={'Indv':[1,2,3,4],'date':[12,12,12,12],'number':[2,3,7,5]}
date13={'Indv':[1,2,3,4],'date':[13,13,13,13],'number':[9,11,4,5]}
date14={'Indv':[1,2,3,4],'date':[14,14,14,14],'number':[3,3,4,5]}



#CreateDataFrame
#Importing first seven days data
df1=pd.DataFrame(date1)
df2=pd.DataFrame(date2)
df3=pd.DataFrame(date3)
df4=pd.DataFrame(date4)
df5=pd.DataFrame(date5)
df6=pd.DataFrame(date6)
df7=pd.DataFrame(date7)

#merging first seven days data into one week data
final_week_1=df1.append([df2,df3,df4,df5,df6,df7])
final_week_1['week']=1

#Performing a groupby on week 1
groupby_week_1=final_week_1.groupby(['Indv','week'])['number'].sum().reset_index(name='weekly_total')

#delete the intermediate  files to freeup memory and keep only the
del final_week_1


#CreateDataFrame
#Importing next set of seven days data of week 2
df8=pd.DataFrame(date8)
df9=pd.DataFrame(date9)
df10=pd.DataFrame(date10)
df11=pd.DataFrame(date11)
df12=pd.DataFrame(date12)
df13=pd.DataFrame(date13)
df14=pd.DataFrame(date14)

#merging second set of seven days and week 2
final_week_2=df8.append([df9,df10,df11,df12,df13,df14])
final_week_2['week']=2

#Performing a groupby on week 2
groupby_week_2=final_week_2.groupby(['Indv','week'])['number'].sum().reset_index(name='weekly_total')
del final_week_2

all_weeks_data=groupby_week_1.append([groupby_week_1])

Need to keep the above code in loop and get the final above weekly grouped data of all_weeks_data

Data is in each day format

Need to merge every seven days from day 1 to day n of 52 weeks. Process files as each week and export the data. merging all n days data throws memory error as the file sizes are very huge.

Need to write python code to merge every seven days data, export it and continue the loop of that weeks

I am sorry but I really cannot understand what you want, what is your real input and what is your expected output. — Serge Ballesta, Apr 29 '20 at 13:58
Updated my Question. Need to merge the seven days files to week file, process on it and continue to work on next week. Working on each week of seven days files has to be kept in loop. — Srikanth Ayithy, Apr 29 '20 at 14:20
Is the data in the form of dictionaries as presented? Moreover, what is the data type of `date`? — Avantika Banerjee, Apr 29 '20 at 14:31
data is in csv formats. Just created dummy datasets using dictonaries. Each df represents one days' data. — Srikanth Ayithy, Apr 29 '20 at 14:36

score 0 · Accepted Answer · answered Apr 29 '20 at 15:20

From what I understand, you want a summed up weekly overview of the given "number". Please update the question if this is not the case:

import pandas as pd
from datetime import datetime

date1={'Indv':[1,2,3,4],'date':['2020-02-01','2020-02-01','2020-02-01','2020-02-01'],'number':[5,5,5,5]}
date2={'Indv':[1,2,3,4],'date':['2020-02-02','2020-02-02','2020-02-02','2020-02-02'],'number':[5,6,5,3]}
date3={'Indv':[1,2,3,4],'date':['2020-02-03','2020-02-03','2020-02-03','2020-02-03'],'number':[2,3,4,5]}
date4={'Indv':[1,2,3,4],'date':['2020-02-04','2020-02-04','2020-02-04','2020-02-04'],'number':[1,3,4,5]}
date5={'Indv':[1,2,3,4],'date':['2020-02-05','2020-02-05','2020-02-05','2020-02-05'],'number':[2,3,7,5]}
date6={'Indv':[1,2,3,4],'date':['2020-02-06','2020-02-06','2020-02-06','2020-02-06'],'number':[9,11,4,5]}
date7={'Indv':[1,2,3,4],'date':['2020-02-07','2020-02-07','2020-02-07','2020-02-07'],'number':[3,3,4,5]}
date8={'Indv':[1,2,3,4],'date':['2020-02-08','2020-02-08','2020-02-08','2020-02-08'],'number':[5,5,5,5]}
date9={'Indv':[1,2,3,4],'date':['2020-02-09','2020-02-09','2020-02-09','2020-02-09'],'number':[5,6,5,3]}
date10={'Indv':[1,2,3,4],'date':['2020-02-10','2020-02-10','2020-02-10','2020-02-10'],'number':[2,3,4,5]}
date11={'Indv':[1,2,3,4],'date':['2020-02-11','2020-02-11','2020-02-11','2020-02-11'],'number':[1,3,4,5]}
date12={'Indv':[1,2,3,4],'date':['2020-02-12','2020-02-12','2020-02-12','2020-02-12'],'number':[2,3,7,5]}
date13={'Indv':[1,2,3,4],'date':['2020-02-13','2020-02-13','2020-02-13','2020-02-13'],'number':[9,11,4,5]}
date14={'Indv':[1,2,3,4],'date':['2020-02-14','2020-02-14','2020-02-14','2020-02-14'],'number':[3,3,4,5]}

#CreateDataFrame
df1=pd.DataFrame(date1)
df2=pd.DataFrame(date2)
df3=pd.DataFrame(date3)
df4=pd.DataFrame(date4)
df5=pd.DataFrame(date5)
df6=pd.DataFrame(date6)
df7=pd.DataFrame(date7)
df8=pd.DataFrame(date8)
df9=pd.DataFrame(date9)
df10=pd.DataFrame(date10)
df11=pd.DataFrame(date11)
df12=pd.DataFrame(date12)
df13=pd.DataFrame(date13)
df14=pd.DataFrame(date14)

df_list = [df1,df2,df3,df4,df5,df6,df7,df8,df9,df10,df11,df12,df13,df14]

def append_df(df_list):
    df = pd.DataFrame()
    df = df.append(df_list)
    df['date'] = df['date'].apply(lambda x: datetime.strptime(x, '%Y-%m-%d'))
    df['week'] = df['date'].dt.week
    return df

all_dates_data = append_df(df_list)
weekly_total = all_dates_data.groupby(['week']).sum()

I have taken the liberty to assume the data type of the date column. Next, I have written a function where you convert a date to a "week number"; and finally group by "week".

You got it right. However, each day file is more than 50MB and more than 1 million rows and four columns. Therefore, I cannot append whole year data and put a group by on. This is giving me a memory error. Thats, that flow I am trying is, just import seven days of files, process it groupby it and export it. Next go to next seven days of second week and then do the same. In this process, I am just processing week by week separately but not merging whole year dataset. Even though you solution works for me. But that will end up thowing memory error. Thats why I am trying keep the same in loop. — Srikanth Ayithy, Apr 29 '20 at 15:47
I have written a code. I want to keep it in loop of seven days instead of merging all files. `MERGED_FILE=pd.DataFrame a = pd.date_range(start='20190103', end='20190109') for i in a: i = str(i) j = i.replace('-', '') j = j.replace(' 00:00:00', '') df = pd.read_csv(r'\\\\path\\' + j + '_file.csv') MERGED_FILE=MERGED_FILE.append(df) df2=MERGED_FILE.groupby(['Indv','week'])['number'].sum().reset_index(name='Total') print(len(MERGED_FILE)) df2.to_csv(r'path_export')` — Srikanth Ayithy, Apr 29 '20 at 15:56
No worries! Thank you for explaining this further. I was wondering maybe it is then better to `groupby` date for each file. i.e. instead of processing a week's worth of data perhaps just do it on a daily basis and then append? — Avantika Banerjee, Apr 29 '20 at 16:32

Grouping by the days data into each weeks possibly using datetime. dataframe is different for each day. Have to merge

1 Answers1