Инструменты ввода-вывода (текст, CSV, HDF5, …)

In [9]: import numpy as np

In [10]: data = "a,b,c,d\n1,2,3,4\n5,6,7,8\n9,10,11"

In [11]: print(data)
a,b,c,d
1,2,3,4
5,6,7,8
9,10,11

In [12]: df = pd.read_csv(StringIO(data), dtype=object)

In [13]: df
Out[13]: 
   a   b   c    d
0  1   2   3    4
1  5   6   7    8
2  9  10  11  NaN

In [14]: df["a"][0]
Out[14]: '1'

In [15]: df = pd.read_csv(StringIO(data), dtype={"b": object, "c": np.float64, "d": "Int64"})

In [16]: df.dtypes
Out[16]: 
a      int64
b     object
c    float64
d      Int64
dtype: object

In [17]: data = "col_1\n1\n2\n'A'\n4.22"

In [18]: df = pd.read_csv(StringIO(data), converters={"col_1": str})

In [19]: df
Out[19]: 
  col_1
0     1
1     2
2   'A'
3  4.22

In [20]: df["col_1"].apply(type).value_counts()
Out[20]: 
col_1
<class 'str'>    4
Name: count, dtype: int64

In [21]: df2 = pd.read_csv(StringIO(data))

In [22]: df2["col_1"] = pd.to_numeric(df2["col_1"], errors="coerce")

In [23]: df2
Out[23]: 
   col_1
0   1.00
1   2.00
2    NaN
3   4.22

In [24]: df2["col_1"].apply(type).value_counts()
Out[24]: 
col_1
<class 'float'>    4
Name: count, dtype: int64

In [31]: data = """a,b,c,d,e,f,g,h,i,j
   ....: 1,2.5,True,a,,,,,12-31-2019,
   ....: 3,4.5,False,b,6,7.5,True,a,12-31-2019,
   ....: """
   ....: 

In [32]: df = pd.read_csv(StringIO(data), dtype_backend="numpy_nullable", parse_dates=["i"])

In [33]: df
Out[33]: 
   a    b      c  d     e     f     g     h          i     j
0  1  2.5   True  a  <NA>  <NA>  <NA>  <NA> 2019-12-31  <NA>
1  3  4.5  False  b     6   7.5  True     a 2019-12-31  <NA>

In [34]: df.dtypes
Out[34]: 
a             Int64
b           Float64
c           boolean
d    string[python]
e             Int64
f           Float64
g           boolean
h    string[python]
i    datetime64[ns]
j             Int64
dtype: object

In [35]: data = "col1,col2,col3\na,b,1\na,b,2\nc,d,3"

In [36]: pd.read_csv(StringIO(data))
Out[36]: 
  col1 col2  col3
0    a    b     1
1    a    b     2
2    c    d     3

In [37]: pd.read_csv(StringIO(data)).dtypes
Out[37]: 
col1    object
col2    object
col3     int64
dtype: object

In [38]: pd.read_csv(StringIO(data), dtype="category").dtypes
Out[38]: 
col1    category
col2    category
col3    category
dtype: object

In [39]: pd.read_csv(StringIO(data), dtype={"col1": "category"}).dtypes
Out[39]: 
col1    category
col2      object
col3       int64
dtype: object

In [40]: from pandas.api.types import CategoricalDtype

In [41]: dtype = CategoricalDtype(["d", "c", "b", "a"], ordered=True)

In [42]: pd.read_csv(StringIO(data), dtype={"col1": dtype}).dtypes
Out[42]: 
col1    category
col2      object
col3       int64
dtype: object

In [43]: dtype = CategoricalDtype(["a", "b", "d"])  # No 'c'

In [44]: pd.read_csv(StringIO(data), dtype={"col1": dtype}).col1
Out[44]: 
0      a
1      a
2    NaN
Name: col1, dtype: category
Categories (3, object): ['a', 'b', 'd']

In [59]: data = "a,b,a\n0,1,2\n3,4,5"

In [60]: pd.read_csv(StringIO(data))
Out[60]: 
   a  b  a.1
0  0  1    2
1  3  4    5

In [86]: from io import BytesIO

In [87]: data = b"word,length\n" b"Tr\xc3\xa4umen,7\n" b"Gr\xc3\xbc\xc3\x9fe,5"

In [88]: data = data.decode("utf8").encode("latin-1")

In [89]: df = pd.read_csv(BytesIO(data), encoding="latin-1")

In [90]: df
Out[90]: 
      word  length
0  Träumen       7
1    Grüße       5

In [91]: df["word"][1]
Out[91]: 'Grüße'

In [92]: data = "a,b,c\n4,apple,bat,5.7\n8,orange,cow,10"

In [93]: pd.read_csv(StringIO(data))
Out[93]: 
        a    b     c
4   apple  bat   5.7
8  orange  cow  10.0

In [94]: data = "index,a,b,c\n4,apple,bat,5.7\n8,orange,cow,10"

In [95]: pd.read_csv(StringIO(data), index_col=0)
Out[95]: 
            a    b     c
index                   
4       apple  bat   5.7
8      orange  cow  10.0

In [96]: data = "a,b,c\n4,apple,bat,\n8,orange,cow,"

In [97]: print(data)
a,b,c
4,apple,bat,
8,orange,cow,

In [98]: pd.read_csv(StringIO(data))
Out[98]: 
        a    b   c
4   apple  bat NaN
8  orange  cow NaN

In [99]: pd.read_csv(StringIO(data), index_col=False)
Out[99]: 
   a       b    c
0  4   apple  bat
1  8  orange  cow

In [100]: data = "a,b,c\n4,apple,bat,\n8,orange,cow,"

In [101]: print(data)
a,b,c
4,apple,bat,
8,orange,cow,

In [102]: pd.read_csv(StringIO(data), usecols=["b", "c"])
Out[102]: 
     b   c
4  bat NaN
8  cow NaN

In [103]: pd.read_csv(StringIO(data), usecols=["b", "c"], index_col=0)
Out[103]: 
     b   c
4  bat NaN
8  cow NaN

In [143]: val = "0.3066101993807095471566981359501369297504425048828125"

In [144]: data = "a,b,c\n1,2,{0}".format(val)

In [145]: abs(
   .....:     pd.read_csv(
   .....:         StringIO(data),
   .....:         engine="c",
   .....:         float_precision=None,
   .....:     )["c"][0] - float(val)
   .....: )
   .....: 
Out[145]: 5.551115123125783e-17

In [146]: abs(
   .....:     pd.read_csv(
   .....:         StringIO(data),
   .....:         engine="c",
   .....:         float_precision="high",
   .....:     )["c"][0] - float(val)
   .....: )
   .....: 
Out[146]: 5.551115123125783e-17

In [147]: abs(
   .....:     pd.read_csv(StringIO(data), engine="c", float_precision="round_trip")["c"][0]
   .....:     - float(val)
   .....: )
   .....: 
Out[147]: 0.0

In [148]: data = (
   .....:     "ID|level|category\n"
   .....:     "Patient1|123,000|x\n"
   .....:     "Patient2|23,000|y\n"
   .....:     "Patient3|1,234,018|z"
   .....: )
   .....: 

In [149]: with open("tmp.csv", "w") as fh:
   .....:     fh.write(data)
   .....: 

In [150]: df = pd.read_csv("tmp.csv", sep="|")

In [151]: df
Out[151]: 
         ID      level category
0  Patient1    123,000        x
1  Patient2     23,000        y
2  Patient3  1,234,018        z

In [152]: df.level.dtype
Out[152]: dtype('O')

In [153]: df = pd.read_csv("tmp.csv", sep="|", thousands=",")

In [154]: df
Out[154]: 
         ID    level category
0  Patient1   123000        x
1  Patient2    23000        y
2  Patient3  1234018        z

In [155]: df.level.dtype
Out[155]: dtype('int64')

In [156]: data = "a,b,c\n1,Yes,2\n3,No,4"

In [157]: print(data)
a,b,c
1,Yes,2
3,No,4

In [158]: pd.read_csv(StringIO(data))
Out[158]: 
   a    b  c
0  1  Yes  2
1  3   No  4

In [159]: pd.read_csv(StringIO(data), true_values=["Yes"], false_values=["No"])
Out[159]: 
   a      b  c
0  1   True  2
1  3  False  4

In [160]: data = "a,b,c\n1,2,3\n4,5,6,7\n8,9,10"

In [161]: pd.read_csv(StringIO(data))
---------------------------------------------------------------------------
ParserError                               Traceback (most recent call last)
Cell In[161], line 1
----> 1 pd.read_csv(StringIO(data))

File ~/work/pandas/pandas/pandas/io/parsers/readers.py:1026, in read_csv(filepath_or_buffer, sep, delimiter, header, names, index_col, usecols, dtype, engine, converters, true_values, false_values, skipinitialspace, skiprows, skipfooter, nrows, na_values, keep_default_na, na_filter, verbose, skip_blank_lines, parse_dates, infer_datetime_format, keep_date_col, date_parser, date_format, dayfirst, cache_dates, iterator, chunksize, compression, thousands, decimal, lineterminator, quotechar, quoting, doublequote, escapechar, comment, encoding, encoding_errors, dialect, on_bad_lines, delim_whitespace, low_memory, memory_map, float_precision, storage_options, dtype_backend)
   1013 kwds_defaults = _refine_defaults_read(
   1014     dialect,
   1015     delimiter,
   (...)
   1022     dtype_backend=dtype_backend,
   1023 )
   1024 kwds.update(kwds_defaults)
-> 1026 return _read(filepath_or_buffer, kwds)

File ~/work/pandas/pandas/pandas/io/parsers/readers.py:626, in _read(filepath_or_buffer, kwds)
    623     return parser
    625 with parser:
--> 626     return parser.read(nrows)

File ~/work/pandas/pandas/pandas/io/parsers/readers.py:1923, in TextFileReader.read(self, nrows)
   1916 nrows = validate_integer("nrows", nrows)
   1917 try:
   1918     # error: "ParserBase" has no attribute "read"
   1919     (
   1920         index,
   1921         columns,
   1922         col_dict,
-> 1923     ) = self._engine.read(  # type: ignore[attr-defined]
   1924         nrows
   1925     )
   1926 except Exception:
   1927     self.close()

File ~/work/pandas/pandas/pandas/io/parsers/c_parser_wrapper.py:234, in CParserWrapper.read(self, nrows)
    232 try:
    233     if self.low_memory:
--> 234         chunks = self._reader.read_low_memory(nrows)
    235         # destructive to chunks
    236         data = _concatenate_chunks(chunks)

File ~/work/pandas/pandas/pandas/_libs/parsers.pyx:838, in pandas._libs.parsers.TextReader.read_low_memory()

File ~/work/pandas/pandas/pandas/_libs/parsers.pyx:905, in pandas._libs.parsers.TextReader._read_rows()

File ~/work/pandas/pandas/pandas/_libs/parsers.pyx:874, in pandas._libs.parsers.TextReader._tokenize_rows()

File ~/work/pandas/pandas/pandas/_libs/parsers.pyx:891, in pandas._libs.parsers.TextReader._check_tokenize_status()

File ~/work/pandas/pandas/pandas/_libs/parsers.pyx:2061, in pandas._libs.parsers.raise_parser_error()

ParserError: Error tokenizing data. C error: Expected 3 fields in line 3, saw 4

In [162]: data = "a,b,c\n1,2,3\n4,5,6,7\n8,9,10"

In [163]: pd.read_csv(StringIO(data), on_bad_lines="skip")
Out[163]: 
   a  b   c
0  1  2   3
1  8  9  10

In [164]: external_list = []

In [165]: def bad_lines_func(line):
   .....:     external_list.append(line)
   .....:     return line[-3:]
   .....: 

In [166]: external_list
Out[166]: []

In [171]: pd.read_csv(StringIO(data), usecols=[0, 1, 2])
---------------------------------------------------------------------------
ValueError                                Traceback (most recent call last)
Cell In[171], line 1
----> 1 pd.read_csv(StringIO(data), usecols=[0, 1, 2])

File ~/work/pandas/pandas/pandas/io/parsers/readers.py:1026, in read_csv(filepath_or_buffer, sep, delimiter, header, names, index_col, usecols, dtype, engine, converters, true_values, false_values, skipinitialspace, skiprows, skipfooter, nrows, na_values, keep_default_na, na_filter, verbose, skip_blank_lines, parse_dates, infer_datetime_format, keep_date_col, date_parser, date_format, dayfirst, cache_dates, iterator, chunksize, compression, thousands, decimal, lineterminator, quotechar, quoting, doublequote, escapechar, comment, encoding, encoding_errors, dialect, on_bad_lines, delim_whitespace, low_memory, memory_map, float_precision, storage_options, dtype_backend)
   1013 kwds_defaults = _refine_defaults_read(
   1014     dialect,
   1015     delimiter,
   (...)
   1022     dtype_backend=dtype_backend,
   1023 )
   1024 kwds.update(kwds_defaults)
-> 1026 return _read(filepath_or_buffer, kwds)

File ~/work/pandas/pandas/pandas/io/parsers/readers.py:620, in _read(filepath_or_buffer, kwds)
    617 _validate_names(kwds.get("names", None))
    619 # Create the parser.
--> 620 parser = TextFileReader(filepath_or_buffer, **kwds)
    622 if chunksize or iterator:
    623     return parser

File ~/work/pandas/pandas/pandas/io/parsers/readers.py:1620, in TextFileReader.__init__(self, f, engine, **kwds)
   1617     self.options["has_index_names"] = kwds["has_index_names"]
   1619 self.handles: IOHandles | None = None
-> 1620 self._engine = self._make_engine(f, self.engine)

File ~/work/pandas/pandas/pandas/io/parsers/readers.py:1898, in TextFileReader._make_engine(self, f, engine)
   1895     raise ValueError(msg)
   1897 try:
-> 1898     return mapping[engine](f, **self.options)
   1899 except Exception:
   1900     if self.handles is not None:

File ~/work/pandas/pandas/pandas/io/parsers/c_parser_wrapper.py:155, in CParserWrapper.__init__(self, src, **kwds)
    152     # error: Cannot determine type of 'names'
    153     if len(self.names) < len(usecols):  # type: ignore[has-type]
    154         # error: Cannot determine type of 'names'
--> 155         self._validate_usecols_names(
    156             usecols,
    157             self.names,  # type: ignore[has-type]
    158         )
    160 # error: Cannot determine type of 'names'
    161 self._validate_parse_dates_presence(self.names)  # type: ignore[has-type]

File ~/work/pandas/pandas/pandas/io/parsers/base_parser.py:988, in ParserBase._validate_usecols_names(self, usecols, names)
    986 missing = [c for c in usecols if c not in names]
    987 if len(missing) > 0:
--> 988     raise ValueError(
    989         f"Usecols do not match columns, columns expected but not found: "
    990         f"{missing}"
    991     )
    993 return usecols

ValueError: Usecols do not match columns, columns expected but not found: [0, 1, 2]

In [172]: pd.read_csv(StringIO(data), names=['a', 'b', 'c', 'd'])
Out[172]: 
        a                b   c   d
0    name             type NaN NaN
1  name a   a is of type a NaN NaN
2  name b  b is of type b" NaN NaN

In [173]: data = "label1,label2,label3\n" 'index1,"a,c,e\n' "index2,b,d,f"

In [174]: print(data)
label1,label2,label3
index1,"a,c,e
index2,b,d,f

In [175]: import csv

In [176]: dia = csv.excel()

In [177]: dia.quoting = csv.QUOTE_NONE

In [178]: pd.read_csv(StringIO(data), dialect=dia)
Out[178]: 
       label1 label2 label3
index1     "a      c      e
index2      b      d      f

In [179]: data = "a,b,c~1,2,3~4,5,6"

In [180]: pd.read_csv(StringIO(data), lineterminator="~")
Out[180]: 
   a  b  c
0  1  2  3
1  4  5  6

In [181]: data = "a, b, c\n1, 2, 3\n4, 5, 6"

In [182]: print(data)
a, b, c
1, 2, 3
4, 5, 6

In [183]: pd.read_csv(StringIO(data), skipinitialspace=True)
Out[183]: 
   a  b  c
0  1  2  3
1  4  5  6

In [184]: data = 'a,b\n"hello, \\"Bob\\", nice to see you",5'

In [185]: print(data)
a,b
"hello, \"Bob\", nice to see you",5

In [186]: pd.read_csv(StringIO(data), escapechar="\\")
Out[186]: 
                               a  b
0  hello, "Bob", nice to see you  5

In [187]: data1 = (
   .....:     "id8141    360.242940   149.910199   11950.7\n"
   .....:     "id1594    444.953632   166.985655   11788.4\n"
   .....:     "id1849    364.136849   183.628767   11806.2\n"
   .....:     "id1230    413.836124   184.375703   11916.8\n"
   .....:     "id1948    502.953953   173.237159   12468.3"
   .....: )
   .....: 

In [188]: with open("bar.csv", "w") as f:
   .....:     f.write(data1)
   .....: 

# Column specifications are a list of half-intervals
In [189]: colspecs = [(0, 6), (8, 20), (21, 33), (34, 43)]

In [190]: df = pd.read_fwf("bar.csv", colspecs=colspecs, header=None, index_col=0)

In [191]: df
Out[191]: 
                 1           2        3
0                                      
id8141  360.242940  149.910199  11950.7
id1594  444.953632  166.985655  11788.4
id1849  364.136849  183.628767  11806.2
id1230  413.836124  184.375703  11916.8
id1948  502.953953  173.237159  12468.3

# Widths are a list of integers
In [192]: widths = [6, 14, 13, 10]

In [193]: df = pd.read_fwf("bar.csv", widths=widths, header=None)

In [194]: df
Out[194]: 
        0           1           2        3
0  id8141  360.242940  149.910199  11950.7
1  id1594  444.953632  166.985655  11788.4
2  id1849  364.136849  183.628767  11806.2
3  id1230  413.836124  184.375703  11916.8
4  id1948  502.953953  173.237159  12468.3

In [195]: df = pd.read_fwf("bar.csv", header=None, index_col=0)

In [196]: df
Out[196]: 
                 1           2        3
0                                      
id8141  360.242940  149.910199  11950.7
id1594  444.953632  166.985655  11788.4
id1849  364.136849  183.628767  11806.2
id1230  413.836124  184.375703  11916.8
id1948  502.953953  173.237159  12468.3

In [197]: pd.read_fwf("bar.csv", header=None, index_col=0).dtypes
Out[197]: 
1    float64
2    float64
3    float64
dtype: object

In [198]: pd.read_fwf("bar.csv", header=None, dtype={2: "object"}).dtypes
Out[198]: 
0     object
1    float64
2     object
3    float64
dtype: object

In [221]: df = pd.DataFrame(np.random.randn(10, 4))

In [222]: df.to_csv("tmp2.csv", sep=":", index=False)

In [223]: pd.read_csv("tmp2.csv", sep=None, engine="python")
Out[223]: 
          0         1         2         3
0  0.469112 -0.282863 -1.509059 -1.135632
1  1.212112 -0.173215  0.119209 -1.044236
2 -0.861849 -2.104569 -0.494929  1.071804
3  0.721555 -0.706771 -1.039575  0.271860
4 -0.424972  0.567020  0.276232 -1.087401
5 -0.673690  0.113648 -1.478427  0.524988
6  0.404705  0.577046 -1.715002 -1.039268
7 -0.370647 -1.157892 -1.344312  0.844885
8  1.075770 -0.109050  1.643563 -1.469388
9  0.357021 -0.674600 -1.776904 -0.968914

In [224]: df = pd.DataFrame(np.random.randn(10, 4))

In [225]: df.to_csv("tmp.csv", index=False)

In [226]: table = pd.read_csv("tmp.csv")

In [227]: table
Out[227]: 
          0         1         2         3
0 -1.294524  0.413738  0.276662 -0.472035
1 -0.013960 -0.362543 -0.006154 -0.923061
2  0.895717  0.805244 -1.206412  2.565646
3  1.431256  1.340309 -1.170299 -0.226169
4  0.410835  0.813850  0.132003 -0.827317
5 -0.076467 -1.187678  1.130127 -1.436737
6 -1.413681  1.607920  1.024180  0.569605
7  0.875906 -2.211372  0.974466 -2.006747
8 -0.410001 -0.078638  0.545952 -1.219217
9 -1.226825  0.769804 -1.281247 -0.727707

In [228]: with pd.read_csv("tmp.csv", chunksize=4) as reader:
   .....:     print(reader)
   .....:     for chunk in reader:
   .....:         print(chunk)
   .....: 
<pandas.io.parsers.readers.TextFileReader object at 0x7f9452705a80>
          0         1         2         3
0 -1.294524  0.413738  0.276662 -0.472035
1 -0.013960 -0.362543 -0.006154 -0.923061
2  0.895717  0.805244 -1.206412  2.565646
3  1.431256  1.340309 -1.170299 -0.226169
          0         1         2         3
4  0.410835  0.813850  0.132003 -0.827317
5 -0.076467 -1.187678  1.130127 -1.436737
6 -1.413681  1.607920  1.024180  0.569605
7  0.875906 -2.211372  0.974466 -2.006747
          0         1         2         3
8 -0.410001 -0.078638  0.545952 -1.219217
9 -1.226825  0.769804 -1.281247 -0.727707

In [229]: with pd.read_csv("tmp.csv", iterator=True) as reader:
   .....:     print(reader.get_chunk(5))
   .....: 
          0         1         2         3
0 -1.294524  0.413738  0.276662 -0.472035
1 -0.013960 -0.362543 -0.006154 -0.923061
2  0.895717  0.805244 -1.206412  2.565646
3  1.431256  1.340309 -1.170299 -0.226169
4  0.410835  0.813850  0.132003 -0.827317

In [230]: dfj = pd.DataFrame(np.random.randn(5, 2), columns=list("AB"))

In [231]: json = dfj.to_json()

In [232]: json
Out[232]: '{"A":{"0":-0.1213062281,"1":0.6957746499,"2":0.9597255933,"3":-0.6199759194,"4":-0.7323393705},"B":{"0":-0.0978826728,"1":0.3417343559,"2":-1.1103361029,"3":0.1497483186,"4":0.6877383895}}'

In [289]: data = [
   .....:     {"id": 1, "name": {"first": "Coleen", "last": "Volk"}},
   .....:     {"name": {"given": "Mark", "family": "Regner"}},
   .....:     {"id": 2, "name": "Faye Raker"},
   .....: ]
   .....: 

In [290]: pd.json_normalize(data)
Out[290]: 
    id name.first name.last name.given name.family        name
0  1.0     Coleen      Volk        NaN         NaN         NaN
1  NaN        NaN       NaN       Mark      Regner         NaN
2  2.0        NaN       NaN        NaN         NaN  Faye Raker

In [291]: data = [
   .....:     {
   .....:         "state": "Florida",
   .....:         "shortname": "FL",
   .....:         "info": {"governor": "Rick Scott"},
   .....:         "county": [
   .....:             {"name": "Dade", "population": 12345},
   .....:             {"name": "Broward", "population": 40000},
   .....:             {"name": "Palm Beach", "population": 60000},
   .....:         ],
   .....:     },
   .....:     {
   .....:         "state": "Ohio",
   .....:         "shortname": "OH",
   .....:         "info": {"governor": "John Kasich"},
   .....:         "county": [
   .....:             {"name": "Summit", "population": 1234},
   .....:             {"name": "Cuyahoga", "population": 1337},
   .....:         ],
   .....:     },
   .....: ]
   .....: 

In [292]: pd.json_normalize(data, "county", ["state", "shortname", ["info", "governor"]])
Out[292]: 
         name  population    state shortname info.governor
0        Dade       12345  Florida        FL    Rick Scott
1     Broward       40000  Florida        FL    Rick Scott
2  Palm Beach       60000  Florida        FL    Rick Scott
3      Summit        1234     Ohio        OH   John Kasich
4    Cuyahoga        1337     Ohio        OH   John Kasich

In [293]: data = [
   .....:     {
   .....:         "CreatedBy": {"Name": "User001"},
   .....:         "Lookup": {
   .....:             "TextField": "Some text",
   .....:             "UserField": {"Id": "ID001", "Name": "Name001"},
   .....:         },
   .....:         "Image": {"a": "b"},
   .....:     }
   .....: ]
   .....: 

In [294]: pd.json_normalize(data, max_level=1)
Out[294]: 
  CreatedBy.Name Lookup.TextField                    Lookup.UserField Image.a
0        User001        Some text  {'Id': 'ID001', 'Name': 'Name001'}       b

In [295]: from io import StringIO

In [296]: jsonl = """
   .....:     {"a": 1, "b": 2}
   .....:     {"a": 3, "b": 4}
   .....: """
   .....: 

In [297]: df = pd.read_json(StringIO(jsonl), lines=True)

In [298]: df
Out[298]: 
   a  b
0  1  2
1  3  4

In [299]: df.to_json(orient="records", lines=True)
Out[299]: '{"a":1,"b":2}\n{"a":3,"b":4}\n'

# reader is an iterator that returns ``chunksize`` lines each iteration
In [300]: with pd.read_json(StringIO(jsonl), lines=True, chunksize=1) as reader:
   .....:     reader
   .....:     for chunk in reader:
   .....:         print(chunk)
   .....: 
Empty DataFrame
Columns: []
Index: []
   a  b
0  1  2
   a  b
1  3  4

In [301]: from io import BytesIO

In [302]: df = pd.read_json(BytesIO(jsonl.encode()), lines=True, engine="pyarrow")

In [303]: df
Out[303]: 
   a  b
0  1  2
1  3  4

In [304]: df = pd.DataFrame(
   .....:     {
   .....:         "A": [1, 2, 3],
   .....:         "B": ["a", "b", "c"],
   .....:         "C": pd.date_range("2016-01-01", freq="d", periods=3),
   .....:     },
   .....:     index=pd.Index(range(3), name="idx"),
   .....: )
   .....: 

In [305]: df
Out[305]: 
     A  B          C
idx                 
0    1  a 2016-01-01
1    2  b 2016-01-02
2    3  c 2016-01-03

In [306]: df.to_json(orient="table", date_format="iso")
Out[306]: '{"schema":{"fields":[{"name":"idx","type":"integer"},{"name":"A","type":"integer"},{"name":"B","type":"string"},{"name":"C","type":"datetime"}],"primaryKey":["idx"],"pandas_version":"1.4.0"},"data":[{"idx":0,"A":1,"B":"a","C":"2016-01-01T00:00:00.000"},{"idx":1,"A":2,"B":"b","C":"2016-01-02T00:00:00.000"},{"idx":2,"A":3,"B":"c","C":"2016-01-03T00:00:00.000"}]}'

In [320]: df = pd.DataFrame(
   .....:     {
   .....:         "foo": [1, 2, 3, 4],
   .....:         "bar": ["a", "b", "c", "d"],
   .....:         "baz": pd.date_range("2018-01-01", freq="d", periods=4),
   .....:         "qux": pd.Categorical(["a", "b", "c", "c"]),
   .....:     },
   .....:     index=pd.Index(range(4), name="idx"),
   .....: )
   .....: 

In [321]: df
Out[321]: 
     foo bar        baz qux
idx                        
0      1   a 2018-01-01   a
1      2   b 2018-01-02   b
2      3   c 2018-01-03   c
3      4   d 2018-01-04   c

In [322]: df.dtypes
Out[322]: 
foo             int64
bar            object
baz    datetime64[ns]
qux          category
dtype: object

In [323]: df.to_json("test.json", orient="table")

In [324]: new_df = pd.read_json("test.json", orient="table")

In [325]: new_df
Out[325]: 
     foo bar        baz qux
idx                        
0      1   a 2018-01-01   a
1      2   b 2018-01-02   b
2      3   c 2018-01-03   c
3      4   d 2018-01-04   c

In [326]: new_df.dtypes
Out[326]: 
foo             int64
bar            object
baz    datetime64[ns]
qux          category
dtype: object

In [327]: df.index.name = "index"

In [328]: df.to_json("test.json", orient="table")

In [329]: new_df = pd.read_json("test.json", orient="table")

In [330]: print(new_df.index.name)
None

In [320]: url = "https://www.fdic.gov/resources/resolutions/bank-failures/failed-bank-list"
In [321]: pd.read_html(url)
Out[321]:
[                         Bank NameBank           CityCity StateSt  ...              Acquiring InstitutionAI Closing DateClosing FundFund
 0                    Almena State Bank             Almena      KS  ...                          Equity Bank    October 23, 2020    10538
 1           First City Bank of Florida  Fort Walton Beach      FL  ...            United Fidelity Bank, fsb    October 16, 2020    10537
 2                 The First State Bank      Barboursville      WV  ...                       MVB Bank, Inc.       April 3, 2020    10536
 3                   Ericson State Bank            Ericson      NE  ...           Farmers and Merchants Bank   February 14, 2020    10535
 4     City National Bank of New Jersey             Newark      NJ  ...                      Industrial Bank    November 1, 2019    10534
 ..                                 ...                ...     ...  ...                                  ...                 ...      ...
 558                 Superior Bank, FSB           Hinsdale      IL  ...                Superior Federal, FSB       July 27, 2001     6004
 559                Malta National Bank              Malta      OH  ...                    North Valley Bank         May 3, 2001     4648
 560    First Alliance Bank & Trust Co.         Manchester      NH  ...  Southern New Hampshire Bank & Trust    February 2, 2001     4647
 561  National State Bank of Metropolis         Metropolis      IL  ...              Banterra Bank of Marion   December 14, 2000     4646
 562                   Bank of Honolulu           Honolulu      HI  ...                   Bank of the Orient    October 13, 2000     4645

 [563 rows x 7 columns]]

In [322]: url = 'https://www.sump.org/notes/request/' # HTTP request reflector
In [323]: pd.read_html(url)
Out[323]:
[                   0                    1
 0     Remote Socket:  51.15.105.256:51760
 1  Protocol Version:             HTTP/1.1
 2    Request Method:                  GET
 3       Request URI:      /notes/request/
 4     Request Query:                  NaN,
 0   Accept-Encoding:             identity
 1              Host:         www.sump.org
 2        User-Agent:    Python-urllib/3.8
 3        Connection:                close]
In [324]: headers = {
In [325]:    'User-Agent':'Mozilla Firefox v14.0',
In [326]:    'Accept':'application/json',
In [327]:    'Connection':'keep-alive',
In [328]:    'Auth':'Bearer 2*/f3+fe68df*4'
In [329]: }
In [340]: pd.read_html(url, storage_options=headers)
Out[340]:
[                   0                    1
 0     Remote Socket:  51.15.105.256:51760
 1  Protocol Version:             HTTP/1.1
 2    Request Method:                  GET
 3       Request URI:      /notes/request/
 4     Request Query:                  NaN,
 0        User-Agent: Mozilla Firefox v14.0
 1    AcceptEncoding:   gzip,  deflate,  br
 2            Accept:      application/json
 3        Connection:             keep-alive
 4              Auth:  Bearer 2*/f3+fe68df*4]

In [331]: html_str = """
   .....:          <table>
   .....:              <tr>
   .....:                  <th>A</th>
   .....:                  <th colspan="1">B</th>
   .....:                  <th rowspan="1">C</th>
   .....:              </tr>
   .....:              <tr>
   .....:                  <td>a</td>
   .....:                  <td>b</td>
   .....:                  <td>c</td>
   .....:              </tr>
   .....:          </table>
   .....:      """
   .....: 

In [332]: with open("tmp.html", "w") as f:
   .....:     f.write(html_str)
   .....: 

In [333]: df = pd.read_html("tmp.html")

In [334]: df[0]
Out[334]: 
   A  B  C
0  a  b  c

In [335]: dfs = pd.read_html(StringIO(html_str))

In [336]: dfs[0]
Out[336]: 
   A  B  C
0  a  b  c

In [337]: html_table = """
   .....: <table>
   .....:   <tr>
   .....:     <th>GitHub</th>
   .....:   </tr>
   .....:   <tr>
   .....:     <td><a href="https://github.com/pandas-dev/pandas">pandas</a></td>
   .....:   </tr>
   .....: </table>
   .....: """
   .....: 

In [338]: df = pd.read_html(
   .....:     StringIO(html_table),
   .....:     extract_links="all"
   .....: )[0]
   .....: 

In [339]: df
Out[339]: 
                                   (GitHub, None)
0  (pandas, https://github.com/pandas-dev/pandas)

In [340]: df[("GitHub", None)]
Out[340]: 
0    (pandas, https://github.com/pandas-dev/pandas)
Name: (GitHub, None), dtype: object

In [341]: df[("GitHub", None)].str[1]
Out[341]: 
0    https://github.com/pandas-dev/pandas
Name: (GitHub, None), dtype: object

In [342]: from IPython.display import display, HTML

In [343]: df = pd.DataFrame(np.random.randn(2, 2))

In [344]: df
Out[344]: 
          0         1
0 -0.345352  1.314232
1  0.690579  0.995761

In [345]: html = df.to_html()

In [346]: print(html)  # raw html
<table border="1" class="dataframe">
  <thead>
    <tr style="text-align: right;">
      <th></th>
      <th>0</th>
      <th>1</th>
    </tr>
  </thead>
  <tbody>
    <tr>
      <th>0</th>
      <td>-0.345352</td>
      <td>1.314232</td>
    </tr>
    <tr>
      <th>1</th>
      <td>0.690579</td>
      <td>0.995761</td>
    </tr>
  </tbody>
</table>

In [347]: display(HTML(html))
<IPython.core.display.HTML object>

In [348]: html = df.to_html(columns=[0])

In [349]: print(html)
<table border="1" class="dataframe">
  <thead>
    <tr style="text-align: right;">
      <th></th>
      <th>0</th>
    </tr>
  </thead>
  <tbody>
    <tr>
      <th>0</th>
      <td>-0.345352</td>
    </tr>
    <tr>
      <th>1</th>
      <td>0.690579</td>
    </tr>
  </tbody>
</table>

In [350]: display(HTML(html))
<IPython.core.display.HTML object>

In [351]: html = df.to_html(float_format="{0:.10f}".format)

In [352]: print(html)
<table border="1" class="dataframe">
  <thead>
    <tr style="text-align: right;">
      <th></th>
      <th>0</th>
      <th>1</th>
    </tr>
  </thead>
  <tbody>
    <tr>
      <th>0</th>
      <td>-0.3453521949</td>
      <td>1.3142323796</td>
    </tr>
    <tr>
      <th>1</th>
      <td>0.6905793352</td>
      <td>0.9957609037</td>
    </tr>
  </tbody>
</table>

In [353]: display(HTML(html))
<IPython.core.display.HTML object>

In [354]: html = df.to_html(bold_rows=False)

In [355]: print(html)
<table border="1" class="dataframe">
  <thead>
    <tr style="text-align: right;">
      <th></th>
      <th>0</th>
      <th>1</th>
    </tr>
  </thead>
  <tbody>
    <tr>
      <td>0</td>
      <td>-0.345352</td>
      <td>1.314232</td>
    </tr>
    <tr>
      <td>1</td>
      <td>0.690579</td>
      <td>0.995761</td>
    </tr>
  </tbody>
</table>

In [356]: display(HTML(html))
<IPython.core.display.HTML object>

In [357]: print(df.to_html(classes=["awesome_table_class", "even_more_awesome_class"]))
<table border="1" class="dataframe awesome_table_class even_more_awesome_class">
  <thead>
    <tr style="text-align: right;">
      <th></th>
      <th>0</th>
      <th>1</th>
    </tr>
  </thead>
  <tbody>
    <tr>
      <th>0</th>
      <td>-0.345352</td>
      <td>1.314232</td>
    </tr>
    <tr>
      <th>1</th>
      <td>0.690579</td>
      <td>0.995761</td>
    </tr>
  </tbody>
</table>

In [358]: url_df = pd.DataFrame(
   .....:     {
   .....:         "name": ["Python", "pandas"],
   .....:         "url": ["https://www.python.org/", "https://pandas.pydata.org"],
   .....:     }
   .....: )
   .....: 

In [359]: html = url_df.to_html(render_links=True)

In [360]: print(html)
<table border="1" class="dataframe">
  <thead>
    <tr style="text-align: right;">
      <th></th>
      <th>name</th>
      <th>url</th>
    </tr>
  </thead>
  <tbody>
    <tr>
      <th>0</th>
      <td>Python</td>
      <td><a href="https://www.python.org/" target="_blank">https://www.python.org/</a></td>
    </tr>
    <tr>
      <th>1</th>
      <td>pandas</td>
      <td><a href="https://pandas.pydata.org" target="_blank">https://pandas.pydata.org</a></td>
    </tr>
  </tbody>
</table>

In [361]: display(HTML(html))
<IPython.core.display.HTML object>

In [362]: df = pd.DataFrame({"a": list("&<>"), "b": np.random.randn(3)})

In [363]: html = df.to_html()

In [364]: print(html)
<table border="1" class="dataframe">
  <thead>
    <tr style="text-align: right;">
      <th></th>
      <th>a</th>
      <th>b</th>
    </tr>
  </thead>
  <tbody>
    <tr>
      <th>0</th>
      <td>&amp;</td>
      <td>2.396780</td>
    </tr>
    <tr>
      <th>1</th>
      <td>&lt;</td>
      <td>0.014871</td>
    </tr>
    <tr>
      <th>2</th>
      <td>&gt;</td>
      <td>3.357427</td>
    </tr>
  </tbody>
</table>

In [365]: display(HTML(html))
<IPython.core.display.HTML object>

In [366]: html = df.to_html(escape=False)

In [367]: print(html)
<table border="1" class="dataframe">
  <thead>
    <tr style="text-align: right;">
      <th></th>
      <th>a</th>
      <th>b</th>
    </tr>
  </thead>
  <tbody>
    <tr>
      <th>0</th>
      <td>&</td>
      <td>2.396780</td>
    </tr>
    <tr>
      <th>1</th>
      <td><</td>
      <td>0.014871</td>
    </tr>
    <tr>
      <th>2</th>
      <td>></td>
      <td>3.357427</td>
    </tr>
  </tbody>
</table>

In [368]: display(HTML(html))
<IPython.core.display.HTML object>

In [369]: df = pd.DataFrame([[1, 2], [3, 4]], index=["a", "b"], columns=["c", "d"])

In [370]: print(df.style.to_latex())
\begin{tabular}{lrr}
 & c & d \\
a & 1 & 2 \\
b & 3 & 4 \\
\end{tabular}

In [371]: print(df.style.format("€ {}").to_latex())
\begin{tabular}{lrr}
 & c & d \\
a & € 1 & € 2 \\
b & € 3 & € 4 \\
\end{tabular}

In [372]: from io import StringIO

In [373]: xml = """<?xml version="1.0" encoding="UTF-8"?>
   .....: <bookstore>
   .....:   <book category="cooking">
   .....:     <title lang="en">Everyday Italian</title>
   .....:     <author>Giada De Laurentiis</author>
   .....:     <year>2005</year>
   .....:     <price>30.00</price>
   .....:   </book>
   .....:   <book category="children">
   .....:     <title lang="en">Harry Potter</title>
   .....:     <author>J K. Rowling</author>
   .....:     <year>2005</year>
   .....:     <price>29.99</price>
   .....:   </book>
   .....:   <book category="web">
   .....:     <title lang="en">Learning XML</title>
   .....:     <author>Erik T. Ray</author>
   .....:     <year>2003</year>
   .....:     <price>39.95</price>
   .....:   </book>
   .....: </bookstore>"""
   .....: 

In [374]: df = pd.read_xml(StringIO(xml))

In [375]: df
Out[375]: 
   category             title               author  year  price
0   cooking  Everyday Italian  Giada De Laurentiis  2005  30.00
1  children      Harry Potter         J K. Rowling  2005  29.99
2       web      Learning XML          Erik T. Ray  2003  39.95

In [376]: df = pd.read_xml("https://www.w3schools.com/xml/books.xml")

In [377]: df
Out[377]: 
   category              title                  author  year  price      cover
0   cooking   Everyday Italian     Giada De Laurentiis  2005  30.00       None
1  children       Harry Potter            J K. Rowling  2005  29.99       None
2       web  XQuery Kick Start  Vaidyanathan Nagarajan  2003  49.99       None
3       web       Learning XML             Erik T. Ray  2003  39.95  paperback

In [378]: file_path = "books.xml"

In [379]: with open(file_path, "w") as f:
   .....:     f.write(xml)
   .....: 

In [380]: with open(file_path, "r") as f:
   .....:     df = pd.read_xml(StringIO(f.read()))
   .....: 

In [381]: df
Out[381]: 
   category             title               author  year  price
0   cooking  Everyday Italian  Giada De Laurentiis  2005  30.00
1  children      Harry Potter         J K. Rowling  2005  29.99
2       web      Learning XML          Erik T. Ray  2003  39.95

In [382]: with open(file_path, "r") as f:
   .....:     sio = StringIO(f.read())
   .....: 

In [383]: df = pd.read_xml(sio)

In [384]: df
Out[384]: 
   category             title               author  year  price
0   cooking  Everyday Italian  Giada De Laurentiis  2005  30.00
1  children      Harry Potter         J K. Rowling  2005  29.99
2       web      Learning XML          Erik T. Ray  2003  39.95

In [385]: with open(file_path, "rb") as f:
   .....:     bio = BytesIO(f.read())
   .....: 

In [386]: df = pd.read_xml(bio)

In [387]: df
Out[387]: 
   category             title               author  year  price
0   cooking  Everyday Italian  Giada De Laurentiis  2005  30.00
1  children      Harry Potter         J K. Rowling  2005  29.99
2       web      Learning XML          Erik T. Ray  2003  39.95

In [388]: df = pd.read_xml(
   .....:     "s3://pmc-oa-opendata/oa_comm/xml/all/PMC1236943.xml",
   .....:     xpath=".//journal-meta",
   .....: )
   .....: 

In [389]: df
Out[389]: 
              journal-id              journal-title       issn  publisher
0  Cardiovasc Ultrasound  Cardiovascular Ultrasound  1476-7120        NaN

In [390]: df = pd.read_xml(file_path, xpath="//book[year=2005]")

In [391]: df
Out[391]: 
   category             title               author  year  price
0   cooking  Everyday Italian  Giada De Laurentiis  2005  30.00
1  children      Harry Potter         J K. Rowling  2005  29.99

In [392]: df = pd.read_xml(file_path, elems_only=True)

In [393]: df
Out[393]: 
              title               author  year  price
0  Everyday Italian  Giada De Laurentiis  2005  30.00
1      Harry Potter         J K. Rowling  2005  29.99
2      Learning XML          Erik T. Ray  2003  39.95

In [394]: df = pd.read_xml(file_path, attrs_only=True)

In [395]: df
Out[395]: 
   category
0   cooking
1  children
2       web

In [396]: xml = """<?xml version='1.0' encoding='utf-8'?>
   .....: <doc:data xmlns:doc="https://example.com">
   .....:   <doc:row>
   .....:     <doc:shape>square</doc:shape>
   .....:     <doc:degrees>360</doc:degrees>
   .....:     <doc:sides>4.0</doc:sides>
   .....:   </doc:row>
   .....:   <doc:row>
   .....:     <doc:shape>circle</doc:shape>
   .....:     <doc:degrees>360</doc:degrees>
   .....:     <doc:sides/>
   .....:   </doc:row>
   .....:   <doc:row>
   .....:     <doc:shape>triangle</doc:shape>
   .....:     <doc:degrees>180</doc:degrees>
   .....:     <doc:sides>3.0</doc:sides>
   .....:   </doc:row>
   .....: </doc:data>"""
   .....: 

In [397]: df = pd.read_xml(StringIO(xml),
   .....:                  xpath="//doc:row",
   .....:                  namespaces={"doc": "https://example.com"})
   .....: 

In [398]: df
Out[398]: 
      shape  degrees  sides
0    square      360    4.0
1    circle      360    NaN
2  triangle      180    3.0

In [399]: xml = """<?xml version='1.0' encoding='utf-8'?>
   .....: <data xmlns="https://example.com">
   .....:  <row>
   .....:    <shape>square</shape>
   .....:    <degrees>360</degrees>
   .....:    <sides>4.0</sides>
   .....:  </row>
   .....:  <row>
   .....:    <shape>circle</shape>
   .....:    <degrees>360</degrees>
   .....:    <sides/>
   .....:  </row>
   .....:  <row>
   .....:    <shape>triangle</shape>
   .....:    <degrees>180</degrees>
   .....:    <sides>3.0</sides>
   .....:  </row>
   .....: </data>"""
   .....: 

In [400]: df = pd.read_xml(StringIO(xml),
   .....:                  xpath="//pandas:row",
   .....:                  namespaces={"pandas": "https://example.com"})
   .....: 

In [401]: df
Out[401]: 
      shape  degrees  sides
0    square      360    4.0
1    circle      360    NaN
2  triangle      180    3.0

In [405]: xml = """<?xml version='1.0' encoding='utf-8'?>
   .....:  <response>
   .....:   <row>
   .....:     <station id="40850" name="Library"/>
   .....:     <month>2020-09-01T00:00:00</month>
   .....:     <rides>
   .....:       <avg_weekday_rides>864.2</avg_weekday_rides>
   .....:       <avg_saturday_rides>534</avg_saturday_rides>
   .....:       <avg_sunday_holiday_rides>417.2</avg_sunday_holiday_rides>
   .....:     </rides>
   .....:   </row>
   .....:   <row>
   .....:     <station id="41700" name="Washington/Wabash"/>
   .....:     <month>2020-09-01T00:00:00</month>
   .....:     <rides>
   .....:       <avg_weekday_rides>2707.4</avg_weekday_rides>
   .....:       <avg_saturday_rides>1909.8</avg_saturday_rides>
   .....:       <avg_sunday_holiday_rides>1438.6</avg_sunday_holiday_rides>
   .....:     </rides>
   .....:   </row>
   .....:   <row>
   .....:     <station id="40380" name="Clark/Lake"/>
   .....:     <month>2020-09-01T00:00:00</month>
   .....:     <rides>
   .....:       <avg_weekday_rides>2949.6</avg_weekday_rides>
   .....:       <avg_saturday_rides>1657</avg_saturday_rides>
   .....:       <avg_sunday_holiday_rides>1453.8</avg_sunday_holiday_rides>
   .....:     </rides>
   .....:   </row>
   .....:  </response>"""
   .....: 

In [406]: xsl = """<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
   .....:    <xsl:output method="xml" omit-xml-declaration="no" indent="yes"/>
   .....:    <xsl:strip-space elements="*"/>
   .....:    <xsl:template match="/response">
   .....:       <xsl:copy>
   .....:         <xsl:apply-templates select="row"/>
   .....:       </xsl:copy>
   .....:    </xsl:template>
   .....:    <xsl:template match="row">
   .....:       <xsl:copy>
   .....:         <station_id><xsl:value-of select="station/@id"/></station_id>
   .....:         <station_name><xsl:value-of select="station/@name"/></station_name>
   .....:         <xsl:copy-of select="month|rides/*"/>
   .....:       </xsl:copy>
   .....:    </xsl:template>
   .....:  </xsl:stylesheet>"""
   .....: 

In [407]: output = """<?xml version='1.0' encoding='utf-8'?>
   .....:  <response>
   .....:    <row>
   .....:       <station_id>40850</station_id>
   .....:       <station_name>Library</station_name>
   .....:       <month>2020-09-01T00:00:00</month>
   .....:       <avg_weekday_rides>864.2</avg_weekday_rides>
   .....:       <avg_saturday_rides>534</avg_saturday_rides>
   .....:       <avg_sunday_holiday_rides>417.2</avg_sunday_holiday_rides>
   .....:    </row>
   .....:    <row>
   .....:       <station_id>41700</station_id>
   .....:       <station_name>Washington/Wabash</station_name>
   .....:       <month>2020-09-01T00:00:00</month>
   .....:       <avg_weekday_rides>2707.4</avg_weekday_rides>
   .....:       <avg_saturday_rides>1909.8</avg_saturday_rides>
   .....:       <avg_sunday_holiday_rides>1438.6</avg_sunday_holiday_rides>
   .....:    </row>
   .....:    <row>
   .....:       <station_id>40380</station_id>
   .....:       <station_name>Clark/Lake</station_name>
   .....:       <month>2020-09-01T00:00:00</month>
   .....:       <avg_weekday_rides>2949.6</avg_weekday_rides>
   .....:       <avg_saturday_rides>1657</avg_saturday_rides>
   .....:       <avg_sunday_holiday_rides>1453.8</avg_sunday_holiday_rides>
   .....:    </row>
   .....:  </response>"""
   .....: 

In [408]: df = pd.read_xml(StringIO(xml), stylesheet=xsl)

In [409]: df
Out[409]: 
   station_id       station_name  ... avg_saturday_rides  avg_sunday_holiday_rides
0       40850            Library  ...              534.0                     417.2
1       41700  Washington/Wabash  ...             1909.8                    1438.6
2       40380         Clark/Lake  ...             1657.0                    1453.8

[3 rows x 6 columns]

In [1]: df = pd.read_xml(
...         "/path/to/downloaded/enwikisource-latest-pages-articles.xml",
...         iterparse = {"page": ["title", "ns", "id"]}
...     )
...     df
Out[2]:
                                                     title   ns        id
0                                       Gettysburg Address    0     21450
1                                                Main Page    0     42950
2                            Declaration by United Nations    0      8435
3             Constitution of the United States of America    0      8435
4                     Declaration of Independence (Israel)    0     17858
...                                                    ...  ...       ...
3578760               Page:Black cat 1897 07 v2 n10.pdf/17  104    219649
3578761               Page:Black cat 1897 07 v2 n10.pdf/43  104    219649
3578762               Page:Black cat 1897 07 v2 n10.pdf/44  104    219649
3578763      The History of Tom Jones, a Foundling/Book IX    0  12084291
3578764  Page:Shakespeare of Stratford (1926) Yale.djvu/91  104     21450

[3578765 rows x 3 columns]

In [410]: geom_df = pd.DataFrame(
   .....:     {
   .....:         "shape": ["square", "circle", "triangle"],
   .....:         "degrees": [360, 360, 180],
   .....:         "sides": [4, np.nan, 3],
   .....:     }
   .....: )
   .....: 

In [411]: print(geom_df.to_xml())
<?xml version='1.0' encoding='utf-8'?>
<data>
  <row>
    <index>0</index>
    <shape>square</shape>
    <degrees>360</degrees>
    <sides>4.0</sides>
  </row>
  <row>
    <index>1</index>
    <shape>circle</shape>
    <degrees>360</degrees>
    <sides/>
  </row>
  <row>
    <index>2</index>
    <shape>triangle</shape>
    <degrees>180</degrees>
    <sides>3.0</sides>
  </row>
</data>

In [412]: print(geom_df.to_xml(root_name="geometry", row_name="objects"))
<?xml version='1.0' encoding='utf-8'?>
<geometry>
  <objects>
    <index>0</index>
    <shape>square</shape>
    <degrees>360</degrees>
    <sides>4.0</sides>
  </objects>
  <objects>
    <index>1</index>
    <shape>circle</shape>
    <degrees>360</degrees>
    <sides/>
  </objects>
  <objects>
    <index>2</index>
    <shape>triangle</shape>
    <degrees>180</degrees>
    <sides>3.0</sides>
  </objects>
</geometry>

In [413]: print(geom_df.to_xml(attr_cols=geom_df.columns.tolist()))
<?xml version='1.0' encoding='utf-8'?>
<data>
  <row index="0" shape="square" degrees="360" sides="4.0"/>
  <row index="1" shape="circle" degrees="360"/>
  <row index="2" shape="triangle" degrees="180" sides="3.0"/>
</data>

In [414]: print(
   .....:     geom_df.to_xml(
   .....:         index=False,
   .....:         attr_cols=['shape'],
   .....:         elem_cols=['degrees', 'sides'])
   .....: )
   .....: 
<?xml version='1.0' encoding='utf-8'?>
<data>
  <row shape="square">
    <degrees>360</degrees>
    <sides>4.0</sides>
  </row>
  <row shape="circle">
    <degrees>360</degrees>
    <sides/>
  </row>
  <row shape="triangle">
    <degrees>180</degrees>
    <sides>3.0</sides>
  </row>
</data>

In [415]: ext_geom_df = pd.DataFrame(
   .....:     {
   .....:         "type": ["polygon", "other", "polygon"],
   .....:         "shape": ["square", "circle", "triangle"],
   .....:         "degrees": [360, 360, 180],
   .....:         "sides": [4, np.nan, 3],
   .....:     }
   .....: )
   .....: 

In [416]: pvt_df = ext_geom_df.pivot_table(index='shape',
   .....:                                  columns='type',
   .....:                                  values=['degrees', 'sides'],
   .....:                                  aggfunc='sum')
   .....: 

In [417]: pvt_df
Out[417]: 
         degrees         sides        
type       other polygon other polygon
shape                                 
circle     360.0     NaN   0.0     NaN
square       NaN   360.0   NaN     4.0
triangle     NaN   180.0   NaN     3.0

In [418]: print(pvt_df.to_xml())
<?xml version='1.0' encoding='utf-8'?>
<data>
  <row>
    <shape>circle</shape>
    <degrees_other>360.0</degrees_other>
    <degrees_polygon/>
    <sides_other>0.0</sides_other>
    <sides_polygon/>
  </row>
  <row>
    <shape>square</shape>
    <degrees_other/>
    <degrees_polygon>360.0</degrees_polygon>
    <sides_other/>
    <sides_polygon>4.0</sides_polygon>
  </row>
  <row>
    <shape>triangle</shape>
    <degrees_other/>
    <degrees_polygon>180.0</degrees_polygon>
    <sides_other/>
    <sides_polygon>3.0</sides_polygon>
  </row>
</data>

In [419]: print(geom_df.to_xml(namespaces={"": "https://example.com"}))
<?xml version='1.0' encoding='utf-8'?>
<data xmlns="https://example.com">
  <row>
    <index>0</index>
    <shape>square</shape>
    <degrees>360</degrees>
    <sides>4.0</sides>
  </row>
  <row>
    <index>1</index>
    <shape>circle</shape>
    <degrees>360</degrees>
    <sides/>
  </row>
  <row>
    <index>2</index>
    <shape>triangle</shape>
    <degrees>180</degrees>
    <sides>3.0</sides>
  </row>
</data>

In [420]: print(
   .....:     geom_df.to_xml(namespaces={"doc": "https://example.com"},
   .....:                    prefix="doc")
   .....: )
   .....: 
<?xml version='1.0' encoding='utf-8'?>
<doc:data xmlns:doc="https://example.com">
  <doc:row>
    <doc:index>0</doc:index>
    <doc:shape>square</doc:shape>
    <doc:degrees>360</doc:degrees>
    <doc:sides>4.0</doc:sides>
  </doc:row>
  <doc:row>
    <doc:index>1</doc:index>
    <doc:shape>circle</doc:shape>
    <doc:degrees>360</doc:degrees>
    <doc:sides/>
  </doc:row>
  <doc:row>
    <doc:index>2</doc:index>
    <doc:shape>triangle</doc:shape>
    <doc:degrees>180</doc:degrees>
    <doc:sides>3.0</doc:sides>
  </doc:row>
</doc:data>

In [421]: print(
   .....:     geom_df.to_xml(xml_declaration=False,
   .....:                    pretty_print=False)
   .....: )
   .....: 
<data><row><index>0</index><shape>square</shape><degrees>360</degrees><sides>4.0</sides></row><row><index>1</index><shape>circle</shape><degrees>360</degrees><sides/></row><row><index>2</index><shape>triangle</shape><degrees>180</degrees><sides>3.0</sides></row></data>

In [422]: xsl = """<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
   .....:    <xsl:output method="xml" omit-xml-declaration="no" indent="yes"/>
   .....:    <xsl:strip-space elements="*"/>
   .....:    <xsl:template match="/data">
   .....:      <geometry>
   .....:        <xsl:apply-templates select="row"/>
   .....:      </geometry>
   .....:    </xsl:template>
   .....:    <xsl:template match="row">
   .....:      <object index="{index}">
   .....:        <xsl:if test="shape!='circle'">
   .....:            <xsl:attribute name="type">polygon</xsl:attribute>
   .....:        </xsl:if>
   .....:        <xsl:copy-of select="shape"/>
   .....:        <property>
   .....:            <xsl:copy-of select="degrees|sides"/>
   .....:        </property>
   .....:      </object>
   .....:    </xsl:template>
   .....:  </xsl:stylesheet>"""
   .....: 

In [423]: print(geom_df.to_xml(stylesheet=xsl))
<?xml version="1.0"?>
<geometry>
  <object index="0" type="polygon">
    <shape>square</shape>
    <property>
      <degrees>360</degrees>
      <sides>4.0</sides>
    </property>
  </object>
  <object index="1">
    <shape>circle</shape>
    <property>
      <degrees>360</degrees>
      <sides/>
    </property>
  </object>
  <object index="2" type="polygon">
    <shape>triangle</shape>
    <property>
      <degrees>180</degrees>
      <sides>3.0</sides>
    </property>
  </object>
</geometry>

In [439]: df = pd.DataFrame(
   .....:     {
   .....:         "A": np.random.randn(1000),
   .....:         "B": "foo",
   .....:         "C": pd.date_range("20130101", periods=1000, freq="s"),
   .....:     }
   .....: )
   .....: 

In [440]: df
Out[440]: 
            A    B                   C
0   -0.317441  foo 2013-01-01 00:00:00
1   -1.236269  foo 2013-01-01 00:00:01
2    0.896171  foo 2013-01-01 00:00:02
3   -0.487602  foo 2013-01-01 00:00:03
4   -0.082240  foo 2013-01-01 00:00:04
..        ...  ...                 ...
995 -0.171092  foo 2013-01-01 00:16:35
996  1.786173  foo 2013-01-01 00:16:36
997 -0.575189  foo 2013-01-01 00:16:37
998  0.820750  foo 2013-01-01 00:16:38
999 -1.256530  foo 2013-01-01 00:16:39

[1000 rows x 3 columns]

In [441]: df.to_pickle("data.pkl.compress", compression="gzip")

In [442]: rt = pd.read_pickle("data.pkl.compress", compression="gzip")

In [443]: rt
Out[443]: 
            A    B                   C
0   -0.317441  foo 2013-01-01 00:00:00
1   -1.236269  foo 2013-01-01 00:00:01
2    0.896171  foo 2013-01-01 00:00:02
3   -0.487602  foo 2013-01-01 00:00:03
4   -0.082240  foo 2013-01-01 00:00:04
..        ...  ...                 ...
995 -0.171092  foo 2013-01-01 00:16:35
996  1.786173  foo 2013-01-01 00:16:36
997 -0.575189  foo 2013-01-01 00:16:37
998  0.820750  foo 2013-01-01 00:16:38
999 -1.256530  foo 2013-01-01 00:16:39

[1000 rows x 3 columns]

In [444]: df.to_pickle("data.pkl.xz", compression="infer")

In [445]: rt = pd.read_pickle("data.pkl.xz", compression="infer")

In [446]: rt
Out[446]: 
            A    B                   C
0   -0.317441  foo 2013-01-01 00:00:00
1   -1.236269  foo 2013-01-01 00:00:01
2    0.896171  foo 2013-01-01 00:00:02
3   -0.487602  foo 2013-01-01 00:00:03
4   -0.082240  foo 2013-01-01 00:00:04
..        ...  ...                 ...
995 -0.171092  foo 2013-01-01 00:16:35
996  1.786173  foo 2013-01-01 00:16:36
997 -0.575189  foo 2013-01-01 00:16:37
998  0.820750  foo 2013-01-01 00:16:38
999 -1.256530  foo 2013-01-01 00:16:39

[1000 rows x 3 columns]

In [447]: df.to_pickle("data.pkl.gz")

In [448]: rt = pd.read_pickle("data.pkl.gz")

In [449]: rt
Out[449]: 
            A    B                   C
0   -0.317441  foo 2013-01-01 00:00:00
1   -1.236269  foo 2013-01-01 00:00:01
2    0.896171  foo 2013-01-01 00:00:02
3   -0.487602  foo 2013-01-01 00:00:03
4   -0.082240  foo 2013-01-01 00:00:04
..        ...  ...                 ...
995 -0.171092  foo 2013-01-01 00:16:35
996  1.786173  foo 2013-01-01 00:16:36
997 -0.575189  foo 2013-01-01 00:16:37
998  0.820750  foo 2013-01-01 00:16:38
999 -1.256530  foo 2013-01-01 00:16:39

[1000 rows x 3 columns]

In [450]: df["A"].to_pickle("s1.pkl.bz2")

In [451]: rt = pd.read_pickle("s1.pkl.bz2")

In [452]: rt
Out[452]: 
0     -0.317441
1     -1.236269
2      0.896171
3     -0.487602
4     -0.082240
         ...   
995   -0.171092
996    1.786173
997   -0.575189
998    0.820750
999   -1.256530
Name: A, Length: 1000, dtype: float64

In [453]: df.to_pickle("data.pkl.gz", compression={"method": "gzip", "compresslevel": 1})

In [470]: df_tl = pd.DataFrame({"A": list(range(5)), "B": list(range(5))})

In [471]: df_tl.to_hdf("store_tl.h5", key="table", append=True)

In [472]: pd.read_hdf("store_tl.h5", "table", where=["index>2"])
Out[472]: 
   A  B
3  3  3
4  4  4

In [473]: df_with_missing = pd.DataFrame(
   .....:     {
   .....:         "col1": [0, np.nan, 2],
   .....:         "col2": [1, np.nan, np.nan],
   .....:     }
   .....: )
   .....: 

In [474]: df_with_missing
Out[474]: 
   col1  col2
0   0.0   1.0
1   NaN   NaN
2   2.0   NaN

In [475]: df_with_missing.to_hdf("file.h5", key="df_with_missing", format="table", mode="w")

In [476]: pd.read_hdf("file.h5", "df_with_missing")
Out[476]: 
   col1  col2
0   0.0   1.0
1   NaN   NaN
2   2.0   NaN

In [477]: df_with_missing.to_hdf(
   .....:     "file.h5", key="df_with_missing", format="table", mode="w", dropna=True
   .....: )
   .....: 

In [478]: pd.read_hdf("file.h5", "df_with_missing")
Out[478]: 
   col1  col2
0   0.0   1.0
2   2.0   NaN

In [481]: store = pd.HDFStore("store.h5")

In [482]: df1 = df[0:4]

In [483]: df2 = df[4:]

# append data (creates a table automatically)
In [484]: store.append("df", df1)

In [485]: store.append("df", df2)

In [486]: store
Out[486]: 
<class 'pandas.io.pytables.HDFStore'>
File path: store.h5

# select the entire object
In [487]: store.select("df")
Out[487]: 
                   A         B         C
2000-01-01  0.858644 -0.851236  1.058006
2000-01-02 -0.080372 -1.268121  1.561967
2000-01-03  0.816983  1.965656 -1.169408
2000-01-04  0.712795 -0.062433  0.736755
2000-01-05 -0.298721 -1.988045  1.475308
2000-01-06  1.103675  1.382242 -0.650762
2000-01-07 -0.729161 -0.142928 -1.063038
2000-01-08 -1.005977  0.465222 -0.094517

# the type of stored data
In [488]: store.root.df._v_attrs.pandas_type
Out[488]: 'frame_table'

In [489]: store.put("foo/bar/bah", df)

In [490]: store.append("food/orange", df)

In [491]: store.append("food/apple", df)

In [492]: store
Out[492]: 
<class 'pandas.io.pytables.HDFStore'>
File path: store.h5

# a list of keys are returned
In [493]: store.keys()
Out[493]: ['/df', '/food/apple', '/food/orange', '/foo/bar/bah']

# remove all nodes under this level
In [494]: store.remove("food")

In [495]: store
Out[495]: 
<class 'pandas.io.pytables.HDFStore'>
File path: store.h5

In [496]: for (path, subgroups, subkeys) in store.walk():
   .....:     for subgroup in subgroups:
   .....:         print("GROUP: {}/{}".format(path, subgroup))
   .....:     for subkey in subkeys:
   .....:         key = "/".join([path, subkey])
   .....:         print("KEY: {}".format(key))
   .....:         print(store.get(key))
   .....: 
GROUP: /foo
KEY: /df
                   A         B         C
2000-01-01  0.858644 -0.851236  1.058006
2000-01-02 -0.080372 -1.268121  1.561967
2000-01-03  0.816983  1.965656 -1.169408
2000-01-04  0.712795 -0.062433  0.736755
2000-01-05 -0.298721 -1.988045  1.475308
2000-01-06  1.103675  1.382242 -0.650762
2000-01-07 -0.729161 -0.142928 -1.063038
2000-01-08 -1.005977  0.465222 -0.094517
GROUP: /foo/bar
KEY: /foo/bar/bah
                   A         B         C
2000-01-01  0.858644 -0.851236  1.058006
2000-01-02 -0.080372 -1.268121  1.561967
2000-01-03  0.816983  1.965656 -1.169408
2000-01-04  0.712795 -0.062433  0.736755
2000-01-05 -0.298721 -1.988045  1.475308
2000-01-06  1.103675  1.382242 -0.650762
2000-01-07 -0.729161 -0.142928 -1.063038
2000-01-08 -1.005977  0.465222 -0.094517

In [626]: df = pd.DataFrame({"a": [1, 2], "b": [3, 4]})

In [627]: df.to_parquet("test.parquet", engine="pyarrow")

In [628]: df.to_parquet("test.parquet", index=False)

In [629]: df = pd.DataFrame({"a": [0, 0, 1, 1], "b": [0, 1, 0, 1]})

In [630]: df.to_parquet(path="test", engine="pyarrow", partition_cols=["a"], compression=None)

In [641]: import datetime

In [642]: c = ["id", "Date", "Col_1", "Col_2", "Col_3"]

In [643]: d = [
   .....:     (26, datetime.datetime(2010, 10, 18), "X", 27.5, True),
   .....:     (42, datetime.datetime(2010, 10, 19), "Y", -12.5, False),
   .....:     (63, datetime.datetime(2010, 10, 20), "Z", 5.73, True),
   .....: ]
   .....: 

In [644]: data = pd.DataFrame(d, columns=c)

In [645]: data
Out[645]: 
   id       Date Col_1  Col_2  Col_3
0  26 2010-10-18     X  27.50   True
1  42 2010-10-19     Y -12.50  False
2  63 2010-10-20     Z   5.73   True

In [646]: data.to_sql("data", con=engine)
Out[646]: 3

In [647]: data.to_sql("data_chunked", con=engine, chunksize=1000)
Out[647]: 3

In [650]: pd.read_sql_table("data", engine)
Out[650]: 
   index  id       Date Col_1  Col_2  Col_3
0      0  26 2010-10-18     X  27.50   True
1      1  42 2010-10-19     Y -12.50  False
2      2  63 2010-10-20     Z   5.73   True

In [651]: pd.read_sql_table("data", engine, index_col="id")
Out[651]: 
    index       Date Col_1  Col_2  Col_3
id                                      
26      0 2010-10-18     X  27.50   True
42      1 2010-10-19     Y -12.50  False
63      2 2010-10-20     Z   5.73   True

In [652]: pd.read_sql_table("data", engine, columns=["Col_1", "Col_2"])
Out[652]: 
  Col_1  Col_2
0     X  27.50
1     Y -12.50
2     Z   5.73

In [653]: pd.read_sql_table("data", engine, parse_dates=["Date"])
Out[653]: 
   index  id       Date Col_1  Col_2  Col_3
0      0  26 2010-10-18     X  27.50   True
1      1  42 2010-10-19     Y -12.50  False
2      2  63 2010-10-20     Z   5.73   True

In [654]: pd.read_sql_query("SELECT * FROM data", engine)
Out[654]: 
   index  id                        Date Col_1  Col_2  Col_3
0      0  26  2010-10-18 00:00:00.000000     X  27.50      1
1      1  42  2010-10-19 00:00:00.000000     Y -12.50      0
2      2  63  2010-10-20 00:00:00.000000     Z   5.73      1

In [655]: pd.read_sql_query("SELECT id, Col_1, Col_2 FROM data WHERE id = 42;", engine)
Out[655]: 
   id Col_1  Col_2
0  42     Y  -12.5

In [656]: df = pd.DataFrame(np.random.randn(20, 3), columns=list("abc"))

In [657]: df.to_sql(name="data_chunks", con=engine, index=False)
Out[657]: 20

In [658]: for chunk in pd.read_sql_query("SELECT * FROM data_chunks", engine, chunksize=5):
   .....:     print(chunk)
   .....: 
          a         b         c
0 -0.395347 -0.822726 -0.363777
1  1.676124 -0.908102 -1.391346
2 -1.094269  0.278380  1.205899
3  1.503443  0.932171 -0.709459
4 -0.645944 -1.351389  0.132023
          a         b         c
0  0.210427  0.192202  0.661949
1  1.690629 -1.046044  0.618697
2 -0.013863  1.314289  1.951611
3 -1.485026  0.304662  1.194757
4 -0.446717  0.528496 -0.657575
          a         b         c
0 -0.876654  0.336252  0.172668
1  0.337684 -0.411202 -0.828394
2 -0.244413  1.094948  0.087183
3  1.125934 -1.480095  1.205944
4 -0.451849  0.452214 -2.208192
          a         b         c
0 -2.061019  0.044184 -0.017118
1  1.248959 -0.675595 -1.908296
2 -0.125934  1.491974  0.648726
3  0.391214  0.438609  1.634248
4  1.208707 -1.535740  1.620399

In [659]: import sqlalchemy as sa

In [660]: pd.read_sql(
   .....:     sa.text("SELECT * FROM data where Col_1=:col1"), engine, params={"col1": "X"}
   .....: )
   .....: 
Out[660]: 
   index  id                        Date Col_1  Col_2  Col_3
0      0  26  2010-10-18 00:00:00.000000     X   27.5      1

In [661]: metadata = sa.MetaData()

In [662]: data_table = sa.Table(
   .....:     "data",
   .....:     metadata,
   .....:     sa.Column("index", sa.Integer),
   .....:     sa.Column("Date", sa.DateTime),
   .....:     sa.Column("Col_1", sa.String),
   .....:     sa.Column("Col_2", sa.Float),
   .....:     sa.Column("Col_3", sa.Boolean),
   .....: )
   .....: 

In [663]: pd.read_sql(sa.select(data_table).where(data_table.c.Col_3 is True), engine)
Out[663]: 
Empty DataFrame
Columns: [index, Date, Col_1, Col_2, Col_3]
Index: []

In [664]: import datetime as dt

In [665]: expr = sa.select(data_table).where(data_table.c.Date > sa.bindparam("date"))

In [666]: pd.read_sql(expr, engine, params={"date": dt.datetime(2010, 10, 18)})
Out[666]: 
   index       Date Col_1  Col_2  Col_3
0      1 2010-10-19     Y -12.50  False
1      2 2010-10-20     Z   5.73   True

In [667]: df = pd.DataFrame(np.random.randn(10, 2), columns=list("AB"))

In [668]: df.to_stata("stata.dta")

In [669]: pd.read_stata("stata.dta")
Out[669]: 
   index         A         B
0      0 -0.165614  0.490482
1      1 -0.637829  0.067091
2      2 -0.242577  1.348038
3      3  0.647699 -0.644937
4      4  0.625771  0.918376
5      5  0.401781 -1.488919
6      6 -0.981845 -0.046882
7      7 -0.306796  0.877025
8      8 -0.336606  0.624747
9      9 -1.582600  0.806340

In [670]: with pd.read_stata("stata.dta", chunksize=3) as reader:
   .....:     for df in reader:
   .....:         print(df.shape)
   .....: 
(3, 3)
(3, 3)
(3, 3)
(1, 3)

In [671]: with pd.read_stata("stata.dta", iterator=True) as reader:
   .....:     chunk1 = reader.read(5)
   .....:     chunk2 = reader.read(5)
   .....: