<jupyter_start><jupyter_text>Data acquisition and wrangling<jupyter_code># mongodb functions
from pymongo import MongoClient
# remember to start te mongodb server: sudo service mongod start

# sql functions
from sqlalchemy import create_engine
from sqlalchemy_utils import database_exists, create_database
import psycopg2

# data wraggling functions 
import pandas as pd
import numpy as np

import pickle<jupyter_output><empty_output><jupyter_text>connect with mongodDB and get proposal information<jupyter_code># establish the connection
myclient = MongoClient("mongodb://localhost:27017/")
mydb = myclient["kick"] #pyramids is the database
mycol = mydb["k1912"] #invoice is the collection
# get project information

records =[]

for proposal in mycol.find({"data.state": { "$in": ["successful", "failed"]}},
                           {"data.id" : 1, 
                            "data.name" : 1,
                            "data.blurb" : 1,
                            "data.goal" : 1 , 
                            "data.pledged" : 1,
                            "data.state" : 1,
                            "data.country_displayable_name" : 1,
                            "data.deadline" : 1,
                            "data.created_at" : 1,
                            "data.launched_at" : 1,
                            "data.staff_pick": 1,
                            "data.backers_count" : 1,
                            "data.static_usd_rate" : 1,
                            "data.creator.id" : 1,
                            "data.category.id" : 1,
                            "data.urls.web" : 1}):
    
    project_id = proposal['data']['id']
    project_name = proposal['data']['name']
    blurb = proposal['data']['blurb']
    goal = proposal['data']['goal']
    pledge = proposal['data']['pledged']
    state = proposal['data']['state']
    country = proposal['data']['country_displayable_name']
    deadline = proposal['data']['deadline']
    create = proposal['data']['created_at']
    launch = proposal['data']['launched_at']
    staff_pick = proposal['data']['staff_pick']
    backers = proposal['data']['backers_count']
    usd_rate = proposal['data']['static_usd_rate']
    creator = proposal['data']['creator']['id']
    category = proposal['data']['category']['id']
    url = proposal['data']['urls']['web']['project']
    
    record = [project_id, project_name, blurb, goal, pledge, state,
             country, deadline, create, launch, staff_pick, backers,
             usd_rate, creator, category, url]
        
    records.append(record)
dat_records = pd.DataFrame(records,
                           columns = ['project_id', 'project_name', 'blurb', 'goal', 'pledge', 'state',
                                      'country', 'deadline', 'create', 'launch', 'staff_pick', 'backers',
                                      'usd_rate', 'creator', 'category', 'url']).drop_duplicates()
# there are duplicates in this data, so I'm dropping them
dat_records.shape
#save a copy of the records
#with open("pickle/project_info.pkl", "wb") as f:
#    pickle.dump(dat_records, f)<jupyter_output><empty_output><jupyter_text>get category info and clean up such to include parent categories<jupyter_code># get category info
categories = []

for proposal in mycol.find({"data.state" : { "$in": ["successful", "failed"]}, 
                            "data.category.parent_id" : {"$exists" : True }},
                            {"data.category.id": 1, 
                             "data.category.name": 1, 
                             "data.category.position" :1 , 
                             "data.category.parent_id" : 1}):
    
    category_id = proposal['data']['category']['id']
    category_name = proposal['data']['category']['name']
    category_position = proposal['data']['category']['position']
    category_parent = proposal['data']['category']['parent_id']
  
    category = [category_id, category_name, category_position, category_parent]
    categories.append(category)

    # convert to dataframe
dat_categories = pd.DataFrame(categories,
                               columns = ['category_id', 
                                          'category_name',
                                          'category_position',
                                          'category_parent'])
# get parent_category info
categories_parent = []

for proposal in mycol.find({"data.state" : { "$in": ["successful", "failed"]}, 
                            "data.category.parent_id" : {"$exists" : False }},
                            {"data.category.id": 1, 
                             "data.category.name": 1, 
                             "data.category.position" :1}):
    
    category_id = proposal['data']['category']['id']
    category_name = proposal['data']['category']['name']
    category_position = proposal['data']['category']['position']
  
    category = [category_id, category_name, category_position]
    categories_parent .append(category)
    
# convert to dataframe
dat_categories_parent = pd.DataFrame(categories_parent,
                                      columns =['category_parent',
                                                'name_parent',
                                                'position_parent'])
# get uniqe rows
data_categories_parent = dat_categories_parent.drop_duplicates().reset_index(drop = True)
data_categories = dat_categories.drop_duplicates().reset_index(drop = True)
# join in the parent categories on child categories
data_catefories_join = data_categories.join(data_categories_parent.set_index('category_parent'),
                                               on = "category_parent", how = 'left'
                                              ).take([0, 1, 3, 4], axis=1)
# shape parent categories such that can be appended to child categories
data_categories_parent_c = data_categories_parent
data_categories_parent_c['category_id'] = data_categories_parent['category_parent']
data_categories_parent_c['category_name'] = data_categories_parent['name_parent']
data_categories_parent_c.take([0, 1, 3, 4], axis=1).head()
# append both parent and child categories in one
data_catefories_cleaned = data_catefories_join.append(data_categories_parent_c.take([0, 1, 3, 4], axis=1),
                                                      sort = True).reset_index()
data_catefories_cleaned.shape
# total of 170 categories, and 15 parent categories
#### save a copy of the category records
#with open("pickle/category_info.pkl", "wb") as f:
#    pickle.dump(data_catefories_cleaned, f)<jupyter_output><empty_output><jupyter_text>merge project info with category info and cleanup<jupyter_code>data_records = dat_records.join(data_catefories_cleaned.set_index('category_id'),
                                on = 'category', 
                                how = 'left')
data_records.shape
data_records['goal_usd'] = data_records['goal'] * data_records['usd_rate']
data_records['pledge_usd'] = data_records['pledge'] * data_records['usd_rate']
data_records['percent'] = data_records['pledge'] / data_records['goal']
data_records['days_active'] = (data_records['deadline'] - data_records['launch'])/86400 #86400 seconds in a day
data_records['days_draft'] = (data_records['launch'] - data_records['create'])/86400<jupyter_output><empty_output><jupyter_text>export project info into the postgre database<jupyter_code>dbname = 'birth_db'
username = 'shengsql'
pswd = '<PASSWORD>'
# connect to the database with psycopg:
con = None
con = psycopg2.connect(database = dbname, user = username, host='localhost', password=<PASSWORD>)
cursor = con.cursor()
# save the cleaned project info into the database
engine = create_engine('postgresql://%s:%s@localhost/%s'%(username,pswd,dbname))
#data_records.to_sql('projects_m', engine)<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.12622971764405264, 'fraction_numerical': 0.008815638175546186, 'mean_word_length': 2.571167883211679, 'pattern_counts': {'":': 9, '<': 23, '<?xml version=': 0, '>': 23, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 21, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '5', 'dir': 'jupyter-structured-clean-dedup', 'id': '531700', 'license': 'MIT', 'n_tokens_mistral': 2232, 'n_tokens_neox': 2142, 'n_words': 464, 'path': 'data_cleaning.ipynb', 'repo_name': 'ShengpeiWang/kickstarter', 'size': '7815'}