.svg)
.svg)
Python is a great language for doing data analysis, primarily because of the fantastic ecosystem of data-centric Python packages. Pandas is one of those packages and makes importing and analyzing data much easier.
Pandas aims to integrate the functionality of NumPy and matplotlib to give you a convenient tool for data analytics and visualization. Besides the integration, it also makes the usage far more better.
In this blog, I’ll give you a list of useful pandas snippets that can be reused over and over again. These will definitely save you some time that you may otherwise need to skim through the comprehensive Pandas docs.
The data structures in Pandas are capable of holding elements of any type: Series, DataFrame.
A one-dimensional object that can hold any data type such as integers, floats, and strings
A Series object can be created of different values. Series can be remembered similar to a Python list.
In the below example, NaN is NumPy’s nan symbol which tells us that the element is not a number but it can be used as one numerical type pointing out to be not a number. The type of series is an object because the series has mixed contents of strings and numbers.
| >>> import pandas as pd | |
| >>> import numpy as np | |
| >>> series = pd.Series([12,32,54,2, np.nan, "a string", 6]) | |
| >>> series | |
| 0 12 | |
| 1 32 | |
| 2 54 | |
| 3 2 | |
| 4 NaN | |
| 5 a string | |
| 6 6 | |
| dtype: object |
Now if we use only numerical values, we get the basic NumPy dtype - float for our series.
| >>> series = pd.Series([1,2,np.nan, 4]) | |
| >>> series | |
| 0 1.0 | |
| 1 2.0 | |
| 2 NaN | |
| 3 4.0 | |
| dtype: float64 |
A two-dimensional labeled data structure where columns can be of different types.
Each column in a Pandas DataFrame represents a Series object in memory.
In order to convert a certain Python object (dictionary, lists, etc) to a DataFrame, it is extremely easy. From the python dictionaries, the keys map to Column names while values correspond to a list of column values.
| >>> d = { | |
| "stats": pd.Series(np.arange(10,15,1.0)), | |
| "year": pd.Series(["2012","2007","2012","2003"]), | |
| "intake": pd.Series(["SUMMER","WINTER","WINTER","SUMMER"]), | |
| } | |
| >>> df = pd.DataFrame(d) | |
| >>> df |
Pandas can work with various file types while reading any file you need to remember.
| pd.read_filetype() |
Now you will have to only replace “filetype” with the actual type of the file, like csv or excel. You will have to give the path of the file inside the parenthesis as the first argument. You can also pass in different arguments that relate to opening the file. (Reading a csv file? See this)
| >>> df = pd.read_csv('companies.csv') | |
| >>> df.head() |
DataFrame comprises of three sub-components, the index, columns, and the data (also known as values).
The index represents a sequence of values. In the DataFrame, it always on the left side. Values in an index are in bold font. Each individual value of the index is called a label. Index is like positions while the labels are values at that particular index. Sometimes the index is also referred to as row labels. In all the examples below, the labels and indexes are the same and are just integers beginning from 0 up to n-1, where n is the number of rows in the table.
Selecting rows is done using loc and iloc:
| >>> df.loc[:5] #similar to df.head() |
Accessing the data using column names
Pandas takes an extra step and allows us to access data through labels in DataFrames.
| >>> df.loc[:5, ["name","vertical", "url"]] |
In Pandas, selecting data is very easy and similar to accessing an element from a dictionary or a list.
You can select a column (df[col_name]) and it will return column with label col_name as a Series, because rows and columns are stored as Series in a DataFrame, If you need to access more columns (df[[col_name_1, col_name_2]]) and it returns columns as a new DataFrame.
Let’s say we want all the companies with the vertical as B2B, the logic would be:
| >>> df[(df['vertical'] == 'B2B')] |
If we want the companies for the year 2009, we would use:
| >>> df[(df['year'] == 2009)] |
Need to combine them both? Here’s how you would do it:
| >>> df[(df['vertical'] == 'B2B') & (df['year'] == 2009)] |
Sort values by a certain column in ascending order by using:
| >>> df.sort_values(colname) |
| >>> df.sort_values(colname,ascending=False) |
Furthermore, it’s also possible to sort values by multiple columns with different orders. colname_1 is being sorted in ascending order and colname_2 in descending order by using:
| >>> df.sort_values([colname_1,colname_2],ascending=[True,False]) |
This operation involves 3 steps; splitting of the data, applying a function on each of the group, and finally combining the results into a data structure. This can be used to group large amounts of data and compute operations on these groups.
df.groupby(colname) returns a groupby object for values from one column while df.groupby([col1,col2]) returns a groupby object for values from multiple columns.
Data cleaning is a very important step in data analysis.
Checking missing values in the data
Check null values in the DataFrame by using:
| >>> df.isnull() |
This returns a boolean array (an array of true for missing values and false for non-missing values).
| >>> df.isnull().sum() |
Check non null values in the DataFrame using pd.notnull(). It returns a boolean array, exactly converse of df.notnull()
Removing Empty Values
Dropping empty values can be done easily by using:
| >>> df.dropna() |
This drops the rows having empty values or df.dropna(axis=1) to drop the columns.
Also, if you wish to fill the missing values with other values, use df.fillna(x). This fills all the missing values with the value x (here you can put any value that you want) or s.fillna(s.mean()) which replaces null values with the mean (mean can be replaced with any function from the arithmetic section).
Operations on Complete Rows, Columns, or Even All Data
| >>> df["url_len"] = df["url"].map(len) |
The .map() operation applies a function to each element of a column.
.apply() applies a function to columns. Use .apply(axis=1) to do it on the rows.
Iterating over rows
Very similar to iterating any of the python primitive types such as list, tuples, dictionaries.
| >>> for i, row in df.iterrows(): | |
| print("Index {0}".format(i)) | |
| print("Row {0}".format(row)) |
The .iterrows() loops 2 variables together i.e, the index of the row and the row itself, variable i is the index and variable row is the row in the code above.
Using ufuncs (also known as Universal Functions). Python has the .apply() which applies a function to columns/rows. Similarly, Ufuncs can be used while preprocessing. What is the difference between ufuncs and .apply()?
Ufuncs is a numpy library, implemented in C which is highly efficient (ufuncs are around 10 times faster).
A list of common Ufuncs:
isinf: Element-wise checks for positive or negative infinity.
isnan: Element-wise checks for NaN and returns result as a boolean array.
isnat: Element-wise checks for NaT (not time) and returns result as a boolean array.
trunc: Return the truncated value of the input, element-wise.
.dt commands: Element-wise processing for date objects.
Pandas performs various vectorized/broadcasted operations and grouping-type operations. These operations are efficient and effective.
As of version 0.13, Pandas included tools that allow us to directly access C-speed operations without costly allocation of intermediate arrays. There are two functions, eval() and query().
DataFrame.eval() for efficient operations:
| >>> import pandas as pd | |
| >>> nrows, ncols = 100000, 100 | |
| >>> rng = np.random.RandomState(42) | |
| >>> df1, df2, df3, df4 = (pd.DataFrame(rng.rand(nrows, ncols)) | |
| for i in range(4)) |
To compute the sum of df1, df2, df3, and df4 DataFrames using the typical Pandas approach, we can just write the sum:
| >>> %timeit df1 + df2 + df3 + df4 | |
| 10 loops, best of 3: 103.1 ms per loop |
A better and optimized approach for the same operation can be computed via pd.eval():
| >>> %timeit pd.eval('df1 + df2 + df3 + df4') | |
| 10 loops, best of 3: 53.6 ms per loop |
%timeit — Measure execution time of small code snippets.
The eval() expression is about 50% faster (it also consumes mush less memory).
And it performs the same result:
| >>> np.allclose(df1 + df2 + df3 + df4,d.eval('df1 + df2 + df3 + df4')) | |
| True |
np.allclose() is a numpy function which returns True if two arrays are element-wise equal within a tolerance.
Normal expression to split the first character of a column and assigning it to the same column can be done by using:
| >>> df['batch'] = df['batch'].str[0] |
By using df.eval(), same expression can be performed much faster:
| >>> df.eval("batch=batch.str[0]") |
DataFrame.query() for efficient operations:
Similar to performing filtering operations with conditional logic, to filter rows with vertical as B2B and year as 2009, we do it by using:
| >>> %timeit df[(df['vertical'] == 'B2B') & (df['year'] == 2009)] | |
| 1.69 ms ± 57 µs per loop (mean ± std. dev. of 7 runs, 1000 loops each) |
With .query() the same filtering can be performed about 50% faster.
| >>> %timeit df.query("vertical == 'B2B' and year == 2009") | |
| 875 µs ± 24.6 µs per loop (mean ± std. dev. of 7 runs, 1000 loops each) |
When to use eval() and query()?
Two aspects: computation time and memory usage.
Memory usage: Every operation which involves NumPy/Pandas DataFrames results into implicit creation of temporary variables. In such cases, if the memory usage of these temporary variables is greater, using eval() and query() is an appropriate choice to reduce the memory usage.
Computation time: Traditional method of performing NumPy/Pandas operations is faster for smaller arrays! The real benefit of eval()/query() is achieved mainly because of the saved memory, and also because of the cleaner syntax they offer.
Pandas is a powerful and fun library for data manipulation/analysis. It comes with easy syntax and fast operations. The blog highlights the most used pandas implementation and optimizations. Best way to master your skills over pandas is to use real datasets, beginning with Kaggle kernels to learning how to use pandas for data analysis. Check out more on real time text classification using Kafka and Scikit-learn and explanatory vs. predictive models in machine learning here.
Did you like the blog? If yes, we're sure you'll also like to work with the people who write them - our best-in-class engineering team.
We're looking for talented developers who are passionate about new emerging technologies. If that's you, get in touch with us.
Python is a great language for doing data analysis, primarily because of the fantastic ecosystem of data-centric Python packages. Pandas is one of those packages and makes importing and analyzing data much easier.
Pandas aims to integrate the functionality of NumPy and matplotlib to give you a convenient tool for data analytics and visualization. Besides the integration, it also makes the usage far more better.
In this blog, I’ll give you a list of useful pandas snippets that can be reused over and over again. These will definitely save you some time that you may otherwise need to skim through the comprehensive Pandas docs.
The data structures in Pandas are capable of holding elements of any type: Series, DataFrame.
A one-dimensional object that can hold any data type such as integers, floats, and strings
A Series object can be created of different values. Series can be remembered similar to a Python list.
In the below example, NaN is NumPy’s nan symbol which tells us that the element is not a number but it can be used as one numerical type pointing out to be not a number. The type of series is an object because the series has mixed contents of strings and numbers.
| >>> import pandas as pd | |
| >>> import numpy as np | |
| >>> series = pd.Series([12,32,54,2, np.nan, "a string", 6]) | |
| >>> series | |
| 0 12 | |
| 1 32 | |
| 2 54 | |
| 3 2 | |
| 4 NaN | |
| 5 a string | |
| 6 6 | |
| dtype: object |
Now if we use only numerical values, we get the basic NumPy dtype - float for our series.
| >>> series = pd.Series([1,2,np.nan, 4]) | |
| >>> series | |
| 0 1.0 | |
| 1 2.0 | |
| 2 NaN | |
| 3 4.0 | |
| dtype: float64 |
A two-dimensional labeled data structure where columns can be of different types.
Each column in a Pandas DataFrame represents a Series object in memory.
In order to convert a certain Python object (dictionary, lists, etc) to a DataFrame, it is extremely easy. From the python dictionaries, the keys map to Column names while values correspond to a list of column values.
| >>> d = { | |
| "stats": pd.Series(np.arange(10,15,1.0)), | |
| "year": pd.Series(["2012","2007","2012","2003"]), | |
| "intake": pd.Series(["SUMMER","WINTER","WINTER","SUMMER"]), | |
| } | |
| >>> df = pd.DataFrame(d) | |
| >>> df |
Pandas can work with various file types while reading any file you need to remember.
| pd.read_filetype() |
Now you will have to only replace “filetype” with the actual type of the file, like csv or excel. You will have to give the path of the file inside the parenthesis as the first argument. You can also pass in different arguments that relate to opening the file. (Reading a csv file? See this)
| >>> df = pd.read_csv('companies.csv') | |
| >>> df.head() |
DataFrame comprises of three sub-components, the index, columns, and the data (also known as values).
The index represents a sequence of values. In the DataFrame, it always on the left side. Values in an index are in bold font. Each individual value of the index is called a label. Index is like positions while the labels are values at that particular index. Sometimes the index is also referred to as row labels. In all the examples below, the labels and indexes are the same and are just integers beginning from 0 up to n-1, where n is the number of rows in the table.
Selecting rows is done using loc and iloc:
| >>> df.loc[:5] #similar to df.head() |
Accessing the data using column names
Pandas takes an extra step and allows us to access data through labels in DataFrames.
| >>> df.loc[:5, ["name","vertical", "url"]] |
In Pandas, selecting data is very easy and similar to accessing an element from a dictionary or a list.
You can select a column (df[col_name]) and it will return column with label col_name as a Series, because rows and columns are stored as Series in a DataFrame, If you need to access more columns (df[[col_name_1, col_name_2]]) and it returns columns as a new DataFrame.
Let’s say we want all the companies with the vertical as B2B, the logic would be:
| >>> df[(df['vertical'] == 'B2B')] |
If we want the companies for the year 2009, we would use:
| >>> df[(df['year'] == 2009)] |
Need to combine them both? Here’s how you would do it:
| >>> df[(df['vertical'] == 'B2B') & (df['year'] == 2009)] |
Sort values by a certain column in ascending order by using:
| >>> df.sort_values(colname) |
| >>> df.sort_values(colname,ascending=False) |
Furthermore, it’s also possible to sort values by multiple columns with different orders. colname_1 is being sorted in ascending order and colname_2 in descending order by using:
| >>> df.sort_values([colname_1,colname_2],ascending=[True,False]) |
This operation involves 3 steps; splitting of the data, applying a function on each of the group, and finally combining the results into a data structure. This can be used to group large amounts of data and compute operations on these groups.
df.groupby(colname) returns a groupby object for values from one column while df.groupby([col1,col2]) returns a groupby object for values from multiple columns.
Data cleaning is a very important step in data analysis.
Checking missing values in the data
Check null values in the DataFrame by using:
| >>> df.isnull() |
This returns a boolean array (an array of true for missing values and false for non-missing values).
| >>> df.isnull().sum() |
Check non null values in the DataFrame using pd.notnull(). It returns a boolean array, exactly converse of df.notnull()
Removing Empty Values
Dropping empty values can be done easily by using:
| >>> df.dropna() |
This drops the rows having empty values or df.dropna(axis=1) to drop the columns.
Also, if you wish to fill the missing values with other values, use df.fillna(x). This fills all the missing values with the value x (here you can put any value that you want) or s.fillna(s.mean()) which replaces null values with the mean (mean can be replaced with any function from the arithmetic section).
Operations on Complete Rows, Columns, or Even All Data
| >>> df["url_len"] = df["url"].map(len) |
The .map() operation applies a function to each element of a column.
.apply() applies a function to columns. Use .apply(axis=1) to do it on the rows.
Iterating over rows
Very similar to iterating any of the python primitive types such as list, tuples, dictionaries.
| >>> for i, row in df.iterrows(): | |
| print("Index {0}".format(i)) | |
| print("Row {0}".format(row)) |
The .iterrows() loops 2 variables together i.e, the index of the row and the row itself, variable i is the index and variable row is the row in the code above.
Using ufuncs (also known as Universal Functions). Python has the .apply() which applies a function to columns/rows. Similarly, Ufuncs can be used while preprocessing. What is the difference between ufuncs and .apply()?
Ufuncs is a numpy library, implemented in C which is highly efficient (ufuncs are around 10 times faster).
A list of common Ufuncs:
isinf: Element-wise checks for positive or negative infinity.
isnan: Element-wise checks for NaN and returns result as a boolean array.
isnat: Element-wise checks for NaT (not time) and returns result as a boolean array.
trunc: Return the truncated value of the input, element-wise.
.dt commands: Element-wise processing for date objects.
Pandas performs various vectorized/broadcasted operations and grouping-type operations. These operations are efficient and effective.
As of version 0.13, Pandas included tools that allow us to directly access C-speed operations without costly allocation of intermediate arrays. There are two functions, eval() and query().
DataFrame.eval() for efficient operations:
| >>> import pandas as pd | |
| >>> nrows, ncols = 100000, 100 | |
| >>> rng = np.random.RandomState(42) | |
| >>> df1, df2, df3, df4 = (pd.DataFrame(rng.rand(nrows, ncols)) | |
| for i in range(4)) |
To compute the sum of df1, df2, df3, and df4 DataFrames using the typical Pandas approach, we can just write the sum:
| >>> %timeit df1 + df2 + df3 + df4 | |
| 10 loops, best of 3: 103.1 ms per loop |
A better and optimized approach for the same operation can be computed via pd.eval():
| >>> %timeit pd.eval('df1 + df2 + df3 + df4') | |
| 10 loops, best of 3: 53.6 ms per loop |
%timeit — Measure execution time of small code snippets.
The eval() expression is about 50% faster (it also consumes mush less memory).
And it performs the same result:
| >>> np.allclose(df1 + df2 + df3 + df4,d.eval('df1 + df2 + df3 + df4')) | |
| True |
np.allclose() is a numpy function which returns True if two arrays are element-wise equal within a tolerance.
Normal expression to split the first character of a column and assigning it to the same column can be done by using:
| >>> df['batch'] = df['batch'].str[0] |
By using df.eval(), same expression can be performed much faster:
| >>> df.eval("batch=batch.str[0]") |
DataFrame.query() for efficient operations:
Similar to performing filtering operations with conditional logic, to filter rows with vertical as B2B and year as 2009, we do it by using:
| >>> %timeit df[(df['vertical'] == 'B2B') & (df['year'] == 2009)] | |
| 1.69 ms ± 57 µs per loop (mean ± std. dev. of 7 runs, 1000 loops each) |
With .query() the same filtering can be performed about 50% faster.
| >>> %timeit df.query("vertical == 'B2B' and year == 2009") | |
| 875 µs ± 24.6 µs per loop (mean ± std. dev. of 7 runs, 1000 loops each) |
When to use eval() and query()?
Two aspects: computation time and memory usage.
Memory usage: Every operation which involves NumPy/Pandas DataFrames results into implicit creation of temporary variables. In such cases, if the memory usage of these temporary variables is greater, using eval() and query() is an appropriate choice to reduce the memory usage.
Computation time: Traditional method of performing NumPy/Pandas operations is faster for smaller arrays! The real benefit of eval()/query() is achieved mainly because of the saved memory, and also because of the cleaner syntax they offer.
Pandas is a powerful and fun library for data manipulation/analysis. It comes with easy syntax and fast operations. The blog highlights the most used pandas implementation and optimizations. Best way to master your skills over pandas is to use real datasets, beginning with Kaggle kernels to learning how to use pandas for data analysis. Check out more on real time text classification using Kafka and Scikit-learn and explanatory vs. predictive models in machine learning here.
.svg)
.svg)
