I have a text file (subject_ID_dict.csv) that contains subject # mappings, like this:
30704 703
30705 849
30714 682
30720 699
30727 105
30729 708
30739 707
30757 854
30758 710
30763 724
30771 715
30773 99
30777 719
30779 717
30798 728
30805 732
30809 727
30831 734
30838 736
30868 735
30908 742
30929 115
30942 747
30944 743
30993 745
31006 116
31018 113
31040 758
31055 756
31057 755
31058 754
31068 760
31091 885
31147 764
31193 765
31196 767
31202 766
31209 117
31235 118
31268 772
31275 771
40017 -88
40018 542
40021 557
40023 28
I want to load this as a dictionary and use it to replace values in the first column in data.csv. So 40023 would become 28, for example.
Here is the code I have:
import pandas as pd
from collections import defaultdict
# load text file where we want to replace things
df = pd.read_csv('data.csv', header=0)
# make dictionary
d = defaultdict(list)
with open('subject_ID_dict.csv') as f:
for line in f:
line=str(line)
k, v = map(int, line.split())
d[k].append(v)
print df['subid'].replace(d, inplace=True)
when I print d, I get this (snippet because it's quite long):
defaultdict(<type 'list'>, {30720: [699], 30727: [105], 30729: [708], 30739: [707], 70319: [7066], 30757: [854], 30758: [710], 30763: [724], 30771: [715], 30773: [99], 70514: [7052], 30777: [719], 30779: [717], 70721: [-88], 70405: [-88], 30798: [728], 50331: [503310], 30805: [732], 30809: [727], 70674: [7080], 30831: [734], 30838: [736],
How can I remap the "subjid" column of data.csv using my dictionary, d, from subject_ID_dict.csv?