Python 3 - Mapping list within Pandas Column

Question

I'm trying to replace a list of existing values within a pandas column.

   team ID ... win
0     1 ...   8
1     1 ...   4
2     3 ...   5
3     4 ...   2
4     5 ...   1

(team IDs can repeat and are not unique)

I'm looking to change the team ID to team names from an existing list that I already have

team_names = ['A', 'B', 'C', 'D']

I was previously doing it like this:

df.name = df.name.replace({
    1: 'A',
    2: 'B',
    3: 'C',
    4: 'D'
})

However, the issue i'm facing now is that the two lists I want to map between is almost 100. So I was wondering if anyone could share a faster method of doing this?

https://stackoverflow.com/questions/36846060/how-to-replace-an-entire-column-on-pandas-dataframe Is this what you're looking to do? — user1394, Oct 26 '18 at 04:32
So do you just want `['A', 'B', 'C', ....., 'AA', 'AB', 'AC']`? — rahlf23, Oct 26 '18 at 04:41

Edgar Ramírez Mondragón · Answer 1 · 2018-10-26T04:51:41.193

0

You can zip the two lists and then pass the result to a dict:

df.element_type = df.element_type.replace(dict(zip(list1, list2)))

zip works like this:

>>> dict(zip([1, 2, 3], ["a", "b", "c"]))
{1: 'a', 2: 'b', 3: 'c'}

EDIT

If the order of the team names in your list corresponds with the ids (1-> "A", 2 -> "B", etc.), then this works:

df["team ID"] = df["team ID"].replace(dict(zip(
    df["team ID"].sort_values().unique(),
    team_names
)))

edited Oct 26 '18 at 04:51

answered Oct 26 '18 at 04:14

Edgar Ramírez Mondragón

2,912
3
19
34

What if I were trying to replace the existing list within the columns itself? df.element_type = df.element_type.replace(dict(zip([df.element_type.unique], [A, B, C, D]))) – Theolck Oct 26 '18 at 04:22
What existing list? Please edit your question to indicate your example input data and your expected output. – Edgar Ramírez Mondragón Oct 26 '18 at 04:24

rahlf23 · Answer 2 · 2018-10-26T04:58:26.817

This solution is inspired by this answer here, and can be adapted to address your question. You can use itertools.product() with string.ascii_uppercase to create a list of letters like ['A', 'B', 'C', ....., 'AA', 'AB', 'AC'] with the same length as your dataframe:

import pandas as pd
import string
import itertools

newlist = []

def iter_all_strings():
    for size in itertools.count(1):
        for s in itertools.product(string.ascii_uppercase, repeat=size):
            yield "".join(s)

for s in itertools.islice(iter_all_strings(), len(df)):
    newlist.append(s)

df['team ID'] = newlist

Yields:

  team ID  win
0       A    8
1       B    4
2       C    5
3       D    2
4       E    1

I see that you modified your input dataframe to include non-unique values in the team ID column after I composed my answer, so a slight modification to the last statement will produce the desired result (note @Edgar R. Mondragón posted this part first in his answer):

df['team ID'] = df["team ID"].replace(dict(zip(
    df["team ID"].sort_values().unique(),
    newlist
)))

Yields:

  team ID  win
0       A    8
1       A    4
2       B    5
3       C    2
4       D    1

user1394 · Answer 3 · 2018-10-26T06:23:14.870

0

If indeed you just want to replace the ID with the corresponding name:

Create the translation dictionary:

dict= {1: 'A', 2: 'B', 3: 'C', 4: 'D', 5: 'E'}

Then replace with the corresponding values:

df['team_ID'] = pd.Series([dict[i] for i in list(df.team_ID) if i in list(df.team_ID)])

Gives:

     team_ID  win
0       A    8
1       A    4
2       C    5
3       D    2
4       E    1

edited Oct 26 '18 at 06:23

answered Oct 26 '18 at 05:34

user1394

538
1
6
17

score 0 · Answer 4 · answered Oct 26 '18 at 06:10

if the position of each team name within your team_names list corresponds to the team_id you can do:

{i[0]:i[1] for i in enumerate(team_names)}

{0: 'A', 1: 'B', 2: 'C', 3: 'D'}

Replace the manual dictionary with the dict comprehension above:

df.name = df.name.replace({i[0]:i[1] for i in enumerate(team_names)})

score 0 · Answer 5 · answered Oct 26 '18 at 06:22

0

my_dict= {1: 'A', 2: 'B', 3: 'C', 4: 'D', 5: 'E'}
df['team Id']=df['team Id'].map(my_dict)

answered Oct 26 '18 at 06:22

Pyd

6,017
18
52
109

Python 3 - Mapping list within Pandas Column

5 Answers5

EDIT