Alejandro A
Alejandro A

Reputation: 1190

Panda's merge returning empty, can't see why

I have the following dataframe:

enter image description here

I then summarize the value 'Hp' by the column 'Dia', obtaining the following dataset using the following syntaxis:

df_Sum=df.groupby(df.Dia.dt.date)['Hp'].sum()

And obtaining the following timeseries(which later I need to convert to_frame() to merge):

enter image description here

What I want to do is that the summarized value for each day, is copied to everyday that matches the original dataframe(There, the date 'Dia' can be repeated, see first image).

So at the end of the day what I want is that each distinct field type date 'Dia' in the first dataframe has its own value summarized, so I opted for the merge syntaxis that follows:

pd.merge(left=df,right=df_Sum.to_frame(),right_index=True,left_on='Dia')

But it's empty!

What is wrong? Is there any better approach?

Thanks!

Upvotes: 1

Views: 264

Answers (1)

jezrael
jezrael

Reputation: 863791

I think you need GroupBy.transform:

df['sum'] = df.groupby(df.Dia.dt.date)['Hp'].transform('sum')

You get empty df, because no match, because merge on different types of dates - one is python dates and second pandas datetimes.

Upvotes: 1

Related Questions