数値データの基本統計量

# summary of numeric data
df %>% 
  select(where(is.numeric)) %>% 
  summary()

##      row_id       postal_code        sales              quantity     
##  Min.   :    1   Min.   : 1040   Min.   :    0.444   Min.   : 1.000  
##  1st Qu.:12823   1st Qu.:23223   1st Qu.:   30.759   1st Qu.: 2.000  
##  Median :25646   Median :56431   Median :   85.053   Median : 3.000  
##  Mean   :25646   Mean   :55190   Mean   :  246.491   Mean   : 3.477  
##  3rd Qu.:38468   3rd Qu.:90008   3rd Qu.:  251.053   3rd Qu.: 5.000  
##  Max.   :51290   Max.   :99301   Max.   :22638.480   Max.   :14.000  
##                  NA's   :41296                                       
##     discount          profit         shipping_cost      order_year  
##  Min.   :0.0000   Min.   :-6599.98   Min.   :  0.00   Min.   :2011  
##  1st Qu.:0.0000   1st Qu.:    0.00   1st Qu.:  2.61   1st Qu.:2012  
##  Median :0.0000   Median :    9.24   Median :  7.79   Median :2013  
##  Mean   :0.1429   Mean   :   28.61   Mean   : 26.38   Mean   :2013  
##  3rd Qu.:0.2000   3rd Qu.:   36.81   3rd Qu.: 24.45   3rd Qu.:2014  
##  Max.   :0.8500   Max.   : 8399.98   Max.   :933.57   Max.   :2014  
##                                                                     
##   order_month       ship_year      ship_month        split_tf    
##  Min.   : 1.000   Min.   :2011   Min.   : 1.000   Min.   :1.000  
##  1st Qu.: 5.000   1st Qu.:2012   1st Qu.: 5.000   1st Qu.:2.000  
##  Median : 8.000   Median :2013   Median : 8.000   Median :3.000  
##  Mean   : 7.523   Mean   :2013   Mean   : 7.464   Mean   :3.009  
##  3rd Qu.:10.000   3rd Qu.:2014   3rd Qu.:10.000   3rd Qu.:4.000  
##  Max.   :12.000   Max.   :2015   Max.   :12.000   Max.   :5.000  
##                                                                  
##     gap_date    
##  Min.   :0.000  
##  1st Qu.:3.000  
##  Median :4.000  
##  Mean   :3.969  
##  3rd Qu.:5.000  
##  Max.   :7.000  
## 

日付データのレンジ

# date range
df %>% 
  select(ends_with("date")) %>% 
  lapply(range)

## $order_date
## [1] "2011-01-01" "2014-12-31"
## 
## $ship_date
## [1] "2011-01-03" "2015-01-07"
## 
## $gap_date
## [1] 0 7

カテゴリカルデータの数

# check the number of unique categorical variables
df_tidy %>% 
  select(where(is.character)) %>% 
  lapply(unique) %>%
  lapply(length)

## $order_id
## [1] 25035
## 
## $order_date
## [1] 1430
## 
## $ship_date
## [1] 1464
## 
## $ship_mode
## [1] 4
## 
## $customer_id
## [1] 1590
## 
## $customer_name
## [1] 795
## 
## $segment
## [1] 3
## 
## $city
## [1] 3636
## 
## $state
## [1] 1094
## 
## $country
## [1] 147
## 
## $market
## [1] 7
## 
## $region
## [1] 13
## 
## $product_id
## [1] 10292
## 
## $category
## [1] 3
## 
## $sub_category
## [1] 17
## 
## $product_name
## [1] 3788
## 
## $order_priority
## [1] 4

月々のオーダーの数と出荷数

毎年11月、12月にかけてオーダー数が増えている。

g1 <- 
  df %>%
  group_by(order_year, order_month) %>% 
  ggplot() + 
  aes(order_date) +
  geom_bar() 

g2 <- 
  g1 <- 
  df %>%
  group_by(order_year, order_month) %>% 
  ggplot() + 
  aes(order_date) +
  geom_bar()

g1/g2

df %>% 
  group_by(order_year, order_date) %>% 
  summarise(total = n()) %>% filter(order_year == 2011, total > 50) 

## # A tibble: 31 × 3
## # Groups:   order_year [1]
##    order_year order_date total
##         <dbl> <date>     <int>
##  1       2011 2011-03-01    53
##  2       2011 2011-06-07    60
##  3       2011 2011-06-22    55
##  4       2011 2011-08-25    53
##  5       2011 2011-09-02    60
##  6       2011 2011-09-08    76
##  7       2011 2011-09-14    63
##  8       2011 2011-09-23    54
##  9       2011 2011-09-26    56
## 10       2011 2011-09-27    57
## # … with 21 more rows

オーダーを受けてから出荷するまでの日付に国ごとのばらつきはあるのか？

There is no significant difference across countires.

# there must be some variations in the gap between the order date and the ship date.
# Q. how is the gap between the order date and the ship date?
df %>% 
  ggplot(aes(gap_date, region)) +
  geom_boxplot()

出荷費用に関しては、どのような国ごとのばらつきはあるのか？

# there must be some variations in the shipping cost across countries.
# Q. how much is that? 
g_corporate <- df %>% 
  filter(segment == "Corporate") %>% 
  ggplot(aes(shipping_cost, market)) +
  geom_boxplot() +
  labs(title = "Corporate")

g_consumer <- df %>% 
  filter(segment == "Consumer") %>% 
  ggplot(aes(shipping_cost, market)) +
  geom_boxplot() + 
  labs(title = "Consumer")

g_home_office <- df %>% 
  filter(segment == "Home Office") %>% 
  ggplot(aes(shipping_cost, market)) +
  geom_boxplot() +
  labs(title = "Home Office")

g_corporate / g_consumer / g_home_office

年毎の利益と地域

# basic info --------------------------------------------------------------

# the ration of regions in profit on the annual basis
df %>% 
  group_by(order_year, order_month, region) %>%
  summarise(sum_profit = sum(profit)) %>% 
  ggplot(aes(order_year, sum_profit, fill = region)) + 
  geom_col()

年ごとの利益

# total profit by year
profit_by_year <- 
  df %>% group_by(order_year) %>% 
  summarise(annual_profit = sum(profit))

# profit ratio by year
df %>% 
  group_by(order_year, order_month, market) %>%
  summarise(sum_profit = sum(profit)) %>% 
  mutate(profit_ratio_by_year = sum_profit/filter(profit_by_year, order_year == order_year)[[2]]) %>% 
  arrange(order_year, desc(sum_profit)) 

## # A tibble: 335 × 5
## # Groups:   order_year, order_month [48]
##    order_year order_month market sum_profit profit_ratio_by_year
##         <dbl>       <dbl> <chr>       <dbl>                <dbl>
##  1       2011           9 EU         13805.               0.0555
##  2       2011          12 APAC       13516.               0.0440
##  3       2011          10 APAC       13496.               0.0439
##  4       2011          12 EU         11540.               0.0464
##  5       2011          11 US          9292.               0.0228
##  6       2011           6 APAC        9216.               0.0300
##  7       2011          12 US          8984.               0.0221
##  8       2011          11 APAC        8951.               0.0291
##  9       2011           9 US          8328.               0.0205
## 10       2011           6 EU          7799.               0.0313
## # … with 325 more rows

どのマーケットが一番儲かっているのか？

Especially the markets in APAc and EU are expanding.

# Q. the most profitable market, product, category, sub_category, 
df %>% 
  group_by(order_year, market) %>% 
  summarise(profit = sum(profit)) %>% 
  arrange(order_year,desc(profit)) %>% 
  top_n(5, profit)

## # A tibble: 20 × 3
## # Groups:   order_year [4]
##    order_year market  profit
##         <dbl> <chr>    <dbl>
##  1       2011 APAC    83032.
##  2       2011 EU      61626.
##  3       2011 US      49544.
##  4       2011 LATAM   36708.
##  5       2011 Africa  10944.
##  6       2012 APAC    89411.
##  7       2012 EU      83985.
##  8       2012 US      61619.
##  9       2012 LATAM   50185.
## 10       2012 Africa  11909.
## 11       2013 APAC   123103.
## 12       2013 EU      98275.
## 13       2013 US      81727.
## 14       2013 LATAM   61415.
## 15       2013 Africa  26687.
## 16       2014 APAC   140454.
## 17       2014 EU     128944.
## 18       2014 US      93508.
## 19       2014 LATAM   73335.
## 20       2014 Africa  39331.

どの国が特に利益を上げているのか？

The presence of China and India is growing.

# Q. which country?
df %>% 
  filter(market %in% c("APAC", "EU")) %>% 
  group_by(order_year, country) %>% 
  summarise(market_profit = sum(profit)) %>% 
  arrange(order_year, desc(market_profit)) %>% 
  top_n(3, market_profit)

## # A tibble: 12 × 3
## # Groups:   order_year [4]
##    order_year country        market_profit
##         <dbl> <chr>                  <dbl>
##  1       2011 China                 33181.
##  2       2011 United Kingdom        20080.
##  3       2011 India                 19929.
##  4       2012 India                 27439.
##  5       2012 United Kingdom        27366.
##  6       2012 China                 26234.
##  7       2013 China                 44474.
##  8       2013 India                 32897.
##  9       2013 France                32416.
## 10       2014 India                 48808.
## 11       2014 China                 46794.
## 12       2014 United Kingdom        36756.

インドと中国では特にどのsubcategoryが売れているのか？

technology, furniture, office suplliesで変らない。

# which category is sold in those region? 
df %>% 
  filter(country %in% c("India", "China")) %>% 
  group_by(order_year, sub_category) %>% 
  summarise(profit_by_subcategory = sum(profit)) %>% 
  arrange(order_year, desc(profit_by_subcategory)) %>% 
  top_n(10, desc(profit_by_subcategory)) 

## # A tibble: 40 × 3
## # Groups:   order_year [4]
##    order_year sub_category profit_by_subcategory
##         <dbl> <chr>                        <dbl>
##  1       2011 Machines                     2599.
##  2       2011 Furnishings                  2008.
##  3       2011 Envelopes                    1187.
##  4       2011 Supplies                     1073.
##  5       2011 Binders                      1022.
##  6       2011 Art                           819.
##  7       2011 Paper                         763.
##  8       2011 Fasteners                     478.
##  9       2011 Labels                        224.
## 10       2011 Tables                      -1465.
## # … with 30 more rows

大手取引先はいるのか？

いるが、それぞれがバラバラ。もっとフォローアップを増やし、one-timeではなく、継続的な大手取引先を作るべき。

df %>% 
  group_by(order_year, customer_id) %>% 
  summarise(profit_by_customer = sum(profit)) %>% 
  arrange(order_year, desc(profit_by_customer)) %>% 
  top_n(10, profit_by_customer) 

## # A tibble: 40 × 3
## # Groups:   order_year [4]
##    order_year customer_id profit_by_customer
##         <dbl> <chr>                    <dbl>
##  1       2011 SC-20095                 5716.
##  2       2011 CA-11965                 3121.
##  3       2011 NM-18445                 2950.
##  4       2011 GT-14710                 2909.
##  5       2011 ON-18715                 2689.
##  6       2011 ER-13855                 2618.
##  7       2011 TB-21400                 2549.
##  8       2011 KN-16390                 2453.
##  9       2011 HL-15040                 2405.
## 10       2011 RB-19330                 2238.
## # … with 30 more rows

大手取引先の属性情報は？

# top10 customers customer's id for every year.
major_customers <- 
  df %>% 
  group_by(order_year, customer_id) %>% 
  summarise(profit_by_customer = sum(profit)) %>% 
  arrange(order_year, desc(profit_by_customer)) %>% 
  top_n(10, profit_by_customer) %>%
  ungroup() %>%  
  select(customer_id) %>% 
  unlist() %>% 
  unname()


df %>% 
  filter(customer_id %in% major_customers) %>%
  group_by(order_year, customer_id, sub_category) %>% 
  summarise(profit_by_subcategory = sum(profit)) %>% 
  arrange(order_year, desc(profit_by_subcategory)) %>% 
  top_n(1, profit_by_subcategory)

## # A tibble: 152 × 4
## # Groups:   order_year, customer_id [152]
##    order_year customer_id sub_category profit_by_subcategory
##         <dbl> <chr>       <chr>                        <dbl>
##  1       2011 SC-20095    Binders                      5480.
##  2       2011 CA-11965    Phones                       2939.
##  3       2011 ER-13855    Appliances                   2476.
##  4       2011 TB-21400    Machines                     2240.
##  5       2011 ON-18715    Chairs                       2125.
##  6       2011 NM-18445    Machines                     1996.
##  7       2011 HL-15040    Phones                       1930.
##  8       2011 DR-12940    Appliances                   1644.
##  9       2011 KN-16390    Tables                       1528.
## 10       2011 GT-14710    Chairs                       1474.
## # … with 142 more rows