Pandas: vlookup type search to get average/mean values of same name/id items from multiple columns in a multi-index dataframe

I have a data frame with names and values for items as shown below for two weeks. Some of the item names in week 1 and 2 aren’t same

week1        week2        
Name Value Name Value 
a     0.1    b  0.6
b     0.3    a  0.3
c     0.5    d  0.1
d     0.7    e  0.4 

What I am trying to do is create a table that has all names and average values in my final list as shown below. Also, say if item d is not in week 2 then the average should be divided by1 and not 2 something like a dynamic average

Name  Average_Value
a      avg from wk1 and 2
b      avg from wk1 and 2
c      avg from wk1 and 2
d          "
e          "

I am thinking of using outer join to get the final table by creating two data frames for week1 and week2 values but not sure if creating two separate data frames is a good choice(doing it as not sure if join can be performed if all values are in the same data frame). What can be a better way do approach it?

Answer

You can stack on level 0 column MultiIndex by .stack() with level=0 for week1 / week2. Then group by Name using .groupby() and take the .mean() on Value, as follows:

(df.stack(level=0)
   .groupby('Name')['Value'].mean()
   .reset_index(name='Average_Value')
)

On grouping by name and take mean on the group, if there are 2 values in the group, it will take the average of 2 values. If only 1 value, the average will be the value itself.

Result:

  Name  Average_Value
0    a           0.20
1    b           0.45
2    c           0.50
3    d           0.40
4    e           0.40

Input Setup

data = {('week1', 'Name'): ['a', 'b', 'c', 'd'],
        ('week1', 'Value'): [0.1, 0.3, 0.5, 0.7],
        ('week2', 'Name'): ['b', 'a', 'd', 'e'],
        ('week2', 'Value'): [0.6, 0.3, 0.1, 0.4]}

df = pd.DataFrame(data)