Counting multiples columns and list the counts in separate columns and retain a column

user_6297819 0
Viewed 60

I have the following Dataframe:

    id  coord_id    val1    val2    record  val3
0   snp chr15_1-1000    1.0 0.9 xx12    2
1   snv chr15_1-1000    1.0 0.7 yy12    -4
2   ins chr15_1-1000    0.01    0.7 jj12    -4
3   ins chr15_1-1000    1.0 1.5 zzy1    -5
4   ins chr15_1-1000    1.0 1.5 zzy1    -5
5   del chr10_2000-4000 0.1 1.2 j112    12
6   del chr10_2000-4000 0.4 1.1 jh12    15

I am trying to count the number of times each coord_id appears by each id but keeping the val1 column in the resulting table but only to include a range of the value in that column so for instance, I am trying accomplish the following result:

  id            snp    snv         ins    del   total val1  
chr15_1-1000    1       1           3      0     5     0.01-1.0
chr10_2000-4000 0       0           0      2     2    0.1-0.4

I want to sort it in ascending order by the column total.

So much appreciate it in advance.

pandas dataframe range count python-3.x
2 Answers

First pivot into id columns with count aggregation and margin sums. Then join() with the val1 min-max strings:

(df.pivot_table(index='coord_id', columns='id', values='val1',
                aggfunc='count', fill_value=0,
                margins=True, margins_name='total')
   .join(df.groupby('coord_id').val1.agg(lambda x: f'{x.min()}-{x.max()}'))
   .sort_values('total', ascending=False)
   .drop('total'))

#                  del  ins  snp  snv  total      val1
# coord_id                                            
# chr15_1-1000       0    3    1    1      5  0.01-1.0
# chr10_2000-4000    2    0    0    0      2   0.1-0.4
user_131383640

I suggest making two computations separately -- get the range and count the frequency.

temp = test_df.groupby(['coord_id']).agg({'val1': ['min', 'max']})
temp.columns = temp.columns.get_level_values(1)
temp['val1'] = temp['min'].astype(str) + '-' + temp['max'].astype(str)

Then,

temp2 = test_df.groupby(['coord_id', 'id']).count().unstack('id').fillna(0)
temp2.columns = temp2.columns.get_level_values(1)

And, finally, merging

answer = pd.concat([temp, temp2], axis=1)
user_131186320
Related