How to Read .data File in Python Pandas

Starting out with Python Pandas DataFrames
What is a Python Pandas DataFrame?
Creating Pandas DataFrames
- Manually entering data
- Loading CSV information into Pandas
Preview and examine data in a Pandas DataFrame
- Impress the data
- DataFrame rows and columns with .shape
- Preview DataFrames with head() and tail()
- Data types (dtypes) of columns
- Describing data with .describe()
Selecting and Manipulating Information
- Selecting columns
- Selecting rows
- Deleting rows and columns (drop)
- Renaming columns
Exporting and Saving Pandas DataFrames
Additional useful functions
- Grouping and aggregation of information
- Plotting Pandas DataFrames – Bars and Lines
Going further

Starting out with Python Pandas DataFrames

If you lot're developing in data scientific discipline, and moving from excel-based analysis to the world of Python, scripting, and automated analysis, you'll come across the incredibly popular data management library, "Pandas" in Python. Pandas development started in 2008 with main developer Wes McKinney and the library has become a standard for data analysis and management using Python. Pandas fluency is essential for whatever Python-based data professional, people interested in trying a Kaggle challenge, or anyone seeking to automate a information process.

The aim of this post is to help beginners become to grips with the basic information format for Pandas – the DataFrame. We will examine basic methods for creating data frames, what a DataFrame actually is, renaming and deleting data frame columns and rows, and where to go next to further your skills.

The topics in this post will enable you (hopefully) to:

Load your data from a file into a Python Pandas DataFrame,
Examine the basic statistics of the data,
Modify some values,
Finally output the result to a new file.

What is a Python Pandas DataFrame?

The Pandas library documentation defines a DataFrame as a "two-dimensional, size-mutable, potentially heterogeneous tabular data structure with labeled axes (rows and columns)". In plain terms, think of a DataFrame as a table of information, i.eastward. a single gear up of formatted two-dimensional data, with the post-obit characteristics:

There tin be multiple rows and columns in the data.
Each row represents a sample of information,
Each cavalcade contains a different variable that describes the samples (rows).
The data in every column is ordinarily the aforementioned type of data – e.one thousand. numbers, strings, dates.
Usually, dissimilar an excel data set, DataFrames avoid having missing values, and there are no gaps and empty values between rows or columns.

By way of case, the following data sets that would fit well in a Pandas DataFrame:

In a school system DataFrame – each row could correspond a single student in the school, and columns may represent the students proper noun (string), historic period (number), date of birth (date), and address (cord).
In an economic science DataFrame, each row may correspond a single urban center or geographical area, and columns might include the the name of surface area (string), the population (number), the average historic period of the population (number), the number of households (number), the number of schools in each expanse (number) etc.
In a shop or due east-commerce system DataFrame, each row in a DataFrame may be used to correspond a client, where there are columns for the number of items purchased (number), the date of original registration (engagement), and the credit card number (string).

Creating Pandas DataFrames

Nosotros'll examine two methods to create a DataFrame – manually, and from comma-separated value (CSV) files.

Manually entering data

The start of every information science project will include getting useful information into an analysis environs, in this case Python. There's multiple ways to create DataFrames of information in Python, and the simplest style is through typing the data into Python manually, which obviously simply works for tiny datasets.

Using Python dictionaries and lists to create DataFrames only works for pocket-size datasets that you can type out manually. At that place are other ways to format manually entered data which y'all can bank check out here.

Note that convention is to load the Pandas library as 'pd' (import pandas as pd). You'll see this notation used frequently online, and in Kaggle kernels.

Loading CSV data into Pandas

Creating DataFrames from CSV (comma-separated value) files is made extremely simple with the read_csv() office in Pandas, one time you know the path to your file. A CSV file is a text file containing data in table form, where columns are separated using the ',' comma grapheme, and rows are on separate lines (run into hither).

If your data is in another form, such equally an SQL database, or an Excel (XLS / XLSX) file, you tin can wait at the other functions to read from these sources into DataFrames, namely read_xlsx, read_sql. However, for simplicity, sometimes extracting data straight to CSV and using that is preferable.

In this example, we're going to load Global Food product data from a CSV file downloaded from the Data Scientific discipline competition website, Kaggle. You can download the CSV file from Kaggle, or straight from hither. The data is nicely formatted, and yous tin can open it in Excel at first to go a preview:

The sample data for this postal service consists of nutrient global product information spanning 1961 to 2013. Here the CSV file is examined in Microsoft Excel.

The sample data contains 21,478 rows of data, with each row corresponding to a food source from a specific country. The first 10 columns correspond information on the sample country and food/feed type, and the remaining columns represent the food product for every twelvemonth from 1963 – 2013 (63 columns in total).

If yous haven't already installed Python / Pandas, I'd recommend setting up Anaconda or WinPython (these are downloadable distributions or bundles that contain Python with the top libraries pre-installed) and using Jupyter notebooks (notebooks allow you to use Python in your browser easily) for this tutorial. Some installation instructions are here.

Load the file into your Python workbook using the Pandas read_csv function similar and so:

Load CSV files into Python to create Pandas Dataframes using the read_csv function. Beginners ofttimes trip upwardly with paths – make sure your file is in the same directory you're working in, or specify the complete path here (it'll start with C:/ if you're using Windows).

If you have path or filename bug, yous'll see FileNotFoundError exceptions like this:

                FileNotFoundError: File b'https://shanelynnwebsite-mid9n9g1q9y8tt.netdna-ssl.com/some/directory/on/your/system/FAO+database.csv' does non exist

Preview and examine data in a Pandas DataFrame

Once you take data in Python, you lot'll want to see the data has loaded, and confirm that the expected columns and rows are present.

Print the information

If you're using a Jupyter notebook, outputs from just typing in the proper name of the data frame will result in nicely formatted outputs. Printing is a user-friendly way to preview your loaded data, yous can confirm that column names were imported correctly, that the data formats are as expected, and if there are missing values anywhere.

pandas output for a dataframe using jupyter notbooks — In a Jupyter notebook, only typing the proper name of a data frame will consequence in a neatly formatted outputs. This is an excellent way to preview data, however notes that, by default, only 100 rows will print, and 20 columns.

Y'all'll observe that Pandas displays just 20 columns past default for wide data dataframes, and only lx or and so rows, truncating the middle section. If you'd like to change these limits, yous tin edit the defaults using some internal options for Pandas displays (simple use pd.options.display.XX = value to set these):

pd.options.display.width – the width of the brandish in characters – employ this if your display is wrapping rows over more than than i line.
pd.options.brandish.max_rows – maximum number of rows displayed.
pd.options.display.max_columns – maximum number of columns displayed.

You can run into the full set of options available in the official Pandas options and settings documentation.

DataFrame rows and columns with .shape

The shape command gives information on the data set up size – 'shape' returns a tuple with the number of rows, and the number of columns for the data in the DataFrame. Another descriptive belongings is the 'ndim' which gives the number of dimensions in your information, typically 2.

Basic descriptions of dataframes are obtained from .shape and .ndim — Get the shape of your DataFrame – the number of rows and columns using .shape, and the number of dimensions using .ndim.

Our food production data contains 21,477 rows, each with 63 columns every bit seen by the output of .shape. We take 2 dimensions – i.e. a 2D data frame with height and width. If your data had but i column, ndim would return 1. Data sets with more than two dimensions in Pandas used to be called Panels, simply these formats take been deprecated. The recommended arroyo for multi-dimensional (>two) data is to use the Xarray Python library.

Preview DataFrames with head() and tail()

The DataFrame.head() function in Pandas, by default, shows you lot the top v rows of information in the DataFrame. The opposite is DataFrame.tail(), which gives yous the last 5 rows.

Pass in a number and Pandas will print out the specified number of rows as shown in the example below. Head() and Tail() need to exist core parts of your go-to Python Pandas functions for investigating your datasets.

Quickly view datasets using pandas head and tail functions. — The first 5 rows of a DataFrame are shown by head(), the final v rows by tail(). For other numbers of rows – simply specify how many yous want!

In our instance here, yous tin see a subset of the columns in the data since at that place are more xx columns overall.

Information types (dtypes) of columns

Many DataFrames take mixed data types, that is, some columns are numbers, some are strings, and some are dates etc. Internally, CSV files do non incorporate information on what data types are contained in each column; all of the information is just characters. Pandas infers the data types when loading the data, e.k. if a cavalcade contains only numbers, pandas volition set that column's data type to numeric: integer or float.

You lot tin can check the types of each column in our case with the '.dtypes' property of the dataframe.

Columns in pandas — Run into the data types of each column in your dataframe using the .dtypes belongings. Notes that graphic symbol/string columns appear as 'object' datatypes.

In some cases, the automated inferring of information types can give unexpected results. Note that strings are loaded as 'object' datatypes, considering technically, the DataFrame holds a pointer to the string information elsewhere in retentiveness. This behaviour is expected, and can be ignored.

To change the datatype of a specific cavalcade, use the .astype() function. For example, to see the 'Item Lawmaking' column as a string, utilize:

data['Item Lawmaking'].astype(str)

Describing information with .describe()

Finally, to see some of the core statistics well-nigh a particular column, you can use the 'describe' function.

For numeric columns, draw() returns basic statistics: the value count, mean, standard departure, minimum, maximum, and 25th, 50th, and 75th quantiles for the data in a column.
For cord columns, describe() returns the value count, the number of unique entries, the nearly often occurring value ('top'), and the number of times the peak value occurs ('freq')

Select a column to describe using a cord within the [] braces, and call depict() as follows:

Describe function in pandas gives basic statistics on the contents of that column — Use the draw() office to get basic statistics on columns in your Pandas DataFrame. Note the differences between columns with numeric datatypes, and columns of strings and characters.

Note that if describe is called on the entire DataFrame, statistics only for the columns with numeric datatypes are returned, and in DataFrame format.

describe() can also be used to summarise all numeric columns in a dataframe — Describing a total dataframe gives summary statistics for the numeric columns just, and the return format is some other DataFrame.

Selecting and Manipulating Data

The information selection methods for Pandas are very flexible. In another post on this site, I've written extensively about the core pick methods in Pandas – namely iloc and loc. For detailed information and to master pick, be sure to read that post. For this case, we volition look at the basic method for column and row selection.

Selecting columns

There are three primary methods of selecting columns in pandas:

using a dot note, e.g. data.column_name,
using square braces and the name of the column every bit a cord, e.chiliad.data['column_name']
or using numeric indexing and the iloc selectorinformation.iloc[:, <column_number>]

selecting columns from data frames in three methods — Three master methods for selecting columns from dataframes in pandas – use the dot notation, foursquare brackets, or iloc methods. The square brackets with column proper name method is the to the lowest degree error prone in my opinion.

When a column is selected using any of these methodologies, a pandas.Series is the resulting datatype. A pandas series is a ane-dimensional fix of data. It'due south useful to know the basic operations that can be carried out on these Series of data, including summing (.sum()), averaging (.hateful()), counting (.count()), getting the median (.median()), and replacing missing values (.fillna(new_value)).

# Series summary operations. # We are selecting the column "Y2007", and performing various calculations. [information['Y2007'].sum(), # Total sum of the column values  data['Y2007'].mean(), # Mean of the column values  data['Y2007'].median(), # Median of the column values  data['Y2007'].nunique(), # Number of unique entries  information['Y2007'].max(), # Maximum of the column values  data['Y2007'].min()] # Minimum of the column values  Out: [10867788.0, 508.48210358863986, seven.0, 1994, 402975.0, 0.0]

Selecting multiple columns at the aforementioned time extracts a new DataFrame from your existing DataFrame. For selection of multiple columns, the syntax is:

square-caryatid selection with a list of column names, east.g.data[['column_name_1', 'column_name_2']]
using numeric indexing with the iloc selector and a list of cavalcade numbers, east.g.data.iloc[:, [0,1,20,22]]

Selecting rows

Rows in a DataFrame are selected, typically, using the iloc/loc selection methods, or using logical selectors (selecting based on the value of another column or variable).

The bones methods to get your heads effectually are:

numeric row selection using the iloc selector, e.k. information.iloc[0:10, :] – select the first 10 rows.
label-based row selection using the loc selector (this is only applicably if yous have set an "index" on your dataframe. e.k.information.loc[44, :]
logical-based row selection using evaluated statements, e.g. information[data["Area"] == "Republic of ireland"] – select the rows where Area value is 'Ireland'.

Notation that you lot can combine the selection methods for columns and rows in many ways to reach the selection of your dreams. For details, delight refer to the post "Using iloc, loc, and ix to select and index data".

Deleting rows and columns (driblet)

To delete rows and columns from DataFrames, Pandas uses the "drop" office.

To delete a column, or multiple columns, use the name of the cavalcade(s), and specify the "axis" as 1. Alternatively, as in the example below, the 'columns' parameter has been added in Pandas which cuts out the need for 'centrality'. The drop function returns a new DataFrame, with the columns removed. To actually edit the original DataFrame, the "inplace" parameter can be ready to True, and there is no returned value.

# Deleting columns  # Delete the "Expanse" column from the dataframe data = information.drop("Area", axis=ane)  # alternatively, delete columns using the columns parameter of driblet data = information.drop(columns="area")  # Delete the Area column from the dataframe in place # Notation that the original 'data' object is changed when inplace=Truthful data.drib("Surface area", axis=ane, inplace=True).   # Delete multiple columns from the dataframe data = data.driblet(["Y2001", "Y2002", "Y2003"], axis=1)

Rows can also exist removed using the "drop" role, by specifying centrality=0. Drop() removes rows based on "labels", rather than numeric indexing. To delete rows based on their numeric position / alphabetize, apply iloc to reassign the dataframe values, every bit in the examples below.

dropping and deleting rows in pandas dataframes — The drop() function in Pandas be used to delete rows from a DataFrame, with the axis ready to 0. As before, the inplace parameter tin be used to alter DataFrames without reassignment.

# Delete the rows with labels 0,1,5 data = data.drop([0,1,two], axis=0)  # Delete the rows with label "Ireland" # For label-based deletion, set the index showtime on the dataframe: data = data.set_index("Surface area") data = data.driblet("Ireland", axis=0). # Delete all rows with label "Ireland"  # Delete the commencement v rows using iloc selector information = information.iloc[5:,]

Renaming columns

Cavalcade renames are accomplished easily in Pandas using the DataFrame rename function. The rename function is piece of cake to use, and quite flexible. Rename columns in these two means:

Rename by mapping erstwhile names to new names using a dictionary, with class {"old_column_name": "new_column_name", …}
Rename by providing a function to change the column names with. Functions are applied to every column name.

# Rename columns using a dictionary to map values # Rename the Surface area columnn to 'place_name' information = data.rename(columns={"Surface area": "place_name"})  # Again, the inplace parameter will change the dataframe without consignment information.rename(columns={"Expanse": "place_name"}, inplace=Truthful)  # Rename multiple columns in one go with a larger dictionary information.rename(     columns={         "Area": "place_name",         "Y2001": "year_2001"     },     inplace=Truthful )  # Rename all columns using a function, e.m. catechumen all column names to lower example: data.rename(columns=str.lower)

In many cases, I use a tidying part for cavalcade names to ensure a standard, camel-case format for variables names. When loading data from potentially unstructured information sets, information technology can be useful to remove spaces and lowercase all column names using a lambda (bearding) function:

# Rapidly lowercase and camelcase all column names in a DataFrame data = pd.read_csv("https://shanelynnwebsite-mid9n9g1q9y8tt.netdna-ssl.com/path/to/csv/file.csv") information.rename(columns=lambda x: ten.lower().replace(' ', '_'))

Exporting and Saving Pandas DataFrames

After manipulation or calculations, saving your data dorsum to CSV is the adjacent step. Data output in Pandas is equally elementary as loading data.

Two two functions you'll need to know are to_csv to write a DataFrame to a CSV file, and to_excel to write DataFrame information to a Microsoft Excel file.

# Output data to a CSV file # Typically, I don't want row numbers in my output file, hence index=Faux. # To avoid character issues, I typically use utf8 encoding for input/output.  information.to_csv("output_filename.csv", alphabetize=False, encoding='utf8')  # Output data to an Excel file. # For the excel output to work, you may need to install the "xlsxwriter" package.  data.to_csv("output_excel_file.xlsx", sheet_name="Sheet 1", index=False)

Boosted useful functions

Grouping and aggregation of data

Every bit before long as y'all load data, you lot'll desire to group it by one value or another, and and then run some calculations. There's another mail service on this blog – Summarising, Aggregating, and Grouping Data in Python Pandas, that goes into extensive detail on this subject.

Plotting Pandas DataFrames – Bars and Lines

At that place's a relatively extensive plotting functionality built into Pandas that tin be used for exploratory charts – especially useful in the Jupyter notebook environs for data analysis.

You lot'll need to have the matplotlib plotting package installed to generate graphics, and the%matplotlib inline notebook 'magic' activated for inline plots. You will also needimport matplotlib.pyplot as plt to add figure labels and centrality labels to your diagrams. A huge amount of functionality is provided by the .plot() command natively by Pandas.

Create a histogram showing the distribution of latitude values in the dataset. Note that "plt" here is imported from matplotlib – 'import matplotlib.pyplot every bit plt'.

bar plots data visualisation using Pandas — Create a bar plot of the top nutrient producers with a combination of information choice, information grouping, and finally plotting using the Pandas DataFrame plot command. All of this could be produced in one line, simply is separated here for clarity.

With plenty interest, plotting and data visualisation with Pandas is the target of a future blog post – let me know in the comments below!

For more information on visualisation with Pandas, brand sure you review:

The official Pandas documentation on plotting and data visualisation.
Simple Graphing with Python from Applied Business Python
Quick and Dirty Data Assay with Pandas from Car Learning Mastery.

Going farther

As your Pandas usage increases, so will your requirements for more advance concepts such every bit reshaping data and merging / joining (see accompanying blog post.). To go started, I'd recommend reading the vi-part "Modern Pandas" from Tom Augspurger every bit an excellent web log postal service that looks at some of the more advanced indexing and information manipulation methods that are possible.

thomasrethe1998.blogspot.com

Source: https://www.shanelynn.ie/using-pandas-dataframe-creating-editing-viewing-data-in-python/