Commit: eb67ef491c60a931acbff68a1687dc108f80d6ee - myprojects-hg-reborn (hg)

Commit MetaInfo

• Revisión: eb67ef491c60a931acbff68a1687dc108f80d6ee (tree)
• Tiempo: 2025-01-16 04:19:12
• Autor: Lorenzo Isella <lorenzo.isella@gmai...>
• Commiter: Lorenzo Isella

Log Message

A script to process the etsy sales data.

Cambiar Resumen

• add: R-codes/accountancy_etsy.R

Diferencia incremental

diff -r fc4c2d28eb76 -r eb67ef491c60 R-codes/accountancy_etsy.R
--- /dev/null	Thu Jan 01 00:00:00 1970 +0000
+++ b/R-codes/accountancy_etsy.R	Wed Jan 15 20:19:12 2025 +0100
@@ -0,0 +1,66 @@
+rm(list=ls())
+
+library(tidyverse)
+library(janitor)
+library(openxlsx)
+
+
+source("/home/lorenzo/myprojects-hg/R-codes/stat_lib.R")
+
+
+# Define the function
+retain_pattern <- function(vec, pattern) {
+  sapply(vec, function(entry) {
+    match <- str_extract(entry, pattern)  # Extract the pattern
+    if (!is.na(match)) {
+      return(match)  # Return the matched pattern if found
+    } else {
+      return(entry)  # Return the original entry if no match
+    }
+  })
+}
+
+df_ini <-  read_csv("../input/etsy_statement_2024_10.csv") |>
+    clean_names()
+
+save_excel(df_ini, "../output/clean_october.xlsx")
+
+
+df <- df_ini |>
+    mutate(across(c(amount, fees_taxes, net), ~convert_euro_to_numeric(.x))) |>
+    select(-c(tax_details, status, availability_date)) |>
+    na_num_to_pattern(0)
+
+
+df_long <- df |>
+    select(-c(type, currency)) |>
+    pivot_longer(cols=c(amount, fees_taxes, net), names_to="transaction_type",
+                 values_to="value_eur") |>
+    filter(value_eur!=0) |>
+    arrange(title) |>
+    mutate(title2=title) |>
+    mutate(title2=retain_pattern(title2, "Payment for Order")) |>
+    mutate(title2=retain_pattern(title2, "Partial refund for Order")) |>
+    mutate(title2=retain_pattern(title2, "Listing fee")) |>
+    mutate(title2=retain_pattern(title2, "Transaction fee")) |>
+    rename("transaction_type_new"="title2")
+
+df_tran <- df_long |>
+    summarise(total=sum(value_eur), .by=transaction_type)
+
+
+df_tran_new <- df_long |>
+    summarise(total=sum(value_eur), .by=c(transaction_type, transaction_type_new))
+
+df_tran_new_wide <- df_tran_new |>
+    pivot_wider(names_from=transaction_type, values_from=total) |>
+    add_total()
+    
+
+save_excel(df_long, "../output/data_masha.xlsx", "raw_data")
+save_excel(df_tran, "../output/data_masha.xlsx", "summary_data")
+save_excel(df_tran_new_wide, "../output/data_masha.xlsx", "breakdown")
+
+
+
+print("So far so good")