R/Splus, AnalyticalWay

The S language’s power is not its data management capability nor is data management the intent of the S language. However, often times when evaluating the output of a model you may need to perform basic data management with R/SPlus and you will find the S language acceptable in this role. The commands will seem more similar to Unix/Linux than SQL. However, the S language has many of the benefits of Unix/Linux (a concise language for data management) while being more data centric (allowing meta data for dataframes which includes column names).

Example:

Customer
CustomerID	Name	Age
1	Joe	23
2	Mika	45
3	Lin	34
4	Sara	56
5	Susan	18

PurchaseOrder
POID	CustomerID	Purchase
1	3	Fiction
2	1	Biography
3	1	Fiction
4	2	Biography
5	3	Fiction
6	4	Fiction

SELECT

customer

CustomerID	Name	Age
1	Joe	23
2	Mika	45
3	Lin	34
4	Sara	56
5	Susan	18

ORDER BY

customer[order(customer[,2]),]

CustomerID	Name	Age
5	Susan	18
1	Joe	23
3	Lin	34
2	Mika	45
4	Sara	56

WHERE

subset(customer, custid == 5 )

CustomerID	Name	Age
5	Susan	18

INNER JOIN

merge (purchaseorder, customer, by.x = custid , by.y = custid , all = FALSE )

CustomerID	Name	Age	POID	Purchase
1	Joe	23	2	Biography
1	Joe	23	3	Fiction
2	Mika	45	4	Biography
3	Lin	34	1	Fiction
3	Lin	34	5	Fiction
4	Sara	56	6	Fiction

LEFT OUTER JOIN

merge (purchaseorder, customer, by.x = custid , by.y = custid , all = TRUE )

CustomerID	Name	Age	POID	Purchase
1	Joe	23	2	Biography
1	Joe	23	3	Fiction
2	Mika	45	4	Biography
3	Lin	34	1	Fiction
3	Lin	34	5	Fiction
4	Sara	56	6	Fiction
5	Susan	18	NULL	NULL

GROUP BY

Cust_sum <- merge (purchaseorder, customer, by.x = custid , by.y = custid , all = FALSE ) xtabs( ~ fname, cust_sum)

Joe	Lin	Mika	Sara
2	2	1	1

UPDATE

customer[1,]$age <-23


CustomerID	Name	Age
1	Joe	26
2	Mika	45
3	Lin	34
4	Sara	56
5	Susan	18

INSERT

newcust <- data.frame(custid = 6, fname = Terry , age =50) rbind(newcust,customer)


CustomerID	Name	Age
1	Joe	23
2	Mika	45
3	Lin	34
4	Sara	56
5	Susan	18
6	Terry	50

DELETE

subset(customer, custid != 1 )


CustomerID	Name	Age
2	Mika	45
3	Lin	34
4	Sara	56
5	Susan	18

Leave a Reply Cancel reply