I have a Pandas data frame that you can call "normalized." For display purposes, I want to “de-normalize” the data. That is, I want some data to be distributed across several key values that I want to put on one line in the output records. Some records need to be summarized as they are combined. (Also: if someone has a better term for this than "denormalization", please make changes to this question or say so in the comments.)
I work with a Pandas data frame with many columns, so I will show you a simplified version below.
The following code sets the (almost) normalized source data frame. (Please note that I am looking for advice on the second block of code, and this block of code is only intended to provide some context.) Like my actual data, there are several duplicates in the identifying data, and some numbers should be summed up:
import pandas as pd
dates = pd.date_range('20170701', periods=21)
datesA1 = pd.date_range('20170701', periods=11)
datesB1 = pd.date_range('20170705', periods=9)
datesA2 = pd.date_range('20170708', periods=10)
datesB2 = pd.date_range('20170710', periods=11)
datesC1 = pd.date_range('20170701', periods=5)
datesC2 = pd.date_range('20170709', periods=9)
cols=['Date','Type','Count']
df_A1 = pd.DataFrame({'Date':datesA1,
'Type':'Apples',
'Count': np.random.randint(30,size=11)})
df_A2 = pd.DataFrame({'Date':datesA2,
'Type':'Apples',
'Count': np.random.randint(30,size=10)})
df_B1 = pd.DataFrame({'Date':datesB1,
'Type':'Berries',
'Count': np.random.randint(30,size=9)})
df_B2 = pd.DataFrame({'Date':datesB2,
'Type':'Berries',
'Count': np.random.randint(30,size=11)})
df_C1 = pd.DataFrame({'Date':datesC1,
'Type':'Canteloupes',
'Count': np.random.randint(30,size=5)})
df_C2 = pd.DataFrame({'Date':datesC2,
'Type':'Canteloupes',
'Count': np.random.randint(30,size=9)})
frames = [df_A1, df_A2, df_B1, df_B2, df_C1, df_C2]
dat_fra_source = pd.concat(frames)
In addition, the following code reaches my intentions. The source data frame has several rows per day and the type of fetus (A, B and C). Destination data has one row per day with the sum of A, B, and C.
dat_fra_dest = pd.DataFrame(0, index=dates, columns=['Apples','Berries','Canteloupes'])
for index,row in dat_fra_source.iterrows():
dat_fra_dest.at[row['Date'],row['Type']]+=row['Count']
, : , / , , . , . , , "" , . , A, B C , .