CODEWITHSUNDEEP
rdplyr
Andrew Buchanan
Andrew Buchanan

Reputation: 81

append count of one dataframe to another

I have two dataframes, the first includes item codes, descriptions and prices (prices are ommitted) and another, which is exactly the same but the price column is a unique list:

head(currentmonthprice)
  LOOKUPLINK ITEMCODE              ITEMDESCRIPTION PRICE
1   032105-1   032105       Basic Berry Mix10x400g £x.xx
4   055800-1   055800       Blueberries     8x400g £x.xx
5   055801-1   055801           Raspberries 8x350g £x.xx
6   055802-1   055802     Straw/Blueberries 8x400g £x.xx
7   055803-1   055803          Cherries DS  8x500g £x.xx
8   055804-1   055804         Summer Fruits 8x500g £x.xx 

head(currentmonthitemcodes)
  ITEMCODE              ITEMDESCRIPTION PRICE COUNTOFPRICE
1   032105       Basic Berry Mix10x400g £x.xx ?
2   055800       Blueberries     8x400g £x.xx ?
3   055801           Raspberries 8x350g £x.xx ?
4   055802     Straw/Blueberries 8x400g £x.xx ?
5   055803          Cherries DS  8x500g £x.xx ?
6   055804         Summer Fruits 8x500g £x.xx ?

I want to apply a countif style function of the number of times a product is a certain price, and append it to the currentmonthitemcodes data frame as a new column. For example, if three customers pay £5.00 for ITEMCODE 032105 in the currentmonthprice df, the appended value to the second data frame would be 3 for that code. Any ideas?

Many thanks

Upvotes: 0

Views: 51

Answers (1)

Adam Warner
Adam Warner

Reputation: 1354

library(dplyr)    
df1 <- read.table(header = TRUE, text = "LOOKUPLINK ITEMCODE ITEMDESCRIPTION PRICE
                           '032105-1' '032105' 'Basic Berry Mix10x400g' '£x.xx'
                           '055800-1' '055800' 'Blueberries 8x400g' '£x.xx'
                           '055801-1' '055801' 'Raspberries 8x350g' '£x.xx'
                           '055802-1' '055802' 'Straw/Blueberries8x400g' '£x.xx'
                           '055803-1' '055803' 'Cherries DS 8x500g' '£x.xx'
                           '055804-1' '055804' 'Summer Fruits 8x500g' '£x.xx'",stringsAsFactors = F )



df2 <- read.table(header = TRUE, text = "ITEMCODE ITEMDESCRIPTION PRICE
                     '032105' 'Basic Berry Mix10x400g' '£x.xx'
                   '055800' 'Blueberries 8x400g' '£x.xx'
                   '055801' 'Raspberries 8x350g' '£x.xx'
                   '055802' 'Straw/Blueberries8x400g' '£x.xx'
                   '055803' 'Cherries DS 8x500g' '£x.xx'
                    '055804' 'Summer Fruits 8x500g' '£x.xx'" , stringsAsFactors = F)

You can just do a simple left join where you group by the itemcode and the price and count the number of occurrences. 

left_join(df2, df1 %>% group_by(ITEMCODE, PRICE) %>% summarise(count = n()))

This looks like this: 

     ITEMCODE         ITEMDESCRIPTION PRICE count
1    32105  Basic Berry Mix10x400g £x.xx     1
2    55800      Blueberries 8x400g £x.xx     1
3    55801      Raspberries 8x350g £x.xx     1
4    55802 Straw/Blueberries8x400g £x.xx     1
5    55803      Cherries DS 8x500g £x.xx     1
6    55804    Summer Fruits 8x500g £x.xx     1

Upvotes: 2

Related Questions