What is the most appropriate transformation to make them as normally distributed as possible?
This data is absolute zero inflated.
Cross Validated is a question and answer site for people interested in statistics, machine learning, data analysis, data mining, and data visualization. It only takes a minute to sign up.Sign up to join this community
As you have described your data (i.e. a large number of exactly 0 data points), there is no reasonable transformation to make your data appear normal. By reasonable transformation, I making these two assumptions:
1.) The transformation is deterministic (i.e. if you can't "add noise" to the transformation)
2.) The transformation is monotonic. There are times when non-monotonic transformations, such as taking the absolute value, are "reasonable". But in your case, there is no reason to think that a non-monotonic transformation might be a good idea.
In your first plot, it appears that almost half the values (maybe more?) are equal to 0. Thus, any monotonic, non-deterministic transformation will transform these 0's into either the min or max value. If about half of your (transformed) data points are either the min or the max in a relatively large sample, there is no way your distribution is anything close to normal (excluding degenerate normals, i.e with $\sigma = 0$).
That being said, I'm not sure what your goal is with these datasets, but it seems foolish to attempt to "remove" the zero-inflated aspect from your data, rather than to include this aspect in your analysis. Zero-inflated models are built for exactly this purpose.