2

Merge two images of data in a group with Group By

 2 years ago
source link: https://www.codesd.com/item/merge-two-images-of-data-in-a-group-with-group-by.html
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.

Merge two images of data in a group with Group By

advertisements

I have two dataframes and need to merge them based on a date, but the merge should be done for each group (participant_id) separately.

df1:
   response_date  summary  epis_mark  participant_id
0     2012-01-04      0.0      False              13
1     2012-01-11      0.0      False              13
2     2012-01-19      0.0      False              13
3     2012-01-29      0.0      False              13
4     2012-02-02      0.0      False              13
0     2012-01-02      8.0       True              14
1     2012-01-10      5.0      False              14
2     2012-01-18      2.0      False              14
3     2012-01-24      1.0      False              14
4     2012-01-31      2.0      False              14
0     2012-01-07      4.0      False              17
1     2012-01-11      NaN      False              17
2     2012-01-18      4.0      False              17
3     2012-01-25      NaN      False              17
4     2012-02-01      NaN      False              17

  df2:
   response_date  summary  epis_mark  participant_id
0     2012-01-04     17.0       True              13
1     2012-01-11     18.0       True              13
2     2012-01-19     16.0       True              13
3     2012-01-29     15.0       True              13
4     2012-02-02     15.0       True              13
0     2012-01-02     12.0       True              14
1     2012-01-10      8.0       True              14
2     2012-01-18     21.0       True              14
3     2012-01-24     19.0       True              14
4     2012-01-31     20.0       True              14
0     2012-01-04      NaN      False              17
1     2012-01-11      NaN      False              17
2     2012-01-18      NaN      False              17
3     2012-01-25      NaN      False              17
4     2012-02-01      NaN      False              17

I need to get one dataframe (wide), where merging is done on response date for each participant_id independently. Something like:

>> pd.merge(df1[df1.participant_id == i], df2[df2.participant_id == i], on='response_date', how='outer')

But without looping over i and using groupby.


Merge on both response_date and participant_id:

In [75]: pd.merge(df1, df2, on=['response_date', 'participant_id'], how='outer')
Out[75]:
   response_date  summary_x epis_mark_x  participant_id  summary_y epis_mark_y
0     2012-01-04        0.0       False              13       17.0        True
1     2012-01-11        0.0       False              13       18.0        True
2     2012-01-19        0.0       False              13       16.0        True
3     2012-01-29        0.0       False              13       15.0        True
4     2012-02-02        0.0       False              13       15.0        True
5     2012-01-02        8.0        True              14       12.0        True
6     2012-01-10        5.0       False              14        8.0        True
7     2012-01-18        2.0       False              14       21.0        True
8     2012-01-24        1.0       False              14       19.0        True
9     2012-01-31        2.0       False              14       20.0        True
10    2012-01-07        4.0       False              17        NaN         NaN
11    2012-01-11        NaN       False              17        NaN       False
12    2012-01-18        4.0       False              17        NaN       False
13    2012-01-25        NaN       False              17        NaN       False
14    2012-02-01        NaN       False              17        NaN       False
15    2012-01-04        NaN         NaN              17        NaN       False




About Joyk


Aggregate valuable and interesting links.
Joyk means Joy of geeK