Addressing multicollinearity when removing or imputing is not an option I'm working on a project where the goal is to predict sales of certain generic products. There are many features, but social media metrics are causing me currently some headache. Social media metrics are a decent indicator of success. I have data related to Twitter and Facebook. The count of 'likes' seems to be a quite simple but effective feature in this context. However, not all products are marketed on both platforms. ~50% of observations are present on both platforms. ~80% in Facebook and ~60% in Twitter. ~10% are not present on either platform.
The VIF for both variables is <2. The pearson correlation coefficient is 0.68. If I put them into the same OLS regression model, Twitter has negative coefficient, while Facebook remains positive. In separate models both are positive and significant.
What I have tried:

*

*If I remove the NaN rows the model will work only on 50% of cases.

*If I filled the NaN values with 0 it creates a huge penalty for the products that are only present in Twitter, as the coefficient is negative if both variables are present in model.

*Summing the values is not a solution either, because the Twitter follower numbers tend to be about twice bigger.

What I have not tried:

*

*Use standard scaler and then sum the values?

Current solution:
What I have done so far is creating a new variables social_media_audience_size which is a a linear combination of the two features based on the univariate beta coefficients as multipliers. y = ax_1  + bx_2 a and b are the coefficient of Facebook likes and Twitter followers. However, I'm not sure if this is an appropriate solution to my problem.
Questions:
What could be a generally accepted way to address the specified issue?
Also, does this problem have a name? I'm having hard time finding any sources dealing with this issue, but I don't see why this would be very unique problem. I would also be interested in any of academic references where this problem is addressed!
Finally, I'm not entirely sure if my variables even are multicollinear, considering that the VIF is small? How should I analyze and report multicollinearity in case there is small VIF?
 A: First of all, if the VIF for both variables is under 2, then this is not a problem of multicolinearity. Neither is this an issue of "missing data" due to item nonresponse or anything like that. Instead, it sounds like your "problem" is just that the world works in a particular way: you are trying to predict success as a function of "likes" on various social media platforms, but if a product wasn't promoted on (say) twitter than the "likes" variable is (understandably) undefined. This is a not a statistical problem that you can solve with statistical tests but THEORIETICAL problem that you need to solve by thinking about what you are trying to do. Different approaches to this issue reflect different research questions and ways of thinking about what you are trying to do.
If you don't do anything to the missing values but run a model with both "facebook likes" and "twitter likes" in it, then the model will only contain products that were marketed on BOTH platforms, and it will tell you the impact of twitter likes CONTROLING for facebook likes (in other words, what would be the impact of having one more twitter like, holding facebook likes constant). If what you are trying to do is predict the relative importance of twitter or facebook likes, then this actually seems like a good model, because it limits the sample to products that were advertised on both (making it a "fair" comparison) and tries to find the effect of each kind of like, net of the other.
On the other hand if you replace the "NaN" on likes with zero for a product that wasn't marketed on a platform then, this reflects a view that "if you don't market a product on twitter then by definition the numbers of likes you get on it it zero." So yes it "penalizes" companies that don't market on twitter (or facebook) but maybe it should: in real life it is TRUE that you can't get likes on a platform if you don't promote on it right? So if you just want to know the extent to which "likes" predict success, and you want to acknowledge the real world fact that likes require promotion in the first place, then this might actually be a fine model.
In short: what you describe isn't really a "problem" but just the general issue of "how do I set up my data to make sure I'm answering the right research question." And there is no one answer: it depends on what research question you are trying to answer, and your background "theories" about how you are thinking about the various issues. So the solution here is to just think hard about the substantive implications of making different choices here, and choose the approach that most reflects the assumptions you want to make and the question you are trying to answer. I know that's unsatisfying but that's how stats works in practice.
A: This is not necessarily a complex problem, but unfortunately there are no specific solutions as well. There are many different approaches that you can take which depend on your dataset and your aims.
If you intend to create a composite index to address your problem (which is my preferred method in your problem as well), this can be a valid method but certain assumptions need to be met. First of all, I suggest you check out the "Handbook on Constructing Composite Indicators" by the OECD: https://www.oecd.org/sdd/42495745.pdf . There must be a theoratical basis for how you create your composite index which depends on your planned analysis and your aims.
What I can suggest, is creating a new composite index based on the guidelines provided in the above link. Also, I suggest you create a weight variable and weight your new index based on the number of available social media data (1,2,3 or none).
Check other solutions for addressing high multicollinearity as well. This blog has a good summary: link
Response to edit:
When including both variables causes a reversal of signs, which is the case here, multicollinearity is causing significant influence on the coefficients. Depending on what you want to do with your model, this can be an indicator that you need to deal with it.
