Python Forum
Averaging data while merging - Printable Version

+- Python Forum (https://python-forum.io)
+-- Forum: Python Coding (https://python-forum.io/forum-7.html)
+--- Forum: Data Science (https://python-forum.io/forum-44.html)
+--- Thread: Averaging data while merging (/thread-19879.html)



Averaging data while merging - SujaiBanerji - Jul-18-2019

I have two dataframes as shown below:

 result1
     
     time browncarbon blackcarbon
 180.0008    0.105270         NaN
 181.3809    0.166545    0.001217
 181.6197    0.071581         NaN

 422 rows x 3 columns
 result2

    start         end      toc 
 179.9989    180.0002    155.0
 180.0002    180.0016    152.0
 180.0016    180.0030    151.0

1364 rows x 3 columns
The multiple start and end rows that get encapsulated into one of the time rows should also correspond to one toc row, as it does right now, however, it should be the average of the multiple toc rows, which is not the case presently. How do I do that? There is a related answer on Stack Overflow. The link is: https://stackoverflow.com/questions/45236581/merging-two-pandas-dataframes-with-complex-conditions

result1['rank'] = np.arange(length1)
result3=pd.merge_asof(result1.sort_values('time'),result2,left_on='time',right_on='start')
result3.sort_values('rank').drop(['rank','start','end'], axis=1)

 result3

     time    browncarbon    blackcarbon    toc
 180.0008        0.10527            NaN  152.0

422 rows X 4 columns