Python Pandas Reading Files

Reading from CSV File

A csv stands for Comma Separated Values, which is defined as a simple file format that uses specific structuring to arrange tabular data. It stores tabular data such as spreadsheet or database in plain text and has a common format for data interchange. The csv file is opened into the excel file, and the rows and columns data define the standard format.

Reading the csv file into a pandas DataFrame is quick and straight forward. We don't require to write several lines of code to open, analyze, and read the csv file in pandas. Instead, we can perform these operations in a single line, and it stores the data in DataFrame.

For reading the Pandas files, firstly we have to load data from file formats into a DataFrame. You need only a single line to load your data in code.

df = pd.read_csv('a.csv')

Code

In the above, the three lines of code are enough to read the file, and only one of them is doing the actual work, i.e., pandas.read_csv().

Output:

        Name                   Hire Date      Salary            Leaves Remaining
0     John Idle                08/15/14       50000.0                    10
1     Smith Gilliam            04/07/15       65000.0                     8
2     Parker Chapman           02/21/14       45000.0                    10
3     Jones Palin              10/14/13       70000.0                     3
4     Terry Gilliam            07/22/14       48000.0                     7
5     Michael Palin            06/28/13       66000.0                     8

However, the pandas are also using the zero-based integer indices in the DataFrame; we didn't tell it what our index should be.

Reading from JSON

If you have any JSON file, Pandas can easily read it through a single line of code.

It allowed indexes to work through nesting.

Pandas convert a list of lists into a DataFrame and also define the column names separately. A JSON parser is responsible for converting a JSON text into another representation that must accept all the texts according to the JSON grammar. It can also accept non JSON forms or extensions.

We have to import the JSON file before reading.

Output:

        Name                   Hire Date        Salary            Leaves Remaining
0     John Idle                08/15/14         50000.0                     10
1     Smith Gilliam            06/01/15         65000.0                     6
2     Parker Chapman           05/12/14         45000.0                     7
3     Jones Palin              11/01/13         70000.0                     3	
4     Terry Gilliam            08/12/14         48000.0                     9
5     Michael Palin            05/23/13         66000.0                     8

Reading from the SQL database

For reading a file from the SQL, first, you need to establish a connection using the Python library and then pass the query to pandas. Here, we use SQLite for demonstration.

Firstly, we have to install pysqlite3 and run this command into the terminal:

sqlite3 is used to establish a connection to the database, and then we can use it to generate a DataFrame through SELECT query.

For establishing a connection to the SQLite database file:

A table called information is present in the SQLite database, and the index of the column called "index". We can read data from the information table by passing the SELECT query and the con.

Output:

Index         E_id         Designation              
0              46              M.Com
1              47              B.Com
2              48              B.Com