Pandas Techniques in Python for Data Manipulation
Introduction
Python is the language of choice for data scientists. It provides a programming language's larger ecosystem as well as the expertise of competent scientific computation packages.
Pandas is a Python toolkit for creating simple, high-performance data structures and data analysis tools. The word 'panel data,' which refers to multidimensional data sets used in statistics and econometrics, inspired the name. Pandas was the most beneficial for data science operations among its scientific computation libraries. Wes McKinney came up with the moniker "Pandas" in 2008, which refers to both "Panel Data" and "Python Data Analysis."
This post will show you how to manipulate data in Python in different ways. I've also included some pointers that will help you work more efficiently.
Why use pandas?
Pandas makes it possible to evaluate large amounts of data and provide conclusions based on statistical theory.
Pandas can clean up and produce understandable and useful data collections.
In data science, relevant data is critical.
Installation
Simply execute pip install pandas inside the Python environment to install pandas.
C:\Users\Your Name>pip install pandasIf this command fails, try Anaconda, Spyder, or another Python distribution that already has Pandas installed.
Import Pandas
Once Pandas is installed, use the import keyword to include it in your applications:
import pandasNow Pandas is imported and ready to use.
import pandas
mydataset = { 'cars': ["BMW", "Volvo", "Ford"],
'passings': [3, 7, 2] }
myvar = pandas.DataFrame(mydataset)
print(myvar)Output:
cars passings
0 BMW 3
1 Volvo 7
2 Ford 2Pandas is typically imported using the pd alias.
import pandas as pdInstead of pandas, the Pandas package is now known as pd.
import pandas as pd
mydataset = {'cars': ["BMW", "Volvo", "Ford"], 'passings': [3, 7, 2]}
myvar = pd.DataFrame(mydataset)
print(myvar)Output:
cars passings
0 BMW 3
1 Volvo 7
2 Ford 2Pandas Series
What is a Series?
A Pandas Series is like a column in a table.
It is a one-dimensional array holding data of any type.
import pandas as pd
a = [1, 7, 2]
myvar = pd.Series(a)
print(myvar)output:
0 1
1 7
2 2
dtype: int64Create Labels
With the index argument, you can name your own labels.
import pandas as pd
a = [1, 7, 2]
myvar = pd.Series(a, index = ["x", "y", "z"])
print(myvar)output:
x 1
y 7
z 2
dtype: int64Series of Key/Value Objects
When creating a Series, you can also use a key/value object like a dictionary.
import pandas as pd
calories = {"day1": 420, "day2": 380, "day3": 390}
myvar = pd.Series(calories)
print(myvar)output:
day1 420
day2 380
day3 390
dtype: int64Note that the dictionary's keys serve as labels.
DataFrames
DataFrames are multi-dimensional tables that Pandas uses to store data.
A DataFrame is the entire table, whereas a Series is a column.
Make a DataFrame by combining two Series.
import pandas as pd
data = {
"calories": [420, 380, 390],
"duration": [50, 40, 45]
}
myvar = pd.DataFrame(data)
print(myvar)output:
calories duration
0 420 50
1 380 40
2 390 45How row is located.
The DataFrame looks like a table with rows and columns, as you can see from the example above.
The loc property is used by Pandas to retrieve one or more specified rows(s)
print(myvar.loc[0])output:
calories 420
duration 50
Name: 0, dtype: int64It's worth noting that this example yields a Pandas Series.
#use a list of indexes:
print(myvar.loc[[0, 1]])Rows 0 and 1 must be returned:
output:
calories duration
0 420 50
1 380 40When you use [], you'll get a Pandas DataFrame as the output.
Indexes with names
You can name your own indexes with the index parameter.
import pandas as pd
data = {"calories": [420, 380, 390], "duration": [50, 40, 45]}
df = pd.DataFrame(data, index = ["day1", "day2", "day3"])
print(df)output:
calories duration
day1 420 50
day2 380 40
day3 390 45Identify Named Indexes
To return the given row, use the named index in the loc property(s).
#refer to the named index:
print(df.loc["day2"])output:
calories 380
duration 40
Name: day2, dtype: int64Files can loaded into a DataFrame.
Pandas can load your data sets into a DataFrame if they're saved in a file.
CSV Files Can Be Read
CSV files are a straightforward way to store large data collections (comma separated files).
CSV files include plain text and are a well-known format that everyone, even Pandas, can read.
We'll be utilizing a CSV file called 'data.csv' in our examples.
Into a DataFrame, load a comma separated file (CSV file):
import pandas as pd
df = pd.read_csv('data.csv')
print(df)output:
Duration Pulse Maxpulse Calories
0 60 110 130 409.1
1 60 117 145 479.0
2 60 103 135 340.0
3 45 109 175 282.4
4 45 117 148 406.0
.. ... ... ... ...
164 60 105 140 290.8
165 60 110 145 300.0
166 60 115 145 310.2
167 75 120 150 320.4
168 75 125 150 330.4
[169 rows x 4 columns]Getting information about the data
Information about the data can be extracted using the descride and info function as shown below
df.info()output:
<class 'pandas.core.frame.DataFrame'>
RangeIndex: 169 entries, 0 to 168
Data columns (total 4 columns):
# Column Non-Null Count Dtype
--- ------ -------------- -----
0 Duration 169 non-null int64
1 Pulse 169 non-null int64
2 Maxpulse 169 non-null int64
3 Calories 164 non-null float64
dtypes: float64(1), int64(3)
memory usage: 5.4 KBdf.describe()output:
Duration Pulse Maxpulse Calories
count 169.000000 169.000000 169.000000 164.000000
mean 63.846154 107.461538 134.047337 375.790244
std 42.299949 14.510259 16.4504342 66.379919
min 15.000000 80.000000 100.000000 50.300000
25% 45.000000 100.000000 124.000000 250.925000
50% 60.000000 105.000000 131.000000 318.600000
75% 60.000000 111.000000 141.000000 387.600000
max 300.000000 159.000000 184.000000 1860.400000JSON could be read into dataframe.
JSON is frequently used to store or extract large data collections.
JSON is plain text with an object format that is well-known in the programming community, including Pandas.
We'll use a JSON file called 'data.js' in our examples.
import pandas as pd
df = pd.read_json('data.js')
print(df) output:
Duration Pulse Maxpulse Calories
0 60 110 130 409.1
1 60 117 145 479.0
2 60 103 135 340.0
3 45 109 175 282.4
4 45 117 148 406.0
.. ... ... ... ...
164 60 105 140 290.8
165 60 110 145 300.4
166 60 115 145 310.2
167 75 120 150 320.4
168 75 125 150 330.4
[169 rows x 4 columns]Viewing the Information
The head() function is one of the most commonly used methods for gaining a fast overview of the DataFrame.
Starting at the top, the head() function returns the headers and a given number of rows.
import pandas as pd
df = pd.read_csv('data.csv')
print(df.head(10))output:
Duration Pulse Maxpulse Calories
0 60 110 130 409.1
1 60 117 145 479.0
2 60 103 135 340.0
3 45 109 175 282.4
4 45 117 148 406.0
5 60 102 127 300.0
6 60 110 136 374.0
7 45 104 134 253.3
8 30 109 133 195.1
9 60 98 124 269.0The head() function will return the top 5 rows if the number of rows is not given.
There is also a tail() method for viewing the last rows of the DataFrame.
The tail() method returns the headers and a specified number of rows, starting from the bottom.
print(df.tail()) output:
Duration Pulse Maxpulse Calories
164 60 105 140 290.8
165 60 110 145 300.0
166 60 115 145 310.2
167 75 120 150 320.4
168 75 125 150 330.4Information on the Data
The DataFrames object contains a method named info() that provides additional data set information.
print(df.info())output:
<class 'pandas.core.frame.DataFrame'>
RangeIndex: 169 entries, 0 to 168
Data columns (total 4 columns):
# Column Non-Null Count Dtype
--- ------ -------------- -----
0 Duration 169 non-null int64
1 Pulse 169 non-null int64
2 Maxpulse 169 non-null int64
3 Calories 164 non-null float64
dtypes: float64(1), int64(3)
memory usage: 5.4 KB
None
Manipulating Data
Adding new column to existing DataFrame in Pandas
# Import pandas package
import pandas as pd
# Define a dictionary containing Students data
data = {'Name': ['Jai', 'Princi', 'Gaurav', 'Anuj'],
'Height': [5.1, 6.2, 5.1, 5.2],
'Qualification': ['Msc', 'MA', 'Msc', 'Msc']}
# Convert the dictionary into DataFrame
df = pd.DataFrame(data)
display(df)output:
Name Height Qualification
0 Jai 5.1 Msc
1 Princi 6.2 MA
2 Gaurav 5.1 Msc
3 Anuj 5.2 Msc
Method #1: By declaring a new list as a column.
# Declare a list that is to be converted into a column
location = ['Delhi', 'Bangalore', 'Chennai', 'Patna']
# Using 'Address' as the column name
# and equating it to the list
df['Location '] = location
# Observe the result
dfoutput:
Name Height Qualification Location
0 Jai 5.1 Msc Delhi
1 Princi 6.2 MA Bangalore
2 Gaurav 5.1 Msc Chennai
3 Anuj 5.2 Msc Patna
Method #2: By using DataFrame.insert()
# Using DataFrame.insert() to add a column
df.insert(2, "Age", [21, 23, 24, 21], True)
# Observe the result
dfoutput:
Name Height Age Qualification Location
0 Jai 5.1 21 Msc Delhi
1 Princi 6.2 23 MA Bangalore
2 Gaurav 5.1 24 Msc Chennai
3 Anuj 5.2 21 Msc Patna
Using Pandas to Work with Missing Data
When no information is supplied for one or more components, or for a whole unit, it is known as missing data. In real-life circumstances, missing data is a major issue. In pandas, missing data is sometimes referred to as NA (Not Available) values. Many datasets come in DataFrame with missing data, either because it exists but was not gathered or because it never existed. Assume that different people being questioned opt not to reveal their income, and that other users choose not to give their address, and that as a result, several datasets are missing.
None is a Python singleton object that is frequently used in Python programs to represent absent data.
NaN: A special floating-point value accepted by all systems that employ the standard IEEE floating-point encoding is NaN (an abbreviation for Not a Number).
None and NaN are used interchangeably in Pandas to indicate missing or null data. In Pandas DataFrame, there are numerous handy methods for detecting, deleting, and replacing null values to help with this convention:
isnull()
isnotnull()
isdropna()
isfillna()
isreplace()
isinterpolate()
We'll be utilizing a CSV file in this tutorial.
Searching for missing data, isnull() and notnull() are two functions that may be used to check if something is null ()
We utilize the functions isnull() and notnull() in Pandas DataFrame to check for missing data (). Both functions assist in determining whether or not a value is NaN. These functions may also be used to find null values in a series in Pandas Series.
isnull is used to check for missing values ()
We use the isnull() method to check for null values in a Pandas DataFrame. This function returns a dataframe of Boolean values that are True for NaN values.
# importing pandas as pd
import pandas as pd
# importing numpy as np
import numpy as np
# dictionary of lists
dict = {'First Score':[100, 90, np.nan, 95],
'Second Score': [30, 45, 56, np.nan],
'Third Score':[np.nan, 40, 80, 98]}
# creating a dataframe from list
df = pd.DataFrame(dict)
# using isnull() function
df.isnull()output:
First Score Second Score ThirdScore
0 False False True
1 False False False
2 True False False
3 False True FalseCODE 2
# importing pandas package
import pandas as pd
# making data frame from csv file
data = pd.read_csv("employees.csv")
# creating bool series True for NaN values
bool_series = pd.isnull(data["Gender"])
# filtering data
# displaying data only with Gender = NaN
data[bool_series]
notnull() is used to check for missing values
We utilize the notnull() method to check for null values in a Pandas Dataframe. This function returns a dataframe of Boolean values that are False for NaN values.
# importing pandas as pd
import pandas as pd
# importing numpy as np
import numpy as np
# dictionary of lists
dict = {'First Score':[100, 90, np.nan, 95],
'Second Score': [30, 45, 56, np.nan],
'Third Score':[np.nan, 40, 80, 98]}
# creating a dataframe using dictionary
df = pd.DataFrame(dict)
# using notnull() function
df.notnull()output:
FirstScore SecondScore ThirdScore
0 True True False
1 True True True
2 False True True
3 True False True# importing pandas package
import pandas as pd
# making data frame from csv file
data = pd.read_csv("employees.csv")
# creating bool series True for NaN values
bool_series = pd.notnull(data["Gender"])
# filtering data
# displayind data only with Gender = Not NaN
data[bool_series]
output:
FirstName Gender StartDate LastLoginTime Salary Bonus % \
0 Douglas Male 8/6/1993 12:42 PM 97308 6.945
1 Thomas Male 3/31/1996 6:53 AM 61933 4.170
2 Maria Female 4/23/1993 11:17 AM 130590 11.858
3 Jerry Male 3/4/2005 1:00 PM 138705 9.340
4 Larry Male 1/24/1998 4:47 PM 101004 1.389
.. ... ... ... ... ... ...
994 George Male 6/21/2013 5:47 PM 98874 4.479
996 Phillip Male 1/31/1984 6:30 AM 42392 19.675
997 Russell Male 5/20/2013 12:39 PM 96914 1.421
998 Larry Male 4/20/2013 4:45 PM 60500 11.985
999 Albert Male 5/15/2012 6:24 PM 129949 10.169
Senior Management Team
0 True Marketing
1 True NaN
2 False Finance
3 True Finance
4 True Client Services
.. ... ...
994 True Marketing
996 False Finance
997 False Product
998 False Business Development
999 True Sales
[855 rows x 8 columns]
Using fillna(), replace(), and interpolate() to fill in missing values
We employ the fillna(), replace(), and interpolate() functions to fill null values in datasets. These functions substitute NaN values with their own values. All of these functions aid in the filling of null values in DataFrame datasets. The Interpolate() method is used to fill NA values in a dataframe, but instead of hard-coding the value, it employs various interpolation techniques to do it.
# importing pandas as pd
import pandas as pd
# importing numpy as np
import numpy as np
# dictionary of lists
dict = {'First Score':[100, 90, np.nan, 95],
'Second Score': [30, 45, 56, np.nan],
'Third Score':[np.nan, 40, 80, 98]}
# creating a dataframe from dictionary
df = pd.DataFrame(dict)
# filling a missing value with
# previous ones
df.fillna(method ='pad')output:
First Score Second Score Third Score
0 100.0 30.0 NaN
1 90.0 45.0 40.0
2 90.0 56.0 80.0
3 95.0 56.0 98.0# importing pandas package
import pandas as pd
# making data frame from csv file
data = pd.read_csv("employees.csv")
# will replace Nan value in dataframe with value -99
data.replace(to_replace = np.nan, value = -99)output:
First Name Gender Start Date Last Login Time Salary Bonus % Senior Management Team
0 Douglas Male 8/6/1993 12:42 PM 97308 6.945 True Marketing
1 Thomas Male 3/31/1996 6:53 AM 61933 4.170 True -99
2 Maria Female 4/23/1993 11:17 AM 130590 11.858 False Finance
3 Jerry Male 3/4/2005 1:00 PM 138705 9.340 True Finance
4 Larry Male 1/24/1998 4:47 PM 101004 1.389 True Client Services
... ... ... ... ... ... ... ... ...
995 Henry -99 11/23/2014 6:09 AM 132483 16.655 False Distribution
996 Phillip Male 1/31/1984 6:30 AM 42392 19.675 False Finance
997 Russell Male 5/20/2013 12:39 PM 96914 1.421 False Product
998 Larry Male 4/20/2013 4:45 PM 60500 11.985 False Business Development
999 Albert Male 5/15/2012 6:24 PM 129949 10.169 True Sales
Using dropna() to remove missing values
We utilized the dropna() method to remove null values from a dataframe. This function removes rows/columns of datasets containing null values in a variety of ways.
# importing pandas as pd
import pandas as pd
# importing numpy as np
import numpy as np
# dictionary of lists
dict = {'First Score':[100, 90, np.nan, 95],
'Second Score': [30, np.nan, 45, 56],
'Third Score':[52, 40, 80, 98],
'Fourth Score':[np.nan, np.nan, np.nan, 65]}
# creating a dataframe from dictionary
df = pd.DataFrame(dict)
dfoutput:
First Score Second Score Third Score Fourth Score
0 100.0 30.0 52 NaN
1 90.0 NaN 40 NaN
2 NaN 45.0 80 NaN
3 95.0 56.0 98 65.0#Dropping rows with at least 1 null value
# using dropna() function
df.dropna()
output:
First Score Second Score Third Score Fourth Score
3 95.0 56.0 98 65.0
#Dropping columns with at least 1 null value.
# using dropna() function
df.dropna(axis = 1)output:
Third Score
0 52
1 40
2 80
3 98# dictionary of lists
dict = {'First Score':[100, np.nan, np.nan, 95],
'Second Score': [30, np.nan, 45, 56],
'Third Score':[52, np.nan, 80, 98],
'Fourth Score':[np.nan, np.nan, np.nan, 65]}
# creating a dataframe from dictionary
df = pd.DataFrame(dict)
dfoutput:
FirstScore SecondScore ThirdScore FourthScore
0 100.0 30.0 52.0 NaN
1 NaN NaN NaN NaN
2 NaN 45.0 80.0 NaN
3 95.0 56.0 98.0 65.0#Dropping rows if all values in that row are missing
# using dropna() function
df.dropna(how = 'all')
First Score Second Score Third Score Fourth Score
0 100.0 30.0 52.0 NaN
2 NaN 45.0 80.0 NaN
3 95.0 56.0 98.0 65.0Dropping Rows with at least 1 null value in CSV file
# making data frame from csv file
data = pd.read_csv("employees.csv")
# making new data frame with dropped NA values
new_data = data.dropna(axis = 0, how ='any')
new_dataoutput:
First Name Gender Start Date Last Login Time Salary Bonus % Senior Management Team
0 Douglas Male 8/6/1993 12:42 PM 97308 6.945 True Marketing
2 Maria Female 4/23/1993 11:17 AM 130590 11.858 False Finance
3 Jerry Male 3/4/2005 1:00 PM 138705 9.340 True Finance
4 Larry Male 1/24/1998 4:47 PM 101004 1.389 True Client Services
5 Dennis Male 4/18/1987 1:35 AM 115163 10.125 False Legal
... ... ... ... ... ... ... ... ...
994 George Male 6/21/2013 5:47 PM 98874 4.479 True Marketing
996 Phillip Male 1/31/1984 6:30 AM 42392 19.675 False Finance
997 Russell Male 5/20/2013 12:39 PM 96914 1.421 False Product
998 Larry Male 4/20/2013 4:45 PM 60500 11.985 False Business Development
999 Albert Male 5/15/2012 6:24 PM 129949 10.169 True Sales
764 rows × 8 columnsyou can also count the unique value in a particular column
new_data.Team.value_counts()output:
Business Development 88
Client Services 85
Product 83
Finance 80
Engineering 79
Human Resources 76
Marketing 74
Sales 72
Legal 67
Distribution 60
Name: Team, dtype: int64you can also change column name as shown below:
new_data = data.rename(columns = {'Team':'Department'})
new_data.head()output:
FirstName Gender StartDate LastLoginTime Salary Bonus% SeniorManagement Department
0 Douglas Male 8/6/1993 12:42PM 97308 6.945 True Marketing
1 Thomas Male 3/31/1996 6:53AM 61933 4.170 True NaN
2 Maria Female 4/23/1993 11:17AM 130590 11.858 False Finance
3 Jerry Male 3/4/2005 1:00PM 138705 9.340 True Finance
4 Larry Male 1/24/1998 4:47PM 101004 1.389 True Client ServicesI’ll be using a company data provided by an Intrinio developer sandbox.
import pandas as pd
import numpy as np
import urllib.request
url='https://raw.githubusercontent.com/leosmigel/analyzingalpha/master/2019-09-30-data-manipulation-with-python/companies.csv'
with urllib.request.urlopen(url) as f:
companies = pd.read_csv(f, index_col='id')
companies.head()output:
name cik sector industry_category industry_group employees
id
1 Apple Inc 320193 Consumer Goods Consumer Durables Electronic Equipment 132000
2 American Express Co 4962 Financial Financial Services Credit Services 59000
3 Boeing Co 12927 Industrial Goods Aerospace/Defense Aerospace/Defense Products & Services 153027
4 Caterpillar Inc 18230 Industrial Goods Manufacturing Farm & Construction Machinery 104000
5 Cisco Systems Inc 858877 Technology Computer Hardware Networking & Communication Devices 74200groupby()
In data analysis, the "groupby()" function is highly important since it helps us to see the underlying correlations between distinct variables. Then, using the "agg()" method, we can apply Aggregations to the groups and feed it different aggregation operations such as mean, size, sum, std, and so on.
Aggregation
Aggregation takes the values and transforms them into a value of a smaller dimension. A function that accepts a series and returns a single scalar value is an example. A grouping column, an aggregate column, and a function column are all present in every GroupBy aggregation, either implicitly or explicitly. The following are some common aggregating functions:
FUNCTION | DESCRIPTION |
mean() | Compute mean of groups |
sum() | Compute sum of group values |
size() | Compute group sizes |
count() | Compute count of group |
std() | Standard deviation of groups |
var() | Compute variance of groups |
sem() | Standard error of the mean of groups |
describe() | Generates descriptive statistics |
first() | Compute first of group values |
last() | Compute last of group values |
nth() | Take nth value, or a subset if n is a list |
min() | Compute min of group values |
max() | Compute max of group values |
Lets consider an example of calculating the total number of enterprises in each industry.
sector_group = companies.groupby(by='sector')
print(sector_group.size())
sector_group.size().sum()output:
sector
Basic Materials 2
Consumer Goods 4
Financial 5
Healthcare 4
Industrial Goods 5
Services 3
Technology 5
dtype: int64
28We may also divide the data into groups based on numerous columns. The aggregate function was automatically called on the workers column because it is the sole number-type column.
companies.groupby(['sector', 'industry_group'])['employees'].sum()output:
sector industry_group
Basic Materials Major Integrated Oil & Gas 119600
Consumer Goods Beverages - Soft Drinks 62600
Electronic Equipment 132000
Personal Products 92000
Textile - Apparel Footwear & Accessories 73100
Financial Credit Services 76000
Investment Brokerage - National 36600
Money Center Banks 256105
Property & Casualty Insurance 30400
Healthcare Drug Manufacturers - Major 296500
Health Care Plans 300000
Industrial Goods Aerospace/Defense Products & Services 393027
Diversified Machinery 376516
Farm & Construction Machinery 104000
Services Discount, Variety Stores 2200000
Home Improvement Stores 413000
Restaurants 210000
Technology Business Software & Services 131000
Information Technology Services 350600
Networking & Communication Devices 74200
Semiconductor - Broad Line 107400
Telecom Services - Domestic 144500
Name: employees, dtype: int64Transformation
The altered data is returned in the same size as the provided data. To alter data, you may use a variety of methods and functions, as demonstrated below.
METHOD / FUNCTION | DESCRIPTION |
Series.map | Substitute each value for another |
Series.apply | Invoke a function elementwise on a series |
DataFrame.applymap | Apply a function elementwise to a dataframe |
DataFrame.apply | Invoke a function on each column or row |
Series.transform | Invoke a function returning series of transformed values |
DataFrame.transform | Invoke a function returning dataframe of transformed values |
pandas.melt | Reshape a dataframe. Useful for graphing, vectorized operations, and tidying data. |
pandas.pivot | Reshape a dataframe. Essentially an unmelt operation
|
We supply a specified function find percent and return the converted series in the example below.
def find_percent(column):
return column / float(column.sum())
companies.groupby('sector').agg({'employees': 'sum'}).transform(find_percent)output:
employees
sector
Basic Materials 0.020003
Consumer Goods 0.060159
Financial 0.066749
Healthcare 0.099763
Industrial Goods 0.146098
Services 0.472141
Technology 0.135086We can pass numerous functions, including lambda functions, just like we can with agg.
companies.groupby('sector').agg({'employees':'sum'}).transform([lambda x: x / x.sum()])output:
employees
<lambda>
sector
Basic Materials 0.020003
Consumer Goods 0.060159
Financial 0.066749
Healthcare 0.099763
Industrial Goods 0.146098
Services 0.472141
Technology 0.135086
While we can't send multiple functions to apply as we can with transform, we can use apply to access other columns that transform can't. Again, it's essential to be aware of what's available and to choose the greatest tool for the work at hand.
companies.groupby('sector').apply(lambda x: x['employees'] * 2)ouput:
sector id
Basic Materials 6 97200
28 142000
Consumer Goods 1 264000
14 125200
19 146200
21 184000
Financial 2 118000
25 34000
8 73200
13 512210
22 60800
Healthcare 12 270200
17 138000
20 184800
23 600000
Industrial Goods 3 306054
4 208000
7 566000
24 480000
16 187032
Services 9 826000
15 420000
27 4400000
Technology 5 148400
10 701200
11 214800
18 262000
26 289000
Name: employees, dtype: int64
Filtration
The data returned by the filter is a subset of the original data. It operates in a similar way as boolean indexing, only it works on individual groups rather than individual rows. For the whole group, Filter must return a True or False response. The services industry is the only one with more than one million workers.
companies.groupby('sector').filter(
lambda x: x['employees'].sum() > 1000000
)[['name', 'employees']]output:
name employees
id
9 Home Depot Inc 413000
15 McDonald's Corp 210000
27 Walmart Inc 2200000
Pivot Tables
Using pandas.pivot table, we may generate the same data in a different format.
companies.pivot_table(columns='sector', values='employees', aggfunc='sum')output:
sector Basic Materials Consumer Goods Financial Healthcare Industrial Goods Services Technology
employees 119600 359700 399105 596500 873543 2823000 807700Joining Data
By using join, merge, and concat, we may swiftly join two dataframes. All join/merge behavior is based on the merging function. The settings for Join are slightly different, and it is given as a convenience technique.
METHOD / FUNCTION | DESCRIPTION |
pandas.DataFrame.join | Join dataframe on on index, or key column |
pandas.DataFrame.merge | Merge dataframe using database-style joins |
pandas.concat | Concatenate dataframes along a particular axis |
import pandas as pd
import numpy as np
import urllib.request
url= 'https://raw.githubusercontent.com/leosmigel/analyzingalpha/master/2019-09-30-data-manipulation-with-python/securities.csv'
with urllib.request.urlopen(url) as f:
securities = pd.read_csv(f, index_col='id')
securities.head() output:
ticker currency figi
id
1 AAPL USD BBG000B9Y5X2
2 AXP USD BBG000BCR153
3 BA USD BBG000BCSV38
4 CAT USD BBG000BF0LJ6
5 CSCO USD BBG000C3JBN9securities_companies = companies.join(securities)
securities_companies.head()output:
name cik sector industry_category industry_group employees ticker currency figi
id
1 Apple Inc 320193 Consumer Goods Consumer Durables Electronic Equipment 132000 AAPL USD BBG000B9Y5X2
2 American Express Co 4962 Financial Financial Services Credit Services 59000 AXP USD BBG000BCR153
3 Boeing Co 12927 Industrial Goods Aerospace/Defense Aerospace/Defense Products & Services 153027 BA USD BBG000BCSV38
4 Caterpillar Inc 18230 Industrial Goods Manufacturing Farm & Construction Machinery 104000 CAT USD BBG000BF0LJ6
5 Cisco Systems Inc 858877 Technology Computer Hardware Networking & Communication Devices 74200 CSCO USD BBG000C3JBN9Conclusion
Pandas is an open source library for analyzing data in Python. It reads data from a CSV or SQL database and generates a data frame, which is an object containing rows and columns. In this post, we discussed different Pandas functions that might make our lives easier while undertaking data exploration and feature engineering. In addition, we built several generic methods that may be utilized to achieve similar goals on various datasets.