0
$\begingroup$

I am fairly new to coding and cannot figure out how to create the AB_perc and CD_perc columns in the Desired Output table below.

The original csv file I am using has all sorts ot DNA sequencing data, so below is a simplified example of what I am trying to do.

CSV file:

enter image description here

Desired Output:

enter image description here

I was able to generate the AB_sum and CD_sum columns by using:

df['AB_sum'] = df['A'] + df['B']
df['CD_sum'] = df['C'] + df['D']
df.groupby('RunNumber', as_index=False).agg({'AB_sum': 'sum', 'Confusion C<->G': 'sum'})

But I cannot figure out how to create the AB_perc and CD_perc columns.

  • For example:

To calculate the percentage of AB for Run 1 (AB_perc: 79.94), the AB_sum value, 570, is divided by 713, the sum of all orange numbers in the csv file, and then multiplied by 100

I tried several things all day and nothing worked for me. I'm sure there's a simple solution, but I feel like if I try any harder my nose will start to bleed. Haha.

Any help would be much appreciated!

$\endgroup$

1 Answer 1

0
$\begingroup$

Try using groupby with a custom function and apply.

df = pd.DataFrame()
df['RunNumber'] = ['Run1','Run1','Run2','Run2']
df['A']=[500,60,20,30]
df['B']=[5,5,2,2]
df['C']=[5,10,2,6]
df['D']=[3,4,5,4]
df['E']=[65,56,56,44]

def calculate_sum_percent(input_df):
    output_df = pd.Series()
    output_df['AB_sum']=input_df['A'].sum() + input_df['B'].sum()
    output_df['CD_sum']=input_df['C'].sum() + input_df['D'].sum()
    output_df['AB_perc']=100*(input_df['A'].sum() + input_df['B'].sum())/input_df.sum().sum()
    output_df['CD_perc']=100*(input_df['C'].sum() + input_df['D'].sum())/input_df.sum().sum()
    return output_df

stats_df = df.groupby('RunNumber').apply(calculate_sum_percent)
display(stats_df)
$\endgroup$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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