-
Notifications
You must be signed in to change notification settings - Fork 82
/
wrangle.py
345 lines (235 loc) · 13.5 KB
/
wrangle.py
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
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
# Wrangle Data With pandas
#======================================================================================================
# Install numpy and pandas
pip install numpy
pip install pandas
#======================================================================================================
# Import numpy and pandas
import numpy as np
import pandas as pd
#======================================================================================================
# Build DataFrame from scratch
transactions = pd.DataFrame({
'TransactionID': np.arange(10)+1,
'TransactionDate': pd.to_datetime(['2010-08-21', '2011-05-26', '2011-06-16', '2012-08-26', '2013-06-06',
'2013-12-23', '2013-12-30', '2014-04-24', '2015-04-24', '2016-05-08']).date,
'UserID': [7, 3, 3, 1, 2, 2, 3, np.nan, 7, 3],
'ProductID': [2, 4, 3, 2, 4, 5, 4, 2, 4, 4],
'Quantity': [1, 1, 1, 3, 1, 6, 1, 3, 3, 4]
})
#======================================================================================================
# Read data from a CSV file
# Load transactions
transactions = pd.read_csv('https://raw.githubusercontent.com/ben519/DataWrangling/master/Data/transactions.csv')
#======================================================================================================
# Meta info
# Full summary
transactions.info()
# How many rows?
transactions.shape[0]
# How many columns?
transactions.shape[1]
# Get the row names
transactions.index.values
# Get the column names
transactions.columns.values
# Change the name of column "Quantity" to "Quant"
transactions.rename(columns={'Quantity': 'Quant'}) # use argument inplace=TRUE to keep the changes
# Change the name of columns ProductID and UserID to PID and UID respectively
transactions.rename(columns={'ProductID': 'PID', 'UserID': 'UID'}) # use argument inplace=TRUE to keep the changes
#======================================================================================================
# Ordering the rows of a DataFrame
# Order the rows of transactions by TransactionID descending
transactions.sort_values('TransactionID', ascending=False)
# Order the rows of transactions by Quantity ascending, TransactionDate descending
transactions.sort_values(['Quantity', 'TransactionDate'], ascending=[True, False])
#======================================================================================================
# Ordering the columns of a DataFrame
# Set the column order of transactions as ProductID, Quantity, TransactionDate, TransactionID, UserID
transactions[['ProductID', 'Quantity', 'TransactionDate', 'TransactionID', 'UserID']]
# Make UserID the first column of transactions
transactions[pd.unique(['UserID'] + transactions.columns.values.tolist()).tolist()]
#======================================================================================================
# Extracting arrays from a DataFrame
# Get the 2nd column
transactions[[1]].values[:, 0]
# Get the ProductID array
transactions.ProductID.values
# Get the ProductID array using a variable
col = "ProductID"
transactions[[col]].values[:, 0]
#======================================================================================================
# Row subsetting
# Subset rows 1, 3, and 6
transactions.iloc[[0,2,5]]
# Subset rows exlcuding 1, 3, and 6
transactions.drop([0,2,5], axis=0)
# Subset the first 3 rows
transactions[:3]
transactions.head(3)
# Subset rows excluding the first 3 rows
transactions[3:]
transactions.tail(-3)
# Subset the last 2 rows
transactions.tail(2)
# Subset rows excluding the last 2 rows
transactions.tail(-2)
# Subset rows where Quantity > 1
transactions[transactions.Quantity > 1]
# Subset rows where UserID = 2
transactions[transactions.UserID == 2]
# Subset rows where Quantity > 1 and UserID = 2
transactions[(transactions.Quantity > 1) & (transactions.UserID == 2)]
# Subset rows where Quantity + UserID is > 3
transactions[transactions.Quantity + transactions.UserID > 3]
# Subset rows where an external array, foo, is True
foo = np.array([True, False, True, False, True, False, True, False, True, False])
transactions[foo]
# Subset rows where an external array, bar, is positive
bar = np.array([1, -3, 2, 2, 0, -4, -4, 0, 0, 2])
transactions[bar > 0]
# Subset rows where foo is TRUE or bar is negative
transactions[foo | (bar < 0)]
# Subset the rows where foo is not TRUE and bar is not negative
transactions[~foo & (bar >= 0)]
#======================================================================================================
# Column subsetting
# Subset by columns 1 and 3
transactions.iloc[:, [0, 2]]
# Subset by columns TransactionID and TransactionDate
transactions[['TransactionID', 'TransactionDate']]
# Subset rows where TransactionID > 5 and subset columns by TransactionID and TransactionDate
transactions.loc[transactions.TransactionID > 5, ['TransactionID', 'TransactionDate']]
# Subset columns by a variable list of columm names
cols = ["TransactionID", "UserID", "Quantity"]
transactions[cols]
# Subset columns excluding a variable list of column names
cols = ["TransactionID", "UserID", "Quantity"]
transactions.drop(cols, axis=1)
#======================================================================================================
# Inserting and updating values
# Convert the TransactionDate column to type Date
transactions['TransactionDate'] = pd.to_datetime(transactions.TransactionDate)
# Insert a new column, Foo = UserID + ProductID
transactions['Foo'] = transactions.UserID + transactions.ProductID
# Subset rows where TransactionID is even and set Foo = NA
transactions.loc[transactions.TransactionID % 2 == 0, 'Foo'] = np.nan
# Add 100 to each TransactionID
transactions.TransactionID = transactions.TransactionID + 100
transactions.TransactionID = transactions.TransactionID - 100 # revert to original IDs
# Insert a column indicating each row number
transactions['RowIdx'] = np.arange(transactions.shape[0])
# Insert columns indicating the rank of each Quantity, minimum Quantity and maximum Quantity
transactions['QuantityRk'] = transactions.Quantity.rank(method='average')
transactions['QuantityMin'] = transactions.Quantity.min()
transactions['QuantityMax'] = transactions.Quantity.max()
# Remove column Foo
transactions.drop('Foo', axis=1, inplace=True)
# Remove multiple columns RowIdx, QuantityRk, and RowIdx
transactions.drop(['QuantityRk', 'QuantityMin', 'QuantityMax'], axis=1, inplace=True)
#======================================================================================================
# Grouping the rows of a DataFrame
#--------------------------------------------------
# Group By + Aggregate
# Group the transations per user, measuring the number of transactions per user
transactions.groupby('UserID').apply(lambda x: pd.Series(dict(
Transactions=x.shape[0]
))).reset_index()
# Group the transactions per user, measuring the transactions and average quantity per user
transactions.groupby('UserID').apply(lambda x: pd.Series(dict(
Transactions=x.shape[0],
QuantityAvg=x.Quantity.mean()
))).reset_index()
# Group the transactions per year of the transaction date, measuring the number of transactions per year
# Group the transactions per (user, transaction-year) pair, measuring the number of transactions per group
# Group the transactions per user, measuring the max quantity each user made for a single transaction and the date of that transaction
# Group the transactions per (user, transaction-year), and then group by transaction-year to get the number of users who made a transaction each year
#--------------------------------------------------
# Group By + Update
# Insert a column in transactions indicating the number of transactions per user
# Insert columns in transactions indicating the first transaction date and last transaction date per user
# For each transaction, get the date of the previous transaction made by the same user
#======================================================================================================
# Joining DataFrames
# Load datasets from CSV
users = pd.read_csv('https://raw.githubusercontent.com/ben519/DataWrangling/master/Data/users.csv')
sessions = pd.read_csv('https://raw.githubusercontent.com/ben519/DataWrangling/master/Data/sessions.csv')
products = pd.read_csv('https://raw.githubusercontent.com/ben519/DataWrangling/master/Data/products.csv')
transactions = pd.read_csv('https://raw.githubusercontent.com/ben519/DataWrangling/master/Data/transactions.csv')
# Convert date columns to Date type
users['Registered'] = pd.to_datetime(users.Registered)
users['Cancelled'] = pd.to_datetime(users.Cancelled)
transactions['TransactionDate'] = pd.to_datetime(transactions.TransactionDate)
#--------------------------------------------------
# Basic Joins
# Join users to transactions, keeping all rows from transactions and only matching rows from users (left join)
transactions.merge(users, how='left', on='UserID')
# Which transactions have a UserID not in users? (anti join)
transactions[~transactions['UserID'].isin(users['UserID'])]
# Join users to transactions, keeping only rows from transactions and users that match via UserID (inner join)
transactions.merge(users, how='inner', on='UserID')
# Join users to transactions, displaying all matching rows AND all non-matching rows (full outer join)
transactions.merge(users, how='outer', on='UserID')
# Determine which sessions occured on the same day each user registered
pd.merge(left=users, right=sessions, how='inner', left_on=['UserID', 'Registered'], right_on=['UserID', 'SessionDate'])
# Build a dataset with every possible (UserID, ProductID) pair (cross join)
df1 = pd.DataFrame({'key': np.repeat(1, users.shape[0]), 'UserID': users.UserID})
df2 = pd.DataFrame({'key': np.repeat(1, products.shape[0]), 'ProductID': products.ProductID})
pd.merge(df1, df2,on='key')[['UserID', 'ProductID']]
# Determine how much quantity of each product was purchased by each user
df1 = pd.DataFrame({'key': np.repeat(1, users.shape[0]), 'UserID': users.UserID})
df2 = pd.DataFrame({'key': np.repeat(1, products.shape[0]), 'ProductID': products.ProductID})
user_products = pd.merge(df1, df2,on='key')[['UserID', 'ProductID']]
pd.merge(user_products, transactions, how='left', on=['UserID', 'ProductID']).groupby(['UserID', 'ProductID']).apply(lambda x: pd.Series(dict(
Quantity=x.Quantity.sum()
))).reset_index().fillna(0)
# For each user, get each possible pair of pair transactions (TransactionID1, TransactionID2)
pd.merge(transactions, transactions, on='UserID')
# Join each user to his/her first occuring transaction in the transactions table
pd.merge(users, transactions.groupby('UserID').first().reset_index(), how='left', on='UserID')
#--------------------------------------------------
# Rolling Joins
# Determine the ID of the last session which occured prior to (and including) the date of each transaction per user
# Determine the ID of the first session which occured after (and including) the date of each transaction per user
#--------------------------------------------------
# Non-equi joins
# Determine the first transaction that occured for each user prior to (and including) his/her Cancelled date
# Get all transactions where TransactionDate is after the user's Cancellation Date
#--------------------------------------------------
# Join + Update
# Insert the price of each product in the transactions dataset (join + update)
# Insert the number of transactions each user made into the users dataset
#--------------------------------------------------
# Setting a key and secondary indexing
# Set the key of Transactions as UserID
# View the key of transactions
# Set the key of users as UserID and join to transactions, matching rows only (inner join)
# Set ProductID as the key of transactions and products without re-ordering the rows, then join matching rows only
# Set each ID column as a secondary join index
# View indices
# Inner join between users, transactions, and products
#======================================================================================================
# Reshaping a data.table
# Read datasets from CSV
users = pd.read_csv('https://raw.githubusercontent.com/ben519/DataWrangling/master/Data/users.csv')
transactions = pd.read_csv('https://raw.githubusercontent.com/ben519/DataWrangling/master/Data/transactions.csv')
# Convert date columns to Date type
users['Registered'] = pd.to_datetime(users.Registered)
users['Cancelled'] = pd.to_datetime(users.Cancelled)
transactions['TransactionDate'] = pd.to_datetime(transactions.TransactionDate)
# Add column TransactionWeekday as Categorical type with categories Sunday through Saturday
transactions['TransactionWeekday'] = pd.Categorical(transactions.TransactionDate.dt.weekday_name, categories=['Sunday', 'Monday', 'Tuesday', 'Wednesday', 'Thursday', 'Friday', 'Saturday'])
#--------------------------------------------------
# Convert data from tall format to wide format
# One-hot encode Weekday (i.e. convert data from tall to wide, where each possible weekday is a column)
#--------------------------------------------------
# Convert data from wide format to tall format
# Build a data.table with columns {UserID, ActionType, Date} where ActionType is either "Registered" or "Cancelled" and Date is the corresponding date value
#======================================================================================================
# To Do
# Get rows which contain at least 1 NA value
# Get rows which contain at least 1 NA value within a subset of columns
# Get rows which contain all NA values
# Get rows which contain all NA values within a subset of columns
# Get the max value per row
# Get the max value per row within a subset of columns