2

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.

Matt
  • 53
  • 1
  • 5

1 Answers1

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
Community
  • 1
  • 1
jezrael
  • 822,522
  • 95
  • 1,334
  • 1,252
  • 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. – Matt Sep 29 '15 at 21:11
  • Ok, I add next solution. – jezrael Sep 29 '15 at 21:23
  • The update with the dictionary reference addressed my issue. Thank you for the additional feedback and edits. – Matt Sep 29 '15 at 23:14