I am doing machine learning using a Word2Vec-like approach on an extremely large corpus on non-human language utterances. I want to collect all pairs of words into a pairs dataframe indexed by the word pairs (u, and v) and containing the following columns: id, freq, first_seen, last_seen. Ids must be unique, have no gaps, and be numbered from 0 until len(dataframe)-1. I am building this dataframe as I traverse a large corpus. Here's what it looks like:
import pandas as pd
idx0 = pd.MultiIndex.from_tuples(list(combinations('the quick brown fox jumps over'.split(),2)))
idx0.names = ['u','v']
idx1 = pd.MultiIndex.from_tuples(list(combinations('fox jumps over the lazy dog'.split(),2)))
idx1.names = ['u','v']
df0 = pd.DataFrame(
data={
'id' : range(len(idx0)),
'freq' : [1]*len(idx0),
'first_seen' : [0]*len(idx0),
'last_seen' : [1]*len(idx0),
},
index=idx0)
df1 = pd.DataFrame(
data={
'id' : range(len(idx0),len(idx0)+len(idx1)),
'freq' : [1]*len(idx1),
'first_seen' : [1]*len(idx1),
'last_seen' : [2]*len(idx1),
},
index=idx1)
df0 looks like:
id freq first_seen last_seen
u v
the quick 0 1 0 1
brown 1 1 0 1
fox 2 1 0 1
jumps 3 1 0 1
over 4 1 0 1
quick brown 5 1 0 1
fox 6 1 0 1
jumps 7 1 0 1
over 8 1 0 1
brown fox 9 1 0 1
jumps 10 1 0 1
over 11 1 0 1
fox jumps 12 2 0 2
over 13 2 0 2
jumps over 14 2 0 2
df1 looks like:
id freq first_seen last_seen
u v
fox jumps 15 1 1 2
over 16 1 1 2
the 17 1 1 2
lazy 18 1 1 2
dog 19 1 1 2
jumps over 20 1 1 2
the 21 1 1 2
lazy 22 1 1 2
dog 23 1 1 2
over the 24 1 1 2
lazy 25 1 1 2
dog 26 1 1 2
the lazy 27 1 1 2
dog 28 1 1 2
lazy dog 29 1 1 2
So far so good. Then I want to merge the two and use the meerge as a template for accumulating totals and calculating time stamps in the first_seen, last_seen columns:
dfm = df0.merge(df1,how='outer',indicator=True,left_on=('u','v'),right_on=('u','v'))
gb = dfm.groupby('_merge')
for u,v in gb.get_group('both').index:
df0.loc[(u,v),'freq'] += df1.loc[(u,v),'freq']
df0.loc[(u,v),'last_seen'] = df1.loc[(u,v),'last_seen']
additions = []
for u,v in gb.get_group('right_only').index:
additions.append((u,v))
midx = pd.MultiIndex.from_tuples(additions)
newdf = pd.concat([df0,df1.loc(axis=0)[midx]])
newdf becomes:
id freq first_seen last_seen
u v
the quick 0 1 0 1
brown 1 1 0 1
fox 2 1 0 1
jumps 3 1 0 1
over 4 1 0 1
quick brown 5 1 0 1
fox 6 1 0 1
jumps 7 1 0 1
over 8 1 0 1
brown fox 9 1 0 1
jumps 10 1 0 1
over 11 1 0 1
fox jumps 12 2 0 2
over 13 2 0 2
jumps over 14 2 0 2
fox the 17 1 1 2
lazy 18 1 1 2
dog 19 1 1 2
jumps the 21 1 1 2
lazy 22 1 1 2
dog 23 1 1 2
over the 24 1 1 2
lazy 25 1 1 2
dog 26 1 1 2
the lazy 27 1 1 2
dog 28 1 1 2
lazy dog 29 1 1 2
which is close, but there's problems:
- Every pair (u,v) should be sequentially numbered. Because (fox,jumps) was id=12 in df0 and id=15 in df1, the frequencies were properly added and the last_seen was updated, but now we are missing id=15, #16, and #20 in newdf.
- Every pair must occur exactly once in the dataframe. I have no assurance that my method is guaranteeing this.
- This seems like a wildly inefficient way to update a dataframe 30 million times or so.
Seems the best way to fix the first problem is to renumber the end of newdf['id']:
ser = newdf['id']
ser.iloc[-12:] = range(len(df0),len(df0)+len(gb.get_group('right_only')))
newdf['id'] = ser
There ought to be a cleaner, more pythonic, more "pandastic" way of doing this. But all my searching of the documentation hasn't found a better way. It feels like .agg() and .unique() should be in my solution somewhere, too.
I welcome any thoughts or clarifications on the matter.
Thanks, Glenn