I have a data set of users. that looks something like this.
there are other columns but I don't believe it's relevant for my question. Each row is unique in the sense that it corresponds to one customer and the customer does not appear more than once in the data set. This data set was aggregated from a set of transactions that took place from 2020 to 2021.
I want to see if there is a correlation between appetizer and main_meal. So I use the function
cor in R to calculate the correlation between them. It is around 0.6. I also made a scatter plot and indeed see that there seems to be a linear trend. I want to conclude that the amount of appetizer is indeed correlated with main_meal. However, I then thought well maybe there is the appearance of correlation but in reality, there is another factor that contributes to this trend. I think this because if I look at a daily period instead of a year period there is no correlation in the number of appetizers and main meals.
What I would like to see is that more appetizers usually means more main meals however this is not the case in a daily period or there seems to be no linear relationship at least.
Can someone explain why there is no correlation for a daily period but if I take the sum totals for a yearly period for each user there is a correlation between the two variables.
Edit Just as an example when looking at the first row we see that there is a customer with the user_id 185. For the year period (2020-2021) this customer had ordered a total of 30 appetizers and 120 main meals. There are many branches of this restaurant so the orders could have taken place in any of these branches. The numbers are indeed a count. There are certain meals that are classified as main meals on the menu and some are classified as appetizers.