Pandas Dataframe iterate over rows

Question

I have a dataframe where X and Y are cell coordinates and mRNA is the number of mRNA per cell.

        ID        X        Y  mRNA
0        0  149.492  189.153     0
1        1  115.084  194.082     2
2        2  135.331  194.831     7
3        3  136.965  184.493     2
4        4  124.025  190.069     1
...    ...      ...      ...   ...
2410  2410  452.596  256.313     0
2411  2411  196.448  333.959    46
2412  2412  190.779  318.418    71
2413  2413  202.941  335.446    37
2414  2414  254.967  369.431    13

At the moment I am trying to apply this formula but I cannot really make it to work. Ideally I want to do this operation:

For ID 0: sqrt[((X0-X1)^2)+((Y0-Y1)^2)]
          sqrt[((X0-X2)^2)+((Y0-Y2)^2)]
          ............
          sqrt[((X0-Xn)^2)+((Y0-Yn)^2)]

(where n is the last cell ID in my csv file 2414)

Then the same operation will have to be done for ID 1 against all the cells, then ID 2, and so on.

import pandas as pd
import numpy as np

df=pd.read_csv('Detailed2.csv', sep=',')
print(df)

df1 = np.sqrt(((df['X'].sub(df['X']))^2).add((df['Y'].sub(df['Y']))^2)).to_frame('col')
print(df1)

This code is not working.

I think that looks like a matrix operation. Dump the values out to a numpy array and compute. — alvas
– alvas, Commented Dec 17, 2019 at 0:08
What does This code is not working mean? Have you read How to Ask? Also, please share your data in a format that is more convenient to use. See: minimal reproducible example. — AMC
– AMC, Commented Dec 17, 2019 at 0:09
The question is fairly clear that each ID should be applied to all other IDs. A correct final result should have as many columns as there are rows (i.e.one column for each ID). It's probably better to have a dict as a result, where the key is the ID representing X0, Y0 and the value is a column, where each of the other IDs is X1, Y1. At least that's how I'm reading the question. — Trenton McKinney
– Trenton McKinney, Commented Dec 17, 2019 at 0:24
@AlexanderCécile It's in response to the answer I'm seeing, not another comment. — Trenton McKinney
– Trenton McKinney, Commented Dec 17, 2019 at 0:44

ansev · Accepted Answer · 2019-12-17 01:22:34Z

Use:

for Id in df['ID']:
    df[f'new_col_{Id}']=( df[['X','Y']].sub(df.loc[df['ID'].eq(Id),['X','Y']].values)
                                     .pow(2)
                                     .sum(axis=1)
                                     .pow(1/2) )

print(df)

Output

          ID        X        Y  mRNA   new_col_0   new_col_1   new_col_2  \
0        0  149.492  189.153     0    0.000000   34.759251   15.256920   
1        1  115.084  194.082     2   34.759251    0.000000   20.260849   
2        2  135.331  194.831     7   15.256920   20.260849    0.000000   
3        3  136.965  184.493     2   13.365677   23.889895   10.466337   
4        4  124.025  190.069     1   25.483468    9.800288   12.267937   
2410  2410  452.596  256.313     0  310.455311  343.201176  323.167320   
2411  2411  196.448  333.959    46  152.228918  161.819886  151.960153   
2412  2412  190.779  318.418    71  135.698403  145.565016  135.455628   
2413  2413  202.941  335.446    37  155.751204  166.441079  156.024647   
2414  2414  254.967  369.431    13  208.866304  224.308996  211.655221   

       new_col_3   new_col_4  new_col_2410  new_col_2411  new_col_2412  \
0      13.365677   25.483468    310.455311    152.228918    135.698403   
1      23.889895    9.800288    343.201176    161.819886    145.565016   
2      10.466337   12.267937    323.167320    151.960153    135.455628   
3       0.000000   14.090258    323.698997    160.867375    144.332436   
4      14.090258    0.000000    335.182293    161.088246    144.670530   
2410  323.698997  335.182293      0.000000    267.657802    269.082093   
2411  160.867375  161.088246    267.657802      0.000000     16.542679   
2412  144.332436  144.670530    269.082093     16.542679      0.000000   
2413  164.741133  165.415257    261.896259      6.661097     20.925272   
2414  219.377610  222.073264    227.712326     68.430521     81.990399   

      new_col_2413  new_col_2414  
0       155.751204    208.866304  
1       166.441079    224.308996  
2       156.024647    211.655221  
3       164.741133    219.377610  
4       165.415257    222.073264  
2410    261.896259    227.712326  
2411      6.661097     68.430521  
2412     20.925272     81.990399  
2413      0.000000     62.142457  
2414     62.142457      0.000000

Solution with itertuples @Trenton McKinney and @Alexander Cécile (recommended)

for row in df.itertuples():
    df[f'id_{row.Index}'] = df[['X', 'Y']].sub([row.X, row.Y], axis='columns').pow(2).sum(axis=1).pow(1/2).round(2)

Solution with apply

df.join(
df['ID'].apply(lambda x:
              df[['X','Y']].sub(df.loc[df['ID'].eq(x),['X','Y']].values)
                                  .pow(2)
                                  .sum(axis=1)
                                  .pow(1/2))
        .add_prefix('new_col_')
)

keep in mind that you cannot have repeated IDs

Thanks, although Trenton McKinney is right, I would need the same operation for each ID.
That looks correct now. Good job. You can mark this comment as no longer needed.
I would suggest checking out the answer by PMende, @TrentonMcKinney and others, it’s pretty cool.

AMC · Accepted Answer · 2019-12-17 02:09:53Z

PMende posted a NumPy solution while I was working on mine, and it's even better. Kudos to him.

Here is a slight variation on his answer which I like because it doesn't use any explicit loops.

raw_str = \
    '''
            ID        X        Y  mRNA
    0        0  149.492  189.153     0
    1        1  115.084  194.082     2
    2        2  135.331  194.831     7
    3        3  136.965  184.493     2
    4        4  124.025  190.069     1
    2410  2410  452.596  256.313     0
    2411  2411  196.448  333.959    46
    2412  2412  190.779  318.418    71
    2413  2413  202.941  335.446    37
    2414  2414  254.967  369.431    13
    '''

df_1 = pd.read_csv(StringIO(raw_str), header=0, delim_whitespace=True, usecols=[1, 2, 3, 4])

coords = df_1[['X', 'Y']].to_numpy()

distances = spsp.distance_matrix(coords, coords)

col_names = df_1['ID'].map(lambda x: f'col_id_{x}').rename()

df_2 = pd.DataFrame(data=distances, columns=col_names)

df_3 = pd.concat((df_1, df_2), axis=1)

The extra variables obviously hurt performance, they're here simply for the sake of clarity.

Creating thousands of columns is kind of crazy, this is a more reasonable solution which saves the distances as lists in each row.

from io import StringIO

import pandas as pd
import scipy.spatial as spsp

raw_str = \
    '''
            ID        X        Y  mRNA
    0        0  149.492  189.153     0
    1        1  115.084  194.082     2
    2        2  135.331  194.831     7
    3        3  136.965  184.493     2
    4        4  124.025  190.069     1
    2410  2410  452.596  256.313     0
    2411  2411  196.448  333.959    46
    2412  2412  190.779  318.418    71
    2413  2413  202.941  335.446    37
    2414  2414  254.967  369.431    13
    '''

df_1 = pd.read_csv(StringIO(raw_str), header=0, delim_whitespace=True, usecols=[1, 2, 3, 4])

coords = df_1[['X', 'Y']].to_numpy()

distances = spsp.distance_matrix(coords, coords)

df_1['dist'] = distances.tolist()

df_1:

     ID        X  ...  mRNA                                               dist
0     0  149.492  ...     0  [0.0, 34.759250639218344, 15.256919905406859, ...
1     1  115.084  ...     2  [34.759250639218344, 0.0, 20.26084919246971, 2...
2     2  135.331  ...     7  [15.256919905406859, 20.26084919246971, 0.0, 1...
3     3  136.965  ...     2  [13.36567727427235, 23.889894976746966, 10.466...
4     4  124.025  ...     1  [25.483468072458283, 9.800288261066603, 12.267...
5  2410  452.596  ...     0  [310.45531146366295, 343.201176433007, 323.167...
6  2411  196.448  ...    46  [152.2289183171187, 161.81988637061886, 151.96...
7  2412  190.779  ...    71  [135.69840306355857, 145.56501613025023, 135.4...
8  2413  202.941  ...    37  [155.75120368716253, 166.4410794996235, 156.02...
9  2414  254.967  ...    13  [208.86630390994137, 224.30899556192568, 211.6...

PMende · Accepted Answer · 2019-12-17 01:42:47Z

1

I would recommend using the underlying numpy arrays and scipy's distance_matrix instead:

from scipy.spatial import distance_matrix

arr = df[["X", "Y"]].to_numpy()
dists = distance_matrix(arr, arr)
dist_col_names = "dist_to_" + df["ID"].astype("str")
for col_name, col in zip(dist_col_names, dists):
    df[col_name] = col

This is likely to be far more speed performant than looping through rows.

answered Dec 17, 2019 at 1:42

PMende

5,4903 gold badges21 silver badges26 bronze badges

1 Comment

AMC Over a year ago

Nicely done! I would go even further than suggesting the use of numpy, I say ditch Pandas entirely

Collectives™ on Stack Overflow

Pandas Dataframe iterate over rows

3 Answers 3

9 Comments

Comments

1 Comment

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

9 Comments

Comments

1 Comment

Linked

Related