> For the complete documentation index, see [llms.txt](https://codingnotes.gitbook.io/coding_notes/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://codingnotes.gitbook.io/coding_notes/coding/pandas/io-file-read-and-write.md).

# Io File Read And Write

## 1. READING THE FILE

We will learn how to read the three most commonly used file format - csv, xls(xlsx) and json

### 1.1 Reading a CSV file

* CSV is a comma separated key-value pairs, where `key` is the column name and `value` its corresponding column values
* `pd.read_csv()` constructor is used to read the `csv` file
* First (and only required) keyword argument is the location of the file. The returned output is the DataFrame with integer indexes and comma-separated column names as column labels for the DataFrame
* However, when we specify the keyword argument `index_col`, we can specify which columns we want to use as row labels

In the example below, we are going to read the file which is available [here.](http://people.stern.nyu.edu/adamodar/New_Home_Page/datacurrent.html) I dropped some columns in the file to make it presentable because our main purpose is to read/write files rather than working with actual content of the file. In addition, following short terms are used column labels:

* Number of firms = No
* Average Unlevered Beta = AU Beta
* Average Levered Beta = AL Beta

```python
import numpy as np
import pandas as pd 

# location of csv file we are going to read
csv_location = "data/totalbeta.csv"

csv_df = pd.read_csv(csv_location)

# .head() shows only the first five rows in output
csv_df.head() 
```

|   | Industry          | No. | AU Beta | AL Beta |
| - | ----------------- | --- | ------- | ------- |
| 0 | Advertising       | 47  | 0.93    | 1.44    |
| 1 | Aerospace/Defense | 77  | 1.08    | 1.23    |
| 2 | Air Transport     | 18  | 0.84    | 1.44    |
| 3 | Apparel           | 51  | 0.83    | 1.06    |
| 4 | Auto & Truck      | 13  | 0.53    | 1.10    |

Let use the kwarg `index_col` to use specified column value as row label:

```python
# Assigning column "Industry Name" as row labels
# Index positon of "industry Name" is 0 

pd.read_csv(csv_location, index_col=0).head()
```

|                   | No. | AU Beta | AL Beta |
| ----------------- | --- | ------- | ------- |
| Industry          |     |         |         |
| ---               | --- | ---     | ---     |
| Advertising       | 47  | 0.93    | 1.44    |
| Aerospace/Defense | 77  | 1.08    | 1.23    |
| Air Transport     | 18  | 0.84    | 1.44    |
| Apparel           | 51  | 0.83    | 1.06    |
| Auto & Truck      | 13  | 0.53    | 1.10    |

### 1.2. Reading an Excel File

* We can read all spreadsheets in an excel workbook or individual sheets inside the workbook
* `pd.read_excel` constructor is used to read an excel file
* We can provide keyword argument, `sheet_name` to read a specific sheet in workbook — it can be either integer index or exact sheet name as string. `sheet_name=None` returns all sheets
* We can provide keyword argument, `index_col` to set the column name as row label (just like we do in `pd.read_csv`)

```python
xls_location  = "data/totalbeta.xls"

xls_df = pd.read_excel(xls_location)
xls_df.head()
```

|   | Industry          | No. | AU Beta  | AL Beta  |
| - | ----------------- | --- | -------- | -------- |
| 0 | Advertising       | 47  | 0.934953 | 1.439612 |
| 1 | Aerospace/Defense | 77  | 1.078522 | 1.231584 |
| 2 | Air Transport     | 18  | 0.843673 | 1.435348 |
| 3 | Apparel           | 51  | 0.829641 | 1.055097 |
| 4 | Auto & Truck      | 13  | 0.525735 | 1.095074 |

* Let’s fetch all sheet names inside the workbook using kwarg `sheet_name=None` and `.keys` method:

```python
# reading by sheet_name
# first, let fetch all the sheet names in the workbook

pd.read_excel(xls_location, sheet_name=None).keys()
```

```
odict_keys(['US', 'Global', 'Europe', 'Emerging'])
```

* Let’s store each sheet content inside the variable

```python
# storing each sheet in its own dataframe

us_sheet = pd.read_excel(xls_location, sheet_name='US')
global_sheet = pd.read_excel(xls_location, sheet_name='Global')
europe_sheet = pd.read_excel(xls_location, sheet_name='Europe')
emerging_sheet = pd.read_excel(xls_location, sheet_name='Emerging')
```

```python
# read the first sheet by integer indexing

pd.read_excel(xls_location, sheet_name=0).head()
```

|   | Industry          | No. | AU Beta  | AL Beta  |
| - | ----------------- | --- | -------- | -------- |
| 0 | Advertising       | 47  | 0.934953 | 1.439612 |
| 1 | Aerospace/Defense | 77  | 1.078522 | 1.231584 |
| 2 | Air Transport     | 18  | 0.843673 | 1.435348 |
| 3 | Apparel           | 51  | 0.829641 | 1.055097 |
| 4 | Auto & Truck      | 13  | 0.525735 | 1.095074 |

```python
# read the first sheet by its sheet name

pd.read_excel(xls_location, sheet_name='US').head()
```

|   | Industry          | No. | AU Beta  | AL Beta  |
| - | ----------------- | --- | -------- | -------- |
| 0 | Advertising       | 47  | 0.934953 | 1.439612 |
| 1 | Aerospace/Defense | 77  | 1.078522 | 1.231584 |
| 2 | Air Transport     | 18  | 0.843673 | 1.435348 |
| 3 | Apparel           | 51  | 0.829641 | 1.055097 |
| 4 | Auto & Truck      | 13  | 0.525735 | 1.095074 |

```python
# reading more than one sheets

excel_file = pd.read_excel(xls_location, sheet_name=[0,-1])
excel_file.keys()
```

```
odict_keys([0, -1])
```

```python
excel_file[-1].head()
```

|   | Industry Name     | No. | AU Beta  | AL Beta  |
| - | ----------------- | --- | -------- | -------- |
| 0 | Advertising       | 103 | 1.463880 | 1.495675 |
| 1 | Aerospace/Defense | 89  | 1.099578 | 1.175447 |
| 2 | Air Transport     | 86  | 0.590283 | 1.111339 |
| 3 | Apparel           | 907 | 0.635054 | 0.749096 |
| 4 | Auto & Truck      | 83  | 1.127485 | 1.379411 |

### 1.3. Reading a JSON file

* Think of json file as a python dictionary with key-value pair
* `pd.read_json()` constructor is used to read json file
* When no keyword argument is used: outer keys are used as column labels and inner keys are used as row labels
* When we use `orient='index'` keyword argument, outer keys are used as row labels and inner keys are used as column labels

```python
json_location = "data/totalbeta.json"

json_df = pd.read_json(json_location)
json_df.head()
```

|   | Industry          | No. | AU Beta | AL Beta |
| - | ----------------- | --- | ------- | ------- |
| 0 | Advertising       | 47  | 0.93    | 1.44    |
| 1 | Aerospace/Defense | 77  | 1.08    | 1.23    |
| 2 | Air Transport     | 18  | 0.84    | 1.44    |
| 3 | Apparel           | 51  | 0.83    | 1.06    |
| 4 | Auto & Truck      | 13  | 0.53    | 1.10    |

## 2. Writing to File

We will write in three most commonly used formats - csv, xls(xlsx) and json

#### 2.1. Writing to a CSV file

* `.to_csv()` constructor is used to write in `csv` format
* The first keyword argument is the name of file
* By default, `to.csv()` uses the index labels as first column in the csv file, which can be non-meaningful if just integer, we can provide `index=False` keyword argument to not write the row label into a csv file
* for the purpose of demonstration, we will use the data we have stored above under variables `csv_df`, `excel_df` and `json_df`

```python
# writing
csv_df.to_csv('data/tocsv_file.csv') 

# reading the written file
pd.read_csv("data/tocsv_file.csv").head()
```

|   | Unnamed: 0 | Industry          | No. | AU Beta | AL Beta |
| - | ---------- | ----------------- | --- | ------- | ------- |
| 0 | 0          | Advertising       | 47  | 0.93    | 1.44    |
| 1 | 1          | Aerospace/Defense | 77  | 1.08    | 1.23    |
| 2 | 2          | Air Transport     | 18  | 0.84    | 1.44    |
| 3 | 3          | Apparel           | 51  | 0.83    | 1.06    |
| 4 | 4          | Auto & Truck      | 13  | 0.53    | 1.10    |

We can see that the index label is used as first column, which is meaningless in our case, let use `index=False` to not use the index as first column

```python
# setting index=False

# writing
csv_df.to_csv('data/tocsv_index_false.csv', index=False) 

# reading the written file
pd.read_csv("data/tocsv_index_false.csv").head()
```

|   | Industry          | No. | AU Beta | AL Beta |
| - | ----------------- | --- | ------- | ------- |
| 0 | Advertising       | 47  | 0.93    | 1.44    |
| 1 | Aerospace/Defense | 77  | 1.08    | 1.23    |
| 2 | Air Transport     | 18  | 0.84    | 1.44    |
| 3 | Apparel           | 51  | 0.83    | 1.06    |
| 4 | Auto & Truck      | 13  | 0.53    | 1.10    |

#### 2.2. Writing to Excel File

* `.to_excel()` constructor is used to write single sheet to the excel workbook
* To write multiple sheets in excel workbook, first we need to load the excel file using `pd.ExcelWriter`, then use it as first argument in the `.to_excel()`
* default label for sheet is `Sheet1`, `Sheet2` unless we provide it explicitly using `sheet_name` keyword argument
* to avoid writing row label as first column, use `index=False`

```python
# writing
xls_df.to_excel('data/toexcel_file.xls', index=False)

# reading the written file
pd.read_excel('data/toexcel_file.xls').head()
```

|   | Industry          | No. | AU Beta  | AL Beta  |
| - | ----------------- | --- | -------- | -------- |
| 0 | Advertising       | 47  | 0.934953 | 1.439612 |
| 1 | Aerospace/Defense | 77  | 1.078522 | 1.231584 |
| 2 | Air Transport     | 18  | 0.843673 | 1.435348 |
| 3 | Apparel           | 51  | 0.829641 | 1.055097 |
| 4 | Auto & Truck      | 13  | 0.525735 | 1.095074 |

```python
# writing individual sheets to excel workbook

with pd.ExcelWriter('data/toexcel_file_multiple_sheets.xls') as writer:
    us_sheet.to_excel(writer, index=False, sheet_name='usa')
    europe_sheet.to_excel(writer, index=False, sheet_name='europe')
    global_sheet.to_excel(writer, index=False, sheet_name='global')
    emerging_sheet.to_excel(writer, index=False, sheet_name='emerging')
    
# reading the written file

xls_df_new = pd.read_excel('data/toexcel_file_multiple_sheets.xls', sheet_name=None)
xls_df_new.keys()
```

```python
odict_keys(['usa', 'europe', 'global', 'emerging'])
```

```python
xls_df_new['usa'].head()
```

|   | Industry          | No. | AU Beta  | AL Beta  |
| - | ----------------- | --- | -------- | -------- |
| 0 | Advertising       | 47  | 0.934953 | 1.439612 |
| 1 | Aerospace/Defense | 77  | 1.078522 | 1.231584 |
| 2 | Air Transport     | 18  | 0.843673 | 1.435348 |
| 3 | Apparel           | 51  | 0.829641 | 1.055097 |
| 4 | Auto & Truck      | 13  | 0.525735 | 1.095074 |

### 2.3. Write to JSON File

* `.to_json()` constructor is used
* Meaning and application of `orient='index'` in `.to_json` will remain the same as explained in `pd.read_json()`

```python
# writing
json_df.to_json('data/tojson_file.json')

# reading the written file
pd.read_json('data/tojson_file.json').head()
```

|   | Industry          | No. | AU Beta | AL Beta |
| - | ----------------- | --- | ------- | ------- |
| 0 | Advertising       | 47  | 0.93    | 1.44    |
| 1 | Aerospace/Defense | 77  | 1.08    | 1.23    |
| 2 | Air Transport     | 18  | 0.84    | 1.44    |
| 3 | Apparel           | 51  | 0.83    | 1.06    |
| 4 | Auto & Truck      | 13  | 0.53    | 1.10    |