<jupyter_start><jupyter_text>Importing the Libraries At first, we will load the libraries, that we will use to conduct the analysis.<jupyter_code># Basic imports
import pandas as pd
import numpy as np


# Visualization libraries
import matplotlib.pyplot as plt
import seaborn as sns



# Sentiment analysis
#!pip install TextBlob

from textblob import TextBlob<jupyter_output><empty_output><jupyter_text>Now, we will load the data. In total, we have 55 files that need to be loaded and merged. We will do this in the next step.<jupyter_code>df = pd.read_csv("data/Kickstarter000.csv")

for i in range(1,56):
    
    num = str(1000 + i)
    
    dfi = pd.read_csv(f"data/Kickstarter{num[1:4]}.csv")
    df = pd.concat([df,dfi], axis = 0)

df.reset_index()    
df.to_csv("data/kickstarter_raw.csv")<jupyter_output><empty_output><jupyter_text>The new data set contains 209222 observations and 37 columns.<jupyter_code>df = pd.read_csv("data/kickstarter_raw.csv", index_col=0)<jupyter_output><empty_output><jupyter_text>Data Preparation At first we need to get a overview of the data to see where data cleaning is needed.<jupyter_code>df.info()<jupyter_output><class 'pandas.core.frame.DataFrame'>
Int64Index: 209222 entries, 0 to 964
Data columns (total 37 columns):
 #   Column                    Non-Null Count   Dtype  
---  ------                    --------------   -----  
 0   backers_count             209222 non-null  int64  
 1   blurb                     209214 non-null  object 
 2   category                  209222 non-null  object 
 3   converted_pledged_amount  209222 non-null  int64  
 4   country                   209222 non-null  object 
 5   created_at                209222 non-null  int64  
 6   creator                   209222 non-null  object 
 7   currency                  209222 non-null  object 
 8   currency_symbol           209222 non-null  object 
 9   currency_trailing_code    209222 non-null  bool   
 10  current_currency          209222 non-null  object 
 11  deadline                  209222 non-null  int64  
 12  disable_communication     209222 non-null  bool   
 13  friends                   300 non-null     obje[...]<jupyter_text>- Since we have some variables of type "object" in the data set, which probably contain relevant information, we will explore them in more detail later.- As we can see from above the variables **"blurb", "friends", "is_backing", "is_starred", "location", "permissions" and "usd_type"** contain missing values. The variables **"friends", "is_backing", "is_starred" and "permissions"** have more than 90% missings. We will delete them from our dataset and take care of of the other variables with missings later.- Further variables that can be delete as they do not have any valuable information for our analysis are **"currency_symbol", currency_trailing_code", "photo", "source_url", "urls", state_changed_at", "spotlight", "disable_communication", "is_starrable"**. In the next step, we will delete them together with the variables that have more than 90% missings.<jupyter_code># Exclusion of the following columns from our data

df = df.drop(["currency_symbol",
         "currency_trailing_code",
         "friends",
         "is_backing",
         "is_starred",
         "permissions", 
         "photo",
         "source_url",
         "urls",
         "state_changed_at",
         "spotlight",
         "disable_communication",
         "is_starrable"], axis=1)<jupyter_output><empty_output><jupyter_text>Further, we can see that the type of the columns **"created_at", "deadline" and "launched_at"** is not correct (integer instead of datetime format). In the next step, we will change that.In addition, we will extract year, month and weekday out of the variables **"launched_at" and "dealine"** since we will later on explore these in more depth.<jupyter_code># convert unix date time to pandas datetime format
df['created_at'] = pd.to_datetime(df['created_at'],unit='s')
df['deadline'] = pd.to_datetime(df['deadline'],unit='s')
df['launched_at'] = pd.to_datetime(df['launched_at'],unit='s')

# pick out year, month and weekday out of date of launching
df['yr_launched'] = df['launched_at'].dt.year
df['mo_launched'] = df['launched_at'].dt.month
df['wd_launched'] = df['launched_at'].dt.weekday

# pick out year, month and weekday out of date of deadline
df['yr_deadline'] = df['deadline'].dt.year
df['mo_deadline'] = df['deadline'].dt.month
df['wd_deadline'] = df['deadline'].dt.weekday<jupyter_output><empty_output><jupyter_text>For our analysis it could also be interesting to investigate the duration between deadline and launched date in more depth. Thus, we will also calculate the duration between the aformentioned variables.<jupyter_code># calculate duration of founding_phase (time between launching and deadline)
founding_phase = df['deadline'] - df['launched_at']
df['founding_phase'] = founding_phase.dt.days

# calculate duration of phase_before (time between creation and launcing)
phase_before = df['launched_at'] - df['created_at']
df['phase_before'] = phase_before.dt.days<jupyter_output><empty_output><jupyter_text>The original variables **"deadline" and "launched_at"** are no longer needed. We can delete them.<jupyter_code># drop columns created_at, deadline, launched_at
df = df.drop(["created_at", "deadline", "launched_at"], axis=1)<jupyter_output><empty_output><jupyter_text>Now lets have a look on our target variable **"state"**. This variable contains info about the outcome of the project (failed or successful). Before, we encode "state", we will explore this variable in more depth.<jupyter_code># Counts
df.state.value_counts()<jupyter_output><empty_output><jupyter_text>We can see the variable "state" has 5 levels. Overall, most of the observations are labeled as "successful". For our research question only the levels "successful" and "failed" relevant. So, we exclude all other levels.<jupyter_code>df = df.query('state in ["successful", "failed"]')

# Prove if it works
df.state.value_counts()

# Shape of df
df.shape<jupyter_output><empty_output><jupyter_text>After dropping rows and colums the data set contains 192664 observations and 29 columns. At next, we will create dummy variables of the **"state" and "staff_pick"** variables as this makes it easier to work with them.<jupyter_code># Encoding of the state (target) variable:
df['state'] = df['state'].apply(lambda x: 1 if x == 'successful' else 0)

# 0 = failed
# 1 = sucessful
df['staff_pick'] = df['staff_pick'].apply(lambda x: 1 if x == True else 0)<jupyter_output><empty_output><jupyter_text>Now, let's have a look at the frequency distribution of the individual variables in our data set. For this purpose, we will use histograms.<jupyter_code>df.hist(bins=50, figsize = (20,15));<jupyter_output><empty_output><jupyter_text>A first glance on the distribution reveals that:- Many variables such as **"phase_before"** have outliers. As outliers can strongly affect our analysis we will exculde data from our df later. - Some variables are categorical like **"mo_lunched" and "staff_pick"** and other such ad **"founding_phase"** are numeric. We have to calculate the goal in usd using the **"goal" and "static_usd_rate"**<jupyter_code>df["goal_usd"] = df["goal"]*df["static_usd_rate"]<jupyter_output><empty_output><jupyter_text>As outliers can strongly affect our analysis we exclude the highest 1.5% of our data.<jupyter_code># get 98.5% quantile (highest 1.5%) of 'phase_before', 'usd_pledged', 'goal_usd'
q_hi1  = df["phase_before"].quantile(0.985)
q_hi2  = df["usd_pledged"].quantile(0.985)
q_hi3  = df["goal_usd"].quantile(0.985)

# remove highest 1.5% percent of the data with the 98.5% quantiles
df = df[(df["phase_before"] < q_hi1) & (df["usd_pledged"] < q_hi2) & (df["goal_usd"] < q_hi3)]<jupyter_output><empty_output><jupyter_text>Now, we come back to our variables that contain missing values.<jupyter_code>df.isnull().sum()<jupyter_output><empty_output><jupyter_text>As shown above, the variables **"location" and "usd_type"** contain a relatively small amount of missings. Since we are working with a large data set, deleting these rows should not be a problem.<jupyter_code># Drop rows which contain any NaN value in the selected columns
df = df.dropna( how='any',
                    subset=['location', 'usd_type', "blurb"])
df.info()<jupyter_output><class 'pandas.core.frame.DataFrame'>
Int64Index: 184113 entries, 0 to 964
Data columns (total 30 columns):
 #   Column                    Non-Null Count   Dtype  
---  ------                    --------------   -----  
 0   backers_count             184113 non-null  int64  
 1   blurb                     184113 non-null  object 
 2   category                  184113 non-null  object 
 3   converted_pledged_amount  184113 non-null  int64  
 4   country                   184113 non-null  object 
 5   creator                   184113 non-null  object 
 6   currency                  184113 non-null  object 
 7   current_currency          184113 non-null  object 
 8   fx_rate                   184113 non-null  float64
 9   goal                      184113 non-null  float64
 10  id                        184113 non-null  int64  
 11  location                  184113 non-null  object 
 12  name                      184113 non-null  object 
 13  pledged                   184113 non-null  floa[...]<jupyter_text>Now we will explore variables of the type "object".<jupyter_code>df[["blurb", "category", "country", "currency", "current_currency", "name", "profile", "slug", "usd_type", "location", "creator"]].head()<jupyter_output><empty_output><jupyter_text>The column **"creator", category" and "location"** contain dictionaries. We would like to extract the content of each column into separate columns. The **"profile"** coulmn contains a dictionary as well but the information provided is not useful for our analysis. Later on, we will drop this column.<jupyter_code>df["category_dict"] = df["category"].apply(eval)
df_category = df["category_dict"].apply(pd.Series)
df_category.head()
df_category["main_category"] = df_category["slug"].str.split("/").str[0]
df_category.head()
df["main_category"] = df_category["main_category"] 
df["subcategory"] = df_category["name"]
df = df.drop(["category", "category_dict"], axis=1)
df["location"].fillna("{}", inplace = True)

df["location"] = df["location"].str.replace(',"is_root":false', "")
df["location"] = df["location"].str.replace(',"is_root":true', "" )
df["location"] = df["location"].str.replace('null', "{}" )

df["location_dict"] = df["location"].apply(eval)
df_location = df["location_dict"].apply(pd.Series)
df_location.head()

df["city"] = df_location["name"]
df["region"] = df_location["state"]
df["city_type"] = df_location["type"]
df = df.drop(["location", "location_dict"], axis=1)
df["creator_id"] = df["creator"].str[6:22]
df["creator_id"] = df["creator_id"].str.replace('"', "")
df["creator_id"] = df["creator_id"].str.split(",").str[0]
df["creator_id"] = df["creator_id"].astype(int)

df_creator = df[["creator_id","state"]].groupby("creator_id").count()
df_creator.rename(columns = {"state": "creator_num_projects"}, inplace = True)
df_creator.reset_index()

df = df.merge( df_creator, how = "left", on = "creator_id")
df.info()<jupyter_output><class 'pandas.core.frame.DataFrame'>
Int64Index: 184113 entries, 0 to 184112
Data columns (total 35 columns):
 #   Column                    Non-Null Count   Dtype  
---  ------                    --------------   -----  
 0   backers_count             184113 non-null  int64  
 1   blurb                     184113 non-null  object 
 2   converted_pledged_amount  184113 non-null  int64  
 3   country                   184113 non-null  object 
 4   creator                   184113 non-null  object 
 5   currency                  184113 non-null  object 
 6   current_currency          184113 non-null  object 
 7   fx_rate                   184113 non-null  float64
 8   goal                      184113 non-null  float64
 9   id                        184113 non-null  int64  
 10  name                      184113 non-null  object 
 11  pledged                   184113 non-null  float64
 12  profile                   184113 non-null  object 
 13  slug                      184113 non-null  o[...]<jupyter_text>The description of the project (**"blurb"**) could be a predictor of successful or failed projects. We will investigate this variable further. First, we will extract the number of words and then perform sentiment analysis.<jupyter_code># Extract number of words
df['words_blurb'] = df['blurb'].apply(lambda x: len(str(x).split()))
# Sentiment analysis
a = TextBlob(df.loc[1000, "blurb"])
print(df.loc[1000, "blurb"])
a.sentiment
df['polarity_blurb'] = df['blurb'].apply(lambda x: TextBlob(str(x)).sentiment[0])
df['subjectivity_blurb'] = df['blurb'].apply(lambda x: TextBlob(str(x)).sentiment[1])<jupyter_output><empty_output><jupyter_text>Finally, we will delete variables that are no longer needed and save the cleaned data.<jupyter_code>df = df.drop(["creator",
             "blurb",
             "static_usd_rate",
             "fx_rate",
             "currency",
             "current_currency",
             "pledged",
             "goal",
             "profile",
             "converted_pledged_amount",
             "slug"], axis=1)
df.to_csv("data/data_clean.csv")
df = pd.read_csv("data/data_clean.csv", index_col=0)<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.11853063652587745, 'fraction_numerical': 0.03554431885782273, 'mean_word_length': 3.5100603621730384, 'pattern_counts': {'":': 3, '<': 84, '<?xml version=': 0, '>': 81, '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': '20', 'dir': 'jupyter-structured-clean-dedup', 'id': '488213', 'license': 'MIT', 'n_tokens_mistral': 4499, 'n_tokens_neox': 4079, 'n_words': 1250, 'path': '01_data_clean.ipynb', 'repo_name': 'Julilia102/project_kickstarter', 'size': '13448'}