Converting DataFrame column data into array

Question

I have data in a pandas DataFrame that looks something like: - Friends' names in a column - Friends' acquittances' names - Distance between my friends and their acquittances (in multiple locations)

   Friend     Acquaintance  Distance  Acq.Country
0  Lennon     Martin        25        England
1  Lennon     McCartney     10        England
2  Lennon     McCartney     60        Scotland
3  Lennon     Harrison      200       India
4  Lennon     Starr         40        England
5  Lennon     Ono           350       Japan
7  McCartney  Eastman       110       United States
8  Harrison   Lennon        200       England
8  Harrison   McCartney     220       England
9  Harrison   Starr         222       England

I want to be able to reformat the data such that I have a matrix of average distances with rows of friends and columns of acquaintances. The method is basically brute force computation. Any suggestions on how I can use more efficient code?

vectorR=data['Friends'].unique() #takes list of friends
vectorC=data['Acquaint'].unique() #list of acquaintances 

distance_matrix=np.zeros((len(vectorR),len(vectorC)))
for i in range(0,len(vectorX)):
    for j in range(0,len(vectorY)):
        inter=(data['Person']==vectorR[i]) & (data['MatchName']==vectorC[j])
    distance_avg=sum(data['Distance'][inter])/len(data['Distance'][inter])
    distance_matrix[(i,j)]=distance_avg

Answer 1

This sounds like a job for pivot_table:

In [11]: df.pivot_table(index='Friend', columns='Acquaintance', values='Distance')
Out[11]:
Acquaintance  Eastman  Harrison  Lennon  Martin  McCartney  Ono  Starr
Friend
Harrison          NaN       NaN     200     NaN        220  NaN    222
Lennon            NaN       200     NaN      25         35  350     40
McCartney         110       NaN     NaN     NaN        NaN  NaN    NaN

Note: the default agg_func is np.mean, which is what you want - but you can set it to something different e.g. 'sum'.