Sesac 32일차

Pandas

import pandas as pd

df = pd.read_csv('data/pew.csv')

df.head(2)

data = pd.melt(df,id_vars='religion', var_name='income', value_name='count')

data.head(2)

data.columns = ['religion', 'income', 'count']

data.sample()

data.rename(columns={"income": "소득영역", "count": "갯수"})

pd.read_csv('data/billboard.csv')

df = pd.read_csv('data/billboard.csv')

df.head(1)

df.columns

Index(['year', 'artist', 'track', 'time', 'date.entered', 'wk1', 'wk2', 'wk3',
       'wk4', 'wk5', 'wk6', 'wk7', 'wk8', 'wk9', 'wk10', 'wk11', 'wk12',
       'wk13', 'wk14', 'wk15', 'wk16', 'wk17', 'wk18', 'wk19', 'wk20', 'wk21',
       'wk22', 'wk23', 'wk24', 'wk25', 'wk26', 'wk27', 'wk28', 'wk29', 'wk30',
       'wk31', 'wk32', 'wk33', 'wk34', 'wk35', 'wk36', 'wk37', 'wk38', 'wk39',
       'wk40', 'wk41', 'wk42', 'wk43', 'wk44', 'wk45', 'wk46', 'wk47', 'wk48',
       'wk49', 'wk50', 'wk51', 'wk52', 'wk53', 'wk54', 'wk55', 'wk56', 'wk57',
       'wk58', 'wk59', 'wk60', 'wk61', 'wk62', 'wk63', 'wk64', 'wk65', 'wk66',
       'wk67', 'wk68', 'wk69', 'wk70', 'wk71', 'wk72', 'wk73', 'wk74', 'wk75',
       'wk76'],
      dtype='object')

pd.melt(df,id_vars=['year', 'artist', 'track', 'time', 'date.entered'], 
        var_name='week', 
        value_name='rating')

df.shape

(317, 81)

df_melt = pd.melt(df,id_vars=['year', 'artist', 'track', 'time', 'date.entered'], 
        var_name='week', 
        value_name='rating')

df_melt.shape

(24092, 7)

pd.read_csv('data/country_timeseries.csv')

df = pd.read_csv('data/country_timeseries.csv')

df.sample()

pd.melt(df,id_vars=['Date', 'Day'])

df_melt = pd.melt(df,id_vars=['Date', 'Day'])

df_melt

df_melt.variable

0       Cases_Guinea
1       Cases_Guinea
2       Cases_Guinea
3       Cases_Guinea
4       Cases_Guinea
            ...     
1947     Deaths_Mali
1948     Deaths_Mali
1949     Deaths_Mali
1950     Deaths_Mali
1951     Deaths_Mali
Name: variable, Length: 1952, dtype: object

# 항목 분리
df_melt.variable.str.split('_')

0       [Cases, Guinea]
1       [Cases, Guinea]
2       [Cases, Guinea]
3       [Cases, Guinea]
4       [Cases, Guinea]
             ...       
1947     [Deaths, Mali]
1948     [Deaths, Mali]
1949     [Deaths, Mali]
1950     [Deaths, Mali]
1951     [Deaths, Mali]
Name: variable, Length: 1952, dtype: object

df_melt['status'] = df_melt.variable.str.split('_').str.get(0)

df_melt['country'] = df_melt.variable.str.split('_').str.get(1)

df_melt.sample(3)

pd.read_csv('data/weather.csv')

df = pd.read_csv('data/weather.csv')

df.head(1)

pd.melt(df, 
        id_vars=['id', 'year', 'month', 'element'], 
        var_name='day', 
        value_name='temp')

df.columns

Index(['id', 'year', 'month', 'element', 'd1', 'd2', 'd3', 'd4', 'd5', 'd6',
       'd7', 'd8', 'd9', 'd10', 'd11', 'd12', 'd13', 'd14', 'd15', 'd16',
       'd17', 'd18', 'd19', 'd20', 'd21', 'd22', 'd23', 'd24', 'd25', 'd26',
       'd27', 'd28', 'd29', 'd30', 'd31'],
      dtype='object')

df_melt = pd.melt(df, 
        id_vars=['id', 'year', 'month', 'element'], 
        var_name='day', 
        value_name='temp')

df_melt.pivot_table(index=['id', 'year', 'month', 'day'],
                   columns='element',
                   values='temp')

df_pivot = df_melt.pivot_table(index=['id', 'year', 'month', 'day'],
                               columns='element',
                               values='temp')

# 주피터 노트북 단축키 정리
https://taptorestart.tistory.com/entry/Jupyter-Notebook-%EC%A3%BC%ED%94%BC%ED%84%B0-%EB%85%B8%ED%8A%B8%EB%B6%81-%EB%8B%A8%EC%B6%95%ED%82%A4shortcuts-%EC%A0%95%EB%A6%AC

df_pivot.index

MultiIndex([('MX17004', 2010,  1, 'd30'),
            ('MX17004', 2010,  2, 'd11'),
            ('MX17004', 2010,  2,  'd2'),
            ('MX17004', 2010,  2, 'd23'),
            ('MX17004', 2010,  2,  'd3'),
            ('MX17004', 2010,  3, 'd10'),
            ('MX17004', 2010,  3, 'd16'),
            ('MX17004', 2010,  3,  'd5'),
            ('MX17004', 2010,  4, 'd27'),
            ('MX17004', 2010,  5, 'd27'),
            ('MX17004', 2010,  6, 'd17'),
            ('MX17004', 2010,  6, 'd29'),
            ('MX17004', 2010,  7,  'd3'),
            ('MX17004', 2010,  7, 'd14'),
            ('MX17004', 2010,  8, 'd23'),
            ('MX17004', 2010,  8,  'd5'),
            ('MX17004', 2010,  8, 'd29'),
            ('MX17004', 2010,  8, 'd13'),
            ('MX17004', 2010,  8, 'd25'),
            ('MX17004', 2010,  8, 'd31'),
            ('MX17004', 2010,  8,  'd8'),
            ('MX17004', 2010, 10,  'd5'),
            ('MX17004', 2010, 10, 'd14'),
            ('MX17004', 2010, 10, 'd15'),
            ('MX17004', 2010, 10, 'd28'),
            ('MX17004', 2010, 10,  'd7'),
            ('MX17004', 2010, 11,  'd2'),
            ('MX17004', 2010, 11,  'd5'),
            ('MX17004', 2010, 11, 'd27'),
            ('MX17004', 2010, 11, 'd26'),
            ('MX17004', 2010, 11,  'd4'),
            ('MX17004', 2010, 12,  'd1'),
            ('MX17004', 2010, 12,  'd6')],
           names=['id', 'year', 'month', 'day'])

df_pivot.columns

Index(['tmax', 'tmin'], dtype='object', name='element')

df_pivot.values

array([[27.8, 14.5],
       [29.7, 13.4],
       [27.3, 14.4],
       [29.9, 10.7],
       [24.1, 14.4],
       [34.5, 16.8],
       [31.1, 17.6],
       [32.1, 14.2],
       [36.3, 16.7],
       [33.2, 18.2],
       [28. , 17.5],
       [30.1, 18. ],
       [28.6, 17.5],
       [29.9, 16.5],
       [26.4, 15. ],
       [29.6, 15.8],
       [28. , 15.3],
       [29.8, 16.5],
       [29.7, 15.6],
       [25.4, 15.4],
       [29. , 17.3],
       [27. , 14. ],
       [29.5, 13. ],
       [28.7, 10.5],
       [31.2, 15. ],
       [28.1, 12.9],
       [31.3, 16.3],
       [26.3,  7.9],
       [27.7, 14.2],
       [28.1, 12.1],
       [27.2, 12. ],
       [29.9, 13.8],
       [27.8, 10.5]])

# inplace=True값으로 원본에 적용
df_pivot.reset_index(inplace=True)

df_pivot.values

array([['MX17004', 2010, 1, 'd30', 27.8, 14.5],
       ['MX17004', 2010, 2, 'd11', 29.7, 13.4],
       ['MX17004', 2010, 2, 'd2', 27.3, 14.4],
       ['MX17004', 2010, 2, 'd23', 29.9, 10.7],
       ['MX17004', 2010, 2, 'd3', 24.1, 14.4],
       ['MX17004', 2010, 3, 'd10', 34.5, 16.8],
       ['MX17004', 2010, 3, 'd16', 31.1, 17.6],
       ['MX17004', 2010, 3, 'd5', 32.1, 14.2],
       ['MX17004', 2010, 4, 'd27', 36.3, 16.7],
       ['MX17004', 2010, 5, 'd27', 33.2, 18.2],
       ['MX17004', 2010, 6, 'd17', 28.0, 17.5],
       ['MX17004', 2010, 6, 'd29', 30.1, 18.0],
       ['MX17004', 2010, 7, 'd3', 28.6, 17.5],
       ['MX17004', 2010, 7, 'd14', 29.9, 16.5],
       ['MX17004', 2010, 8, 'd23', 26.4, 15.0],
       ['MX17004', 2010, 8, 'd5', 29.6, 15.8],
       ['MX17004', 2010, 8, 'd29', 28.0, 15.3],
       ['MX17004', 2010, 8, 'd13', 29.8, 16.5],
       ['MX17004', 2010, 8, 'd25', 29.7, 15.6],
       ['MX17004', 2010, 8, 'd31', 25.4, 15.4],
       ['MX17004', 2010, 8, 'd8', 29.0, 17.3],
       ['MX17004', 2010, 10, 'd5', 27.0, 14.0],
       ['MX17004', 2010, 10, 'd14', 29.5, 13.0],
       ['MX17004', 2010, 10, 'd15', 28.7, 10.5],
       ['MX17004', 2010, 10, 'd28', 31.2, 15.0],
       ['MX17004', 2010, 10, 'd7', 28.1, 12.9],
       ['MX17004', 2010, 11, 'd2', 31.3, 16.3],
       ['MX17004', 2010, 11, 'd5', 26.3, 7.9],
       ['MX17004', 2010, 11, 'd27', 27.7, 14.2],
       ['MX17004', 2010, 11, 'd26', 28.1, 12.1],
       ['MX17004', 2010, 11, 'd4', 27.2, 12.0],
       ['MX17004', 2010, 12, 'd1', 29.9, 13.8],
       ['MX17004', 2010, 12, 'd6', 27.8, 10.5]], dtype=object)

pd.read_csv('data/billboard.csv')

df = pd.read_csv('data/billboard.csv')

df.sample()

df.columns

Index(['year', 'artist', 'track', 'time', 'date.entered', 'wk1', 'wk2', 'wk3',
       'wk4', 'wk5', 'wk6', 'wk7', 'wk8', 'wk9', 'wk10', 'wk11', 'wk12',
       'wk13', 'wk14', 'wk15', 'wk16', 'wk17', 'wk18', 'wk19', 'wk20', 'wk21',
       'wk22', 'wk23', 'wk24', 'wk25', 'wk26', 'wk27', 'wk28', 'wk29', 'wk30',
       'wk31', 'wk32', 'wk33', 'wk34', 'wk35', 'wk36', 'wk37', 'wk38', 'wk39',
       'wk40', 'wk41', 'wk42', 'wk43', 'wk44', 'wk45', 'wk46', 'wk47', 'wk48',
       'wk49', 'wk50', 'wk51', 'wk52', 'wk53', 'wk54', 'wk55', 'wk56', 'wk57',
       'wk58', 'wk59', 'wk60', 'wk61', 'wk62', 'wk63', 'wk64', 'wk65', 'wk66',
       'wk67', 'wk68', 'wk69', 'wk70', 'wk71', 'wk72', 'wk73', 'wk74', 'wk75',
       'wk76'],
      dtype='object')

pd.melt(df, 
        id_vars=['year', 'artist', 'track', 'time', 'date.entered'], 
        var_name='week',
        value_name='rating')

df_melt = pd.melt(df, 
        id_vars=['year', 'artist', 'track', 'time', 'date.entered'], 
        var_name='week',
        value_name='rating')

df_melt

df.shape, df_melt.shape

((317, 81), (24092, 7))

df_melt[ df_melt.track == 'loser']

df_songs = df_melt[['year', 'artist', 'track', 'time']]

df_songs.shape

(24092, 4)

df_songs.drop_duplicates(inplace=True)

C:\Users\user\AppData\Local\Temp\ipykernel_7456\1127336564.py:1: SettingWithCopyWarning: 
A value is trying to be set on a copy of a slice from a DataFrame

See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy
  df_songs.drop_duplicates(inplace=True)

df_songs.shape

(317, 4)

df_songs['id']=range(len(df_songs))

C:\Users\user\AppData\Local\Temp\ipykernel_7456\1922217867.py:1: SettingWithCopyWarning: 
A value is trying to be set on a copy of a slice from a DataFrame.
Try using .loc[row_indexer,col_indexer] = value instead

See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy
  df_songs['id']=range(len(df_songs))

df_songs.head()

df_melt.merge(df_songs, on=['year', 'artist', 'track', 'time'])

df_ratings = df_melt.merge(df_songs, on=['year', 'artist', 'track', 'time'])

df_ratings

df_ratings = df_ratings[['date.entered', 'week', 'rating']]

df_ratings

df_songs

---

import pandas as pd

pd.read_csv('전국_평균_분양가격(2013년_9월부터_2015년_8월까지).csv', encoding = 'cp949')
# 보통 한글폰트가 깨지는데 뒤에 인코딩 cp94를 넣으면 대부분 해결된다.

first_df = pd.read_csv('전국_평균_분양가격(2013년_9월부터_2015년_8월까지).csv', encoding = 'cp949')

first_df.head(2)

first_df.info()

<class 'pandas.core.frame.DataFrame'>
RangeIndex: 17 entries, 0 to 16
Data columns (total 22 columns):
 #   Column    Non-Null Count  Dtype 
---  ------    --------------  ----- 
 0   지역        17 non-null     object
 1   2013년12월  17 non-null     int64 
 2   2014년1월   17 non-null     int64 
 3   2014년2월   17 non-null     int64 
 4   2014년3월   17 non-null     int64 
 5   2014년4월   17 non-null     int64 
 6   2014년5월   17 non-null     int64 
 7   2014년6월   17 non-null     int64 
 8   2014년7월   17 non-null     int64 
 9   2014년8월   17 non-null     int64 
 10  2014년9월   17 non-null     int64 
 11  2014년10월  17 non-null     int64 
 12  2014년11월  17 non-null     int64 
 13  2014년12월  17 non-null     int64 
 14  2015년1월   17 non-null     int64 
 15  2015년2월   17 non-null     int64 
 16  2015년3월   17 non-null     int64 
 17  2015년4월   17 non-null     int64 
 18  2015년5월   17 non-null     int64 
 19  2015년6월   17 non-null     int64 
 20  2015년7월   17 non-null     int64 
 21  2015년8월   17 non-null     int64 
dtypes: int64(21), object(1)
memory usage: 3.0+ KB

pd.read_csv('주택도시보증공사_전국_평균_분양가격(2019년_12월).csv', encoding = 'cp949')

last_df = pd.read_csv('주택도시보증공사_전국_평균_분양가격(2019년_12월).csv', encoding = 'cp949')

last_df.info()

<class 'pandas.core.frame.DataFrame'>
RangeIndex: 4335 entries, 0 to 4334
Data columns (total 5 columns):
 #   Column   Non-Null Count  Dtype 
---  ------   --------------  ----- 
 0   지역명      4335 non-null   object
 1   규모구분     4335 non-null   object
 2   연도       4335 non-null   int64 
 3   월        4335 non-null   int64 
 4   분양가격(㎡)  4058 non-null   object
dtypes: int64(2), object(3)
memory usage: 169.5+ KB

last_df.isna().sum()

지역명          0
규모구분         0
연도           0
월            0
분양가격(㎡)    277
dtype: int64

pd.to_numeric(last_df['분양가격(㎡)'])

---------------------------------------------------------------------------

ValueError                                Traceback (most recent call last)

~\anaconda3\lib\site-packages\pandas\_libs\lib.pyx in pandas._libs.lib.maybe_convert_numeric()


ValueError: Unable to parse string "  "


During handling of the above exception, another exception occurred:


ValueError                                Traceback (most recent call last)

~\AppData\Local\Temp\ipykernel_4668\3953485802.py in <module>
----> 1 pd.to_numeric(last_df['분양가격(㎡)'])


~\anaconda3\lib\site-packages\pandas\core\tools\numeric.py in to_numeric(arg, errors, downcast)
    182         coerce_numeric = errors not in ("ignore", "raise")
    183         try:
--> 184             values, _ = lib.maybe_convert_numeric(
    185                 values, set(), coerce_numeric=coerce_numeric
    186             )


~\anaconda3\lib\site-packages\pandas\_libs\lib.pyx in pandas._libs.lib.maybe_convert_numeric()


ValueError: Unable to parse string "  " at position 28

from numpy import nan

type(nan)

float

pd.to_numeric(last_df['분양가격(㎡)'],errors='coerce')

0       5841.0
1       5652.0
2       5882.0
3       5721.0
4       5879.0
         ...  
4330    3882.0
4331       NaN
4332    3898.0
4333       NaN
4334    3601.0
Name: 분양가격(㎡), Length: 4335, dtype: float64

last_df['분양가격'] = pd.to_numeric(last_df['분양가격(㎡)'],errors='coerce')

last_df.info()

<class 'pandas.core.frame.DataFrame'>
RangeIndex: 4335 entries, 0 to 4334
Data columns (total 6 columns):
 #   Column   Non-Null Count  Dtype  
---  ------   --------------  -----  
 0   지역명      4335 non-null   object 
 1   규모구분     4335 non-null   object 
 2   연도       4335 non-null   int64  
 3   월        4335 non-null   int64  
 4   분양가격(㎡)  4058 non-null   object 
 5   분양가격     3957 non-null   float64
dtypes: float64(1), int64(2), object(3)
memory usage: 203.3+ KB

last_df['분양가격']*3.3

0       19275.3
1       18651.6
2       19410.6
3       18879.3
4       19400.7
         ...   
4330    12810.6
4331        NaN
4332    12863.4
4333        NaN
4334    11883.3
Name: 분양가격, Length: 4335, dtype: float64

last_df['평당분양가격'] = last_df['분양가격']*3.3

last_df['평당분양가격'].describe()

count     3957.000000
mean     10685.824488
std       4172.222780
min       6164.400000
25%       8055.300000
50%       9484.200000
75%      11751.300000
max      42002.400000
Name: 평당분양가격, dtype: float64

last_df

last_df.규모구분.unique()

array(['전체', '전용면적 60㎡이하', '전용면적 60㎡초과 85㎡이하', '전용면적 85㎡초과 102㎡이하',
       '전용면적 102㎡초과'], dtype=object)

last_df.규모구분.str.replace('전용면적','')

0                  전체
1               60㎡이하
2         60㎡초과 85㎡이하
3        85㎡초과 102㎡이하
4              102㎡초과
            ...      
4330               전체
4331            60㎡이하
4332      60㎡초과 85㎡이하
4333     85㎡초과 102㎡이하
4334           102㎡초과
Name: 규모구분, Length: 4335, dtype: object

last_df['전용면적'] = last_df.규모구분.str.replace('전용면적','')

last_df['전용면적'] = last_df['전용면적'].str.replace('초과','~')

last_df['전용면적'] = last_df['전용면적'].str.replace('이하','')

last_df['전용면적'] = last_df['전용면적'].str.replace(' ','').str.strip()

last_df

last_df.columns

Index(['지역명', '규모구분', '연도', '월', '분양가격(㎡)', '분양가격', '평당분양가격', '전용면적'], dtype='object')

last_df.drop(columns=['규모구분', '분양가격(㎡)'],inplace=True)

last_df

last_df.groupby(['지역명'])['평당분양가격'].mean()

지역명
강원     7890.750000
경기    13356.895200
경남     9268.778138
경북     8376.536515
광주     9951.535821
대구    11980.895455
대전    10253.333333
부산    12087.121200
서울    23599.976400
세종     9796.516456
울산    10014.902013
인천    11915.320732
전남     7565.316532
전북     7724.235484
제주    11241.276712
충남     8233.651883
충북     7634.655600
Name: 평당분양가격, dtype: float64

last_df.groupby(['지역명'])['평당분양가격'].mean().plot(kind='bar')

<AxesSubplot:xlabel='지역명'>

last_df.groupby(['지역명'])['평당분양가격'].mean().plot(kind='barh')

<AxesSubplot:ylabel='지역명'>

last_df.groupby(['전용면적'])['평당분양가격'].mean()

전용면적
102㎡~       11517.705634
60㎡         10375.137421
60㎡~85㎡     10271.040071
85㎡~102㎡    11097.599573
전체          10276.086207
Name: 평당분양가격, dtype: float64

last_df.groupby(['전용면적','지역명'])['평당분양가격'].mean()

전용면적   지역명
102㎡~  강원      8311.380000
       경기     14771.790000
       경남     10358.363265
       경북      9157.302000
       광주     11041.532432
                  ...     
전체     전남      7283.562000
       전북      7292.604000
       제주     10784.994000
       충남      7815.324000
       충북      7219.014000
Name: 평당분양가격, Length: 85, dtype: float64

last_df.groupby(['전용면적','지역명'])['평당분양가격'].mean().reset_index()

data = last_df.groupby(['전용면적','지역명'])['평당분양가격'].mean().reset_index()

data.round()

data.round(-1)

pd.pivot_table(last_df,index=['전용면적','지역명'],values=['평당분양가격']).reset_index().round()