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

Question

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.

Community · Accepted Answer · 2017-05-23 12:25:46Z

1

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

edited May 23, 2017 at 12:25

CommunityBot

11 silver badge

answered Sep 29, 2015 at 20:49

jezrael

868k103 gold badges1.4k silver badges1.3k bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Matt Over a year ago

Thanks for the response. I used the names like Screen A, B, etc. for simplicity reasons, but the names are actually like Policy Information or Customer Data, so I don't actually have a single character I could easily convert. I need to find a method that allows me to convert the entire name into a screen order as opposed to a single character.

jezrael Over a year ago

Ok, I add next solution.

Matt Over a year ago

The update with the dictionary reference addressed my issue. Thank you for the additional feedback and edits.

Collectives™ on Stack Overflow

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

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related