The Python Book
 
join pandas
20160529

Join two dataframes, sql style

You have a number of users, and a number of transactions against those users. Join these 2 dataframes.

import pandas as pd 

User dataframe

ids= [9000, 9001, 9002, 9003, 9004, 9005, 9006, 9007, 9008]
nms=[u'Gerd Abrahamsson', u'Hanna Andersson', u'August Bergsten',
      u'Arvid Bohlin', u'Edvard Marklund', u'Ragnhild Br\xe4nnstr\xf6m',
      u'B\xf6rje Wallin', u'Otto Bystr\xf6m',u'Elise Dahlstr\xf6m']


udf=pd.DataFrame(ids, columns=['uid'])
udf['name']=nms

Content of udf:

    uid                 name
0  9000     Gerd Abrahamsson
1  9001      Hanna Andersson
2  9002      August Bergsten
3  9003         Arvid Bohlin
4  9004      Edvard Marklund
5  9005  Ragnhild Brännström
6  9006         Börje Wallin
7  9007         Otto Byström
8  9008      Elise Dahlström

Transaction dataframe

tids= [5000, 5001, 5002, 5003, 5004, 5005, 5006, 5007, 5008, 5009, 5010, 5011, 5012,
       5013, 5014, 5015, 5016, 5017, 5018, 5019, 5020]

uids= [9008, 9003, 9003, 9007, 9004, 9007, 9002, 9008, 9005, 9008, 9006, 9008, 9008,
       9005, 9005, 9001, 9000, 9003, 9002, 9001, 9004] 

tamt= [498, 268, 621, -401, 720, -492, -153, 272, -250, 82, 549, -571, 814, -114,
      819, -404, -95, 428, -549, -462, -339]

tdt= ['2016-02-21 06:28:49', '2016-01-17 13:37:38', '2016-02-24 15:36:53',
      '2016-01-14 16:43:27', '2016-05-14 16:29:54', '2016-02-24 23:58:57',
      '2016-02-18 17:58:33', '2016-05-26 12:00:00', '2016-02-24 23:14:52',
      '2016-04-20 18:33:25', '2016-02-16 14:37:25', '2016-02-28 13:05:33',
      '2016-03-20 13:29:11', '2016-02-06 14:55:10', '2016-01-18 10:50:20',
      '2016-02-20 22:08:23', '2016-05-09 10:26:05', '2016-03-27 15:30:47',
      '2016-04-15 21:44:49', '2016-03-09 20:32:35', '2016-05-03 17:11:21']


tdf=pd.DataFrame(tids, columns=['xid'])
tdf['uid']=uids
tdf['amount']=tamt
tdf['date']=tdt

Content of tdf:

     xid   uid  amount                 date
0   5000  9008     498  2016-02-21 06:28:49
1   5001  9003     268  2016-01-17 13:37:38
2   5002  9003     621  2016-02-24 15:36:53
3   5003  9007    -401  2016-01-14 16:43:27
4   5004  9004     720  2016-05-14 16:29:54
5   5005  9007    -492  2016-02-24 23:58:57
6   5006  9002    -153  2016-02-18 17:58:33
7   5007  9008     272  2016-05-26 12:00:00
8   5008  9005    -250  2016-02-24 23:14:52
9   5009  9008      82  2016-04-20 18:33:25
10  5010  9006     549  2016-02-16 14:37:25
11  5011  9008    -571  2016-02-28 13:05:33
12  5012  9008     814  2016-03-20 13:29:11
13  5013  9005    -114  2016-02-06 14:55:10
14  5014  9005     819  2016-01-18 10:50:20
15  5015  9001    -404  2016-02-20 22:08:23
16  5016  9000     -95  2016-05-09 10:26:05
17  5017  9003     428  2016-03-27 15:30:47
18  5018  9002    -549  2016-04-15 21:44:49
19  5019  9001    -462  2016-03-09 20:32:35
20  5020  9004    -339  2016-05-03 17:11:21

Join sql-style: pd.merge

pd.merge( tdf, udf, how='inner', left_on='uid', right_on='uid')

     xid   uid  amount                 date                 name
0   5000  9008     498  2016-02-21 06:28:49      Elise Dahlström
1   5007  9008     272  2016-05-26 12:00:00      Elise Dahlström
2   5009  9008      82  2016-04-20 18:33:25      Elise Dahlström
3   5011  9008    -571  2016-02-28 13:05:33      Elise Dahlström
4   5012  9008     814  2016-03-20 13:29:11      Elise Dahlström
5   5001  9003     268  2016-01-17 13:37:38         Arvid Bohlin
6   5002  9003     621  2016-02-24 15:36:53         Arvid Bohlin
7   5017  9003     428  2016-03-27 15:30:47         Arvid Bohlin
8   5003  9007    -401  2016-01-14 16:43:27         Otto Byström
9   5005  9007    -492  2016-02-24 23:58:57         Otto Byström
10  5004  9004     720  2016-05-14 16:29:54      Edvard Marklund
11  5020  9004    -339  2016-05-03 17:11:21      Edvard Marklund
12  5006  9002    -153  2016-02-18 17:58:33      August Bergsten
13  5018  9002    -549  2016-04-15 21:44:49      August Bergsten
14  5008  9005    -250  2016-02-24 23:14:52  Ragnhild Brännström
15  5013  9005    -114  2016-02-06 14:55:10  Ragnhild Brännström
16  5014  9005     819  2016-01-18 10:50:20  Ragnhild Brännström
17  5010  9006     549  2016-02-16 14:37:25         Börje Wallin
18  5015  9001    -404  2016-02-20 22:08:23      Hanna Andersson
19  5019  9001    -462  2016-03-09 20:32:35      Hanna Andersson
20  5016  9000     -95  2016-05-09 10:26:05     Gerd Abrahamsson

Sidenote: fake data creation

This is the way the above fake data was created:

import random
from faker import Factory

fake = Factory.create('sv_SE') 

ids=[]
nms=[]
for i in range(0,9):
    ids.append(9000+i)
    nms.append(fake.name())
    print "%d\t%s" % ( ids[i],nms[i])


tids=[]
uids=[]
tamt=[]
tdt=[]
sign=[-1,1]
for i in range(0,21):
    tids.append(5000+i)
    tamt.append(sign[random.randint(0,1)]*random.randint(80,900))
    uids.append(ids[random.randint(0,len(ids)-1)])
    tdt.append(str(fake.date_time_this_year()))
    print "%d\t%d\t%d\t%s" % ( tids[i], tamt[i], uids[i], tdt[i])
 
Notes by Willem Moors. Generated on momo:/home/willem/sync/20151223_datamungingninja/pythonbook at 2019-07-31 19:22