-1

Firstly: I have seen other posts about AVERAGEIF translations from excel into R but I didn't see one that worked on my specific case and I couldn't get around to making one work.

I have a dataset which encompasses the daily pricings of a bunch of listings.

It looks like this

  listing_id     date price
1       1000 1/2/2015  $100
2       1200 2/4/2016  $150

Sample of the dataset (and desired outcome) @ https://send.firefox.com/download/228f31e39d18738d/#rlMmm6UeGxgbkzsSD5OsQw

The dataset I would like to have has only the date and the average prices of all listings on that date. The goal is to get a (different) dataframe which would look something like this so I can work with it:

       Date Average Price
1  4/5/2015      204.5438
2  4/6/2015      182.6439
3  4/7/2015       176.553
4  4/8/2015      182.0448
5  4/9/2015      183.3617
6 4/10/2015      205.0997
7 4/11/2015      197.0118
8 4/12/2015      172.2943

I created this in Excel using the Average.if function (and copy pasting by value) from the sample provided above.

I tried to format the data in Excel first where I could use the AVERAGE.IF function saying take the average if it is this specific date. The problem with this is that the dataset consists of 30million rows and excel only allows for 1 million so it didn't work.

What I have done so far: I created a data frame in R (where i want the average prices to go into) using 

Avg = data.frame("Date" =1:2, "Average Price"=1:2)
Avg[nrow(Avg) + 2036,] = list("v1","v2")
Avg$Date = seq(from = as.Date("2015-04-05"), to = as.Date("2020-11-01"), by = 'day')

I tried to create an averageif-like function by this article and another but could not get it to work.

I hope this is enough information to go on otherwise I would be more than happy to provide more.

Dim Dros
  • 3
  • 4
  • 1
    It's easier to help you if you include a simple [reproducible example](https://stackoverflow.com/questions/5963269/how-to-make-a-great-r-reproducible-example) with sample input and desired output that can be used to test and verify possible solutions. Pictures of data are not useful because we can't copy/paste those values into R. Show any code attempts you tried and describe exactly where you are getting stuck. – MrFlick May 26 '20 at 15:50
  • Hi Dim, your links don't go to any files. – Ian Campbell May 26 '20 at 16:17
  • I have reuploaded the files I hope you can access them now. – Dim Dros May 26 '20 at 16:30

1 Answers1

0

If your question is how to replicate the AVERAGEIF function, you can use logical indexing :

Excel data

R code :

> df
  Dates Prices
1     1    100
2     2    120
3     3    150
4     1    320
5     2    250
6     3    210
7     1    102
8     2    180
9     3    150

idx <- df$Dates == 1  # Positions where condition is true
mean(df$Prices[idx])  # Prints same output as Excel
Trusky
  • 483
  • 2
  • 13
  • I will go see if i can get this to work using as.data specifications, I think it might thanks! – Dim Dros May 26 '20 at 16:24
  • It looks like you have more than one question then, try it out and don't forget to [mark your questions as answered](https://meta.stackexchange.com/questions/5234/how-does-accepting-an-answer-work) once answered. – Trusky May 26 '20 at 16:28