I have a dataframe as below:

I want to change the 'None' value in FieldPosition column to either 'Front, 'Middle' or 'Back' based on the Position column.

Each team will always have 1 Front,2 Backs and 1 Middle for the FieldPosition Column. Striker will always be Front, Defender will always be Back and Midfielder will always be Middle.

For example: Fill position in team A will have a 'Back' FieldPosition Fill position in team B will have a 'Middle' FieldPosition Fill position in team C will have a 'Front' FieldPosition

I am guessing creating a function with loop and if-statements will solve this since there are more than 20 Teams. How can I do this?

Final answer should be as below:

CodePudding user response：

Use a Counter difference after mapping the fixed positions:

from collections import Counter

ref = Counter({'Front': 1, 'Middle': 1, 'Back': 2})

positions = {'Striker': 'Front', 'Defender': 'Back', 'Midfielder': 'Middle'}

df['FieldPosition'] = df['Position'].map(positions).fillna(df['FieldPosition'].replace({'None': float('nan')}))

def guess_missing(x):
    c = ref-Counter(x)
    if len(c) == 1:
        return next(iter(c))

df['FieldPosition'] = df['FieldPosition'].fillna(df.groupby('Team')['FieldPosition'].transform(guess_missing))

Output:

   Team   Name    Position FieldPosition
0     A   John     Striker         Front
1     A   Carl    Defender          Back
2     A   Alex        Fill          Back
3     A    Ron  Midfielder        Middle
4     B  Tyler     Striker         Front
5     B   Chad    Defender          Back
6     B    Guy  Goalkeeper          Back
7     B    Tom        Fill        Middle
8     C  James        Fill         Front
9     C  Lucas    Defender          Back
10    C    Ben  Midfielder        Middle
11    C   Noah  Goalkeeper          Back

CodePudding user response：

We can write a function that subtracts the FieldPositions from the required set and fills in the missing one:

import pandas as pd
import numpy as np
from io import StringIO
from collections import Counter

csv = """
Team    Name    Position    FieldPosition
A   John    Striker Front
A   Carl    Defender    Back
A   Alex    Fill    None
A   Ron Midfielder  Middle
B   Tyler   Striker Front
B   Chad    Defender    Back
B   Guy Goalkeeper  Back
B   Tom Fill    None
C   James   Fill    None
C   Lucas   Defender    Back
C   Ben Midfielder  Middle
C   Noah    Goalkeeper  Back"""

df = pd.read_csv(StringIO(csv), sep = '\t')

correct_values = ['Front','Back','Back','Middle']

def fill_none(lst):
    c = Counter(correct_values)-Counter(lst)-Counter(['None'])
    return list(c.elements())[0]

df['FieldPosition'] = np.where(df['FieldPosition'] == 'None',
                               df.groupby('Team').FieldPosition.transform(fill_none),
                               df['FieldPosition'])