Finding weighted averages without including the cells which are empty in certain rows

I have a list of data in which certain cells are empty in a row, but I need to calculate weighted average of each row, so the way I'm going about it is to change weights depending upon whether a cell has a value or not. For example in the first row, where each cell of the data has value, the weight of one particular cell might be 0.2, but in the next row where one of the cells is empty, the weights would reflect that and the new weight would become something like 0.3.

I've attached a sample notebook with two worksheets, one sheet contains the data for which averages are required and the other sheet contains populations for calculating weights, a sample output is also in the workbook.

I've tried to attempt this in python but I'm not too adept at pandas and am having a hard time figuring it out.

Dude, this worked perfectly, I was so confused with this one. Thanks, you're a goddamn genius.