Replacing letters with numbers in python objects

user_12153884 0
Viewed 2174

I am trying to replace all letters of a python object with numbers, in a Pandas DataFrame.

Example: I have a column of 3000 course codes, ex. A0101P. I am trying to replace all the letters of the alphabet in the course code with corresponding numbers (A =1, B=2 etc) so the output looks like this "1010116" (and most importantly, is an integer not an object/string)

The course code was initially a python object. So I have used

course.to_string()

to change it to string value.

Then, I have created a mapping and then used str.replace to replace the values.

mapping = {"A": 1, "B": 2, "C": 3, "D": 4, "E": 5, "F": 6, "G": 7, "H": 8, "I": 9, "J": 10, "K": 11, "L": 12, "M": 13, "N": 14, "O": 15, "P": 16, "Q":17,"R":18, "S": 19, "T": 20,"U": 21, "V": 22, "W": 23, "X": 24, "Y": 25, "Z":26}

courseDone = course.str.replace(course["Cursus code"], mapping)

It raises an error 

AttributeError: 'str' object has no attribute 'str'

I have also tried 

for key, value in mapping.items():
    course = course.replace(key, value)

and it raises error 

TypeError: replace() argument 2 must be str, not int

Even though the datatype is a string.

Example data: 

1       A0101P
2       A0111P
3       A0200P
4       A0201P
5       A0202P

Desired output: 

1       1010116
2       1011116
3       1020016
4       1020116
5       1020216

I have also tried to change the datatype with str() and the end errors are the same.

When I use 

for key, value in mapping.items():
    course["Cursus code"] = course["Cursus code"].replace(key, value)

I receive no error, but the output remains the same.

I am new to python and I have tried my best to find a solution but nothing seems to work. Can anyone help me please?

pandas python-3.x python string
4 Answers

strings are kept as 'objects' in pandas. You can use info() method of a dataframe to see which columns are integer, objects (for strings), timestampts, etc like:

df.info()

As to your question, you can use apply method and replace your string with desired mapping, like that:

def str_to_int_map(string, mapping):

   return int(''.join([str(mapping.get(x, x)) for x in string]))
mapping = {"A": 1, "B": 2, "C": 3, "D": 4, "E": 5, "F": 6, "G": 7, "H": 8, "I": 9, "J": 10, "K": 11, "L": 12, "M": 13, "N": 14, "O": 15, "P": 16, "Q":17,"R":18, "S": 19, "T": 20,"U": 21, "V": 22, "W": 23, "X": 24, "Y": 25, "Z":26}
df['Course'] = df['Course'].apply(lambda x: str_to_int_map(x, mapping))
user_72292490

A silly solution is to replace the letters one by one, similar to yours, but have to map numbers to string:

for k,v in mapping.items():
    v = str(v)
    course["Cursus code"] = course["Cursus code"].str.replace(k,v)

Output:

0    1010116
1    1011116
2    1020016
3    1020116
4    1020216
user_42384080

you can use map aswell to replace values with a dict, it's pretty clean. Then about your string issue, is it because you didn't allocate your result?

like : course = course.to_string() courseDone = course.str.replace(course["Cursus code"], mapping)

or

courseDone = course.to_string().str.replace(course["Cursus code"], mapping)

user_72257910

The below example is like your current structure. But there are lots of ways to do this. Expand the mapping as you see fit. And also note that iterating through a dict is different in Python 2/3. Notice that both the key and value are strings in the dict. 

string_to_change = 'abc123'
mapping_dict = dict(zip(['a','b','c'],['1','2','3']))
for k, v in mapping_dict.iteritems():
    string_to_change = string_to_change.replace(k,v)
'''
user_74927370
Related