CODEWITHSUNDEEP
r
Romil Garg
Romil Garg

Reputation: 33

Create new data set based on a unique ID and frequency of same data in a row

I have data set DATA1(I am providing a trim version of data here) which has customer_id and there bookings(three different kind of bookings are denoted by 1,2,3.

I want to make a new table Output out of this table which will have Customer ID and frequency of Type1 booking which is denoted by 1 here.

Customer_ID Booking
1   1
2   2
2   1
2   2
3   2
3   1
3   2
3   3
3   1
4   2
4   3

Required Output is

Customer_ID Frequency_Type1
1   1
2   1
3   2
4   0

Upvotes: 0

Views: 44

Answers (2)

Ronak Shah
Ronak Shah

Reputation: 389135

We can use xtabs to calculate the frequency

xtabs(Booking~Customer_ID, df[df$Booking ==1, ])

#Customer_ID
#1 2 3 4 
#1 1 2 0

Another base R solution would be converting the Customer_ID into factor and then use table on only those rows where Booking is equal to 1.

df$Customer_ID <- as.factor(df$Customer_ID)
table(df[df$Booking ==1, ])

#          Booking
#Customer_ID 1
#          1 1
#          2 1
#          3 2
#          4 0

Upvotes: 0

A5C1D2H2I1M1N2O1R2T1
A5C1D2H2I1M1N2O1R2T1

Reputation: 193637

Since you're just looking at frequencies, you should just be able to use table.

Example:

table(mydf)
##            Booking
## Customer_ID 1 2 3
##           1 1 0 0
##           2 1 2 0
##           3 2 2 1
##           4 0 1 1

Then, if you wanted just the first column, you could do:

table(mydf)[, "1"]
## 1 2 3 4 
## 1 1 2 0

This would give you a named vector. If you wanted that as a data.frame, you could stack it and rename the columns if required:

stack(table(mydf)[, "1"])
##   values ind
## 1      1   1
## 2      1   2
## 3      2   3
## 4      0   4

Alternatively, you could be more verbose and use with(mydf, table(Customer_ID, Booking == 1)[, "TRUE"]).

Upvotes: 2

Related Questions