Function that does groupby and stats analysis on a Pandas dataframe

user_14271017 0
Viewed 63

In the dataset below, I want to write a function that does the following:

  1. compares fuel consumption mpg of No_of_cylinders for each unique model.

  2. export all results (6 models) as a single csv file

# libraries
import pandas as pd
import numpy as np
from statsmodels.stats.multicomp import pairwise_tukeyhsd

# dataset 
df= {
    'Year':[2010,2010,2010,2010,2010,2010,2010,2010,2010,2010,2010,2010,2010,2010,2010,2010,2010,2010,2010,2010,2010,2010,2010,2010,2010,2010,2010,2010,2010,2010,2010,2010,2010,2010,2010,2010],
    'Car_brand':['Honda','Honda','Honda','Honda','Honda','Honda','Toyota','Toyota','Toyota','Toyota','Toyota','Toyota','benz','benz','benz','benz','benz','benz','audi','audi','audi','audi','audi','audi','Honda','Honda','Honda','Honda','Honda','Honda','Toyota','Toyota','Toyota','Toyota','Toyota','Toyota'],
    'model':['AA','AB','AC','AD','AE','AF','AA','AB','AC','AD','AE','AF','AA','AB','AC','AD','AE','AF','AA','AB','AC','AD','AE','AF','AA','AB','AC','AD','AE','AF','AA','AB','AC','AD','AE','AF'],
    'No_of_cylinders':[4,4,4,4,4,4,4,4,4,4,4,4,6,6,6,6,6,6,12,12,12,12,12,12,4,4,4,4,4,4,12,12,12,12,12,12],
    'mpg':[100.78,112.34,108.52,139.19,149.02,177.77,79.18,89.10,106.78,102.34,128.52,119.19,129.02,147.77,169.18,170.11,134.56,176.1,112.0,123,124,523,112,334.5,55,449,221,332,235.6,239,235,223.7,235,204,315,614],
     
}
df = pd.DataFrame(df,columns = ['Year','No_of_cylinders','Car_brand','model','mpg'])
df


from statsmodels.stats.multicomp import pairwise_tukeyhsd
# perform Tukey's test 
def tukeyHD(data,cont,cat):
    tukeyResult= pairwise_tukeyhsd(endog=data[cont],groups=data[cat],alpha=0.05)
    print(tukeyResult)
    
    
tukeyHD(df,'mpg','No_of_cylinders')

The code above runs tukeyHD on the entire dataset, I want to run a tukeyHD on each unique model (AA, AB,...., AF)

My attempt below:

def tukeyHD(data,cont,cat):
    for group in data.groupby('model'):

       tukeyResult= pairwise_tukeyhsd(data[cont],data[cat],alpha=0.05)
       print(tukeyResult) # Preferrably want to export all 6 results in 1 csv file
       tukeyResult.to_csv('tukeyresults05122022.csv',index = False)
    
tukeyHD(df,'mpg','No_of_cylinders')

Things to be aware of

No_of_cylinder has 3 groups - 4, 6,12

mpg is a the value we are using for the cylinder comparison

model - has 6 distinct groups - AA, AB, AC, AD, AE, AF

Please share your full code with comments for better understanding.....Thanks for your attempt.

statsmodels pandas for-loop function
3 Answers

As pairwise_tukeyhsd returns a TukeyHSDResults instance, you can extract data from .results_table.data. For each group, the first element of the data is the header and other elements are values. You can reconstruct a DataFrame for each group with a dict comprehension:

def tukeyHD(data, cont, cat):
    tukeyResult = pairwise_tukeyhsd(endog=data[cont], groups=data[cat], alpha=0.05)
    return pd.DataFrame({k: v for k, *v in zip(*tukeyResult._results_table.data)})
    
out = (df.groupby('model').apply(tukeyHD, cont='mpg', cat='No_of_cylinders')
         .droplevel(1).reset_index())
print(out)

# Output
   model  group1  group2  meandiff   p-adj     lower      upper  reject
0     AA       4       6   50.7000  0.7166 -207.5929   308.9929   False
1     AA       4      12   95.1800  0.2714 -109.0185   299.3785   False
2     AA       6      12   44.4800  0.7851 -229.4810   318.4410   False
3     AB       4       6  -69.0433  0.9000 -886.1744   748.0877   False
4     AB       4      12  -43.4633  0.9000 -689.4621   602.5355   False
5     AB       6      12   25.5800  0.9000 -841.1183   892.2783   False
6     AC       4       6   23.7467  0.9000 -313.9966   361.4900   False
7     AC       4      12   34.0667  0.8553 -232.9429   301.0762   False
8     AC       6      12   10.3200  0.9000 -347.9109   368.5509   False
9     AD       4       6  -21.0667  0.9000 -814.6350   772.5017   False
10    AD       4      12  172.3233  0.5585 -455.0475   799.6942   False
11    AD       6      12  193.3900  0.6491 -648.3163  1035.0963   False
12    AE       4       6  -36.4867  0.9000 -494.3427   421.3693   False
13    AE       4      12   42.4533  0.8760 -319.5136   404.4203   False
14    AE       6      12   78.9400  0.7847 -406.6896   564.5696   False
15    AF       4       6   -2.5533  0.9000 -600.9983   595.8916   False
16    AF       4      12  295.5967  0.1535 -177.5156   768.7090   False
17    AF       6      12  298.1500  0.2677 -336.5967   932.8967   False
user_152399510

One alternative would be to change your code to: Create first a list of models:

list_model = list(set(df.model))

and then

def tukeyHD(data,cont,cat):
    for group in list_model:
        data = df[df.model==group]

        tukeyResult= pairwise_tukeyhsd(data[cont],data[cat],alpha=0.05)
   
        a = pd.DataFrame(data=tukeyResult._results_table.data[1:], columns=tukeyResult._results_table.data[0])
        print(a)
        a.to_csv("dfj.csv")

    
tukeyHD(df,'mpg','No_of_cylinders')

All result tables end up in the same csv and look like this:

   group1  group2  meandiff   p-adj     lower     upper  reject
0       4       6   -2.5533  0.9998 -601.5903  596.4837   False
1       4      12  295.5967  0.1538 -177.9837  769.1770   False
2       6      12  298.1500  0.2680 -337.2247  933.5247   False
   group1  group2  meandiff   p-adj     lower     upper  reject
0       4       6   23.7467  0.9543 -314.3308  361.8241   False
1       4      12   34.0667  0.8619 -233.2070  301.3403   False
2       6      12   10.3200  0.9921 -348.2653  368.9053   False
   group1  group2  meandiff   p-adj     lower     upper  reject
0       4       6  -69.0433  0.9352 -886.9827  748.8961   False
1       4      12  -43.4633  0.9580 -690.1012  603.1745   False
2       6      12   25.5800  0.9917 -841.9758  893.1358   False
   group1  group2  meandiff   p-adj     lower     upper  reject
0       4       6  -36.4867  0.9420 -494.7956  421.8223   False
1       4      12   42.4533  0.8811 -319.8717  404.7784   False
2       6      12   78.9400  0.7912 -407.1700  565.0500   False
   group1  group2  meandiff   p-adj     lower      upper  reject
0       4       6  -21.0667  0.9933 -815.4201   773.2867   False
1       4      12  172.3233  0.5544 -455.6682   800.3148   False
2       6      12  193.3900  0.6466 -649.1490  1035.9290   False
   group1  group2  meandiff   p-adj     lower     upper  reject
0       4       6     50.70  0.7187 -207.8484  309.2484   False
1       4      12     95.18  0.2716 -109.2205  299.5805   False
2       6      12     44.48  0.7916 -229.7520  318.7120   False
i = df.col1

Maybe better to save them individually.

After the excellent suggestion from @Corralien, you could do this:

def tukeyHD(data,cont,cat):
    Groups_data =[]
    for group in list_model:
        
        data = df[df.model==group]

        tukeyResult= pairwise_tukeyhsd(data[cont],data[cat],alpha=0.05)
       
        a = pd.DataFrame(data=tukeyResult._results_table.data[1:], columns=tukeyResult._results_table.data[0])
        a['model'] = group
        Groups_data.append(a)
    gs = pd.concat(Groups_data)
    gs.to_csv("dfj.csv")

    
tukeyHD(df,'mpg','No_of_cylinders')

which returna single dataframe with a mention of the model.

  group1  group2  meandiff   p-adj     lower      upper  reject model
0       4       6   -2.5533  0.9998 -601.5903   596.4837   False    AF
1       4      12  295.5967  0.1538 -177.9837   769.1770   False    AF
2       6      12  298.1500  0.2680 -337.2247   933.5247   False    AF
0       4       6   23.7467  0.9543 -314.3308   361.8241   False    AC
1       4      12   34.0667  0.8619 -233.2070   301.3403   False    AC
2       6      12   10.3200  0.9921 -348.2653   368.9053   False    AC
0       4       6  -69.0433  0.9352 -886.9827   748.8961   False    AB
1       4      12  -43.4633  0.9580 -690.1012   603.1745   False    AB
2       6      12   25.5800  0.9917 -841.9758   893.1358   False    AB
0       4       6  -36.4867  0.9420 -494.7956   421.8223   False    AE
1       4      12   42.4533  0.8811 -319.8717   404.7784   False    AE
2       6      12   78.9400  0.7912 -407.1700   565.0500   False    AE
0       4       6  -21.0667  0.9933 -815.4201   773.2867   False    AD
1       4      12  172.3233  0.5544 -455.6682   800.3148   False    AD
2       6      12  193.3900  0.6466 -649.1490  1035.9290   False    AD
0       4       6   50.7000  0.7187 -207.8484   309.2484   False    AA
1       4      12   95.1800  0.2716 -109.2205   299.5805   False    AA
2       6      12   44.4800  0.7916 -229.7520   318.7120   False    AA
user_53636860

I'd concat all the data together to a df and then export it to csv

def tukeyHD(data,cont,cat):
    df = pd.DataFrame()
    for grp, grouped_data in data.groupby('model'):
        tukeyResult= pairwise_tukeyhsd(grouped_data[cont],grouped_data[cat],alpha=0.05)
        cols = tukeyResult.summary().data[0]
        vals = tukeyResult.summary().data[1:]
        s = pd.DataFrame(vals, columns=cols, index=[grp]*len(vals))
        df = pd.concat([df, s])
        #tukeyResult.to_csv('tukeyresults05122022.csv',index = False)
    print(df)
    df.to_csv('name_your_csv.csv')
tukeyHD(df,'mpg','No_of_cylinders')

    group1  group2  meandiff   p-adj     lower      upper  reject
AA       4       6   50.7000  0.7166 -207.5929   308.9929   False
AA       4      12   95.1800  0.2714 -109.0185   299.3785   False
AA       6      12   44.4800  0.7851 -229.4810   318.4410   False
AB       4       6  -69.0433  0.9000 -886.1744   748.0877   False
AB       4      12  -43.4633  0.9000 -689.4621   602.5355   False
AB       6      12   25.5800  0.9000 -841.1183   892.2783   False
AC       4       6   23.7467  0.9000 -313.9966   361.4900   False
AC       4      12   34.0667  0.8553 -232.9429   301.0762   False
AC       6      12   10.3200  0.9000 -347.9109   368.5509   False
AD       4       6  -21.0667  0.9000 -814.6350   772.5017   False
AD       4      12  172.3233  0.5585 -455.0475   799.6942   False
AD       6      12  193.3900  0.6491 -648.3163  1035.0963   False
AE       4       6  -36.4867  0.9000 -494.3427   421.3693   False
AE       4      12   42.4533  0.8760 -319.5136   404.4203   False
AE       6      12   78.9400  0.7847 -406.6896   564.5696   False
AF       4       6   -2.5533  0.9000 -600.9983   595.8916   False
AF       4      12  295.5967  0.1535 -177.5156   768.7090   False
AF       6      12  298.1500  0.2677 -336.5967   932.8967   False
user_155213920
Related