import pandas as pd
import numpy as np

mpg=pd.read_csv('mpg.csv')
mpg.head()

#변수 속성 및 데이터 타입 출력
mpg.info()

    <class 'pandas.core.frame.DataFrame'>
    RangeIndex: 234 entries, 0 to 233
    Data columns (total 11 columns):
     #   Column        Non-Null Count  Dtype  
    ---  ------        --------------  -----  
     0   manufacturer  234 non-null    object 
     1   model         234 non-null    object 
     2   displ         234 non-null    float64
     3   year          234 non-null    int64  
     4   cyl           234 non-null    int64  
     5   trans         234 non-null    object 
     6   drv           234 non-null    object 
     7   cty           234 non-null    int64  
     8   hwy           234 non-null    int64  
     9   fl            234 non-null    object 
     10  category      234 non-null    object 
    dtypes: float64(1), int64(4), object(6)
    memory usage: 20.2+ KB

#요약통계
mpg.describe()

#변수의 이름 바꾸기
mpg.rename(columns={'manufacturer' : '제조_회사','displ' : '배기량','cyl' : '실린더_개수','drv' : '구동_방식','hwy' : '고속도로_연비','class' : '자동차_종류','model' : '자동차_모델명','year' : '생산연도','trans' : '변속기_종류','cty' : '도시_연비','fl' : '연료_종류','category':'카테고리'}, inplace=True)
mpg

data1=mpg.sort_values('배기량',ascending=False)[['배기량','실린더_개수']].reset_index()
data1

t={}
for i in range(len(data1)):
    x=data1['배기량'][i]
    y=data1['실린더_개수'][i]
    if x not in t:
        t[x]=y
    else:
        continue
print(t)

    {7.0: 8, 6.5: 8, 6.2: 8, 6.1: 8, 6.0: 8, 5.9: 8, 5.7: 8, 5.6: 8, 5.4: 8, 5.3: 8, 5.2: 8, 5.0: 8, 4.7: 8, 4.6: 8, 4.4: 8, 4.2: 6, 4.0: 6, 3.9: 6, 3.8: 6, 3.7: 6, 3.6: 6, 3.5: 6, 3.4: 6, 3.3: 6, 3.1: 6, 3.0: 6, 2.8: 6, 2.7: 6, 2.5: 4, 2.4: 4, 2.2: 4, 2.0: 4, 1.9: 4, 1.8: 4, 1.6: 4}

data2=pd.DataFrame({'배기량':t.keys(), '실린더_개수':t.values()})
data2.sort_values('배기량',ascending=False)

mpg['average_y']=mpg['도시_연비']*0.55+mpg['고속도로_연비']*0.45
mpg.head()

mpg.groupby('자동차_모델명').agg(best=('average_y','max'))

mpg.groupby('자동차_모델명').agg(best=('average_y','max')).max()

    best    39.05
    dtype: float64

mpg['aver_y']=(mpg['도시_연비']+mpg['고속도로_연비'])/2
mpg.head()

mpg.groupby('자동차_모델명').agg(best=('aver_y','max'))

mpg.groupby('자동차_모델명').agg(best=('aver_y','max')).max()

    best    39.5
    dtype: float64

mpg.query('제조_회사=="audi" & 자동차_모델명=="a4"')['aver_y'].mean()

    23.571428571428573

mpg.query('제조_회사=="audi" & 자동차_모델명=="a4"')['average_y'].mean()

    23.1

mpg.groupby('카테고리').agg(mean_t=("도시_연비","max")).query('카테고리=="midsize"')

mpg.query('도시_연비==23 & 카테고리=="midsize"')

mpg.groupby('카테고리').agg(mean_t=("고속도로_연비","max")).query('카테고리=="midsize"')

mpg.query('고속도로_연비==32 & 카테고리=="midsize"')

mpg.groupby('카테고리').agg(mean_t=("average_y","max")).query('카테고리=="midsize"')

mpg.query('average_y==27.05 & 카테고리=="midsize"')

mpg.groupby('카테고리').agg(mean_t=("aver_y","max")).query('카테고리=="midsize"')

mpg.query('aver_y==27.5 & 카테고리=="midsize"')

mpg.groupby('카테고리').agg(min=('배기량','min'),
                       max=('배기량','max'),
                       mean=('배기량','mean'))

t1=mpg.query('제조_회사=="audi" | 제조_회사=="volkswagen"').agg(germany=("average_y","max"))
t1

t2=mpg.query('제조_회사=="chevrolet" | 제조_회사=="dodge" | 제조_회사=="ford" | 제조_회사=="jeep" | 제조_회사=="lincoln"| 제조_회사=="mercury" | 제조_회사=="pontiac"').agg(usa=("average_y","max"))
t2

t3=mpg.query('제조_회사=="honda" | 제조_회사=="nissan"| 제조_회사=="subaru"| 제조_회사=="toyota"').agg(japan=("average_y","max"))
t3

t4=mpg.query('제조_회사=="hyundai"').agg(korea=("average_y","max"))
t4

t5=mpg.query('제조_회사=="land rover"').agg(uk=("average_y","max"))
t5

pd.concat([t1,t2,t3,t4,t5]).sort_values('average_y',ascending=False)

mpg.query('제조_회사=="audi" | 제조_회사=="volkswagen"').sort_values('average_y',ascending=False).head()

mpg.query('생산연도 == 1999').sort_values(['배기량','average_y'],ascending=[False,False]).head(10)

mpg.query('생산연도 == 2008').sort_values(['배기량','average_y'],ascending=[False,False]).head(10)

mpg['구동_방식'].unique()

    array(['f', '4', 'r'], dtype=object)

mpg.query('구동_방식=="4"')['average_y'].unique()

mpg.query('구동_방식=="4"').agg(mean=("average_y","mean"))

mpg['제조_회사'].unique()

    array(['audi', 'chevrolet', 'dodge', 'ford', 'honda', 'hyundai', 'jeep',
           'land rover', 'lincoln', 'mercury', 'nissan', 'pontiac', 'subaru',
           'toyota', 'volkswagen'], dtype=object)

mpg.query('제조_회사=="hyundai"').agg(small=('배기량','min'))

mpg.query('제조_회사=="hyundai" & 배기량 ==2')

mpg['연료_종류'].unique()

    array(['p', 'r', 'e', 'd', 'c'], dtype=object)

mpg.query('연료_종류=="e"').agg(best=("average_y","max"))

mpg.query('연료_종류=="e" & average_y>=13.7')

mpg[mpg['도시_연비']<mpg['고속도로_연비']]