CODEWITHSUNDEEP
pythonpandas
Matt
Matt

Reputation: 43

Setting a column value based on another column in a pandas dataframe

I have a dataframe filled primarily with text data that is related to input fields on program and is used for tracking test results. One of my columns tracks the screen position in the program and I want to maintain the order of the screens when I sort the input fields. I realize I could do this by setting a Category for my screens, but I am concerned with importing typos from test plans, so I wanted to set a numeric value for each of my screens in a separate column and use that for my sort.

Right now my data looks like this:

Screen Order   Screen       Input Field  Result
               Screen A     ....         ....
               Screen A     ....         ....
               Screen B     ....         ....
               Screen C     ....         ....
               Screen D     ....         ....
               Screen D     ....         ....
               Screen E     ....         ....

I am trying to get it to look like this, although the screens could be out of order:

Screen Order   Screen       Input Field  Result
1              Screen A     ....         ....
1              Screen A     ....         ....
2              Screen B     ....         ....
3              Screen C     ....         ....
4              Screen D     ....         ....
4              Screen D     ....         ....
5              Screen E     ....         ....

I have been searching for a few hours and haven't been able to get anything to work, although I feel like this should be very simple. I think the solution is going to use a dictionary and applymap() function, but I haven't been able to find any good documentation on how to get the solution to work on a new column, only if I was to change the existing values in my Screen column.

I apologize if this is duplicative, but I have been searching and haven't found any solution yet.

Upvotes: 2

Views: 2624

Answers (1)

jezrael
jezrael

Reputation: 862441

EDIT1: It is different, you need dictionary for convert.

dic = {'Screen A': 1, 'Screen B': 2, 'Screen C': 3,'Screen D': 4,'Screen E': 5}
print df
     Screen
0  Screen A
1  Screen A
2  Screen B
3  Screen C
4  Screen D
5  Screen D
6  Screen E

df['Screen Order'] = df['Screen'].apply(lambda x: dic[x])
#reordering
df = df[['Screen Order', 'Screen' ]]

print df
   Screen Order    Screen
0             1  Screen A
1             1  Screen A
2             2  Screen B
3             3  Screen C
4             4  Screen D
5             4  Screen D
6             5  Screen E

I convert last char to number. More info about converting is here.

print df
     Screen
0  Screen A
1  Screen A
2  Screen B
3  Screen C
4  Screen D
5  Screen D
6  Screen E

#convert each last letter from column screen to number
# https://stackoverflow.com/a/4528997/2901002
df['Screen Order'] = df['Screen'].apply(lambda x: ord(x[-1].lower()) - 96)
#reordering columns
df = df[['Screen Order', 'Screen' ]]

print df
   Screen Order    Screen
0             1  Screen A
1             1  Screen A
2             2  Screen B
3             3  Screen C
4             4  Screen D
5             4  Screen D
6             5  Screen E

Upvotes: 1

Related Questions