<jupyter_start><jupyter_text>Data cleaningIn this notebook we will clean our Dataframe 'Kickstarter_merged.csv'. That means we will create new columns which we think are important as well as we will drop columns with unimportance. Overview new columns    * column 'blurbs' will be replaced with -> 'blurb_len_c', 'blurb_len_w'    * column 'slug' will be trplaced with -> 'slug_len_c'. 'slug_len_w', 'cat_in_slug'    * column 'category' will be replaced with -> 'category_parent_id', 'category_id', 'category_name'    * column 'created_at' will be replaced with -> 'created_year', 'created_month'    * column 'deadline' will be replaced with -> 'deadline_year', 'deadline_month'    * column 'goal' will be converted in USD    * column 'launched_at' will be replaced with -> 'launched_year', 'launched_month'    * new column duration of the crowdfunding    * new column preparation time (from created_at until launched_at    * column 'state_changed_at' will be replaced with -> 'state_changed_year', 'state_changed_month,     * new column pledged/backer    * new column pledged/goal in % Overview dropped columns    * converted_pledged_amount    * creator    * currency     * currency_symbol    * currency_trailing_code    * current_curency    * disable_communication    * friends    * fx_rate    * id after using it for other transformations    * is_backing    * is_starrable    * is_starred    * location    * name     * permissions    * photo    * pledged    * profile     * slug    * source_url    * spotlight    * state_changed_at     * static_usd_rate     * urls    * usd_type         Overview dropped rows    * 8 rows with missing values in column 'blurbs'    * drop the duplicates    * drop rows with values 'suspended' and 'live' in column 'state'<jupyter_code># import packages
import pandas as pd
import numpy as np
import seaborn as sns
import time
import datetime as dt
import json<jupyter_output><empty_output><jupyter_text>Read in file.<jupyter_code>df = pd.read_csv('data/Kickstarter_merged.csv', index_col=0)<jupyter_output><empty_output><jupyter_text>Create new columns    Let´s start with creating new columns: We will replace the columns 'blurbs', 'slug', category', 'created_at', 'deadline', 'launched_at', 'state_changed_at'.<jupyter_code>#function to return length of string (number of characters)
def string_len_c(string):
    string_str = str(string)
    string_len = len(string_str)
    return string_len
#function to return length of string (number of words)
def string_len_w(string):
    string_str = str(string)
    string_list = string_str.split()
    string_len = len(string_list)
    return string_len
#function to return length of string (number of words, seperated by "-"
def string_len_slug_w(string):
    string_str = str(string)
    string_list = string_str.split("-")
    string_len = len(string_list)
    return string_len
#function that checks if category or subcategory in slug
def word_in_lists(string, list_cat, list_subcat):
    string_str = str(string)
    string_list = string_str.split("-")
    for word in string_list:
        if word in list_cat:
            return word
        elif word in list_subcat:
            return word
        else:
            return 0
# replace column 'blurbs' and 'slug'
df['blurb_len_c'] = df.apply(lambda x: string_len_c(x['blurb']), axis=1)
df['blurb_len_w'] = df.apply(lambda x: string_len_w(x['blurb']), axis=1)

df['slug_len_c'] = df.apply(lambda x: string_len_c(x['slug']), axis=1)
df['slug_len_w'] = df.apply(lambda x: string_len_slug_w(x['slug']), axis=1)
subcategories = ['Playing Cards', 'Rock', 'Nonfiction', 'Classical Music', 'Music',
       'Restaurants', 'Small Batch', 'Footwear', 'Sculpture',
       'Photography', 'Fashion', 'Mixed Media', 'Software',
       'Performance Art', 'Theater', 'Graphic Design', 'Narrative Film',
       'Television', 'Sound', 'Video Art', 'Printing', 'Anthologies',
       'Art Books', 'Live Games', 'Performances', 'Technology',
       'Civic Design', 'Literary Spaces', 'Taxidermy', 'Vegan', 'Gadgets',
       'Spaces', 'Pet Fashion', 'Poetry', 'Jewelry', 'Puzzles',
       'Camera Equipment', 'Installations', '3D Printing', 'Indie Rock',
       'Events', 'Accessories', 'Shorts', 'Documentary', 'Web',
       'Translations', 'Comics', 'Pottery', 'Tabletop Games', 'Art',
       'Faith', 'Zines', 'Action', 'Public Art', 'Ceramics',
       'Illustration', 'Hip-Hop', 'Cookbooks', 'Conceptual Art',
       'Wearables', 'Food', 'Thrillers', 'Design', 'World Music',
       'Movie Theaters', "Children's Books", 'Academic', 'Video Games',
       'Childrenswear', 'Music Videos', 'Fantasy', 'Product Design',
       'Latin', 'Romance', 'Stationery', 'Typography', 'Comedy',
       'Apparel', 'Animation', 'Fine Art', 'Woodworking', 'Experimental',
       'Digital Art', 'DIY', 'Crafts', 'Gaming Hardware', 'Glass',
       'Painting', 'Architecture', 'Young Adult', 'R&B', 'People',
       'Film & Video', 'Robots', 'Blues', 'Periodicals', 'Kids',
       'Candles', 'Knitting', 'Workshops', 'Jazz', 'Pop', 'Dance',
       'Fiction', 'Family', 'Flight', 'Country & Folk', 'Science Fiction',
       'Interactive Design', 'Plays', 'Calendars', 'Print', 'Weaving',
       'Comic Books', 'Webseries', 'Animals', 'Photobooks', 'Food Trucks',
       'Apps', 'Drinks', 'Graphic Novels', 'Webcomics',
       'Fabrication Tools', 'Textiles', 'Publishing', 'Ready-to-wear',
       'Audio', 'Horror', 'Nature', 'Video', 'Makerspaces',
       "Farmer's Markets", 'Punk', 'Literary Journals', 'Bacon',
       'Community Gardens', 'Letterpress', 'Drama', 'Mobile Games',
       'Hardware', 'Farms', 'Places', 'Space Exploration', 'Journalism',
       'Musical', 'Festivals', 'Radio & Podcasts', 'Couture', 'Metal',
       'Electronic Music', 'DIY Electronics', 'Games', 'Immersive',
       'Residencies', 'Embroidery', 'Photo', 'Quilts', 'Crochet',
       'Chiptune']
# making lower case subcategories and split if more than one word
subcategories_lower = []
for cat in subcategories:
    cat_split = cat.split()
    for cat in cat_split:
        subcategories_lower.append(cat.lower())
categories = ['Games', 'Music', 'Publishing', 'Food', 'Fashion', 'Art',
       'Photography', 'Technology', 'Theater', 'Design', 'Film & Video',
       'Crafts', 'Comics', 'Dance', 'Journalism']
# making lower case subcategories and split if more than one word
categories_lower = []
for cat in categories:
    cat_split = cat.split(" & ")
    for cat in cat_split:
        categories_lower.append(cat.lower())
#function that checks if category or subcategory in slug
def word_in_lists(string, list_cat, list_subcat):
    string_str = str(string)
    string_list = string_str.split("-")
    for word in string_list:
        if word in list_cat:
            return 1
        elif word in list_subcat:
            return 1
        else:
            return 0
df['cat_in_slug'] = df.apply(lambda x: word_in_lists(x['slug'], categories_lower, subcategories_lower), axis=1)
# new columns with information from 'category'
df['category_parent_id'] = pd.DataFrame([json.loads(df["category"][i]).get("parent_id") for i in range(df.shape[0])])
df['category_id'] = pd.DataFrame([json.loads(df["category"][i]).get("id") for i in range(df.shape[0])])
df['category_name'] = pd.DataFrame([json.loads(df["category"][i]).get("name") for i in range(df.shape[0])])
df['category_parent_id'] = df['category_parent_id'].fillna(0).astype("int")
#funtion to extract the year out of the number
def extract_year(number):
    gmtime = time.gmtime(number)
    return gmtime[0]
#funtion to extract the month out of the number
def extract_month(number):
    gmtime = time.gmtime(number)
    return gmtime[1]
# replace column 'created_at'
df["created_year"] = df.apply(lambda x: extract_year(x["created_at"]), axis=1)  
df["created_month"] = df.apply(lambda x: extract_month(x["created_at"]), axis=1)
# replace column 'deadline'
df["deadline_year"] = df.apply(lambda x: extract_year(x["deadline"]), axis=1)  
df["deadline_month"] = df.apply(lambda x: extract_month(x["deadline"]), axis=1)
# replace column 'launched_at'
df["launched_year"] = df.apply(lambda x: extract_year(x["launched_at"]), axis=1)  
df["launched_month"] = df.apply(lambda x: extract_month(x["launched_at"]), axis=1)
# function for calculating difference launched_at und deadline and converting to days
def duration(deadline, launched_at):
    duration = deadline - launched_at
    duration_complete = dt.timedelta(seconds=duration)
    return duration_complete.days
# calculating duration column
df["duration_days"] = df.apply(lambda x: duration(x["deadline"], x["launched_at"]), axis=1)
# created_at < launched at < deadline
def preparation(created_at, launched_at):
    duration = launched_at - created_at
    duration_complete = dt.timedelta(seconds=duration) 
    return duration_complete.days
# calculating preparation column
df["preparation"] = df.apply(lambda x: preparation(x["created_at"], x["launched_at"]), axis=1)
# create new column pledge per backer
df['pledged_per_backer'] = (df['usd_pledged'] / df['backers_count']).round(2)
df.usd_pledged = df.usd_pledged.astype("int")
df.goal = df.goal.astype("int")
df.pledged_per_backer = df.pledged_per_backer.fillna(0).astype("int")<jupyter_output><empty_output><jupyter_text>The column 'goal' will also be converted into USD with the static_usd_rate to ensure the comparability.<jupyter_code># convert 'goal' in USD
df['goal'] = (df['goal'] * df['static_usd_rate']).round(2)

# round usd_pledged
df['usd_pledged'] = df['usd_pledged'].round(2)
# create new column pledged/goal in %
df['rel_pledged_goal'] = ((df['usd_pledged'] / df['goal']) * 100).round(2) 
empty = []
for i in range(df.shape[0]):
    if df["category_parent_id"][i] != 0:
        empty.append(df["category_parent_id"][i])
    else:
        empty.append(df["category_id"][i])
df["filled_parent"] = pd.DataFrame(empty)
parents_dict = {1: "Art", 3: "Comics", 6: "Dance", 7: "Design", 9: "Fashion", 10: "Food",
                11: "Film & Video", 12: "Games", 13: "Journalism", 14: "Music", 15: "Photography", 16: "Technology",
               17: "Theater", 18: "Publishing", 26: "Crafts"}
df["parent_name"] = df["filled_parent"].apply(lambda x: parents_dict.get(x))<jupyter_output><empty_output><jupyter_text>Drop rowsSo far we created new columns, we dropped unimportant columns. Now we can drop unimportant rows because they have missing values or they don´t support our business case.<jupyter_code># drop 8 rows with missing values in column 'blurbs'
df.dropna(subset = ['blurb'], inplace=True)
# drop the duplicates

# creating dataframe with only the the last of all duplicates entry of the duplicates. 
df_duplicates = df[df.id.duplicated(keep='last')]

# creating dataframe and dropping all duplicates and keep the last ("newest") duplicate 
df = df.drop_duplicates(subset=['id'], keep='last')
# drop rows with values 'suspended' and 'live' in column 'state'
df = df.drop(df[df['state'] == "suspended" ].index)
df = df.drop(df[df['state'] == "live" ].index)
# drop row with goal == 0
df = df.drop(df[df['goal'] == 0].index)<jupyter_output><empty_output><jupyter_text>Drop columnsNow we drop the columns we don´t need.At first we will drop friends, is_backing, is_starred and permissions because there are a lot of missing values.<jupyter_code># drop 'friends', 'is_backing', 'is_starred', 'permissions'
df.drop(['friends', 'is_backing', 'is_starred', 'permissions'], axis=1, inplace=True)<jupyter_output><empty_output><jupyter_text>Let´s drop the currency related columns we don´t need because we will focus on the goal and pledge amount.<jupyter_code># drop all currency related columns 
df.drop(['converted_pledged_amount', 'currency', 'currency_symbol', 'currency_trailing_code', 'current_currency', 
         'fx_rate', 'pledged', 'static_usd_rate', 'usd_type'], axis=1, inplace = True)<jupyter_output><empty_output><jupyter_text>We will also drop the columns which we modified/will be replaced or we needed for modifying other columns.<jupyter_code>df.drop(['blurb', 'category', 'created_at', 'deadline', 'launched_at', 'slug', 'state_changed_at', 'id'], axis=1, inplace=True)<jupyter_output><empty_output><jupyter_text>At last we will drop the other columns which are not important for our analysis.<jupyter_code>df.drop(['creator', 'disable_communication', 'is_starrable', 'location', 'name', 'photo', 'profile', 'source_url',
        'spotlight', 'urls'], axis=1, inplace=True)
df.info()
df.head()<jupyter_output><empty_output><jupyter_text>conversion<jupyter_code>df['country'] = df['country'].astype('category')
df['state'] = df['state'].astype('category')
df['category_name'] = df['category_name'].astype('category')
df['cat_in_slug'] = df['cat_in_slug'].astype('int')<jupyter_output><empty_output><jupyter_text>export to csv<jupyter_code>df.to_csv('data/kickstarter_preprocess.csv', index=False)<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.1452275723036365, 'fraction_numerical': 0.005272544002481197, 'mean_word_length': 4.979601298099212, 'pattern_counts': {'":': 0, '<': 47, '<?xml version=': 0, '>': 52, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '11', 'dir': 'jupyter-structured-clean-dedup', 'id': '281738', 'license': 'MIT', 'n_tokens_mistral': 4342, 'n_tokens_neox': 4178, 'n_words': 1134, 'path': 'Dataframe_final.ipynb', 'repo_name': 'olehansen538/MPO-Kickstarter', 'size': '12897'}