Pandas python updating values in a table based on preexisting values and conditions
Question: I have a dataframe:

    
    
    import pandas as pd
    
    df=pd.DataFrame({
    'Player': ['John','John','John','Steve','Steve','Ted', 'James','Smitty','SmittyJr','DJ'],
    'Name': ['A','B', 'A','B','B','C', 'A','D','D','D'],
    'Group':['2A','1B','2A','2A','1B','1C','2A','1C','1C','2A'],
    'Medal':['G', '?', '?', 'S', 'B','?','?','?','G','?']
    })
    
    df =  df[['Player','Group', 'Name', 'Medal']]
    print(df)
    

I want to update all the '?' in the column `Medal` with values for any of the
rows with matching `Name` & `Group` columns that are already filled in.

For example since the first `row 0` is `Name:A, Group:2A, Medal:G`, then the
'?' on `row 6` and `2` would be 'G'

The results should look like:

    
    
    res=pd.DataFrame({
    'Player': ['John','John','John','Steve','Steve','Ted', 'James','Smitty','SmittyJr','DJ'],
    'Name': ['A','B', 'A','B','B','C', 'A','D','D','D'],
    'Group':['2A','1B','2A','2A','1B','1C','2A','1C','1C','2A'],
    'Medal':['G', 'B', 'G', 'S', 'B','?','G','G','G','?']
    })
    
    res =  res[['Player','Group', 'Name', 'Medal']]
    print(res)
    

What is the most efficient way to do this?


Answer: Another solution with [`replace`](http://pandas.pydata.org/pandas-
docs/stable/generated/pandas.Series.replace.html) `?` by last value (with
[`iloc`](http://pandas.pydata.org/pandas-
docs/stable/generated/pandas.Series.iloc.html)) of sorted `Medal` (with
[`sort_values`](http://pandas.pydata.org/pandas-
docs/stable/generated/pandas.Series.sort_values.html)) in each group:

    
    
    df['Medal'] = df.groupby(['Group','Name'])['Medal']
                    .apply(lambda x: x.replace('?', x.sort_values().iloc[-1]))
    
    print(df)
         Player Group Name Medal
    0      John    2A    A     G
    1      John    1B    B     B
    2      John    2A    A     G
    3     Steve    2A    B     S
    4     Steve    1B    B     B
    5       Ted    1C    C     ?
    6     James    2A    A     G
    7    Smitty    1C    D     G
    8  SmittyJr    1C    D     G
    9        DJ    2A    D     ?
    

**Timings** :

    
    
    In [81]: %timeit (df.groupby(['Group','Name'])['Medal'].apply(lambda x: x.replace('?', x.sort_values().iloc[-1])))
    100 loops, best of 3: 4.13 ms per loop
    
    In [82]: %timeit (df.replace('?', np.nan).groupby(['Name', 'Group']).apply(lambda df: df.ffill().bfill()).fillna('?'))
    100 loops, best of 3: 11.3 ms per loop
    

