Why is the Wilcox test not significant for this data? The 2 data.frame count values look quite significantly different to me, so I'm wondering why the output of the Wilcox test is not "significant."
Here is my data:
df1 <- structure(list(time = c("Day 1", "Day 1"), count = c(59L, 1L)), row.names = c(NA, -2L), class = "data.frame")

df2 <- structure(list(time = c("Day 2", "Day 2", "Day 2", "Day 2", "Day 2", "Day 2", "Day 2", "Day 2"), count = c(1L, 207L, 5L, 3L, 3L, 11L, 2L, 1L)), row.names = c(NA, -8L), class = "data.frame")

The p-value calculated is: p-value = 0.8353
The code I ran for the test is:
wilcox.test(df1$count, df2$count, alternative = 'two.sided', exact = FALSE)


Here is part of what ?wilcox.test will give you:

if both ‘x’ and ‘y’ are given and ‘paired’ is ‘FALSE’,
a Wilcoxon rank sum test (equivalent to the Mann-Whitney test: see
the Note) is carried out.  In this case, the null hypothesis is
that the distributions of ‘x’ and ‘y’ differ by a location shift
of ‘mu’ and the alternative is that they differ by some other
location shift (and the one-sided alternative ‘"greater"’ is that
‘x’ is shifted to the right of ‘y’).

Note that df1 has only two entries, while df2 has only eight, and (importantly!) they overlap in range - specifically, the entire range of df1$count is contained in the range of df2$count.
There is simply not enough signal there to reject the null hypothesis that the distributions do not differ in location.

Put conversely, for sample sizes of 2 and 8, your distributions would need to be completely disjoint for the Wilcoxon test to reject the null:
> wilcox.test(1:2, 3:10)

        Wilcoxon rank sum exact test

data:  1:2 and 3:10
W = 0, p-value = 0.04444
alternative hypothesis: true location shift is not equal to 0

Of course, since this is a rank-based test, if they samples do not overlap, it does not matter how far apart they are:
> wilcox.test(1:2, 4:11)

        Wilcoxon rank sum exact test

data:  1:2 and 4:11
W = 0, p-value = 0.04444
alternative hypothesis: true location shift is not equal to 0

(Note how both the test statistic and the p value are identical to the previous case.)

You write that "The 2 data.frame count values look quite significantly different to me", but (1) differences in distributions is not what the Wilcoxon test looks at. As above, it looks at differences in location between two samples. If you want to check whether two samples are different "as such", you could try something based on tabulating the data, perhaps with a $\chi^2$ test. However, (2) your sample is also much too small for that.
