How to organize your data for use in Python: Boxplot example with statistical calculations.

import pandas 
fn = '../../data/stats_data.xlsx'
dirty_df = pandas.read_excel(fn, 'dirty_sheet')
clean_df = pandas.read_excel(fn, 'clean_sheet')


clean_df

clean_df['lung_weight_fraction'] = clean_df['lung_weight_g'] / clean_df['mouse_weight_g']

# Here we go through all possible combinations of 
# exposure time and dose and only add data that we 
# find.   
exposure_times = set(clean_df['exposure_time_h'])
dosages = set(clean_df['dose_ug'])

trial_dfs = {}
for et in exposure_times:
    for dose in dosages:

        # We use the query method to extract all the data from one trial into its
        # own DataFrame.
        query_string = 'exposure_time_h == {} and dose_ug == {}'.format(et, dose)
        print('Executing query: ', query_string)
        trial_df = clean_df.query(query_string)

        if trial_df.empty:
            print('  No data found for query: ', query_string)
            continue

        exp_key = '{}_{}'.format(et, dose)
        if exp_key == '0_0':
            exp_key = 'control'

        print('  Data found. Adding trial_df under the experiment_key: ', exp_key)

        trial_dfs[exp_key] = trial_df

Executing query:  exposure_time_h == 0 and dose_ug == 0
  Data found. Adding trial_df under the experiment_key:  control
Executing query:  exposure_time_h == 0 and dose_ug == 200
  No data found for query:  exposure_time_h == 0 and dose_ug == 200
Executing query:  exposure_time_h == 0 and dose_ug == 400
  No data found for query:  exposure_time_h == 0 and dose_ug == 400
Executing query:  exposure_time_h == 24 and dose_ug == 0
  No data found for query:  exposure_time_h == 24 and dose_ug == 0
Executing query:  exposure_time_h == 24 and dose_ug == 200
  Data found. Adding trial_df under the experiment_key:  24_200
Executing query:  exposure_time_h == 24 and dose_ug == 400
  No data found for query:  exposure_time_h == 24 and dose_ug == 400
Executing query:  exposure_time_h == 72 and dose_ug == 0
  No data found for query:  exposure_time_h == 72 and dose_ug == 0
Executing query:  exposure_time_h == 72 and dose_ug == 200
  Data found. Adding trial_df under the experiment_key:  72_200
Executing query:  exposure_time_h == 72 and dose_ug == 400
  Data found. Adding trial_df under the experiment_key:  72_400
Executing query:  exposure_time_h == 6 and dose_ug == 0
  No data found for query:  exposure_time_h == 6 and dose_ug == 0
Executing query:  exposure_time_h == 6 and dose_ug == 200
  Data found. Adding trial_df under the experiment_key:  6_200
Executing query:  exposure_time_h == 6 and dose_ug == 400
  No data found for query:  exposure_time_h == 6 and dose_ug == 400

%matplotlib inline
import matplotlib.pylab as plt

fig, ax1 = plt.subplots(figsize=(12, 4))

# We sort the DataFrames by their median values.
ordered_trials = sorted((df['lung_weight_fraction'].median(), exp_key) 
                         for exp_key, df in trial_dfs.items())

# We only keep the experimental keys.
labels = [exp_key for _, exp_key in ordered_trials]

# The boxplot method requires a list of lists, with each inner list 
# being the data from one trial.
X = [trial_dfs[exp_key]['lung_weight_fraction'] for exp_key in labels]

# The bootstrap keyword specifies the number of iterations used to 
# bootstrap the 95% confidance intervals for each box.
ax1.boxplot(X, bootstrap=5000)
ax1.set_ylabel('Fraction of weight that is lung.', fontsize=15)
ax1.set_xlabel('time(h)_dose($\mu$g) or control')
ax1.set_title('Lung tumor trials sorted by the median value of the fractional weight of lung.')

xtick_names = plt.setp(ax1, xticklabels=labels)
plt.setp(xtick_names, rotation=30)

plt.show()

import scipy.stats as stats

# We don't need to assume that the variances are the same.
control_and_first = stats.ttest_ind(X[0], X[1], equal_var=False)
control_and_last = stats.ttest_ind(X[1], X[-1], equal_var=False)

print('The p-value between control and trial 72_400 is: ', control_and_first[1])
print('The p-value between control and trial 72_200 is: ', control_and_last[1])

The p-value between control and trial 72_400 is:  0.0015037647302
The p-value between control and trial 72_200 is:  0.000135155817627