Agfdhyk

I've recently started programming and I was tasked with finding the mean of different responses of a drug based on its dosage vs a placebo. I have a 4 column Excel file which is split into Drug_1_Dosage, Drug_1_Response, Drug_2_Dosage, Drug_2_Response, respectively.

I'm trying to have those values sorted in Jupyter notebook so that all instances where Drug_1_Dosage was 1-8 are sorted in an ascending manner for the response (there was more than 1 row with dosage 1 for example), while also doing the same for Drug_2_Dosage (but without affecting the first one).

I want to sort them so that I can calculate the mean later and express it as a two-column matrix.

So far I have this:

import numpy as np

import pandas as pd



file = open('/Users/Envy/Desktop/Assingment Data.csv',"r")

table = pd.read_csv('/Users/Envy/Desktop/Assingment Data.csv')

drug_1_d = table.iloc[:,0]

drug_1_r = table.iloc[:,1]

drug_2_d = table.iloc[:,2]

drug_2_r = table.iloc[:,3]

Up to here everything is working fine as I can select each column independently. I tried the following for sorting to no success:

1) table = table.sort_values(['Dose drug 1', 'Dose drug 1'],ascending = True)

   table = pd.DataFrame.as_matrix(table)

   table = table[table[:,0].argsort()]

2) table.sort(order=['f1'],axis=0)

3) table.sort_values(['Dose drug 1', 'Dose drug 2'], ascending=[True])

4) table = table.sort_values([:0,:2],ascending=[True]) 

EDIT:

Hey, I did some prodding around and this works with the above code

table = table.sort_values(['Dose drug 1', 'Dose drug 1'],ascending = True)

table = pd.DataFrame.as_matrix(table) 

print(table) 

But it returns

[[ 1 21  3 27]

 [ 1 19  7 10]

 [ 1 32  3 12]

 ...

 [ 8 18  4 24]

 [ 8  9  1 10]

 [ 8 13  2  9]]

Meaning that it only sorts by column 0 and not by column 2 also like I want it. Any idea how I can have both sorts independently?

Edit: After a lot of trial and error I now have the solution;

#Generate average response to dosage in 2 column matrix

table = pd.read_csv('Assingment Data.csv', sep=',')

final_means = pd.DataFrame()

# Grouping by Drug 1

final_means['Average Response Drug'] = table.groupby(['Dose drug 1'])['Response drug 1'].mean()

# Grouping by Drug 2

final_means['Average Response Placebo'] = table.groupby(['Dose drug 2'])['Response drug 2'].mean()

final_means.index.names = ['Dose']

print(final_means)

Are you familiar with Pandas' groupby operation? This allows you to reason per group without having to explicitly sort your table

A simple example:

>>> import pandas as pd

>>> df = pd.DataFrame(data={'col1': [0,0,1,1], 'col2': [1,2,3,4]})

>>> df

   col1  col2

0     0     1

1     0     2

2     1     3

3     1     4

>>> df.groupby('col1').mean()

      col2

col1

0      1.5

1      3.5

(Thanks to @Sean-Pianka for suggesting I post my comment as a separate answer)