Reading 2 csv files with pandas, using a value in one file to look up other values in the second file

Question

I have 2 txt files being read by Pandas.

The first file contains:

code  speed1  speed2
VST    50/20M  15/1M
BMR    50/20M  15/1M
BMQ    50/20M  15/1M
VSK    50/20M  50/20M

The second file contains:

VST,L_602356450160818666
VSK, 602356450160818556
and so on

I would like to take e.g. VST in the second file and match the code in the first file and retrieve its corresponding speed 1 and 2 values. And do this for VSK etc.

I have begun by using by using index_col=0 in the first file and using df.loc[VST] to get the values. How do I put df.loc into a for loop to iterate over 100's of codes in the second file. Or is there a better way?

Tks.

UPDATE:*************

Come up with this:

dataset = pd.read_csv('firstFile.csv', usecols=cols, 
header=None, index_col=0)

df = pd.read_csv('secondFile.txt', header=None, index_col=0)

for i in df.iterrows():
   for j in dataset.iterrows():
      if i[0] == j[0]:
         print(dataset.loc[j[0]])

Out:

20    50/20M
22    50/20M
Name: VST, dtype: object
20    50/20M
22    50/20M
Name: VSK, dtype: object

The output I would like is a dictionary/json form and use VST as the key and get the values. Should turn the two files into a json first before any other process?

Can you turn this into a running script that initializes a dataframe and shows what you've tried? That's a great starting point for us. Also, are the codes unique? — tdelaney, Nov 14 '20 at 00:15
If you just want the value in the second df to be a column in the first `merge` is going to be your friend here. Check out [Pandas Merging 101](https://stackoverflow.com/questions/53645882/pandas-merging-101) — noah, Nov 14 '20 at 00:25

score 0 · Accepted Answer · answered Nov 15 '20 at 15:11

What you want is a merge:

dataset = pd.read_csv('firstFile.csv', sep='\s+')
df = pd.read_csv('secondFile.csv', header=None, names=['code', 'value'])

result = df.merge(dataset, how='left', left_on='code', right_on='code')

Output:

  code                 value  speed1  speed2
0  VST  L_602356450160818666  50/20M   15/1M
1  VSK    602356450160818556  50/20M  50/20M

Reading 2 csv files with pandas, using a value in one file to look up other values in the second file

1 Answers1