Dirty data cleaning: a case study

How to tranform dirty data into tidy data.

Source: dirty_data_cleaning01.Rmd

任务需求

数据如下：

# A tibble: 6 x 6
  zone  quarter1 quarter2 quarter3 quarter4 cat_var  
  <fct> <fct>    <fct>    <fct>    <fct>    <fct>    
1 上海  产品B    产品A    产品C    产品D    prod_name
2 上海  626      598      466      333      sale_num 
3 南京  产品A    产品C    产品D    产品B    prod_name
4 南京  888      777      666      555      sale_num 
5 杭州  产品C    产品A    产品B    产品D    prod_name
6 杭州  999      897      653      465      sale_num 

任务描述：如何根据上述数据，按需求统计各地、每个产品、每个季度的销量？

数据操作(Data Wrangling)

数据结构分析

• zone:为地市名
• quarter1-4:为列名，季度名。这四列的值分别包括了产品名和产品销量两个变量。也就是说这四列其实不是变量，而是变量值。
• cat_var:实为两行数据变量名的说明，分别为产品名和产品销量，这列也不是变量，是变量名。

此数虽然形式上是表格数据，但其列不均是变量，行也不是观测，因此这个数据集是一个“脏数据”，需要对其进行消洗后才能方便计算统计。

逐步操作

#提取地区名

df_names <- df %>%
  pull(zone)
df_names

[1] 上海 上海 南京 南京 杭州 杭州
Levels: 杭州 南京 上海

#先去掉第一列zone和第6列cat_var

df_tidy <- df %>%
  select(!c(1, 6)) 
df_tidy

# A tibble: 6 x 4
  quarter1 quarter2 quarter3 quarter4
  <fct>    <fct>    <fct>    <fct>   
1 产品B    产品A    产品C    产品D   
2 626      598      466      333     
3 产品A    产品C    产品D    产品B   
4 888      777      666      555     
5 产品C    产品A    产品B    产品D   
6 999      897      653      465     

#转置表格

df_tidy <- df_tidy %>%
  
  sjmisc::rotate_df() 
df_tidy

            V1  V2    V3  V4    V5  V6
quarter1 产品B 626 产品A 888 产品C 999
quarter2 产品A 598 产品C 777 产品A 897
quarter3 产品C 466 产品D 666 产品B 653
quarter4 产品D 333 产品B 555 产品D 465

#用原来的地区名和变量名替换新转置数据集的列名，默认为V1-6

df_tidy <- df_tidy %>%
  rename_with( ~ paste0(c('prod_name_', 'sale_num_'), df_names)) 
df_tidy

         prod_name_上海 sale_num_上海 prod_name_南京 sale_num_南京
quarter1          产品B           626          产品A           888
quarter2          产品A           598          产品C           777
quarter3          产品C           466          产品D           666
quarter4          产品D           333          产品B           555
         prod_name_杭州 sale_num_杭州
quarter1          产品C           999
quarter2          产品A           897
quarter3          产品B           653
quarter4          产品D           465

#将行名转为列：quarter1-4

df_tidy <- df_tidy %>%
  rownames_to_column() 
df_tidy

   rowname prod_name_上海 sale_num_上海 prod_name_南京 sale_num_南京
1 quarter1          产品B           626          产品A           888
2 quarter2          产品A           598          产品C           777
3 quarter3          产品C           466          产品D           666
4 quarter4          产品D           333          产品B           555
  prod_name_杭州 sale_num_杭州
1          产品C           999
2          产品A           897
3          产品B           653
4          产品D           465

#把地区名转变变量值

df_tidy <- df_tidy %>%
  pivot_longer(
    2:last_col(),
    names_to = c(".value", "zone"),
    names_pattern = "(.*_.*)_(.*)")
df_tidy

# A tibble: 12 x 4
   rowname  zone  prod_name sale_num
   <chr>    <chr> <fct>     <fct>   
 1 quarter1 上海  产品B     626     
 2 quarter1 南京  产品A     888     
 3 quarter1 杭州  产品C     999     
 4 quarter2 上海  产品A     598     
 5 quarter2 南京  产品C     777     
 6 quarter2 杭州  产品A     897     
 7 quarter3 上海  产品C     466     
 8 quarter3 南京  产品D     666     
 9 quarter3 杭州  产品B     653     
10 quarter4 上海  产品D     333     
11 quarter4 南京  产品B     555     
12 quarter4 杭州  产品D     465     

#把字符型转换为数字型

df_tidy <- df_tidy %>% 
  mutate(sale_num = as.character(sale_num) %>% parse_integer)
df_tidy

# A tibble: 12 x 4
   rowname  zone  prod_name sale_num
   <chr>    <chr> <fct>        <int>
 1 quarter1 上海  产品B          626
 2 quarter1 南京  产品A          888
 3 quarter1 杭州  产品C          999
 4 quarter2 上海  产品A          598
 5 quarter2 南京  产品C          777
 6 quarter2 杭州  产品A          897
 7 quarter3 上海  产品C          466
 8 quarter3 南京  产品D          666
 9 quarter3 杭州  产品B          653
10 quarter4 上海  产品D          333
11 quarter4 南京  产品B          555
12 quarter4 杭州  产品D          465

这时数据集就变整洁数据了，其中：

• rowname：为季度变量，变量值为季度1-4
• zone: 为原来的zone变量
• prod_name: 为产品名
• sale_num: 为产品销量

数据分析

计算分地区各产品的销量

df_tidy_l <- df_tidy %>% 
  group_by(zone, prod_name) %>% 
  summarise(sale_num = sum(sale_num))
df_tidy_l

# A tibble: 12 x 3
# Groups:   zone [3]
   zone  prod_name sale_num
   <chr> <fct>        <int>
 1 杭州  产品A          897
 2 杭州  产品B          653
 3 杭州  产品C          999
 4 杭州  产品D          465
 5 南京  产品A          888
 6 南京  产品B          555
 7 南京  产品C          777
 8 南京  产品D          666
 9 上海  产品A          598
10 上海  产品B          626
11 上海  产品C          466
12 上海  产品D          333

数据可视化

df_tidy_l %>% 
  ggplot(aes(zone, 
             sale_num, 
             fill = prod_name)) +
  geom_col(position = "dodge")+
  theme_classic()

或者可以把数据呈现为宽数据

df_tidy_l %>% 
  pivot_wider(
    names_from = prod_name,
    values_from = sale_num)

# A tibble: 3 x 5
# Groups:   zone [3]
  zone  产品A 产品B 产品C 产品D
  <chr> <int> <int> <int> <int>
1 杭州    897   653   999   465
2 南京    888   555   777   666
3 上海    598   626   466   333

计算各季度分地区各产品的销量

df_tidy_q <- df_tidy %>% 
  group_by(rowname, zone, prod_name) %>% 
  summarise(sale_num = sum(sale_num))
df_tidy_q

# A tibble: 12 x 4
# Groups:   rowname, zone [12]
   rowname  zone  prod_name sale_num
   <chr>    <chr> <fct>        <int>
 1 quarter1 杭州  产品C          999
 2 quarter1 南京  产品A          888
 3 quarter1 上海  产品B          626
 4 quarter2 杭州  产品A          897
 5 quarter2 南京  产品C          777
 6 quarter2 上海  产品A          598
 7 quarter3 杭州  产品B          653
 8 quarter3 南京  产品D          666
 9 quarter3 上海  产品C          466
10 quarter4 杭州  产品D          465
11 quarter4 南京  产品B          555
12 quarter4 上海  产品D          333

数据可视化

df_tidy_q %>% 
  ggplot(aes(zone, sale_num, fill = prod_name)) +
  geom_col() +
  coord_flip() +
  facet_grid(rowname~. ) 

或者可以把数据以宽数据呈现

df_tidy_q %>%
  pivot_wider(
    names_from = prod_name,
    values_from = sale_num,
    names_sort = TRUE
  ) %>% 
  split(.$rowname)

$quarter1
# A tibble: 3 x 6
# Groups:   rowname, zone [3]
  rowname  zone  产品A 产品B 产品C 产品D
  <chr>    <chr> <int> <int> <int> <int>
1 quarter1 杭州     NA    NA   999    NA
2 quarter1 南京    888    NA    NA    NA
3 quarter1 上海     NA   626    NA    NA

$quarter2
# A tibble: 3 x 6
# Groups:   rowname, zone [3]
  rowname  zone  产品A 产品B 产品C 产品D
  <chr>    <chr> <int> <int> <int> <int>
1 quarter2 杭州    897    NA    NA    NA
2 quarter2 南京     NA    NA   777    NA
3 quarter2 上海    598    NA    NA    NA

$quarter3
# A tibble: 3 x 6
# Groups:   rowname, zone [3]
  rowname  zone  产品A 产品B 产品C 产品D
  <chr>    <chr> <int> <int> <int> <int>
1 quarter3 杭州     NA   653    NA    NA
2 quarter3 南京     NA    NA    NA   666
3 quarter3 上海     NA    NA   466    NA

$quarter4
# A tibble: 3 x 6
# Groups:   rowname, zone [3]
  rowname  zone  产品A 产品B 产品C 产品D
  <chr>    <chr> <int> <int> <int> <int>
1 quarter4 杭州     NA    NA    NA   465
2 quarter4 南京     NA   555    NA    NA
3 quarter4 上海     NA    NA    NA   333

简洁算法

df1 = df %>% 
  select(-cat_var) %>% 
  group_nest(zone) %>% 
  mutate(data = map(data, sjmisc::rotate_df)) %>% 
  unnest(data) 
df1

# A tibble: 12 x 3
   zone  V1    V2   
   <fct> <fct> <fct>
 1 杭州  产品C 999  
 2 杭州  产品A 897  
 3 杭州  产品B 653  
 4 杭州  产品D 465  
 5 南京  产品A 888  
 6 南京  产品C 777  
 7 南京  产品D 666  
 8 南京  产品B 555  
 9 上海  产品B 626  
10 上海  产品A 598  
11 上海  产品C 466  
12 上海  产品D 333  

df1 %>% 
  pivot_wider(names_from = V1, values_from = V2)

# A tibble: 3 x 5
  zone  产品C 产品A 产品B 产品D
  <fct> <fct> <fct> <fct> <fct>
1 杭州  999   897   653   465  
2 南京  777   888   555   666  
3 上海  466   598   626   333  

df1 %>% 
  group_split(V1) %>% 
  reduce(bind_rows)

# A tibble: 12 x 3
   zone  V1    V2   
   <fct> <fct> <fct>
 1 杭州  产品A 897  
 2 南京  产品A 888  
 3 上海  产品A 598  
 4 杭州  产品B 653  
 5 南京  产品B 555  
 6 上海  产品B 626  
 7 杭州  产品C 999  
 8 南京  产品C 777  
 9 上海  产品C 466  
10 杭州  产品D 465  
11 南京  产品D 666  
12 上海  产品D 333