6
$\begingroup$

Assume that we have the following pandas dataframe:

df = pd.DataFrame({'col1':['A>G','C>T','C>T','G>T','C>T', 'A>G','A>G','A>G'],'col2':['TCT','ACA','TCA','TCA','GCT', 'ACT','CTG','ATG'], 'start':[1000,2000,3000,4000,5000,6000,10000,20000]})

input:
 col1 col2  start
0  A>G  TCT   1000
1  C>T  ACA   2000
2  C>T  TCA   3000
3  G>T  TCA   4000
4  C>T  GCT   5000
5  A>G  ACT   6000
6  A>G  CTG  10000
7  A>G  ATG  20000
8  C>A  TCT  10000
9  C>T  ACA   2000
10 C>T  TCA   3000
11 C>T  TCA   4000

What I want to get is the number of consecutive values in col1 and length of these consecutive values and the difference between the last element's start and first element's start:

output:
 type length  diff
0  C>T  2   1000
1  A>G  3   14000
2  C>T  3   2000
$\endgroup$
1
  • $\begingroup$ your df assignment doesn't match the input $\endgroup$
    – oW_
    Nov 19 '18 at 21:29
9
$\begingroup$

Break col1 into sub-groups of consecutive strings. Extract first and last entry per sub-group.

Something like this:

df = pd.DataFrame({'col1':['A>G','C>T','C>T','G>T','C>T', 'A>G','A>G','A>G'],'col2':['TCT','ACA','TCA','TCA','GCT', 'ACT','CTG','ATG'], 'start':[1000,2000,3000,4000,5000,6000,10000,20000]})

df['subgroup'] = (df['col1'] != df['col1'].shift(1)).cumsum()


  col1 col2  start  subgroup
0  A>G  TCT   1000         1
1  C>T  ACA   2000         2
2  C>T  TCA   3000         2
3  G>T  TCA   4000         3
4  C>T  GCT   5000         4
5  A>G  ACT   6000         5
6  A>G  CTG  10000         5
7  A>G  ATG  20000         5


df.groupby('subgroup',as_index=False).apply(lambda x: (x['col1'].head(1),
                                                        x.shape[0],
                                                        x['start'].iloc[-1] - x['start'].iloc[0]))

0        ([A>G], 1, 0)
1     ([C>T], 2, 1000)
2        ([G>T], 1, 0)
3        ([C>T], 1, 0)
4    ([A>G], 3, 14000)

Tweak as needed.

$\endgroup$
6
  • $\begingroup$ Thanks for your solution. shift(1) shifts the column one row down but how does the cumsum works after this point? I did not understand that part. Could you please explain? Thanks. $\endgroup$
    – burcak
    Nov 19 '18 at 22:26
  • $\begingroup$ The first part is True whenever a new subgroup starts, .cumsum() simply adds (or counts) all the True values up to this point (True and False can be treated as 1 and 0). Since there is exactly one True per subgroup this gives the result. $\endgroup$
    – oW_
    Nov 19 '18 at 22:44
  • $\begingroup$ Thanks @oW_ How do we return a dataframe and other columns of df that are after start column? $\endgroup$
    – burcak
    Nov 19 '18 at 23:10
  • $\begingroup$ you can add them to the tuple and unpack it. if you need it in a different way I suggest asking a question on stackoverflow. this is not really data science related. $\endgroup$
    – oW_
    Nov 19 '18 at 23:27
  • 1
    $\begingroup$ @oW_ I don't believe this works anymore, at least in my environment. I get an error ValueError: 1 columns passed, passed data had 3 columns. $\endgroup$
    – d84_n1nj4
    Dec 7 '20 at 15:18

Not the answer you're looking for? Browse other questions tagged or ask your own question.