pandas: create column from list of tuple upoon matching first element

user_14450380 0
Viewed 398

I have a sample dataframe like:

          cid             pos 
0         11              29      
1         22              29      
2         22              29      
3         33              29   
4         44              29

And now a list of tuple like:

[(11, 3), (22, 1), (33, 4), (44, 4), (55, 7), (66, 2)]

I want to create another column from the list of tuple (2nd element). Only if the first element matches in the df column cid:

Like:

          cid             pos     new_pos
0         11              29      3
1         22              29      1
2         22              29      1
3         33              29      4
4         44              29      4

beginner here with pandas, any help would be great! thank you

pandas python
4 Answers

You can convert your list of tuples to a dictionary and the use map on that:

b = [(11, 3), (22, 1), (33, 4), (44, 4), (55, 7), (66, 2)]
df["b"] = df["cid"].map(dict(b))

print(df)
   cid  pos  b
0   11   29  3
1   22   29  1
2   22   29  1
3   33   29  4
4   44   29  4
user_142784480

Try:

# Toy dataframe

df = pd.DataFrame({"cid":[11,22,22,33,44],"pos":[29,29,29,29,29]})

lista = [(11, 3), (22, 1), (33, 4), (44, 4), (55, 7), (66, 2)]

# Solution:

df.merge(pd.DataFrame(lista, columns = ["cid", "new_pos"]), on = "cid")

Output:

enter image description here

user_62541490

We can create a second dataframe from your tuple then using one of panda's many merge functions we can grab the second element from the tuple.

Let's use map in this instance:

t = [(11, 3), (22, 1), (33, 4), (44, 4), (55, 7), (66, 2)]
df['new_pos'] = df['cid'].map(pd.DataFrame(t,columns=['cid','pos']).set_index('cid')['pos'])

   cid  pos  new_pos
0   11   29        3
1   22   29        1
2   22   29        1
3   33   29        4
4   44   29        4

Breaking this down:

pd.DataFrame(t,columns=['cid','pos']).set_index('cid')['pos']

yields:

cid
11    3
22    1
33    4
44    4
55    7
66    2
Name: pos, dtype: int64

Using map on your column matches instances from cid from your dataframe to your tuple and returns the column value selected, in this instance pos.

user_93751020

I'd vote make the list of tuokes into a 2nd dataframe, then do a left merge:

df1 = pd.DataFrame([{'cid': 11, 'pos': 29},
                    {'cid': 22, 'pos': 29},
                    {'cid': 22, 'pos': 29},
                    {'cid': 33, 'pos': 29},
                    {'cid': 44, 'pos': 29}])
df2 = pd.DataFrame([(11, 3), (22, 1), (33, 4), (44, 4),
                    (55, 7), (66, 2)], columns=['cid', 'new_pos'])

df3 = pd.merge(df1, df2, how='left', on='cid')
user_142038170
Related