How to create a python dictionary from two pandas DF columns

I wanted to create a Python dictionary from 2 DataFrame columns. I had this DF which may be familiar to Scottish Football fans:

    Position          Team  GP   W   D   L  GF  GA  GD  Pts   PPG
0          1        Celtic  38  27   6   5  77  20  57   87  2.29
1          2       Rangers  38  23   9   6  82  27  55   78  2.05
2          3    Kilmarnock  38  19  10   9  50  31  19   67  1.76
3          4      Aberdeen  38  20   7  11  57  44  13   67  1.76
4          5     Hibernian  38  14  12  12  51  39  12   54  1.42
5          6  St Johnstone  38  15   7  16  38  48 -10   52  1.37
6          7        Hearts  38  15   6  17  42  50  -8   51  1.34
7          8    Motherwell  38  15   6  17  46  56 -10   51  1.34
8          9    Livingston  38  11  11  16  42  44  -2   44  1.16
9         10      Hamilton  38   9   6  23  28  75 -47   33  0.87
10        11     St Mirren  38   8   8  22  34  66 -32   32  0.84
11        12     Dundee FC  38   5   6  27  31  78 -47   21  0.55

I specifically wanted a dictionary with entries of Team and Position. It only took a few minutes to find this code stackoverflow:

NewDF = pd.Series(df.Team,index=df.Position).to_dict()

Which Gives the result:

{1: 'Rangers',
2: 'Kilmarnock',
3: 'Aberdeen',
4: 'Hibernian',
5: 'St Johnstone',
6: 'Hearts',
7: 'Motherwell',
8: 'Livingston',
9: 'Hamilton',
10: 'St Mirren',
11: 'Dundee FC',
12: nan}

Of course, this isn’t right. Sevco will never be No. 1!!
It seems that Position1 doesn’t show the correct value. I grew tired of trying to fix this after an hour or so.
The best way I found was:

b = dict(zip(a.Team, a.Position))

This is simply using the Python zip method on the 2 columns and forming a dictionary from them.
Zip takes 2 iterables and makes tuples ( a,b). The dict function changes them into s dictionary type.

It gives this result:
{'Celtic': 1,
'Rangers': 2,
'Kilmarnock': 3,
'Aberdeen': 4,
'Hibernian': 5,
'St Johnstone': 6,
'Hearts': 7,
'Motherwell': 8,
'Livingston': 9,
'Hamilton': 10,
'St Mirren': 11,
'Dundee FC': 12}

The world is put to rights!! But why didn’t it work on my original DF?

if I make a DF from scratch:

xx =[1,2,3,4,5 ]
yy = ['a','b','c','d','e']

d = {'Month':xx,'Text':yy}
df = pd.DataFrame(d)
     month Text
 0      1    a
 1      2    b
 2      3    c
 3      4    d
 4      5    e

However the following code gives me the same problem as my original;

zz = pd.Series(df.Text,index=dx.Month).to_dict()

{1: ‘b’, 2: ‘c’, 3: ‘d’, 4: ‘e’, 5: nan}

This gives the same problem as my original one. However:

aa = dict(zip(df.Text, df.month))

gives the correct result (or at least the ones I want)
{‘a’: 1, ‘b’: 2, ‘c’: 3, ‘d’: 4, ‘e’: 5}

The pd.series version was found on stackoverflow. It doesn’t work for me and I won’t spend time trying to figure out why.

Conclusion

If you want to make a dictionary from 2 DF columns, then use the inbuilt zip method and create a dictionary.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s