In [1]:
%load_ext autoreload
%autoreload 2

from pprint import pprint
import pandas as pd
import pymongo
import numpy as np
import time
import networkx as nx
import operator
import vislab.util

In [5]:
vislab.util.print_collection_counts()
db = vislab.util.get_mongodb_client()['pinscraping']


ava_feat_db |		system.indexes: 2
ava_feat_db |		decaf_fc6: 0
flickr |		system.indexes: 40
flickr |		Detailed: 7000
flickr |		Pastel: 7000
flickr |		Melancholy: 4496
flickr |		Noir: 4496
flickr |		HDR: 4497
flickr |		Vintage: 5996
flickr |		Long Exposure: 4500
flickr |		Horror: 4493
flickr |		Sunny: 4492
flickr |		Bright, Energetic: 4498
flickr |		Hazy: 4483
flickr |		Bokeh: 4497
flickr |		Serene: 4497
flickr |		Texture: 4479
flickr |		Ethereal: 4487
flickr |		Macro: 4500
flickr |		Depth of Field: 5506
flickr |		Geometric Composition: 4498
flickr |		Minimal: 4478
flickr |		Romantic: 4489
local |		startup_log: 6
predict |		system.indexes: 3
predict |		default: 1
predict |		behance_dec28: 2
predict |		behance_illustration_jan15: 2
vislab_feats |		system.indexes: 6
vislab_feats |		noise: 0
vislab_feats |		size: 0
vislab_feats |		caffe_fc6: 20

Load boards collection into a DataFrame.


In [3]:
pprint(db['boards'].find_one())


{u'_id': u'www.pinterest.com/000Bren000/%7Espring%7E/',
 u'board_name': u'~Spring~',
 u'board_url': u'www.pinterest.com/000Bren000/%7Espring%7E/',
 u'cover_img': u'http://media-cache-ak0.pinimg.com/custom_covers/216x146/293085956939005631_1387268206.jpg',
 u'num_pins': 210,
 u'source': u'query: sad ',
 u'thumb_imgs': [u'http://media-cache-ak0.pinimg.com/45x45/6d/c7/30/6dc730dd1f455ec857a808d0585199f8.jpg',
                 u'http://media-cache-ec0.pinimg.com/45x45/7b/b7/0b/7bb70b5a9f86316596a472919608a98d.jpg',
                 u'http://media-cache-ak0.pinimg.com/45x45/61/b7/20/61b720430490402a00e0422c29a666eb.jpg',
                 u'http://media-cache-ec0.pinimg.com/45x45/72/57/ad/7257ad90ba9fcfd5f7e0965d1908eb35.jpg'],
 u'username': u'SugarLuvsPups'}

In [4]:
t = time.time()
df = pd.DataFrame(
    list(db['boards'].find(fields={
        'thumb_imgs': False, 'cover_img': False, 'board_url': False, 'source': False
    }))
)
print('Assembling boards took {:.2f} s'.format(time.time() - t))
N = df.shape[0]

# Drop duplicates
boards_df = df.drop_duplicates(cols=['username', 'board_name'], take_last=True)
print("Dropped {} duplicates, now have {} rows".format(N - boards_df.shape[0], boards_df.shape[0]))


Assembling boards took 5.75 s
Dropped 296 duplicates, now have 1339814 rows

Load users collection as a DataFrame


In [5]:
print db['users'].count()
user_document = db['users'].find({'username': u'pixelmao'})[0]
pprint(user_document)


21647
{u'_id': ObjectId('5302da14ac2b72513bcdc950'),
 u'num_boards': 12,
 u'query': [u'hdr', u'hdr'],
 u'username': u'pixelmao'}

In [6]:
# Construct DataFrame of users and make sure that only unique
# queries are stored in the 'query' column.
users_df = pd.DataFrame(list(db['users'].find(fields={'_id': False})))
users_df['query'] = [np.unique(_) for _ in users_df['query']]

In [7]:
# Join the users and boards DataFrames on username column.
# Then expand the 'query' column to separate boolean columns,
# one for each possible query.
boards_users_df = pd.merge(users_df, boards_df, on='username')
print boards_users_df.shape
print boards_users_df.columns


(1339814, 6)
Index([u'num_boards', u'query', u'username', u'_id', u'board_name', u'num_pins'], dtype='object')

In [8]:
boards_users_df_expanded = pd.DataFrame(
    data=[
        dict([('query_' + _, True) for _ in row])
        for row in boards_users_df['query']
    ],
    index=boards_users_df.index
).fillna(False)
print boards_users_df_expanded.shape
print boards_users_df_expanded.columns


(1339814, 49)
Index([u'query_animals', u'query_black and white', u'query_bokeh', u'query_bright', u'query_calm', u'query_cloudy', u'query_corporate', u'query_depth of field', u'query_detailed', u'query_energetic', u'query_ethereal', u'query_fall', u'query_futuristic', u'query_geometric composition', u'query_happy', u'query_hazy', u'query_hdr', u'query_horror', u'query_industrial', u'query_instagram', u'query_landscape', u'query_long exposure', u'query_macro', u'query_melancholy', u'query_minimal', u'query_nature', u'query_night', u'query_noir', u'query_organic', u'query_pastel', u'query_pensive', u'query_portrait', u'query_radiant', u'query_romantic', u'query_sad', u'query_scary', u'query_sepia', u'query_serene', u'query_sleek', u'query_soft', u'query_spring', u'query_summer', u'query_sunny', u'query_tense', u'query_texture', u'query_upbeat', u'query_vintage', u'query_washed out', u'query_winter'], dtype='object')

In [9]:
boards_users_df = boards_users_df.join(boards_users_df_expanded)
boards_users_df.set_index('_id', inplace=True)
del boards_users_df['query']
boards_users_df


Out[9]:
num_boards username board_name num_pins query_animals query_black and white query_bokeh query_bright query_calm query_cloudy query_corporate query_depth of field query_detailed query_energetic query_ethereal query_fall query_futuristic query_geometric composition query_happy query_hazy
_id
www.pinterest.com/asukah0220/%E3%81%8A%E8%8A%B1/ 19 asukah0220 お花❃ 16 False False False False False False False False False False False False False False False False ...
www.pinterest.com/asukah0220/%E3%82%A2%E3%82%A4%E3%82%B7%E3%83%B3%E3%82%B0%E3%82%AF%E3%83%83%E3%82%AD%E3%83%BC/ 19 asukah0220 アイシングクッキー 70 False False False False False False False False False False False False False False False False ...
www.pinterest.com/asukah0220/%E3%82%A6%E3%82%A7%E3%83%87%E3%82%A3%E3%83%B3%E3%82%B0/ 19 asukah0220 ♡ウェディング♡ 142 False False False False False False False False False False False False False False False False ...
www.pinterest.com/asukah0220/%E3%82%AB%E3%83%83%E3%83%97%E3%82%B1%E3%83%BC%E3%82%AD/ 19 asukah0220 カップケーキ 28 False False False False False False False False False False False False False False False False ...
www.pinterest.com/asukah0220/%E3%83%8D%E3%82%A4%E3%83%AB/ 19 asukah0220 ネイル☆ 25 False False False False False False False False False False False False False False False False ...
www.pinterest.com/asukah0220/%E3%83%95%E3%82%A1%E3%83%96%E3%83%AA%E3%83%83%E3%82%AF/ 19 asukah0220 ファブリック 24 False False False False False False False False False False False False False False False False ...
www.pinterest.com/asukah0220/%E3%83%98%E3%82%A2%E3%82%A2%E3%83%AC%E3%83%B3%E3%82%B8/ 19 asukah0220 ヘアアレンジ 15 False False False False False False False False False False False False False False False False ...
www.pinterest.com/asukah0220/%E3%83%99%E3%83%83%E3%83%89%E3%83%AB%E3%83%BC%E3%83%A0/ 19 asukah0220 ベッドルーム 41 False False False False False False False False False False False False False False False False ...
www.pinterest.com/asukah0220/%E3%83%AA%E3%83%9C%E3%83%B3/ 19 asukah0220 リボン 30 False False False False False False False False False False False False False False False False ...
www.pinterest.com/asukah0220/chandelier/ 19 asukah0220 chandelier 64 False False False False False False False False False False False False False False False False ...
www.pinterest.com/asukah0220/denim-style/ 19 asukah0220 denim style 17 False False False False False False False False False False False False False False False False ...
www.pinterest.com/asukah0220/invitation/ 19 asukah0220 invitation 32 False False False False False False False False False False False False False False False False ...
www.pinterest.com/asukah0220/lace/ 19 asukah0220 Lace 48 False False False False False False False False False False False False False False False False ...
www.pinterest.com/asukah0220/laduree/ 19 asukah0220 LADUREE 50 False False False False False False False False False False False False False False False False ...
www.pinterest.com/asukah0220/pastel/ 19 asukah0220 PASTEL 109 False False False False False False False False False False False False False False False False ...
www.pinterest.com/asukah0220/sabon/ 19 asukah0220 SABON 36 False False False False False False False False False False False False False False False False ...
www.pinterest.com/asukah0220/stone-art/ 19 asukah0220 stone art 5 False False False False False False False False False False False False False False False False ...
www.pinterest.com/asukah0220/tocca/ 19 asukah0220 tocca 35 False False False False False False False False False False False False False False False False ...
www.pinterest.com/asukah0220/wreath/ 19 asukah0220 wreath 28 False False False False False False False False False False False False False False False False ...
www.pinterest.com/sunnyLENA/accessories/ 106 sunnyLENA Accessories 219 True False False False False False False False False False False True False False True False ...
www.pinterest.com/sunnyLENA/animals/ 106 sunnyLENA ANIMALS 192 True False False False False False False False False False False True False False True False ...
www.pinterest.com/sunnyLENA/art/ 106 sunnyLENA ART 128 True False False False False False False False False False False True False False True False ...
www.pinterest.com/sunnyLENA/bags/ 106 sunnyLENA Bags 98 True False False False False False False False False False False True False False True False ...
www.pinterest.com/sunnyLENA/beautiful-japan/ 106 sunnyLENA Beautiful JAPAN 142 True False False False False False False False False False False True False False True False ...
www.pinterest.com/sunnyLENA/beautiful-nature/ 106 sunnyLENA Beautiful Nature 151 True False False False False False False False False False False True False False True False ...
www.pinterest.com/sunnyLENA/bento/ 106 sunnyLENA BENTO 54 True False False False False False False False False False False True False False True False ...
www.pinterest.com/sunnyLENA/bikini/ 106 sunnyLENA BIKINI 271 True False False False False False False False False False False True False False True False ...
www.pinterest.com/sunnyLENA/black-cat/ 106 sunnyLENA Black Cat 116 True False False False False False False False False False False True False False True False ...
www.pinterest.com/sunnyLENA/blue/ 106 sunnyLENA BLUE 216 True False False False False False False False False False False True False False True False ...
www.pinterest.com/sunnyLENA/bucketlist/ 106 sunnyLENA Bucketlist 86 True False False False False False False False False False False True False False True False ...
www.pinterest.com/sunnyLENA/camping-1/ 106 sunnyLENA CAMPING-1 301 True False False False False False False False False False False True False False True False ...
www.pinterest.com/sunnyLENA/camping-2/ 106 sunnyLENA CAMPING-2 325 True False False False False False False False False False False True False False True False ...
www.pinterest.com/sunnyLENA/cars/ 106 sunnyLENA Cars 100 True False False False False False False False False False False True False False True False ...
www.pinterest.com/sunnyLENA/christmas-2/ 106 sunnyLENA Christmas-2 221 True False False False False False False False False False False True False False True False ...
www.pinterest.com/sunnyLENA/christmas-is-just-around-the-corner/ 106 sunnyLENA CHRISTMAS is just around the corner. 283 True False False False False False False False False False False True False False True False ...
www.pinterest.com/sunnyLENA/citiesplaces-ive-visited/ 106 sunnyLENA CITIES:Places I’ve visited 168 True False False False False False False False False False False True False False True False ...
www.pinterest.com/sunnyLENA/cooool-design/ 106 sunnyLENA Cooool Design 57 True False False False False False False False False False False True False False True False ...
www.pinterest.com/sunnyLENA/cottage-and-lovely-house/ 106 sunnyLENA Cottage and Lovely House 187 True False False False False False False False False False False True False False True False ...
www.pinterest.com/sunnyLENA/cozy-small-space-1/ 106 sunnyLENA Cozy Small Space-1 300 True False False False False False False False False False False True False False True False ...
www.pinterest.com/sunnyLENA/cozy-small-space-2/ 106 sunnyLENA Cozy Small Space-2 52 True False False False False False False False False False False True False False True False ...
www.pinterest.com/sunnyLENA/cute-lovely-things/ 106 sunnyLENA CUTE & LOVELY THINGS 186 True False False False False False False False False False False True False False True False ...
www.pinterest.com/sunnyLENA/diy/ 106 sunnyLENA DIY 148 True False False False False False False False False False False True False False True False ...
www.pinterest.com/sunnyLENA/done-list/ 106 sunnyLENA Done List 42 True False False False False False False False False False False True False False True False ...
www.pinterest.com/sunnyLENA/fall-colors/ 106 sunnyLENA FALL colors 61 True False False False False False False False False False False True False False True False ...
www.pinterest.com/sunnyLENA/fancy-cakes-o/ 106 sunnyLENA Fancy Cakes:-o 101 True False False False False False False False False False False True False False True False ...
www.pinterest.com/sunnyLENA/fashion-1/ 106 sunnyLENA FASHION-1 300 True False False False False False False False False False False True False False True False ...
www.pinterest.com/sunnyLENA/fashion-2/ 106 sunnyLENA FASHION-2 300 True False False False False False False False False False False True False False True False ...
www.pinterest.com/sunnyLENA/fashion-3/ 106 sunnyLENA FASHION-3 300 True False False False False False False False False False False True False False True False ...
www.pinterest.com/sunnyLENA/fashion-4/ 106 sunnyLENA FASHION-4 61 True False False False False False False False False False False True False False True False ...
www.pinterest.com/sunnyLENA/female/ 106 sunnyLENA Female 293 True False False False False False False False False False False True False False True False ...
www.pinterest.com/sunnyLENA/film/ 106 sunnyLENA FILM 38 True False False False False False False False False False False True False False True False ...
www.pinterest.com/sunnyLENA/flowers-1/ 106 sunnyLENA FLOWERS-1 300 True False False False False False False False False False False True False False True False ...
www.pinterest.com/sunnyLENA/flowers-2/ 106 sunnyLENA FLOWERS-2 123 True False False False False False False False False False False True False False True False ...
www.pinterest.com/sunnyLENA/food/ 106 sunnyLENA FOOD 173 True False False False False False False False False False False True False False True False ...
www.pinterest.com/sunnyLENA/funny/ 106 sunnyLENA Funny 327 True False False False False False False False False False False True False False True False ...
www.pinterest.com/sunnyLENA/good-life-vision-board/ 106 sunnyLENA Good Life / Vision Board 358 True False False False False False False False False False False True False False True False ...
www.pinterest.com/sunnyLENA/gorgeous-design/ 106 sunnyLENA Gorgeous Design 50 True False False False False False False False False False False True False False True False ...
www.pinterest.com/sunnyLENA/green/ 106 sunnyLENA Green 126 True False False False False False False False False False False True False False True False ...
www.pinterest.com/sunnyLENA/grief-solitude-serene/ 106 sunnyLENA Grief, Solitude & Serene 300 True False False False False False False False False False False True False False True False ...
www.pinterest.com/sunnyLENA/hair-style/ 106 sunnyLENA HAIR STYLE 126 True False False False False False False False False False False True False False True False ...
... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ...

1339814 rows × 53 columns


In [11]:
boards_users_df.to_hdf('../data/results/boards_users_df_feb27.h5', 'df', mode='w', complib='blosc:lz4', complevel=9)
!ls -lh ../data/results


total 119M
-rw-rw-r-- 1 sergeyk sergeyk 119M Feb 27 22:08 boards_users_df_feb27.h5
lrwxrwxrwx 1 sergeyk sergeyk   33 Feb 27 21:53 mongodb -> /research_data/pinsplorer/mongodb