特別講義DS Ch6 データの取得と編集

# pandas を import し, pdと呼ぶという意味
# pandas はデータの読み込みなどに利用します
import pandas as pd

#------------------------------------------------------------------
# データの読み込み
#------------------------------------------------------------------
# CSVファイルを読み込んでデータフレーム(後述)に格納
# pd.関数名 でpandasの関数が利用できる
# Dataフォルダを作成し,そこにデータを入れておきましょう
# data/salary_data.csv は  data フォルダの salary.csv という意味
df = pd.read_csv('data/salary.csv')
print(df)

❯ python3 salary.py
         industry  salary
0    Construction    4503
1   Manufacturing    4756
2       Wholesale    3186
3    Accomodation    1949
4         Finance    5711
5     Real Estate    3786
6       Transport    3909
7          Energy    8199
8            Info    5337
9             Edu    3913
10            Med    4144
11          Other    4239
12       Suervice    3086
13           AFFC    2190

# dfのvalues属性を表示したい場合
print(df.values)

#dfのcolumns属性を表示したい場合
print(df.columns)

#dfのindex属性を表示したい場合
print(df.index)

[['Construction' 4503]
 ['Manufacturing' 4756]
 ['Wholesale' 3186]
 ['Accomodation' 1949]
 ['Finance' 5711]
 ['Real Estate' 3786]
 ['Transport' 3909]
 ['Energy' 8199]
 ['Info' 5337]
 ['Edu' 3913]
 ['Med' 4144]
 ['Other' 4239]
 ['Suervice' 3086]
 ['AFFC' 2190]]
Index(['industry', 'salary'], dtype='object')
RangeIndex(start=0, stop=14, step=1)

#列名の変更
df.columns = ['Ind','Sal']

#行名の変更
df.index = ['a','b','c','d','e','f','g','h','i','j','k','l','m','n']

print(df)

             Ind   Sal
a   Construction  4503
b  Manufacturing  4756
c      Wholesale  3186
d   Accomodation  1949
e        Finance  5711
f    Real Estate  3786
g      Transport  3909
h         Energy  8199
i           Info  5337
j            Edu  3913
k            Med  4144
l          Other  4239
m       Suervice  3086
n           AFFC  2190

#元の連番に戻す
## ()の中に inplace = True と書くことでdfのindexが変更される
## inplace=Trueを書かないと変更されません.
## drop = True と書くことで,変更前に使用していたindexが消去されます.
## drop = False の場合先ほどのアルファベットが列として残ります.
df.reset_index(inplace=True,drop=True)
print(df)

             Ind   Sal
0    Construction  4503
1   Manufacturing  4756
2       Wholesale  3186
3    Accomodation  1949
4         Finance  5711
5     Real Estate  3786
6       Transport  3909
7          Energy  8199
8            Info  5337
9             Edu  3913
10            Med  4144
11          Other  4239
12       Suervice  3086

#列の指定(Ind列)
print(df['Ind'])
#列の指定(Sal列)
print(df.Sal)

0      Construction
1     Manufacturing
2         Wholesale
3      Accomodation
4           Finance
5       Real Estate
6         Transport
7            Energy
8              Info
9               Edu
10              Med
11            Other
12         Suervice
13             AFFC
Name: Ind, dtype: object
0     4503
1     4756
2     3186
3     1949
4     5711
5     3786
6     3909
7     8199
8     5337
9     3913
10    4144
11    4239
12    3086
13    2190
Name: Sal, dtype: int64

#範囲指定(3行目,'Sal'列)
print(df.loc[3,'Sal'])

#スライスを利用した範囲指定
print(df.loc[3:5,'Ind':'Sal'])

#リストを利用した範囲指定
print(df.loc[[2,4,7],['Ind','Sal']])

1949
            Ind   Sal
3  Accomodation  1949
4       Finance  5711
5   Real Estate  3786
         Ind   Sal
2  Wholesale  3186
4    Finance  5711
7     Energy  8199

#atによる単独のセルの指定
print(df.at[3,'Sal'])

1949

#行番号,列番号による範囲指定
print(df.iloc[3:5,0:])

#行番号,列番号による単独のセルの指定
print(df.iat[3,1])

         Ind   Sal
2  Wholesale  3186
4    Finance  5711
7     Energy  8199
1949

#真偽値のリストによる抜き出し
bools = [False,True] * 7
print(bools)
print(df[bools])

[False, True, False, True, False, True, False, True, False, True, False, True, False, True]
              Ind   Sal
1   Manufacturing  4756
3    Accomodation  1949
5     Real Estate  3786
7          Energy  8199
9             Edu  3913
11          Other  4239
13           AFFC  2190

#DataFrameオブジェクトに対する論理演算
print(df['Sal'])
print(df['Sal'] >= 3000)

0     4503
1     4756
2     3186
3     1949
4     5711
5     3786
6     3909
7     8199
8     5337
9     3913
10    4144
11    4239
12    3086
13    2190
Name: Sal, dtype: int64
0      True
1      True
2      True
3     False
4      True
5      True
6      True
7      True
8      True
9      True
10     True
11     True
12     True
13    False
Name: Sal, dtype: bool

#DataFrameオブジェクトに対する条件抽出
print(df[df['Sal'] >= 3000]['Sal'])

Name: Sal, dtype: bool
0     4503
1     4756
2     3186
4     5711
5     3786
6     3909
7     8199
8     5337
9     3913
10    4144
11    4239
12    3086
Name: Sal, dtype: int64

print(df)
df.at[1,'Sal'] = 0
print(df)

              Ind   Sal
0    Construction  4503
1   Manufacturing  4756
2       Wholesale  3186
3    Accomodation  1949
4         Finance  5711
5     Real Estate  3786
6       Transport  3909
7          Energy  8199
8            Info  5337
9             Edu  3913
10            Med  4144
11          Other  4239
12       Suervice  3086
13           AFFC  2190
              Ind   Sal
0    Construction  4503
1   Manufacturing     0
2       Wholesale  3186
3    Accomodation  1949
4         Finance  5711
5     Real Estate  3786
6       Transport  3909
7          Energy  8199
8            Info  5337
9             Edu  3913
10            Med  4144
11          Other  4239
12       Suervice  3086
13           AFFC  2190

#範囲の変更
df.loc[3:5,'Sal'] = [1,2,3]
print(df)

              Ind   Sal
0    Construction  4503
1   Manufacturing     0
2       Wholesale  3186
3    Accomodation     1
4         Finance     2
5     Real Estate     3
6       Transport  3909
7          Energy  8199
8            Info  5337
9             Edu  3913
10            Med  4144
11          Other  4239
12       Suervice  3086
13           AFFC  2190

#一括変更
df.loc[3:5,'Sal'] = 4
print(df)

              Ind   Sal
0    Construction  4503
1   Manufacturing     0
2       Wholesale  3186
3    Accomodation     4
4         Finance     4
5     Real Estate     4
6       Transport  3909
7          Energy  8199
8            Info  5337
9             Edu  3913
10            Med  4144
11          Other  4239
12       Suervice  3086
13           AFFC  2190

print('DataFrameのデータ型:\n',df.dtypes)
print('Sal列のデータ型:',df['Sal'].dtypes)
print('Ind列のデータ型:',df['Ind'].dtypes)
print('Ind列の要素のデータ型:',type(df.at[0,'Ind']))

DataFrameのデータ型:
 Ind    object
Sal     int64
dtype: object
Sal列のデータ型: int64
Ind列のデータ型: object
Ind列の要素のデータ型: <class 'str'>

#データ型の変更
df['Sal'] = df['Sal'].astype(float)
print('Sal列のデータ型:',df['Sal'].dtypes)

Sal列のデータ型:float64

#読み込み時のデータ型の指定
df = pd.read_csv('data/salary.csv'
                ,dtype = {'industry':str
                         ,'salary':float})
df.columns = ['Ind','Sal']
print(df.dtypes)

Ind     object
Sal    float64
dtype: object

#DataFrameのvalues部分をリストで作成する
## 行のリストになっていることに注意

#DataFrameのvalues部分をリストで作成する
## 行のリストになっていることに注意
values = [['dog',5.0,60.0]
         ,['cat',3.0,30.0]
         ,['fish',0.15,0.5]]

#DataFrameの作成
df = pd.DataFrame(data=values
                 ,columns=['animal_kind'
                          ,'weight'
                          ,'size'])
print(df)

  animal_kind  weight  size
0         dog    5.00  60.0
1         cat    3.00  30.0
2        fish    0.15   0.5

df = pd.DataFrame({'animal_kind':['dog','cat','fisg']
                  ,'weight':[5.0,6.0,0.15]
                  ,'size':[60.0,30.0,0.5]})
print(df)

#データの追加
#列の追加
print('追加前:\n',df)
df['name'] = ['pochi','tama','kintaro']
print('列の追加:\n',df)
df.loc[3] = ['bird',1.0,3,'piyo']
print('行の追加:\n',df)

追加前:
   animal_kind weight  size
0         dog    5.0  60.0
1         cat    6.0  30.0
2        fish   0.15   0.5
列の追加:
   animal_kind weight  size     name
0         dog    5.0  60.0    pochi
1         cat    6.0  30.0     tama
2        fish   0.15   0.5  kintaro
行の追加:
   animal_kind weight  size     name
0         dog    5.0  60.0    pochi
1         cat    6.0  30.0     tama
2        fish   0.15   0.5  kintaro
3        bird    1.0     3     piyo

#列を利用した計算
df['density'] = df['weight'] / df['size']
print(df)

  animal_kind  weight  size     name   density
0         dog    5.00  60.0    pochi  0.083333
1         cat    6.00  30.0     tama  0.200000
2        fish    0.15   0.5  kintaro  0.300000
3        bird    1.00   3.0     piyo  0.333333

#列の削除
df.drop('name'
       ,axis='columns' # or axis = 1
       ,inplace=True)
print(df)

#行の削除
df.drop(3
       ,axis='index' # or axis = 0
       ,inplace=True)
print(df)

 animal_kind  weight  size   density
0         dog    5.00  60.0  0.083333
1         cat    6.00  30.0  0.200000
2        fish    0.15   0.5  0.300000
3        bird    1.00   3.0  0.333333
  animal_kind  weight  size   density
0         dog    5.00  60.0  0.083333
1         cat    6.00  30.0  0.200000
2        fish    0.15   0.5  0.300000

#複数行の削除
print(df.drop(['weight','size','density'],axis=1))

  animal_kind
0         dog
1         cat
2        fish

#DataFrameの作成
df = pd.DataFrame(data = [['dog',5,60]
                         ,['cat',30]
                         ,['fish',0.15]]
                  ,columns=['animal_kind'
                          ,'weight'
                          ,'size'])
print(df)


#かけたデータの読み込み
df = pd.read_csv('data/salary_nan.csv')
print('-----\n',df)

  animal_kind  weight  size
0         dog    5.00  60.0
1         cat   30.00   NaN
2        fish    0.15   NaN
-----
          Industry  Salary
0    Construction  4503.0
1   Manufacturing  4756.0
2       Wholesale  3186.0
3   Accommodation     NaN
4         Finance  5711.0
5     Real Estate  3786.0
6       Transport  3909.0
7          Energy  8199.0
8            Info     NaN
9             Edu  3913.0
10            Med  4144.0
11          Other     NaN
12       Suervice  3086.0
13           AFFC  2190.0

df = pd.read_csv('data/salary_nan.csv')
print(df.at[8,'Salary'])
print('NaN == 1:',df.at[8,'Salary'] == 1)
print('NaN == NaN:',df.at[8,'Salary'] == float('NaN'))
print('NaN != 1:',df.at[8,'Salary'] != 1)
print('NaN != NaN:',df.at[8,'Salary'] == float('NaN'))

nan
NaN == 1: False
NaN == NaN: False
NaN != 1: True
NaN != NaN: False

df = pd.read_csv('data/salary_nan.csv')
print(df.isna())
print(pd.isna(df.at[8,'Salary']))

    Industry  Salary
0      False   False
1      False   False
2      False   False
3      False    True
4      False   False
5      False   False
6      False   False
7      False   False
8      False    True
9      False   False
10     False   False
11     False    True
12     False   False
13     False   False
True

#欠損値の削除
print(df.dropna(subset=['Salary'],how='any'))
print('---\n',df)

         Industry  Salary
0    Construction  4503.0
1   Manufacturing  4756.0
2       Wholesale  3186.0
4         Finance  5711.0
5     Real Estate  3786.0
6       Transport  3909.0
7          Energy  8199.0
9             Edu  3913.0
10            Med  4144.0
12       Suervice  3086.0
13           AFFC  2190.0
---
         Industry  Salary
0    Construction  4503.0
1   Manufacturing  4756.0
2       Wholesale  3186.0
3   Accommodation     NaN
4         Finance  5711.0
5     Real Estate  3786.0
6       Transport  3909.0
7          Energy  8199.0
8            Info     NaN
9             Edu  3913.0
10            Med  4144.0
11          Other     NaN
12       Suervice  3086.0
13           AFFC  2190.0

df.dropna(subset=['Salary']
         ,how='any'
         ,inplace=True)
print(df)

         Industry  Salary
0    Construction  4503.0
1   Manufacturing  4756.0
2       Wholesale  3186.0
4         Finance  5711.0
5     Real Estate  3786.0
6       Transport  3909.0
7          Energy  8199.0
9             Edu  3913.0
10            Med  4144.0
12       Suervice  3086.0
13           AFFC  2190.0

values = [['dog',5.0,60.0]
         ,['cat',3.0,30.0]
         ,['fish',0.15,0.5]]

#DataFrameの作成
df = pd.DataFrame(data=values
                 ,columns=['animal_kind'
                          ,'weight'
                          ,'size'])
#csvとして保存
df.to_csv('data/animal.csv'
         ,encoding='utf-8-sig')