# Simplifying Python Pandas code for selecting co-occurrences in a window of time

I am a beginner at programming. I was able to build the thing below, which achieves what I want with a small dataset. With larger datasets, my RAM gets swamped bringing the computer to a halt (2014 Macbook Pro with 16GB RAM). Can I simplify my process somehow?

# This code starts from a co-occurrence list of pairs with dates in the first column,
#like this:
#
# Jan-20; Monkey; Dog
# Jan-21; Dog; Horse
# Jan-22; Monkey; Cat
# Jan-23; Monkey; Dog
#
# That is, these animals occurred together on these specific days.
#
# This code cleans out the list, keeping only those lines that have co-occurrences
# including an animal below a certain "age" in the dataset. Let's say 2 days. Like this:
#
# Monkey occurred the first time on Jan-20, meaning that lines including 'Monkey' should
# be kept only if they are dated Jan-# 20 or Jan-21. Cat occurred the first time on Jan-21,
# meaning that lines including 'Cat' should be kept in the dataset only # if they are dated
# Jan-21 or Jan-22.

# Gather data on dates of earliest occurrence for all included animals
# Set a time window
# Extract lines based on 1 and 2

import pandas

# STEP 1: Gather data on earliest occurence ('entrydates') for all included items

## Set column names
colnames=['Date','Item1','Item2']

data = pandas.read_csv('/Users/Simon/Dropbox/Work/Datasets/idlehash.csv', names=colnames)

## Create a dataframe with info on dates for first column
datelist1 = data[['Date', 'Item1']]

## Create a dataframe with info on dates for second column
datelist2 = data[['Date', 'Item2']]

## Join the two dataframes into one
entrydates = datelist1.append(datelist2)

## Melt the resulting dataframe into two columns
entrydates = pandas.melt(entrydates, id_vars='Date')[['Date','value']]

## Sort the dataframe by Date and k eep only the earliest occurence of a value
## drop_duplicates considers the column 'value' and keeps only first occurence
entrydates = entrydates.sort('Date').drop_duplicates(cols=['value'])

# STEP 2: Calculate item "ages" in dataset at each co-occurence event

## Create a dataframe with co-occurrence pairs and the entrydates of Item1 in each pair
matrix = pandas.merge(left=data, right=entrydates, left_on='Item1', right_on='value')

## Create a dataframe with co-occurrence pairs and the entrydates of Item2 in each pair
matrix2 = pandas.merge(left=data, right=entrydates, left_on='Item2', right_on='value')

## Rename some of the columns for clarity
matrix = matrix.rename(columns={'Date_x':'co-oc date', 'Date_y':'entrydate of item 1',
'value':'Item1 (check)'})
matrix2 = matrix2.rename(columns={'Date_x':'co-oc date', 'Date_y':'entrydate of item 2',
'value':'Item2 (check)'})

## Sort them
matrix = matrix.sort(['co-oc date','entrydate of item 1'], ascending=False)
matrix2 = matrix2.sort(['co-oc date','entrydate of item 2'], ascending=False)

## Join them
gorillaking = pandas.merge(matrix, matrix2, on='Item2', how='outer')

## Build dataframe with selected columns from gorillaking
gorillaking = pandas.concat([gorillaking['co-oc date_x'], gorillaking['Item1_x'],
gorillaking['Item2'], gorillaking['entrydate of item 1'], gorillaking['entrydate of item 2']],
axis=1, keys=['date', 'item1', 'item2', 'item1 birth', 'item2 birth'])

## Add a column calculating the "age" of Item 1 on the occasion of each co-occurrence
gorillaking['item1 age'] = gorillaking['date'] - gorillaking['item1 birth']

## Add a column calculating the "age" of Item 2 on the occasion of each co-occurrence
gorillaking['item2 age'] = gorillaking['date'] - gorillaking['item2 birth']

# STEP 3: Select only the co-occurrences that happen in a certain window of time

## Set a timewindow
timewindow = 7

## Extract only the rows where the "age" of Item 1
## is less than or equal to the user defined timewindow
## That is: ('date' - 'item1 birth') <= timewindow
mask = (gorillaking['date'] - gorillaking['item1 birth'] <= timewindow)

## Output kept pairs to a file
dataset = keptpairs[['item1', 'item2']]
dataset.to_csv('/Users/Simon/Dropbox/Work/Datasets/#keptpairs.csv', sep='\t', encoding='utf-8',

## Print result
print dataset


Started testing it line by line myself while checking the Activity Monitor in OSX, and it all works fine up until this line:

## Join them
gorillaking = pandas.merge(matrix, matrix2, on='Item2', how='outer')


That's where RAM pressure starts mushrooming.

• Just a friendly comment to say this is an excellent well-presented question! Hope you'll have good reviews! – Marc-Andre Jun 11 '14 at 16:37
• Thanks @Marc-Andre! Let's hope there will be some input. – Simon L Jun 11 '14 at 17:50
• doesn't Cat occur for the first time Jan-22? or have I misunderstood? – Stuart Jun 11 '14 at 19:26

What I like

• Comments at the beginning describing what the code should do
• Variable names that are easy to read
• constants are put one place (timewindow = 7) so they are easy to understand and modify

What I didn't like

• Some of the variable names confused me and didn't help me understand the code ('gorillaking')
• In some cases, the comment isn't providing any more useful information then the code does. For example, ## Join them tells me what you are doing, but not why.
• I didn't really understand the results you wanted. In particular, it was unclear if one animal was within the time period but the other was not, what should happen. I suggest redoing the example in the top of the file to include one case where a row stays in because both animals are within the time period, one where the first animal is out, one where the second animal is out, and one where they both are out. In and out should be one day difference, and the time period in the example should match the one in the code (7 days). Give us the expected output in the comment as well.
• I guessed that you used panda because you wanted to learn it. There are probably non-panda ways to solve this simple problem, but they wouldn't let you learn panda. If this is true, please say so.

Questions:

and it all works fine up until this line: gorillaking = pandas.merge(matrix, matrix2, on='Item2', how='outer')

This is probably a StackOverflow question, but I'll tell you what they will probably tell you.

• I don't think this is doing what you think it is doing. Take a small example, and print out each variable when it was changed at every step. I think you will find that gorillaking doesn't include what you expect it to include
• I am not a panda developer (I read the documentation on merge to answer this question, but I have not tried it). However, they mention that their join is based on SQL joins. Outer Joins in SQL are nasty. Read up on them if you aren't familiar with them, but the basic idea is they take every row from table 1 and concatenate it with every row from table 2. So if table 1 had 500 rows and table 2 had 1,000, you would get 500 x 1,000 = 500,000 rows. That's probably why you are having memory problems.

Can I simplify my process somehow?

This is probably a programmers StackExchange question, and if you print out the results after every line like I mentioned, you may find that there is a better way. Still, I'll tell you what I would tell you over there.

I liked how you got entrydates. At that point, though, I'd go back to data and add two columns of original dates, one for each item, rather then split it into two separate sets of data. Then add the two columns of ages, then make your mask, then get back to the first three columns to print. That avoids the whole merge problem you are having. I am not sure this would work, but it seems like it should.

All in all, I have to say that this is more impressive then many beginners I've seen. Keep up the good work. I hope this helped.