Pandas

マッピング　vlookupに似たもの

2017年11月29日 by 河副太智 Leave a Comment

<span class="cm-keyword">import</span> <span class="cm-variable">pandas</span> <span class="cm-keyword">as</span> <span class="cm-variable">pd</span>
<span class="cm-keyword">from</span> <span class="cm-variable">pandas</span> <span class="cm-keyword">import</span> <span class="cm-variable">DataFrame</span>

<span class="cm-variable">attri_data1</span> = {<span class="cm-string">'ID'</span>: [<span class="cm-string">'100'</span>, <span class="cm-string">'101'</span>, <span class="cm-string">'102'</span>, <span class="cm-string">'103'</span>, <span class="cm-string">'104'</span>, <span class="cm-string">'106'</span>, <span class="cm-string">'108'</span>, <span class="cm-string">'110'</span>, <span class="cm-string">'111'</span>, <span class="cm-string">'113'</span>]
        ,<span class="cm-string">'city'</span>: [<span class="cm-string">'Tokyo'</span>, <span class="cm-string">'Osaka'</span>, <span class="cm-string">'Kyoto'</span>, <span class="cm-string">'Hokkaido'</span>, <span class="cm-string">'Tokyo'</span>, <span class="cm-string">'Tokyo'</span>, <span class="cm-string">'Osaka'</span>, <span class="cm-string">'Kyoto'</span>, <span class="cm-string">'Hokkaido'</span>, <span class="cm-string">'Tokyo'</span>]
        ,<span class="cm-string">'birth_year'</span> :[<span class="cm-number">1990</span>, <span class="cm-number">1989</span>, <span class="cm-number">1992</span>, <span class="cm-number">1997</span>, <span class="cm-number">1982</span>, <span class="cm-number">1991</span>, <span class="cm-number">1988</span>, <span class="cm-number">1990</span>, <span class="cm-number">1995</span>, <span class="cm-number">1981</span>]
        ,<span class="cm-string">'name'</span> :[<span class="cm-string">'Hiroshi'</span>, <span class="cm-string">'Akiko'</span>, <span class="cm-string">'Yuki'</span>, <span class="cm-string">'Satoru'</span>, <span class="cm-string">'Steeve'</span>, <span class="cm-string">'Mituru'</span>, <span class="cm-string">'Aoi'</span>, <span class="cm-string">'Tarou'</span>, <span class="cm-string">'Suguru'</span>, <span class="cm-string">'Mitsuo'</span>]}
<span class="cm-variable">attri_data_frame1</span> = <span class="cm-variable">DataFrame</span>(<span class="cm-variable">attri_data1</span>)

<span class="cm-variable">attri_data_frame1</span>

import pandas as pd

from pandas import DataFrame

attri_data1 = {'ID': ['100', '101', '102', '103', '104', '106', '108', '110', '111', '113']

,'city': ['Tokyo', 'Osaka', 'Kyoto', 'Hokkaido', 'Tokyo', 'Tokyo', 'Osaka', 'Kyoto', 'Hokkaido', 'Tokyo']

,'birth_year' :[1990, 1989, 1992, 1997, 1982, 1991, 1988, 1990, 1995, 1981]

,'name' :['Hiroshi', 'Akiko', 'Yuki', 'Satoru', 'Steeve', 'Mituru', 'Aoi', 'Tarou', 'Suguru', 'Mitsuo']}

attri_data_frame1 = DataFrame(attri_data1)

attri_data_frame1

もう一つの辞書を追加

<span class="cm-variable">city_map</span> ={<span class="cm-string">'Tokyo'</span>:<span class="cm-string">'Kanto'</span>
          ,<span class="cm-string">'Hokkaido'</span>:<span class="cm-string">'Hokkaido'</span>
          ,<span class="cm-string">'Osaka'</span>:<span class="cm-string">'Kansai'</span>
          ,<span class="cm-string">'Kyoto'</span>:<span class="cm-string">'Kansai'</span>}
</code><code class="cm-s-ipython language-python"><span class="cm-variable">city_map</span></code><code class="cm-s-ipython language-python"><span class="cm-variable">


最初のattri_data_frame1のcityカラムを対象とし、</span>

city_map ={'Tokyo':'Kanto'

,'Hokkaido':'Hokkaido'

,'Osaka':'Kansai'

,'Kyoto':'Kansai'}

</code><code class="cm-s-ipython language-python">city_map</code><code class="cm-s-ipython language-python">

最初のattri_data_frame1のcityカラムを対象とし、

<span class="cm-variable">そのカラムの文字列、数値に一致するカラムを追加していく
</span>

1 2	<span class="cm-variable">そのカラムの文字列、数値に一致するカラムを追加していく </span>

<span class="cm-comment">対応するデータがない場合はNaNになる

</span>
</code><code class="cm-s-ipython language-python"><span class="cm-variable">attri_data_frame1</span></code><code class="cm-s-ipython language-python">[<span class="cm-string">'region'</span>] = <span class="cm-variable">attri_data_frame1</span>[<span class="cm-string">'city'</span>].<span class="cm-property">map</span>(<span class="cm-variable">city_map</span>)
<span class="cm-variable">attri_data_frame1</span>

対応するデータがない場合はNaNになる

</code><code class="cm-s-ipython language-python">attri_data_frame1</code><code class="cm-s-ipython language-python">['region'] = attri_data_frame1['city'].map(city_map)

attri_data_frame1

出力結果
cityに合わせてregionが追加されている

重複データの削除

2017年11月29日 by 河副太智 Leave a Comment

重複するデータを削除

<span class="cm-keyword">import</span> <span class="cm-variable">pandas</span> <span class="cm-keyword">as</span> <span class="cm-variable">pd</span>
<span class="cm-keyword">from</span> <span class="cm-variable">pandas</span> <span class="cm-keyword">import</span> <span class="cm-variable">DataFrame</span>

<span class="cm-variable">dupli_data</span> = <span class="cm-variable">DataFrame</span>({<span class="cm-string">'col1'</span>:[<span class="cm-number">1</span>, <span class="cm-number">1</span>, <span class="cm-number">2</span>, <span class="cm-number">3</span>, <span class="cm-number">4</span>, <span class="cm-number">4</span>, <span class="cm-number">6</span>, <span class="cm-number">6</span>]
                       ,<span class="cm-string">'col2'</span>:[<span class="cm-string">'a'</span>, <span class="cm-string">'b'</span>, <span class="cm-string">'b'</span>, <span class="cm-string">'b'</span>, <span class="cm-string">'c'</span>, <span class="cm-string">'c'</span>, <span class="cm-string">'b'</span>, <span class="cm-string">'b'</span>]})
<span class="cm-variable">dupli_data</span>

import pandas as pd

from pandas import DataFrame

dupli_data = DataFrame({'col1':[1, 1, 2, 3, 4, 4, 6, 6]

,'col2':['a', 'b', 'b', 'b', 'c', 'c', 'b', 'b']})

dupli_data

duplicatedで縦の列同士、重複のある行にTrueかFalseがでる

<span class="cm-variable">dupli_data</span>.<span class="cm-property">duplicated</span>()

1 2	<span class="cm-variable">dupli_data</span>.<span class="cm-property">duplicated</span>()

0    False
1    False
2    False
3    False
4    False
5     True
6    False
7     True
dtype: bool

0 False

1 False

2 False

3 False

4 False

5 True

6 False

7 True

dtype: bool

drop_duplicatesで重複したデータ削除

<span class="cm-variable">dupli_data</span>.<span class="cm-property">drop_duplicates</span>()

1 2	<span class="cm-variable">dupli_data</span>.<span class="cm-property">drop_duplicates</span>()

csvのkey,indexを指定して平均を出す

2017年11月29日 by 河副太智 Leave a Comment

import pandas as pd

df = pd.read_csv(“http://archive.ics.uci.edu/ml/machine-learning-databases/wine/wine.data”, header=None)
df.columns=[“”, “Alcohol”, “Malic acid”, “Ash”, “Alcalinity of ash”, “Magnesium”,”Total phenols”, “Flavanoids”, “Nonflavanoid phenols”, “Proanthocyanins”,”Color intensity”, “Hue”, “OD280/OD315 of diluted wines”, “Proline”]

df[“Nonflavanoid phenols”].mean()

fillna欠損値と直前のデータを補完

2017年11月29日 by 河副太智 Leave a Comment

import numpy as np
from numpy import nan as NA
import pandas as pd

aa = pd.DataFrame(np.random.rand(10,4))

#一部のデータNanにする
sample_data_frame.iloc[1,0] = NA
sample_data_frame.iloc[2,2] = NA
sample_data_frame.iloc[5:,3] = NA

import numpy as np

from numpy import nan as NA

import pandas as pd

aa = pd.DataFrame(np.random.rand(10,4))

#一部のデータNanにする

sample_data_frame.iloc[1,0] = NA

sample_data_frame.iloc[2,2] = NA

sample_data_frame.iloc[5:,3] = NA

fillna()を用いると、引数として与えた数をNaNの部分に代入、
この例では0を代入

sample_data_frame.fillna(0)

1 2	sample_data_frame.fillna(0)

methodをにffillを指定し、上の値がそのままコピーされる

<span class="cm-variable">sample_data_frame</span>.<span class="cm-property">fillna</span>(<span class="cm-variable">method</span>=<span class="cm-string">"ffill"</span>)

1 2	<span class="cm-variable">sample_data_frame</span>.<span class="cm-property">fillna</span>(<span class="cm-variable">method</span>=<span class="cm-string">"ffill"</span>)

#欠損値処理
df['Fare'] = df['Fare'].fillna(df['Fare'].median())#Fareの平均値を代入
df['Age'] = df['Age'].fillna(df['Age'].median())#Ageの平均値を代入
df['Embarked'] = df['Embarked'].fillna('S')#Embarkedの値はQ,O,Sのどれかなので代表的なSを代入
df=df.dropna(subset=['Age'])#AgeのカラムにNanがある物を削除

#欠損値処理

df['Fare'] = df['Fare'].fillna(df['Fare'].median())#Fareの平均値を代入

df['Age'] = df['Age'].fillna(df['Age'].median())#Ageの平均値を代入

df['Embarked'] = df['Embarked'].fillna('S')#Embarkedの値はQ,O,Sのどれかなので代表的なSを代入

df=df.dropna(subset=['Age'])#AgeのカラムにNanがある物を削除

dropna() データを欠損させ、その行を削除

2017年11月29日 by 河副太智 Leave a Comment

<span class="cm-keyword">import</span> <span class="cm-variable">numpy</span> <span class="cm-keyword">as</span> <span class="cm-variable">np</span>
<span class="cm-keyword">from</span> <span class="cm-variable">numpy</span> <span class="cm-keyword">import</span> <span class="cm-variable">nan</span> <span class="cm-keyword">as</span> <span class="cm-variable">NA</span>
<span class="cm-keyword">import</span> <span class="cm-variable">pandas</span> <span class="cm-keyword">as</span> <span class="cm-variable">pd</span>

<span class="cm-variable">sample_data_frame</span> = <span class="cm-variable">pd</span>.<span class="cm-property">DataFrame</span>(<span class="cm-variable">np</span>.<span class="cm-property">random</span>.<span class="cm-property">rand</span>(<span class="cm-number">10</span>,<span class="cm-number">4</span>))

<span class="cm-comment">#一部のデータをわざと欠損</span>
<span class="cm-variable">sample_data_frame</span>.<span class="cm-property">iloc</span>[<span class="cm-number">1</span>,<span class="cm-number">0</span>] = <span class="cm-variable">NA</span>


<span class="cm-variable">sample_data_frame</span>

import numpy as np

from numpy import nan as NA

import pandas as pd

sample_data_frame = pd.DataFrame(np.random.rand(10,4))

#一部のデータをわざと欠損

sample_data_frame.iloc[1,0] = NA

sample_data_frame

dropnaを用いると、NaNのある行を全て取り除く

<span class="cm-variable">sample_data_frame</span>.<span class="cm-property">dropna</span>()

1 2	<span class="cm-variable">sample_data_frame</span>.<span class="cm-property">dropna</span>()

ペアワイズ削除

<span class="cm-variable">sample_data_frame</span>[[<span class="cm-number">0</span>,<span class="cm-number">1</span>]].<span class="cm-property">dropna</span>()
</code><code class="cm-s-ipython language-python">

sample_data_frame[[0,1]].dropna()

</code><code class="cm-s-ipython language-python">

DataFrame 内部結合　共通データのみ結合

2017年11月29日 by 河副太智 Leave a Comment

print(1)

   amount      fruits  year
0       1       apple  2001
1       4      orange  2002
2       5      banana  2001
3       6  strawberry  2008
4       3   kiwifruit  2006

print(2)
       fruits  price  year
0       apple    150  2001
1      orange    120  2002
2      banana    100  2001
3  strawberry    250  2008
4       mango   3000  2007


<span role="presentation"><span class="cm-variable">df3</span> = <span class="cm-variable">pd</span>.<span class="cm-property">merge</span>(<span class="cm-variable">1</span>, <span class="cm-variable">2</span>, <span class="cm-variable">on</span>=<span class="cm-string">"fruits"</span>, <span class="cm-variable">how</span>=<span class="cm-string">"inner"</span>)
<span class="cm-builtin">print</span>(<span class="cm-variable">df3</span>)</span>
   amount      fruits  year_x  price  year_y
0       1       apple    2001    150    2001
1       4      orange    2002    120    2002
2       5      banana    2001    100    2001
3       6  strawberry    2008    250    2008

amount fruits year

0 1 apple 2001

1 4 orange 2002

2 5 banana 2001

3 6 strawberry 2008

4 3 kiwifruit 2006

print(2)

fruits price year

0 apple 150 2001

1 orange 120 2002

2 banana 100 2001

3 strawberry 250 2008

4 mango 3000 2007

df3 = pd.merge(1, 2, on="fruits", how="inner")

print(df3)

amount fruits year_x price year_y

0 1 apple 2001 150 2001

1 4 orange 2002 120 2002

2 5 banana 2001 100 2001

3 6 strawberry 2008 250 2008