In the dataset below, I want to write a function that does the following:
compares fuel consumption
mpgofNo_of_cylindersfor each uniquemodel.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.
CodePudding user response：
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
CodePudding user response：
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
CodePudding user response：
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