emax emax - 22 days ago 6
Python Question

Pandas: how to find values in a dataframe?

I have two dataframes

df
and
df1
.

df =
Days 0 2 4 5 7 8 10
0 2012-01-01 27 30 5 34 1 2 10


df1 =

Lat Lon ID
0 1.3678 103.9826 0
1 1.4166 103.8654 1
2 1.3399 103.8878 2
3 1.3455 103.6806 3
4 1.3764 103.8492 3
5 1.3337 103.7768 5
6 1.4439 103.7854 6
7 1.2500 103.8279 7
8 1.3302 103.7205 8
9 1.3134 103.9619 9
10 1.2799 103.8703 10


I want find the values of the column of
df
in
df1
and return:

df2 =

Lat Lon val ID
0 1.3678 103.9826 27 0
1 1.4166 103.8654 30 2
2 1.3399 103.8878 5 3
3 1.3455 103.6806 34 5
4 1.3764 103.8492 1 7
5 1.3337 103.7768 2 8
6 1.4439 103.7854 10 10


this is what I am doing

for i in range(1, len(df.columns)):
c = l[i] ## name of the colum
z = np.int(c)
tmp1 = df1[df1.ID==z]
df2.Lat[i-1] = tmp1.Lat[tmp1.index[0]]
df2.Lon[i-1] = tmp1.Lon[tmp1.index[0]]
df2.val[i-1] = df[c][tmp.index[0]]
df2.ID[i-1] = c

Answer

Perhaps, create a dff with transposed data and merge it on ID

In [56]: dff = pd.DataFrame({'ID': df.columns[1:].astype(int), 'V': df.values[0][1:]})

In [57]: dff
Out[57]:
   ID   V
0   0  27
1   2  30
2   4   5
3   5  34
4   7   1
5   8   2
6  10  10

In [58]: df1.merge(dff)
Out[58]:
      Lat       Lon  ID   V
0  1.3678  103.9826   0  27
1  1.3399  103.8878   2  30
2  1.3337  103.7768   5  34
3  1.2500  103.8279   7   1
4  1.3302  103.7205   8   2
5  1.2799  103.8703  10  10