Skip to main content

Clean: social platforms

The social platforms CSV from scrape_social.qmd stores monthly active users as text like "3.070 billion[1][2][3]" or "930 million". The code below pulls out the number and the unit separately and converts everything to millions. It also collapses the long tail of owning companies down to the five with more than one platform in the table (Meta Platforms, Tencent, ByteDance, Microsoft, and Alphabet) and labels everyone else "Other".

%%{init: {'theme': 'base', 'themeVariables': { 'fontFamily': 'monospace', "fontSize":"16px", "darkMode":false}}}%%

flowchart TB

    raw[("<code>data/social_platforms.csv</code>")] -->|"i. Extract"| ext["Number and unit from<br/><code>monthly_active_users</code>"]
    ext -->|"ii. Convert"| mau["<code>mau_millions</code>"]
    mau -->|"iii. Group"| grp["<code>company_group</code><br/>top five or Other"]
    grp -->|"iv. Filter"| filt["Drop helper columns<br/>and missing counts"]
    filt -->|"v. Save"| clean[("<code>data/social_platforms_clean.csv</code>")]

    style raw fill:#FFFFFF,color:#000000,stroke:#333,stroke-width:1px
    style clean fill:#FFFFFF,color:#000000,stroke:#333,stroke-width:1px
    style ext fill:#4CBB9D,color:#FFFFFF,rx:5,ry:5
    style mau fill:#4CBB9D,color:#FFFFFF,rx:5,ry:5
    style grp fill:#4CBB9D,color:#FFFFFF,rx:5,ry:5
    style filt fill:#4CBB9D,color:#FFFFFF,rx:5,ry:5

library(dplyr)
library(readr)
library(stringr)

social_clean <- read_csv("data/social_platforms.csv") |>
  mutate(
    mau_value = as.numeric(str_extract(monthly_active_users, "[\\d.]+")),
    mau_unit = str_extract(monthly_active_users, "billion|million"),
    mau_millions = case_when(
      mau_unit == "billion" ~ mau_value * 1000,
      mau_unit == "million" ~ mau_value,
      TRUE ~ NA_real_
    ),
    company_group = if_else(
      company %in% c("Meta Platforms", "Tencent", "ByteDance", "Microsoft", "Alphabet Inc."),
      company, "Other"
    )
  ) |>
  select(-mau_value, -mau_unit) |>
  filter(!is.na(mau_millions))
write_csv(social_clean, "data/social_platforms_clean.csv")
social_clean
## # A tibble: 36 × 8
##    name      company        country  launched monthly_active_users other_metrics
##    <chr>     <chr>          <chr>       <dbl> <chr>                <chr>        
##  1 Facebook  Meta Platforms United …     2004 3.070 billion[1][2]… <NA>         
##  2 YouTube   Alphabet Inc.  United …     2005 2.504 billion[4]     3 billion us…
##  3 WhatsApp  Meta Platforms United …     2009 2 billion[4]         Had 1 billio…
##  4 Instagram Meta Platforms United …     2010 3 billion[6]         <NA>         
##  5 TikTok    ByteDance      China        2016 1.582 billion[4]     <NA>         
##  6 WeChat    Tencent        China        2011 1.343 billion[4]     <NA>         
##  7 Messenger Meta Platforms United …     2011 1.01 billion[4]      <NA>         
##  8 Telegram  Telegram       UAE[7]       2013 1 billion[8]         <NA>         
##  9 LinkedIn  Microsoft      United …     2003 930 million          700 million …
## 10 Snapchat  Snap Inc.      United …     2011 900 million[10]      460 million …
## # ℹ 26 more rows
## # ℹ 2 more variables: mau_millions <dbl>, company_group <chr>
import pandas as pd

social = pd.read_csv("data/social_platforms.csv")
mau_value = pd.to_numeric(social["monthly_active_users"].str.extract(r"([\d.]+)")[0], errors="coerce")
mau_unit = social["monthly_active_users"].str.extract(r"(billion|million)")[0]
social["mau_millions"] = mau_value * mau_unit.map({"billion": 1000, "million": 1})
top_companies = ["Meta Platforms", "Tencent", "ByteDance", "Microsoft", "Alphabet Inc."]
social["company_group"] = social["company"].where(social["company"].isin(top_companies), "Other")

social_clean = social.dropna(subset=["mau_millions"])
social_clean.to_csv("data/social_platforms_clean.csv", index=False)
social_clean.head()
        name         company  ... mau_millions   company_group
0   Facebook  Meta Platforms  ...       3070.0  Meta Platforms
1    YouTube   Alphabet Inc.  ...       2504.0   Alphabet Inc.
2   WhatsApp  Meta Platforms  ...       2000.0  Meta Platforms
3  Instagram  Meta Platforms  ...       3000.0  Meta Platforms
4     TikTok       ByteDance  ...       1582.0       ByteDance

[5 rows x 8 columns]
NoteEither works

case_when() and a map() from unit to multiplier convert the units equally well, and if_else() and where() both collapse the smaller companies into “Other”.

See viz_social.qmd for the chart built on this.