-
Notifications
You must be signed in to change notification settings - Fork 0
/
Copy pathCode_SK_20140222_v0.0.R
148 lines (107 loc) · 5.48 KB
/
Code_SK_20140222_v0.0.R
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
################################################
#1 Understanding the Data
################################################
#setting the working directory
setwd("/Users/SKarkhanis/Desktop/MMA_UGent/2013_2014/06_Marketing_Models_&_Engineering/ModelingAssignment/data/")
################################################
#Importing the data
################################################
#subscriptions
subscriptions <- read.table("subscriptions.txt",header=TRUE, sep=";", colClasses=c("character","character","character","character","character","character","integer","integer","character","factor","factor","character","character","numeric","numeric","numeric","numeric","numeric","numeric","numeric","numeric"))
str(subscriptions);
#customers
customers <-read.table("customers.txt", header=TRUE, sep=";",colClasses=c("character","character","character","character","character","character"))
#Delivery
delivery <-read.table("delivery.txt", header=TRUE, sep=";",colClasses=c("character","character","character","character","character","character"),na.strings = "NA" )
#Formula
formula <-read.table("formula.txt", header=TRUE, sep=";",colClasses=c("character","character","character","character" ),na.strings = "NA" )
#Credit
credit <-read.table("credit.txt", header=TRUE, sep=";",colClasses=c("character","character","character","character","numeric","numeric"),na.strings = "NA" )
#Complaints
complaints <-read.table("complaints.txt", header=TRUE, sep=";",colClasses=c("character","character","character","character","numeric","numeric","numeric" ),na.strings = "NA" )
################################################
#basic investigations across diff variables
################################################
#Checking summary stats for subscription start date
#used to get a feel of the time period for which data is available
#might be useful in time-window
summary(subscriptions$StartDate)
#Min. 1st Qu. Median Mean 3rd Qu. Max.
#"2006-01-02" "2008-01-01" "2009-03-04" "2009-01-24" "2010-03-18" "2012-01-28"
#setting the format for date
Date_format <- "%d/%m/%Y"
#start of indep period
start_IP <- as.Date("02/01/2006", Date_format)
#end of indep period
end_IP <- as.Date("01/02/2010", Date_format)
#start of dep period
start_DP <- as.Date("03/02/2010", Date_format)
#end of dep period
end_DP<- as.Date("03/02/2011", Date_format)
table(subscriptions$ProductID)
# 1 2 3 4 5 6 7 8
#789 1459 793 971 494 1547 977 8429
table(customers$Gender)
#F M
#508 1749
table(subscriptions$Pattern)
#10 100 110 10000 10010 100010 1000000 1000010 1010000 1010010
#273 83 15 18 3 6 20 15 9 9
#1100010 1111110
#2 15006
table(subscriptions$PaymentType)
#BT DD
#13200 2259
################################################
#Missings per variable in Subscriptions Table
################################################
colSums(is.na(subscriptions)) #result below
#~2% of the data in the following columns is missing
#GrossFormulaPrice | NetFormulaPrice | NetNewspaperPrice |
#ProductDiscount | FormulaDiscount | TotalDiscount
#TotalPrice | TotalCredit
colSums(is.na(customers))#result below
#~4% of the data in the Gender column is missing
colSums(is.na(delivery))#result below - NOT WORKING CORRECTLY FOR DELIVERY TABLE
#~4% of the data in the column is missing
colSums(is.na(formula))#result below
#no missing data
colSums(is.na(credit))#result below
#~27% of missing data in column NbrNewspapers
colSums(is.na(complaints))#result below
#~26% of missing data in column SolutionType
#~60% of missing data in column FeedbackType
#<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
#Steps for creating Base Table
#<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
#For this we need to,
# 01 select cust active @ end of indep period
# 02 then only focus on ALL ASPECTS(R,F,M) of these customers
# 03 I created some other Indep var based on Payment Type, Gender, Complaints, Delivery, Credit tables
# 04 NOT TO FORGET, CREATING DEPENDENT var (we wait for instruction fom professor)
# 05 once, 01 ,02, 03, 04 are complete, we can merge all to create BASETABLE
################################################
#Tranforming the Subscriptions table
################################################
#We can create RFM variables based on Subscriptions table & dummy variables for other catergorical variables
################################################
#Tranforming the Complaints table
################################################
#Finding Nr of Complaints per customer
nr_complaints_per_cust <- data.frame(table(complaints$CustomerID))
colnames(nr_complaints_per_cust)[1] <- "CustomerID"
colnames(nr_complaints_per_cust)[2] <- "Nr_Complaints"
#Finding type of Complaints Type per customer (needed???)
################################################
#Tranforming the Credit table
################################################
#Finding Total credit amount per subscription, Total # news papers credited,
tmp_credit <- data.frame(credit$SubscriptionID, credit$Amount,credit$NbrNewspapers)
#renaming columns in tmp_credit data frame
colnames(tmp_credit)[1] <- "SubscriptionID"
colnames(tmp_credit)[2] <- "Amount"
colnames(tmp_credit)[3] <- "NbrNewspapers"
#Summing the amount & # news papers
total_amt_newspapers_per_subscriptionn <- data.frame (aggregate(tmp_credit[,names(tmp_credit) != 'SubscriptionID'], by=list(ID=tmp_credit$SubscriptionID),sum))
#removing tmp_credit to save space
rm(tmp_credit)