詳説Pandasのread_csvとread_table関数の使い方

In [1]: import pandas as pd

In [2]: df = pd.read_csv("sample1.csv")

In [3]: df
Out[3]:
  class  grade       name
0     A      1      Satou
1     B      1  Hashimoto
2     B      3  Takahashi
3     A      2     Aikawa

In [9]: df = pd.read_csv("sample2.csv", sep=";")

In [10]: df
Out[10]:
   id class  grade       name
0   0     A      1      Satou
1   1     B      1  Hashimoto
2   2     B      3  Takahashi
3   3     A      2     Aikawa

In [11]: df = pd.read_csv("sample2.csv", delimiter=";")

In [12]: df
Out[12]:
   id class  grade       name
0   0     A      1      Satou
1   1     B      1  Hashimoto
2   2     B      3  Takahashi
3   3     A      2     Aikawa

In [13]: df = pd.read_csv("sample3.csv", delim_whitespace=True)

In [14]: df
Out[14]:
   id class  grade       name
0   0     A      1      Satou
1   1     B      1  Hashimoto
2   2     B      3  Takahashi
3   3     A      2     Aikawa

In [16]: df = pd.read_csv("sample4.csv", header=None)

In [17]: df
Out[17]:
   0  1          2
0  A  1      Satou
1  B  1  Hashimoto
2  B  3  Takahashi
3  A  2     Aikawa

In [18]: df = pd.read_csv("sample5.csv", header=1)

In [19]: df
Out[19]:
  class  grade       name
0     A      1      Satou
1     B      1  Hashimoto
2     B      3  Takahashi
3     A      2     Aikawa

In [20]: df = pd.read_csv("sample6.csv",header=[0,1])

In [21]: df
Out[21]:
  personal_data                  score        
          class grade       name  math english
0             A     1      Satou    80      31
1             B     1  Hashimoto    29      28
2             B     3  Takahashi    65      76
3             A     2     Aikawa    88      92

In [22]: df = pd.read_csv("sample4.csv",names=["class","grade","name"])

In [23]: df
Out[23]:
  class  grade       name
0     A      1      Satou
1     B      1  Hashimoto
2     B      3  Takahashi
3     A      2     Aikawa

In [24]: df = pd.read_csv("sample7.csv", index_col="id")

In [25]: df
Out[25]:
   class  grade       name
id                        
2      A      1      Satou
9      B      1  Hashimoto
19     B      3  Takahashi
25     A      2     Aikawa

In [26]: df = pd.read_csv("sample7.csv", index_col=0)

In [27]: df
Out[27]:
   class  grade       name
id                        
2      A      1      Satou
9      B      1  Hashimoto
19     B      3  Takahashi
25     A      2     Aikawa

In [28]: df = pd.read_csv("sample1.csv",usecols=["class"])

In [29]: df
Out[29]:
  class
0     A
1     B
2     B
3     A

In [30]: df = pd.read_csv("sample1.csv", usecols=["grade","name"])

In [31]: df
Out[31]:
   grade       name
0      1      Satou
1      1  Hashimoto
2      3  Takahashi
3      2     Aikawa

In [37]: df = pd.read_csv("sample1.csv", usecols = lambda x: x.upper() in ["NAME", "CLASS"])

In [38]: df
Out[38]:
  class       name
0     A      Satou
1     B  Hashimoto
2     B  Takahashi
3     A     Aikawa

In [32]: df = pd.read_csv("sample1.csv", usecols=["name"],squeeze=True)

In [33]: df
Out[33]:
0        Satou
1    Hashimoto
2    Takahashi
3       Aikawa
Name: name, dtype: object

In [34]: type(df)
Out[34]: pandas.core.series.Series

In [35]: df = pd.read_csv("sample1.csv",usecols=["name"]) # squeezeを指定しないとDataFrameになる

In [36]: type(df)
Out[36]: pandas.core.frame.DataFrame

In [14]: df = pd.read_csv("sample4.csv", header=None, prefix="classA_")

In [15]: df
Out[15]:
  classA_0  classA_1   classA_2
0        A         1      Satou
1        B         1  Hashimoto
2        B         3  Takahashi
3        A         2     Aikawa

In [3]: df = pd.read_csv("sample8.csv") # 何もオプションをつけないと同じラベルは区別される

In [4]: df
Out[4]:
  class  grade       name  name.1
0     A      1      Satou  Takeru
1     B      1  Hashimoto     NaN
2     B      3  Takahashi  Satoru
3     A      2     Aikawa   Yohko

df = pd.read_csv("sample8.csv", mangle_dupe_cols=False)

In [2]: import numpy as np # データ型を明記するためにNumPyをインポートしておく

In [3]: df = pd.read_csv("sample9.csv",engine='c',dtype={'class':str,'grade':np.int32,'name':str,'height':np.float32})

In [4]: df
Out[4]:
  class  grade       name      height
0     A      1      Satou  171.100006
1     B      1  Hashimoto  155.300003
2     B      3  Takahashi  180.399994
3     A      2     Aikawa  160.800003

In [15]: df_c = pd.read_csv("sample1.csv", engine="c")

In [16]: df_c
Out[16]:
  class  grade       name
0     A      1      Satou
1     B      1  Hashimoto
2     B      3  Takahashi
3     A      2     Aikawa

In [18]: df_python = pd.read_csv("sample1.csv", engine="python")

In [19]: df_python
Out[19]:
  class  grade       name
0     A      1      Satou
1     B      1  Hashimoto
2     B      3  Takahashi
3     A      2     Aikawa

In [21]: %timeit pd.read_csv("sample1.csv", engine="c")
3.96 ms ± 97.3 µs per loop (mean ± std. dev. of 7 runs, 100 loops each)

In [22]: %timeit pd.read_csv("sample1.csv", engine="python")
5.09 ms ± 211 µs per loop (mean ± std. dev. of 7 runs, 100 loops each)

In [39]: def func_1(x):
    ...:     x = float(x)
    ...:     return x*2
    ...:

In [40]: df = pd.read_csv("sample9.csv",converters={"class":lambda x : x.lower()
    ...: , "height":func_1})

In [41]: df
Out[41]:
  class  grade       name  height
0     a      1      Satou   342.2
1     b      1  Hashimoto   310.6
2     b      3  Takahashi   360.8
3     a      2     Aikawa   321.6

In [42]: df = pd.read_csv("sample11.csv", true_values=["Yes"],false_values=["No"])

In [43]: df
Out[43]:
        name    q_1    q_2    q_3
0      Satou   True  False  False
1  Hashimoto   True   True  False
2  Takahashi  False  False  False
3     Aikawa   True   True  False

In [54]: df = pd.read_csv("sample12.csv",skipinitialspace=True)

In [58]: df
Out[58]:
  class  grade       name
0     A      1      Satou
1     B      1  Hashimoto
2     B      3  Takahashi
3     A      2     Aikawa

In [59]: df.iloc[1,2]
Out[59]: 'Hashimoto'

In [60]: df.columns # スペースがなくなった状態になっている。  
Out[60]: Index(['class', 'grade', 'name'], dtype='object')

In [61]: df_2 = pd.read_csv("sample12.csv") # skipinitialspace=Trueにしないと

In [62]: df_2.columns # スペースが削除されずに読み込まれる
Out[62]: Index(['    class', '    grade', '     name'], dtype='object')

In [63]: df = pd.read_csv("sample13.csv", skiprows=[0,2])

In [64]: df
Out[64]:
  class  grade       name
0     A      1      Satou
1     B      1  Hashimoto
2     B      3  Takahashi
3     A      2     Aikawa

In [65]: df = pd.read_csv("sample14.csv", engine="python", skipfooter=2) # 2行分
    ...: 読み飛ばす。 engineはpythonで

In [66]: df
Out[66]:
  class  grade       name
0     A      1      Satou
1     B      1  Hashimoto
2     B      3  Takahashi
3     A      2     Aikawa

In [69]: df = pd.read_csv("sample1.csv", nrows=2) # 上から2行だけ読み込む

In [70]: df
Out[70]:
  class  grade       name
0     A      1      Satou
1     B      1  Hashimoto

In [74]: df = pd.read_csv("sample1.csv",low_memory=True) # こちらがデフォルトの設定

In [75]: df.info()
<class 'pandas.core.frame.DataFrame'>
RangeIndex: 4 entries, 0 to 3
Data columns (total 3 columns):
class    4 non-null object
grade    4 non-null int64
name     4 non-null object
dtypes: int64(1), object(2)
memory usage: 176.0+ bytes

In [76]: df_2 = pd.read_csv("sample1.csv",low_memory=False)

In [77]: df_2.info() # 読み込んだ後のメモリー使用量自体は変わらない
<class 'pandas.core.frame.DataFrame'>
RangeIndex: 4 entries, 0 to 3
Data columns (total 3 columns):
class    4 non-null object
grade    4 non-null int64
name     4 non-null object
dtypes: int64(1), object(2)
memory usage: 176.0+ bytes

In [78]: df = pd.read_csv("sample1.csv", memory_map=True)

In [79]: df
Out[79]:
  class  grade       name
0     A      1      Satou
1     B      1  Hashimoto
2     B      3  Takahashi
3     A      2     Aikawa

In [82]: df = pd.read_csv("sample15.csv", na_values=["no_value", 0, "missing"])

In [83]: df
Out[83]:
  class  grade       name
0     A    1.0        NaN
1     B    NaN  Hashimoto
2   NaN    3.0  Takahashi
3   NaN    2.0        NaN

In [84]: df = pd.read_csv("sample15.csv", keep_default_na=False) # keep_default_naだけFalseにすると何もNaN値として読み込まない

In [85]: df
Out[85]:
      class  grade       name
0         A      1    missing
1         B      0  Hashimoto
2  no_value      3  Takahashi
3                2           


In [86]: df_2 = pd.read_csv("sample15.csv", keep_default_na=False,na_values=["no_value", 0, "missing"]) # 追加で設定した値だけNaN値として読み込まれる

In [87]: df_2
Out[87]:
  class  grade       name
0     A    1.0        NaN
1     B    NaN  Hashimoto
2   NaN    3.0  Takahashi
3          2.0           

In [88]: df_3 = pd.read_csv("sample15.csv", keep_default_na=True,na_values=["no_value", 0, "missing"]) # デフォルトの設定も追加の設定もて適用される

In [89]: df_3
Out[89]:
  class  grade       name
0     A    1.0        NaN
1     B    NaN  Hashimoto
2   NaN    3.0  Takahashi
3   NaN    2.0        NaN

In [90]: df = pd.read_csv("sample15.csv",na_values=["no_value",0,"missing"]) # 欠損値にあたる場所は全てNaNになる

In [91]: df
Out[91]:
  class  grade       name
0     A    1.0        NaN
1     B    NaN  Hashimoto
2   NaN    3.0  Takahashi
3   NaN    2.0        NaN

In [92]: df_2 = pd.read_csv("sample15.csv", na_values=["na_value",0,"missing"],na_filter=False) # na_filter=Falseにするとそのままデータを読み込む

In [93]: df_2
Out[93]:
      class  grade       name
0         A      1    missing
1         B      0  Hashimoto
2  no_value      3  Takahashi
3                2           

In [94]: df = pd.read_csv("sample15.csv",na_values=["no_value",0,"missing"], verbose=True)
Tokenization took: 0.01 ms
Type conversion took: 1.61 ms
Parser memory cleanup took: 0.01 ms

In [96]: df = pd.read_csv("sample16.csv",skip_blank_lines=True) # skip_blank_lines=Trueにして読み込み

In [97]: df
Out[97]:
  class  grade       name
0     A      1      Satou
1     B      1  Hashimoto
2     B      3  Takahashi
3     A      2     Aikawa

In [98]: df = pd.read_csv("sample17.csv", parse_dates=["birth_date"])

In [99]: df
Out[99]:
    id class  grade       name birth_date
0    1     A      1      Satou 1999-09-21
1    3     B      1  Hashimoto 2000-02-15
2   15     B      3  Takahashi 1997-04-05
3  102     A      2     Aikawa 1998-11-28
4  112     B      1     Sasaki 1999-10-30

In [101]: df.dtypes # datetime型として読み込まれてるか確認
Out[101]:
id                     int64
class                 object
grade                  int64
name                  object
birth_date    datetime64[ns]
dtype: object

In [104]: df_2 = pd.read_csv("sample18.csv",parse_dates=[["birth_y","birth_m","birth_d"]])

In [105]: df_2
Out[105]:
  birth_y_birth_m_birth_d   id class  grade       name
0              1999-09-21    1     A      1      Satou
1              2000-02-15    3     B      1  Hashimoto
2              1997-04-05   15     B      3  Takahashi
3              1998-11-28  102     A      2     Aikawa
4              1999-10-30  112     B      1     Sasaki

In [106]: df_3 = pd.read_csv("sample18.csv",parse_dates={'birth_date':["birth_y","birth_m","birth_d"]})

In [107]: df_3
Out[107]:
  birth_date   id class  grade       name
0 1999-09-21    1     A      1      Satou
1 2000-02-15    3     B      1  Hashimoto
2 1997-04-05   15     B      3  Takahashi
3 1998-11-28  102     A      2     Aikawa
4 1999-10-30  112     B      1     Sasaki

In [108]: df_4 = pd.read_csv("sample17.csv", parse_dates=True, index_col="birth_date") # インデックスに指定された"birth_date"列がdatetime型に変換される

In [109]: df_4
Out[109]:
             id class  grade       name
birth_date                             
1999-09-21    1     A      1      Satou
2000-02-15    3     B      1  Hashimoto
1997-04-05   15     B      3  Takahashi
1998-11-28  102     A      2     Aikawa
1999-10-30  112     B      1     Sasaki

In [110]: df = pd.read_csv("sample17.csv",parse_dates=["birth_date"],infer_datetime_format=True)

In [111]: df
Out[111]:
    id class  grade       name birth_date
0    1     A      1      Satou 1999-09-21
1    3     B      1  Hashimoto 2000-02-15
2   15     B      3  Takahashi 1997-04-05
3  102     A      2     Aikawa 1998-11-28
4  112     B      1     Sasaki 1999-10-30

In [112]: df = pd.read_csv("sample17.csv",parse_dates=["birth_date"],infer_datetime_format=False) # デフォルトはFalse

In [113]: df
Out[113]:
    id class  grade       name birth_date
0    1     A      1      Satou 1999-09-21
1    3     B      1  Hashimoto 2000-02-15
2   15     B      3  Takahashi 1997-04-05
3  102     A      2     Aikawa 1998-11-28
4  112     B      1     Sasaki 1999-10-30

In [121]: df = pd.read_csv("sample18.csv",parse_dates={'birth_date':["birth_y","birth_m","birth_d"]}, keep_date_col=True)

In [122]: df
Out[122]:
  birth_date   id class  grade       name birth_y birth_m birth_d
0 1999-09-21    1     A      1      Satou    1999      09      21
1 2000-02-15    3     B      1  Hashimoto    2000      02      15
2 1997-04-05   15     B      3  Takahashi    1997      04      05
3 1998-11-28  102     A      2     Aikawa    1998      11      28
4 1999-10-30  112     B      1     Sasaki    1999      10      30

In [123]: from datetime import datetime

In [124]: def parse_dates(x):
     ...:     return datetime.strptime(x, '%Y/%m/%d %H:%M')
     ...:

In [125]: df = pd.read_csv("sample19.csv", parse_dates=["birth"],date_parser=parse_dates)

In [126]: df
Out[126]:
    id class  grade       name               birth
0    1     A      1      Satou 1999-09-21 12:11:00
1    3     B      1  Hashimoto 2000-02-15 21:31:00
2   15     B      3  Takahashi 1997-04-05 09:11:00
3  102     A      2     Aikawa 1998-11-28 11:11:00
4  112     B      1     Sasaki 1999-10-30 17:38:00

In [127]: df = pd.read_csv("sample20.csv",parse_dates=["birth_date"],dayfirst=True)

In [128]: df
Out[128]:
    id class  grade       name birth_date
0    1     A      1      Satou 1999-09-21
1    3     B      1  Hashimoto 2000-02-15
2   15     B      3  Takahashi 1997-05-04
3  102     A      2     Aikawa 1998-11-28
4  112     B      1     Sasaki 1999-10-30

In [176]: df = pd.read_csv("sample1.csv", iterator=True)

In [177]: df.get_chunk(1)
Out[177]:
  class  grade   name
0     A      1  Satou

In [178]: df.get_chunk(3)
Out[178]:
  class  grade       name
1     B      1  Hashimoto
2     B      3  Takahashi
3     A      2     Aikawa

In [179]: iterate = pd.read_csv("sample1.csv", chunksize=2)

In [180]: for row in iterate:
     ...:     print(row)
     ...:     print("next\n")
     ...:     
  class  grade       name
0     A      1      Satou
1     B      1  Hashimoto
next

  class  grade       name
2     B      3  Takahashi
3     A      2     Aikawa
next

In [183]: df = pd.read_csv("sample1.zip") # デフォルトの設定でも読み込むことは可能

In [184]: df
Out[184]:
  class  grade       name
0     A      1      Satou
1     B      1  Hashimoto
2     B      3  Takahashi
3     A      2     Aikawa

In [185]: df = pd.read_csv("sample1.zip", compression="zip") # zipファイルであることを明記して読み込ませる

In [186]: df
Out[186]:
  class  grade       name
0     A      1      Satou
1     B      1  Hashimoto
2     B      3  Takahashi
3     A      2     Aikawa

In [193]: df = pd.read_csv("sample21.csv",thousands=",")

In [194]: df
Out[194]:
  name   price
0    A   12000
1    B   13231
2    C  145215

In [195]: df.dtypes
Out[195]:
name     object
price     int64
dtype: object

In [196]: df = pd.read_csv("sample22.csv",decimal=",")

In [197]: df
Out[197]:
      name  amount
0    water  123.23
1      oil   22.22
2  alcohol    3.12

In [198]: df = pd.read_csv("sample23.csv",float_precision="None") # これがデフォルト  

In [199]: df
Out[199]:
      name      amount
0    water  123.012346
1      oil   22.012346
2  alcohol    3.012346

In [200]: df_2 = pd.read_csv("sample23.csv",float_precision="high")

In [201]: df_2
Out[201]:
      name      amount
0    water  123.012346
1      oil   22.012346
2  alcohol    3.012346

In [202]: df_3 = pd.read_csv("sample23.csv",float_precision="round-trip")

In [203]: df_3
Out[203]:
      name      amount
0    water  123.012346
1      oil   22.012346
2  alcohol    3.012346

In [210]: df = pd.read_csv("sample24.txt",lineterminator="\\") # エスケープするのを忘れないように
In [211]: df
Out[211]:
  class  grade       name
0     A      1      Satou
1     B      1  Hashimoto
2     B      3  Takahashi
3     A      2     Aikawa

In [253]: df = pd.read_csv("sample25.csv", quotechar='<')

In [254]: df
Out[254]:
  name                     text
0    A  this quotation, is used
1    B  He said you,are lucky\n

In [263]: df_minimal = pd.read_csv("sample26.csv", quoting=0)

In [264]: df_minimal
Out[264]:
                        name  text
A  this 3 quotation, is used     3
B   He said you,are 7 lucky      5

In [265]: df_all = pd.read_csv("sample26.csv",quoting=1) # 全部引用

In [266]: df_all
Out[266]:
                        name  text
A  this 3 quotation, is used     3
B   He said you,are 7 lucky      5

In [267]: df_nonnumeric = pd.read_csv("sample26.csv",quoting=2) # 数値データ以外引用

In [268]: df_nonnumeric
Out[268]:
                        name  text
A  this 3 quotation, is used   3.0
B   He said you,are 7 lucky    5.0

In [269]: df_none = pd.read_csv("sample26.csv", quoting=3) # 引用しない  

In [270]: df_none
Out[270]:
                              name  text
A "this 3 quotation       is used"     3
B "He said you       are 7 lucky "     5

In [271]: df_none.index
Out[271]:
MultiIndex(levels=[['A', 'B'], ['"He said you', '"this 3 quotation']],
           labels=[[0, 1], [1, 0]])

In [272]: df_all.index
Out[272]: Index(['A', 'B'], dtype='object')

In [273]: df_nonnumeric.index
Out[273]: Index(['A', 'B'], dtype='object')

In [274]: df_minimal.index
Out[274]: Index(['A', 'B'], dtype='object')

In [290]: df = pd.read_csv("sample27.csv",doublequote=True) # doublequote=Trueにするとうまく読み込める

In [291]: df
Out[291]:
  name                      text  number
0    A  this "quotation, is used       3
1    B     He said you,are lucky       5

In [292]: df = pd.read_csv("sample27.csv",doublequote=False) # doublequote=Falseだとうまくいかない

In [293]: df
Out[293]:
                    name       text  number
A        this "quotation   is used"     3.0
B  He said you,are lucky          5     NaN

In [295]: df = pd.read_csv("sample28.csv", escapechar="\\")

In [296]: df
Out[296]:
  name                      text  number
0    A  this quotation, is used"       3
1    B    He said you,are lucky"       5

In [298]: df = pd.read_csv("sample13.csv", comment='#')

In [299]: df
Out[299]:
  class  grade       name
0     A      1      Satou
1     B      1  Hashimoto
2     B      3  Takahashi
3     A      2     Aikawa

In [300]: df = pd.read_csv("sample29.csv",encoding="shift_jis")

In [301]: df
Out[301]:
  class  grade       name
0     A      1      Satou
1     B      1  Hashimoto
2     B      3  Takahashi
3     A      2     Aikawa

In [324]: import csv

In [328]: dialect = csv.excel()

In [329]: dialect.delimiter = ";"

In [330]: df = pd.read_csv("sample2.csv",dialect=dialect)

In [331]: df
Out[331]:
   id class  grade       name
0   0     A      1      Satou
1   1     B      1  Hashimoto
2   2     B      3  Takahashi
3   3     A      2     Aikawa

In [365]: df = pd.read_csv("sample30.csv",error_bad_lines=False, index_col=False)

In [366]: df
Out[366]:
  class  grade       name
0     A      1      Satou
1     B      1  Hashimoto
2     B      3  Takahashi
3     A      2     Aikawa

In [367]: df = pd.read_csv("sample31.csv",error_bad_lines=False, warn_bad_lines=True)
b'Skipping line 3: expected 3 fields, saw 4\n'

In [368]: df
Out[368]:
   a  b   c
0  1  2   3
1  8  9  10