Real Life Data Science: Exploring Clubhouse Data
Tags: datascience, reallife, userusage, clubhouse
Published: 2021-04-21 16:34:56

---
title: Real Life Data Science: Exploring Clubhouse Data
published: true
description: 
tags: ['datascience', 'reallife', 'userusage', 'clubhouse']
cover_image: https://dev-to-uploads.s3.amazonaws.com/uploads/articles/i7o7bgnuvwei7odgsybe.png
---


Original Post here: https://koalatea.io/real-life-data-science-clubhouse/

Hello! And welcome to an article on real life data analysis. Recently, I found a clubhouse user data set on Kaggle. I though using this data would be a good opportunity to go over how review growth for your company. The data set is small, but we can learn some basic from it. Also, if the data is accurate, it is fun to see how a trendy company is doing in real life. Let's get started.


The data set can be fround here. https://www.kaggle.com/johntukey/clubhouse-dataset


```python
import numpy as np
import pandas as pd
import matplotlib
import matplotlib.pyplot as plt
import seaborn as sns
import sqlite3
```

## Load the Data

Our first step is to load the data. The data was given a a sqllite db, so we first connect then run a select query to load all users into a dataframe.


```python
Sql = sqlite3.connect('./Clubhouse_Dataset_v1.db/Clubhouse_Dataset_v1.db')
df = pd.read_sql_query("SELECT * FROM user", Sql)
```

## View at the Data

Next, we will take a look at the data to get an idea. We start by looking at the first few rows.


```python
df.head()
```




<div>
<style scoped>
    .dataframe tbody tr th:only-of-type {
        vertical-align: middle;
    }

    .dataframe tbody tr th {
        vertical-align: top;
    }

    .dataframe thead th {
        text-align: right;
    }
</style>
<table border="1" class="dataframe">
  <thead>
    <tr style="text-align: right;">
      <th></th>
      <th>user_id</th>
      <th>name</th>
      <th>photo_url</th>
      <th>username</th>
      <th>twitter</th>
      <th>instagram</th>
      <th>num_followers</th>
      <th>num_following</th>
      <th>time_created</th>
      <th>invited_by_user_profile</th>
    </tr>
  </thead>
  <tbody>
    <tr>
      <th>0</th>
      <td>4</td>
      <td>Rohan Seth</td>
      <td>https://clubhouseprod.s3.amazonaws.com:443/4_b...</td>
      <td>rohan</td>
      <td>rohanseth</td>
      <td>null</td>
      <td>4187268</td>
      <td>599</td>
      <td>2020-03-17T07:51:28.085566+00:00</td>
      <td>null</td>
    </tr>
    <tr>
      <th>1</th>
      <td>5</td>
      <td>Paul Davison</td>
      <td>https://clubhouseprod.s3.amazonaws.com:443/5_e...</td>
      <td>paul</td>
      <td>pdavison</td>
      <td>null</td>
      <td>3718334</td>
      <td>1861</td>
      <td>2020-03-17T14:36:19.468976+00:00</td>
      <td>null</td>
    </tr>
    <tr>
      <th>2</th>
      <td>8</td>
      <td>Johnny Appleseed</td>
      <td>None</td>
      <td>apple1</td>
      <td>null</td>
      <td>srt_tester_9</td>
      <td>20</td>
      <td>81</td>
      <td>2020-03-19T19:47:00.323603+00:00</td>
      <td>null</td>
    </tr>
    <tr>
      <th>3</th>
      <td>10</td>
      <td>DK 🖍</td>
      <td>https://clubhouseprod.s3.amazonaws.com:443/10_...</td>
      <td>dk</td>
      <td>dksf</td>
      <td>null</td>
      <td>49538</td>
      <td>173</td>
      <td>2020-03-19T23:38:52.574777+00:00</td>
      <td>null</td>
    </tr>
    <tr>
      <th>4</th>
      <td>12</td>
      <td>Jonathan Gheller</td>
      <td>https://clubhouseprod.s3.amazonaws.com:443/12_...</td>
      <td>jonathan</td>
      <td>jgheller</td>
      <td>null</td>
      <td>21250</td>
      <td>81</td>
      <td>2020-03-20T02:30:22.188084+00:00</td>
      <td>null</td>
    </tr>
  </tbody>
</table>
</div>



We look at the shape of the data and see that we have 1,300,515 rows. That's quite a lot of users. There are also 10 columns, so not a ton of detail about each user.


```python
df.shape
```




    (1300515, 10)




```python
df.describe()
```




<div>
<style scoped>
    .dataframe tbody tr th:only-of-type {
        vertical-align: middle;
    }

    .dataframe tbody tr th {
        vertical-align: top;
    }

    .dataframe thead th {
        text-align: right;
    }
</style>
<table border="1" class="dataframe">
  <thead>
    <tr style="text-align: right;">
      <th></th>
      <th>user_id</th>
      <th>num_followers</th>
      <th>num_following</th>
    </tr>
  </thead>
  <tbody>
    <tr>
      <th>count</th>
      <td>1.300515e+06</td>
      <td>1.300515e+06</td>
      <td>1.300515e+06</td>
    </tr>
    <tr>
      <th>mean</th>
      <td>7.303800e+05</td>
      <td>2.603839e+02</td>
      <td>1.154931e+02</td>
    </tr>
    <tr>
      <th>std</th>
      <td>4.276844e+05</td>
      <td>1.674231e+04</td>
      <td>1.945045e+02</td>
    </tr>
    <tr>
      <th>min</th>
      <td>4.000000e+00</td>
      <td>0.000000e+00</td>
      <td>0.000000e+00</td>
    </tr>
    <tr>
      <th>25%</th>
      <td>3.592120e+05</td>
      <td>1.400000e+01</td>
      <td>2.600000e+01</td>
    </tr>
    <tr>
      <th>50%</th>
      <td>7.258520e+05</td>
      <td>3.700000e+01</td>
      <td>6.100000e+01</td>
    </tr>
    <tr>
      <th>75%</th>
      <td>1.099280e+06</td>
      <td>9.900000e+01</td>
      <td>1.190000e+02</td>
    </tr>
    <tr>
      <th>max</th>
      <td>1.499999e+06</td>
      <td>4.187268e+06</td>
      <td>2.504000e+03</td>
    </tr>
  </tbody>
</table>
</div>




```python
df.info()
```

    <class 'pandas.core.frame.DataFrame'>
    RangeIndex: 1300515 entries, 0 to 1300514
    Data columns (total 10 columns):
     #   Column                   Non-Null Count    Dtype 
    ---  ------                   --------------    ----- 
     0   user_id                  1300515 non-null  int64 
     1   name                     1300515 non-null  object
     2   photo_url                1236637 non-null  object
     3   username                 1300515 non-null  object
     4   twitter                  1300515 non-null  object
     5   instagram                1300515 non-null  object
     6   num_followers            1300515 non-null  int64 
     7   num_following            1300515 non-null  int64 
     8   time_created             1300515 non-null  object
     9   invited_by_user_profile  1300515 non-null  object
    dtypes: int64(3), object(7)
    memory usage: 99.2+ MB
    

## View Data Problems

Our next step is to look at any data issues and convert. We also will create a few extra columns that will help use later on.

The first thing I noticed is that the "invited_by_user_profile" is tehnically a categirical variable, albeit a large one. You would want to see a unique count of the options here. The "time_created" is a datetime variable, but was recognized, so we will convert that too.


```python
df['invited_by_user_profile'] = pd.Categorical(df['invited_by_user_profile'])
df['time_created'] = pd.to_datetime(df['time_created'])
```

From looking at the data above, I thought it would be good to create a few more categorical variables. It would be nice to see the distribution of users who are in twitter vs instragram. Also, we would like to see how has a photo or not and who has been invited or not. 

We start with a social type column. I use the compare to "null" because the null value seemed to not be read equally. Basically, we are just saying, if you have dona twitter, we mark you as a instagram user otherwise a twitter.

Next, we do similar logic for if the user has a photo or has been invited.


```python
df['social_type'] = np.where(df['twitter'] == "null", 'instagram', 'twitter')
df['has_photo'] = np.where(pd.isnull(df['photo_url']), 0, 1)
df['invited'] = np.where(df['invited_by_user_profile'] == "null", 0, 1)

df.head()
```




<div>
<style scoped>
    .dataframe tbody tr th:only-of-type {
        vertical-align: middle;
    }

    .dataframe tbody tr th {
        vertical-align: top;
    }

    .dataframe thead th {
        text-align: right;
    }
</style>
<table border="1" class="dataframe">
  <thead>
    <tr style="text-align: right;">
      <th></th>
      <th>user_id</th>
      <th>name</th>
      <th>photo_url</th>
      <th>username</th>
      <th>twitter</th>
      <th>instagram</th>
      <th>num_followers</th>
      <th>num_following</th>
      <th>time_created</th>
      <th>invited_by_user_profile</th>
      <th>social_type</th>
      <th>has_photo</th>
      <th>invited</th>
    </tr>
  </thead>
  <tbody>
    <tr>
      <th>0</th>
      <td>4</td>
      <td>Rohan Seth</td>
      <td>https://clubhouseprod.s3.amazonaws.com:443/4_b...</td>
      <td>rohan</td>
      <td>rohanseth</td>
      <td>null</td>
      <td>4187268</td>
      <td>599</td>
      <td>2020-03-17 07:51:28.085566+00:00</td>
      <td>null</td>
      <td>instagram</td>
      <td>1</td>
      <td>0</td>
    </tr>
    <tr>
      <th>1</th>
      <td>5</td>
      <td>Paul Davison</td>
      <td>https://clubhouseprod.s3.amazonaws.com:443/5_e...</td>
      <td>paul</td>
      <td>pdavison</td>
      <td>null</td>
      <td>3718334</td>
      <td>1861</td>
      <td>2020-03-17 14:36:19.468976+00:00</td>
      <td>null</td>
      <td>instagram</td>
      <td>1</td>
      <td>0</td>
    </tr>
    <tr>
      <th>2</th>
      <td>8</td>
      <td>Johnny Appleseed</td>
      <td>None</td>
      <td>apple1</td>
      <td>null</td>
      <td>srt_tester_9</td>
      <td>20</td>
      <td>81</td>
      <td>2020-03-19 19:47:00.323603+00:00</td>
      <td>null</td>
      <td>twitter</td>
      <td>0</td>
      <td>0</td>
    </tr>
    <tr>
      <th>3</th>
      <td>10</td>
      <td>DK 🖍</td>
      <td>https://clubhouseprod.s3.amazonaws.com:443/10_...</td>
      <td>dk</td>
      <td>dksf</td>
      <td>null</td>
      <td>49538</td>
      <td>173</td>
      <td>2020-03-19 23:38:52.574777+00:00</td>
      <td>null</td>
      <td>instagram</td>
      <td>1</td>
      <td>0</td>
    </tr>
    <tr>
      <th>4</th>
      <td>12</td>
      <td>Jonathan Gheller</td>
      <td>https://clubhouseprod.s3.amazonaws.com:443/12_...</td>
      <td>jonathan</td>
      <td>jgheller</td>
      <td>null</td>
      <td>21250</td>
      <td>81</td>
      <td>2020-03-20 02:30:22.188084+00:00</td>
      <td>null</td>
      <td>instagram</td>
      <td>1</td>
      <td>0</td>
    </tr>
  </tbody>
</table>
</div>



## Univariate Analysis

In this section, we look at the distribution of our features or columns. In general, you can just loop through each column and plot their distribtuions. People tend to get fancy, but essentially we just want a boxplot for each numeric column and a bar plot for each cateogirical column.

There are definitely more plots to do depending on what model you will do in the future, but for generic reporting, these will do.

We first attempt to review the "invited_by_user_profile" column. I would like to null the top users who invite the most. The data was quite large, so we just create a table of the top 20. The top user is "null" which means there was no invite logged. Then we can see that user 12492 takes the lead.


```python
# Found the trick here: https://stackoverflow.com/questions/47136436/python-pandas-convert-value-counts-output-to-dataframe
data = df['invited_by_user_profile'].value_counts()[:20].rename_axis('inviting_user').reset_index(name='count')
invitedby = pd.DataFrame(data)
invitedby.columns

invitedby.head(100)
```




<div>
<style scoped>
    .dataframe tbody tr th:only-of-type {
        vertical-align: middle;
    }

    .dataframe tbody tr th {
        vertical-align: top;
    }

    .dataframe thead th {
        text-align: right;
    }
</style>
<table border="1" class="dataframe">
  <thead>
    <tr style="text-align: right;">
      <th></th>
      <th>inviting_user</th>
      <th>count</th>
    </tr>
  </thead>
  <tbody>
    <tr>
      <th>0</th>
      <td>null</td>
      <td>3214</td>
    </tr>
    <tr>
      <th>1</th>
      <td>12492</td>
      <td>593</td>
    </tr>
    <tr>
      <th>2</th>
      <td>402821</td>
      <td>356</td>
    </tr>
    <tr>
      <th>3</th>
      <td>3272</td>
      <td>306</td>
    </tr>
    <tr>
      <th>4</th>
      <td>881</td>
      <td>301</td>
    </tr>
    <tr>
      <th>5</th>
      <td>50152</td>
      <td>240</td>
    </tr>
    <tr>
      <th>6</th>
      <td>3547</td>
      <td>203</td>
    </tr>
    <tr>
      <th>7</th>
      <td>104</td>
      <td>200</td>
    </tr>
    <tr>
      <th>8</th>
      <td>14605</td>
      <td>199</td>
    </tr>
    <tr>
      <th>9</th>
      <td>7557</td>
      <td>168</td>
    </tr>
    <tr>
      <th>10</th>
      <td>27950</td>
      <td>161</td>
    </tr>
    <tr>
      <th>11</th>
      <td>2710</td>
      <td>158</td>
    </tr>
    <tr>
      <th>12</th>
      <td>846</td>
      <td>152</td>
    </tr>
    <tr>
      <th>13</th>
      <td>5864</td>
      <td>138</td>
    </tr>
    <tr>
      <th>14</th>
      <td>8406</td>
      <td>137</td>
    </tr>
    <tr>
      <th>15</th>
      <td>42125</td>
      <td>135</td>
    </tr>
    <tr>
      <th>16</th>
      <td>1940</td>
      <td>133</td>
    </tr>
    <tr>
      <th>17</th>
      <td>5954</td>
      <td>123</td>
    </tr>
    <tr>
      <th>18</th>
      <td>144372</td>
      <td>118</td>
    </tr>
    <tr>
      <th>19</th>
      <td>1720</td>
      <td>116</td>
    </tr>
  </tbody>
</table>
</div>



We now look at the distribution of our users based on social platform. We can see that twitter is the most popular by almost double.


```python
fig = sns.catplot(x="social_type", kind="count", data=df, hue=None)
fig.set_xticklabels(rotation=90)
plt.show()
```


    
![Alt Text](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/5z8js2dmuj32spnnmlus.png)
    


Photo usage is also quite high, which is to be expected. Most users have added a photo which is common on a social media platform.


```python
fig = sns.catplot(x="has_photo", kind="count", data=df, hue=None)
fig.set_xticklabels(rotation=90)
plt.show()
```


    
![Alt Text](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/yzd9fliyhx7jv08owciv.png)
    


Here, we can clearly see that the majority of users were invited. Clubhouse is a primarily invite only, which is a nice referal growth tactice. From the plot, you can't really see how many users weren't invited. 

Remember from our table before that the "null" invite was the number of users not invited. We use the `value_counts` method to display those results again.


```python
fig = sns.catplot(x="invited", kind="count", data=df, hue=None)
fig.set_xticklabels(rotation=90)
plt.show()
```


    
![Alt Text](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/j1itos15iuersb69v3d4.png)



```python
df['invited'].value_counts()
```




    1    1297301
    0       3214
    Name: invited, dtype: int64



Now, we turn our attention to the numerical features. We loop through each of these and plot boxplots of each. 


```python
columns = ['num_followers', 'num_following']

figure = plt.figure(figsize=(20, 10))
figure.add_subplot(1, len(columns), 1)

for index, col in enumerate(columns):
    if index > 0:
        figure.add_subplot(1, len(columns), index + 1)
    sns.boxplot(y=col, data=df, boxprops={'facecolor': 'None'})
    
figure.tight_layout()
plt.show()
```


    
![Alt Text](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/jwpoq8xw2r1a6shpasrm.png)
    


The plots above are hard to gather any information from. The box plots show a large amount of outliers. This might suggest that there is a small minority of users using the two features we describe. 

From the left, that would be that there is a minority of users with the majority of followers.

On right right, that would suggest that a minority of users following a lot of users.

We would probably need to look at more data to get an idea of what's going on, but the left plot does help know where our distribtion of "popular" users are.

## Bivariate or (Multi) Analysis

In this section, we would like to review two or more vairables at a time. This would give us an idea of the relationship between variables. 

However, we don't have too much data. There is really only one question I have left which is the count of new users over time. This would tell us what the grow of Clubhouse is like.


```python
df_copy = df.copy()
df_time = df_copy.set_index('time_created')
df_agg_day = df_time.resample('D').sum()
sns.lineplot(data=df_agg_day, x="time_created", y="user_id")
```




    <AxesSubplot:xlabel='time_created', ylabel='user_id'>




    
![Alt Text](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/2pcq2p1jzjzuu4et2ri2.png)
    


From the plot above, we can see that the over all user groth surged in Jan 2021, but dipped since. There also seems to be a downward trend. This is common with a viral spike. Let's look at the recent dates so that we can determine if Clubhouse has stabilized.


```python
df_reset = df_agg_day.reset_index()
recent = df_reset[df_reset['time_created'] > '02-01-2021']
sns.lineplot(data=recent, x="time_created", y="user_id")
```




    <AxesSubplot:xlabel='time_created', ylabel='user_id'>




    
![Alt Text](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/pfcbsbb7pm5hgc01wk62.png)
    


From Feb to March, the number of new users seems to be low. So either our data is missing information or Clubhouse seemst to be decreasing growth. 

Note, that they could still have a stable usage data, this data is only on created accounts. Also, they data could be wrong or inauthentic. 

## Conclusion

I hope you have enjoyed this brief exploritory data analysis of Clubhouse data. I hope if gives you some insights into how to view your own users data in the future. Please leave comments and let me know if you have any questions. I'm happy to help you answer questions on other data sets as well.