[Python] pandas populating a DataFrame from a data table

I have two data frames where I would like to populate one with the others data. Both use the same indexes, just oriented differently. The second array is not a simple pivot though as the indexes are selected for some analysis down the road.

I have a solution below. It is very simple but it uses a for loop which might not be the most efficient. Is there a way to populate the second table without using a loop?

data structure for visualization

a b c values
a . . . a d .
b . . . g .
c . . . b h .
d . . . c e .
e . . . d .
f . . . --> j .
g . . .
h . . .
i . . .
j . . .


simple code to do the above

np.random.seed(0) # for reproducibility
test = pd.DataFrame(np.random.rand(10,3), index=["a","b","c","d","e","f","g","h","i","j"], columns=["a","b","c"])
new_array = pd.DataFrame({"t1":["a","a","b","c","c","c"],"t2":["d","g","h","e","d","j"], "value":[np.nan, np.nan,np.nan,np.nan,np.nan,np.nan]})
new_array = new_array.set_index(["t1", "t2"])
for indexes in new_array.index:
new_array.loc[indexes, "value"] = test.loc[indexes[1], indexes[0]]

Continue reading...