python - Add two pandas DataFrame with dates and float -
i have 2 datafames this:
date bin value 0 2013-11-01 100 1 1 2013-12-01 200 1 date bin value 0 2013-11-01 100 2 1 2013-12-01 300 5 2 2013-10-01 100 10
i find sum of values each date , bin, ie final dataframe should be:
date bin value 0 2013-11-01 100 3 1 2013-12-01 200 1 2 2013-12-01 300 15 3 2013-10-01 100 10
(indices , order not important)
when try:
df.add(df2, fill_value = 0)
but following error :
typeerror: ufunc add cannot use operands types dtype('o') , dtype('<m8[ns]')
i tried using multiindex didn't manage set double (date, bin) index afterwards...
any idea how make work ?
you can indeed setting 2 columns (date , bin) multi-index, , use add
specify fill value (otherwise nans):
in [65]: df1b = df1.set_index(['date', 'bin']) in [66]: df2b = df2.set_index(['date', 'bin']) in [67]: df1b + df2b out[67]: value date bin 2013-10-01 100 nan 2013-11-01 100 3 2013-12-01 200 nan 300 nan in [68]: df1b.add(df2b, fill_value = 0) out[68]: value date bin 2013-10-01 100 10 2013-11-01 100 3 2013-12-01 200 1 300 5
another option concatenate 2 frames, , group 2 columns date , bin:
in [71]: df = pd.concat([df1, df2], ignore_index=true) in [72]: df out[72]: date bin value 0 2013-11-01 100 1 1 2013-12-01 200 1 2 2013-11-01 100 2 3 2013-12-01 300 5 4 2013-10-01 100 10 in [73]: df.groupby(['date', 'bin']).sum() out[73]: value date bin 2013-10-01 100 10 2013-11-01 100 3 2013-12-01 200 1 300 5
the reason error message above apparantly dtypes of column in df1
, df2
not matching. in case, adding 2 dataframes not work, cannot add datetimes.
Comments
Post a Comment