learn-co-students / dsc-introduction-pandas-etl-notredame-se-ds-preview

Introduction - Pandas ETL

Introduction

In this section, you will learn invaluable skills that will form the foundation of your data processing work. Before you can apply machine learning algorithms or do interesting analysis, you often must clean and transform your data into a suitable format. Such initial data wrangling processes are often refered to as Extract Transform Load (ETL). Our primary tool of choice for performing ETL and basic analyses will be the Pandas package.

Objectives

You will be able to:

• Apply functions to columns to extract pertinent information
• Group datasets by categories or features
• Find duplicate data
• Remove duplicate data
• Create pivot tables

Why ETL?

ETL is an essential first step to data analysis and data science. It also will form the foundation for exploratory data analysis. Often, you will be thrown a dataset that you have little to no information about. In these cases, your first step is to explore the data and get familiar with it. What are the columns? How many observations do you have? Are there missing values? If we have user level data, how can we explore aggregate trends along features like gender, race, or geography? All of these can be answered by applying ETL to transform raw datasets into alternative useful views.

Quick ETL Examples

While you'll see complete examples and explanations for all of these techniques (and more), here's a quick preview of some ETL techniques covered in this section! For more details, continue on to future lessons!

Reading in Data

import pandas as pd
df = pd.read_csv('Yelp_Reviews.csv')
df.head()

<style scoped> .dataframe tbody tr th:only-of-type { vertical-align: middle; }

.dataframe tbody tr th {
    vertical-align: top;
}

.dataframe thead th {
    text-align: right;
}

</style>

	Unnamed: 0	business_id	cool	date	funny	review_id	stars	text	useful	user_id
0	1	pomGBqfbxcqPv14c3XH-ZQ	0	2012-11-13	0	dDl8zu1vWPdKGihJrwQbpw	5	I love this place! My fiance And I go here atl...	0	msQe1u7Z_XuqjGoqhB0J5g
1	2	jtQARsP6P-LbkyjbO1qNGg	1	2014-10-23	1	LZp4UX5zK3e-c5ZGSeo3kA	1	Terrible. Dry corn bread. Rib tips were all fa...	3	msQe1u7Z_XuqjGoqhB0J5g
2	4	Ums3gaP2qM3W1XcA5r6SsQ	0	2014-09-05	0	jsDu6QEJHbwP2Blom1PLCA	5	Delicious healthy food. The steak is amazing. ...	0	msQe1u7Z_XuqjGoqhB0J5g
3	5	vgfcTvK81oD4r50NMjU2Ag	0	2011-02-25	0	pfavA0hr3nyqO61oupj-lA	1	This place sucks. The customer service is horr...	2	msQe1u7Z_XuqjGoqhB0J5g
4	10	yFumR3CWzpfvTH2FCthvVw	0	2016-06-15	0	STiFMww2z31siPY7BWNC2g	5	I have been an Emerald Club member for a numbe...	0	TlvV-xJhmh7LCwJYXkV-cg

Appling Functions

df['Review_Word_Length'] = df.text.map(lambda x: len(x.split()))
df.head()

<style scoped> .dataframe tbody tr th:only-of-type { vertical-align: middle; }

.dataframe tbody tr th {
    vertical-align: top;
}

.dataframe thead th {
    text-align: right;
}

</style>

	Unnamed: 0	business_id	cool	date	funny	review_id	stars	text	useful	user_id	Review_Word_Length
0	1	pomGBqfbxcqPv14c3XH-ZQ	0	2012-11-13	0	dDl8zu1vWPdKGihJrwQbpw	5	I love this place! My fiance And I go here atl...	0	msQe1u7Z_XuqjGoqhB0J5g	58
1	2	jtQARsP6P-LbkyjbO1qNGg	1	2014-10-23	1	LZp4UX5zK3e-c5ZGSeo3kA	1	Terrible. Dry corn bread. Rib tips were all fa...	3	msQe1u7Z_XuqjGoqhB0J5g	30
2	4	Ums3gaP2qM3W1XcA5r6SsQ	0	2014-09-05	0	jsDu6QEJHbwP2Blom1PLCA	5	Delicious healthy food. The steak is amazing. ...	0	msQe1u7Z_XuqjGoqhB0J5g	30
3	5	vgfcTvK81oD4r50NMjU2Ag	0	2011-02-25	0	pfavA0hr3nyqO61oupj-lA	1	This place sucks. The customer service is horr...	2	msQe1u7Z_XuqjGoqhB0J5g	82
4	10	yFumR3CWzpfvTH2FCthvVw	0	2016-06-15	0	STiFMww2z31siPY7BWNC2g	5	I have been an Emerald Club member for a numbe...	0	TlvV-xJhmh7LCwJYXkV-cg	32

Grouping Data

df.groupby('business_id')['stars'].mean().head()

business_id
-050d_XIor1NpCuWkbIVaQ    5.0
-0qht1roIqleKiQkBLDkbw    1.0
-3zffZUHoY8bQjGfPSoBKQ    5.0
-6tvduBzjLI1ISfs3F_qTg    5.0
-9nai28tnoylwViuJVrYEQ    5.0
Name: stars, dtype: float64

Checking for Duplicates

#Use the keep=False to keep the duplicates and sort values to put duplicates next to each other
df[df.duplicated(keep=False)].sort_values(by='business_id')

<style scoped> .dataframe tbody tr th:only-of-type { vertical-align: middle; }

.dataframe tbody tr th {
    vertical-align: top;
}

.dataframe thead th {
    text-align: right;
}

</style>

	Unnamed: 0	business_id	cool	date	funny	review_id	stars	text	useful	user_id	Review_Word_Length
2232	1729	-GY2fx-8udXPY8qn2HVBCg	0	2016-08-30	0	yQ6P1_CvM94wMLYw1T0UWA	5	Just opened a new account today. So far I am ...	1	sZfZGrI592euyacKUcwQYg	55
473	1729	-GY2fx-8udXPY8qn2HVBCg	0	2016-08-30	0	yQ6P1_CvM94wMLYw1T0UWA	5	Just opened a new account today. So far I am ...	1	sZfZGrI592euyacKUcwQYg	55
1690	754	-LRlx2j9_LB3evsRRcC9MA	0	2017-10-07	0	kUqPsZmWwLIMSstGHhWssA	5	The vet took the time to explain what was poss...	0	VgaYZ7004pTwEDSDWR6u4Q	33
222	754	-LRlx2j9_LB3evsRRcC9MA	0	2017-10-07	0	kUqPsZmWwLIMSstGHhWssA	5	The vet took the time to explain what was poss...	0	VgaYZ7004pTwEDSDWR6u4Q	33
811	2767	-MKWJZnMjSit406AUKf7Pg	0	2015-01-03	2	rJhrQD3-b9GjTso0dxIkwg	1	Drove 37 miles on a Saturday at 12:30pm for lu...	0	kzP96uX8TUMmmvLtd-I3RQ	18
2358	2767	-MKWJZnMjSit406AUKf7Pg	0	2015-01-03	2	rJhrQD3-b9GjTso0dxIkwg	1	Drove 37 miles on a Saturday at 12:30pm for lu...	0	kzP96uX8TUMmmvLtd-I3RQ	18
898	3126	-eIvRc3aEvufstBumpBTPQ	0	2015-07-02	0	rn49y5dm_3sRZ5_LrODFLA	5	After going to several physio clinics over the...	0	uK0LdXjF93PuPzVgo-Jsgg	95
2588	3126	-eIvRc3aEvufstBumpBTPQ	0	2015-07-02	0	rn49y5dm_3sRZ5_LrODFLA	5	After going to several physio clinics over the...	0	uK0LdXjF93PuPzVgo-Jsgg	95
2074	2092	-lJtyCOTVInWusU9YF120A	0	2015-11-08	0	qFNtxmCldq0bUoRF1OdpQA	5	One of our favorite Italian restaurants. Fabul...	0	Ac3vNCPDwirpxSkIwtcvVA	27
594	2092	-lJtyCOTVInWusU9YF120A	0	2015-11-08	0	qFNtxmCldq0bUoRF1OdpQA	5	One of our favorite Italian restaurants. Fabul...	0	Ac3vNCPDwirpxSkIwtcvVA	27
467	1707	01fuY2NNscttoTxOYbuZXw	0	2016-07-08	0	TUFmvhAynUbjSbOJa79sRw	5	Loved the food! Atmosphere funky and artsy. St...	0	MejkKZgdUNfekUoNO1QJOA	45
1489	1707	01fuY2NNscttoTxOYbuZXw	0	2016-07-08	0	TUFmvhAynUbjSbOJa79sRw	5	Loved the food! Atmosphere funky and artsy. St...	0	MejkKZgdUNfekUoNO1QJOA	45
1473	1527	04u-szAykldu-caSDHQaKA	0	2012-02-09	0	VkEeSwaqHgp6VFeANMoFow	4	I have been looking for a good Chinese restrai...	0	iNSL4q8MUvZ1ItVGboPpbQ	19
398	1527	04u-szAykldu-caSDHQaKA	0	2012-02-09	0	VkEeSwaqHgp6VFeANMoFow	4	I have been looking for a good Chinese restrai...	0	iNSL4q8MUvZ1ItVGboPpbQ	19
2012	744	07F9bkUm3cs83CzGvTi0TA	1	2015-03-15	0	wgr_hOfIWlXR-glOC-qKYQ	5	Kudos to Charlotte Motor Speedway!! They took ...	1	Cxv3SNudPrCavvHJfIbwhQ	384
215	744	07F9bkUm3cs83CzGvTi0TA	1	2015-03-15	0	wgr_hOfIWlXR-glOC-qKYQ	5	Kudos to Charlotte Motor Speedway!! They took ...	1	Cxv3SNudPrCavvHJfIbwhQ	384
2520	2202	0QBFtNNj9RIggZGeivcbEg	0	2013-07-23	0	6bzlzJj3jrbWSnXtv80D_A	5	Great food and service. Spinach enchiladas are...	0	VTjCebjbadCMAGRoXvs7Zw	13
629	2202	0QBFtNNj9RIggZGeivcbEg	0	2013-07-23	0	6bzlzJj3jrbWSnXtv80D_A	5	Great food and service. Spinach enchiladas are...	0	VTjCebjbadCMAGRoXvs7Zw	13
871	3004	0bbWKI1lA-bmEeeWOrDmSA	0	2015-11-12	0	71xNE0qpjhgEz44PgbfIDA	3	It's an ok place. Quantities are greater than ...	0	hjHrV1WzO8RDRYJ-ep2OZA	64
2192	3004	0bbWKI1lA-bmEeeWOrDmSA	0	2015-11-12	0	71xNE0qpjhgEz44PgbfIDA	3	It's an ok place. Quantities are greater than ...	0	hjHrV1WzO8RDRYJ-ep2OZA	64
1992	3243	0dFOy1BeJWuYTyIsi-gAqw	0	2015-05-10	1	ed8to80UuSExhgfJOyWphQ	1	Well I had to give a star because required. Th...	1	dOsSS5r_YXViQznl6EvXlg	42
940	3243	0dFOy1BeJWuYTyIsi-gAqw	0	2015-05-10	1	ed8to80UuSExhgfJOyWphQ	1	Well I had to give a star because required. Th...	1	dOsSS5r_YXViQznl6EvXlg	42
859	2953	0yAJCh0TBZcnZkdZWZ6bVQ	0	2016-01-18	0	qrzJrDPyfqP7djhb6sxb3A	4	i've been going for over a year. great service...	0	Y5DIwt-653VndVu53ATpLQ	37
1435	2953	0yAJCh0TBZcnZkdZWZ6bVQ	0	2016-01-18	0	qrzJrDPyfqP7djhb6sxb3A	4	i've been going for over a year. great service...	0	Y5DIwt-653VndVu53ATpLQ	37
1538	4134	19VNsxhnPZ11zc0KBauonQ	0	2015-02-03	0	iXEO9493AVcDZmOkGJpIgw	1	worst customer service.Mainly the manager Matt...	1	z7dWikbkqL7NelG4JeSt4g	20
1169	4134	19VNsxhnPZ11zc0KBauonQ	0	2015-02-03	0	iXEO9493AVcDZmOkGJpIgw	1	worst customer service.Mainly the manager Matt...	1	z7dWikbkqL7NelG4JeSt4g	20
1678	3132	1ForN8iXqYZ_dZZcOkvVeA	0	2014-03-29	0	9vEk6nrlA8fcej0SsbD3gA	5	This was the cutest little diner I have ever b...	1	ZcgHNMGUKp-J9hRqgoDx6A	34
903	3132	1ForN8iXqYZ_dZZcOkvVeA	0	2014-03-29	0	9vEk6nrlA8fcej0SsbD3gA	5	This was the cutest little diner I have ever b...	1	ZcgHNMGUKp-J9hRqgoDx6A	34
1316	4652	1HnYxHZw2icWQ7-T4AmQ0Q	0	2017-06-18	0	fekMspN8qzJtai03tCUUvQ	5	Awesome place!!! Love that they have old style...	0	vuJ39vRaVZTgkT6I4L1jjA	24
1536	4652	1HnYxHZw2icWQ7-T4AmQ0Q	0	2017-06-18	0	fekMspN8qzJtai03tCUUvQ	5	Awesome place!!! Love that they have old style...	0	vuJ39vRaVZTgkT6I4L1jjA	24
...	...	...	...	...	...	...	...	...	...	...	...
1077	3776	wkChwNgC7YSc8KZgXiGT0Q	1	2016-09-12	0	9n1T-wVxsfQZbQ3cdRf46A	5	Magnificent views at sunset. Excellent appetiz...	1	ceIxPqfjhZbMdht4tGk7TQ	22
2452	3776	wkChwNgC7YSc8KZgXiGT0Q	1	2016-09-12	0	9n1T-wVxsfQZbQ3cdRf46A	5	Magnificent views at sunset. Excellent appetiz...	1	ceIxPqfjhZbMdht4tGk7TQ	22
2311	1777	wke61EJKd1Yw6q1BR1npZw	0	2014-07-23	0	B03DD42snaOU-mbDtntohw	5	Now this is a great bar! Bartenders were perso...	0	9zVMFFM_J51ZaS2wMzPziA	49
482	1777	wke61EJKd1Yw6q1BR1npZw	0	2014-07-23	0	B03DD42snaOU-mbDtntohw	5	Now this is a great bar! Bartenders were perso...	0	9zVMFFM_J51ZaS2wMzPziA	49
2330	3749	x5iQFVJkFl7fSXC6uVjwPw	0	2012-06-18	1	6qTtineIwJXZHVDmHtKDZg	1	We just moved to Mesa and were happy to hear a...	3	fOCV2evqdjDpfI-6ULvr5Q	97
1064	3749	x5iQFVJkFl7fSXC6uVjwPw	0	2012-06-18	1	6qTtineIwJXZHVDmHtKDZg	1	We just moved to Mesa and were happy to hear a...	3	fOCV2evqdjDpfI-6ULvr5Q	97
1430	2894	xVEtGucSRLk5pxxN0t4i6g	1	2009-06-09	0	9bfnqemozlvKzbmc8gbItw	5	The best piece of meat I have ever had!!! This...	1	r2hoX58seF3kwP-U2W__QA	59
845	2894	xVEtGucSRLk5pxxN0t4i6g	1	2009-06-09	0	9bfnqemozlvKzbmc8gbItw	5	The best piece of meat I have ever had!!! This...	1	r2hoX58seF3kwP-U2W__QA	59
556	1985	xkiYAerQQXL25legNhVsSw	0	2015-04-26	0	cIyDRhWeHtZ_pDQBe4sG8Q	4	The pizza was ok and the chips as an appetizer...	0	zdG-3ABz7-mv1P3cs2_Rfg	23
1943	1985	xkiYAerQQXL25legNhVsSw	0	2015-04-26	0	cIyDRhWeHtZ_pDQBe4sG8Q	4	The pizza was ok and the chips as an appetizer...	0	zdG-3ABz7-mv1P3cs2_Rfg	23
1789	1984	xmgLfJ5Jo6hHjY61hzO_EQ	0	2014-03-13	0	1dartEoF9n7-Fcgx7DWilA	1	Went on a Sunday and the gal did not use sanit...	0	Xu6kxX18Uv14MSZTY7PSgw	57
555	1984	xmgLfJ5Jo6hHjY61hzO_EQ	0	2014-03-13	0	1dartEoF9n7-Fcgx7DWilA	1	Went on a Sunday and the gal did not use sanit...	0	Xu6kxX18Uv14MSZTY7PSgw	57
788	2669	y9hgPwF68tpWEp6onX-3TQ	0	2017-10-26	0	aNIPAlsp_cO1VhyP8ReJ7g	3	I wanted to give this place 5 stars the beer i...	0	U_SbWcSZRLfkvZt3jshKJA	52
2499	2669	y9hgPwF68tpWEp6onX-3TQ	0	2017-10-26	0	aNIPAlsp_cO1VhyP8ReJ7g	3	I wanted to give this place 5 stars the beer i...	0	U_SbWcSZRLfkvZt3jshKJA	52
1359	4846	yADOyFmSlHuixlQ4MtHhcQ	0	2017-10-05	0	pE6BNYU3mEi9y1pS5fnPpQ	5	The servers in this place are great they all k...	0	06LACu59TbIHOXYn14CkPA	39
1655	4846	yADOyFmSlHuixlQ4MtHhcQ	0	2017-10-05	0	pE6BNYU3mEi9y1pS5fnPpQ	5	The servers in this place are great they all k...	0	06LACu59TbIHOXYn14CkPA	39
1414	3046	yQUXMWSA8H7wvkLa4iCD8g	0	2014-08-28	0	KmKCR_cHmAYA12Uy3XPHCA	2	Rude waitress and slow service. No offers for ...	1	KeYB7tU5F5PFb7X1QoWJ6Q	78
883	3046	yQUXMWSA8H7wvkLa4iCD8g	0	2014-08-28	0	KmKCR_cHmAYA12Uy3XPHCA	2	Rude waitress and slow service. No offers for ...	1	KeYB7tU5F5PFb7X1QoWJ6Q	78
2373	3509	yY3jNsrpCyKTqQuRuLV8gw	0	2017-07-16	0	rWc72Fx8ZKuat39dI-JLyw	5	Amazing food and drinks. I've tried multiple m...	0	SGc1Qk_LR8G8WEEA5QafEw	26
989	3509	yY3jNsrpCyKTqQuRuLV8gw	0	2017-07-16	0	rWc72Fx8ZKuat39dI-JLyw	5	Amazing food and drinks. I've tried multiple m...	0	SGc1Qk_LR8G8WEEA5QafEw	26
2129	3041	ynyDiLHzTdf4du9xMhscxg	0	2015-10-30	0	M-0AaEDTwBSIv_HJu4-pjw	5	Rachael is an artist! I've been going to her f...	0	lYXRVN2TJDEVB39G3HzplA	31
882	3041	ynyDiLHzTdf4du9xMhscxg	0	2015-10-30	0	M-0AaEDTwBSIv_HJu4-pjw	5	Rachael is an artist! I've been going to her f...	0	lYXRVN2TJDEVB39G3HzplA	31
1515	2659	zJGtD3y-pAIGNId4codEEg	1	2013-08-11	1	lwInYPtM4M2jfAftXk-xlA	4	We've been to Otro many times. Stick with the ...	1	BjmYO90f7e7oiwyoffC82w	150
785	2659	zJGtD3y-pAIGNId4codEEg	1	2013-08-11	1	lwInYPtM4M2jfAftXk-xlA	4	We've been to Otro many times. Stick with the ...	1	BjmYO90f7e7oiwyoffC82w	150
1521	2193	zKw09ftu1730wEIZBZPoFg	3	2015-01-04	0	JV-yxKxMFp-d0rLDc_2_6w	5	So relaxing combined with the meditation and ...	5	3mZFkwfa6XV0BBazRTva9w	31
623	2193	zKw09ftu1730wEIZBZPoFg	3	2015-01-04	0	JV-yxKxMFp-d0rLDc_2_6w	5	So relaxing combined with the meditation and ...	5	3mZFkwfa6XV0BBazRTva9w	31
1483	496	zg5rJfgT4jhzg1d6r2twnA	0	2014-06-21	0	Zbj0HgdN3AT4l-mbH-EfjA	3	Burger week\r\n\r\n1. Blazing Pineapple Burger...	0	UGW-9bbBEB3eP1o6mWD_WA	62
118	496	zg5rJfgT4jhzg1d6r2twnA	0	2014-06-21	0	Zbj0HgdN3AT4l-mbH-EfjA	3	Burger week\r\n\r\n1. Blazing Pineapple Burger...	0	UGW-9bbBEB3eP1o6mWD_WA	62
274	988	ziv21pDfyrgdhlrlNIgDfg	0	2016-08-11	0	fus9odxu9bjE2lSxfwNfdw	5	Get this!!! Wow Karlo is amazing and best cus...	2	ywjqPgnMrDZKOhA33v92Cw	62
1833	988	ziv21pDfyrgdhlrlNIgDfg	0	2016-08-11	0	fus9odxu9bjE2lSxfwNfdw	5	Get this!!! Wow Karlo is amazing and best cus...	2	ywjqPgnMrDZKOhA33v92Cw	62

666 rows × 11 columns

Removing Duplicates

df = df[df.duplicated()]

Rechecking for Duplicates

#Duplicates should no longer exist
df[df.duplicated(keep=False)].sort_values(by='business_id')

<style scoped> .dataframe tbody tr th:only-of-type { vertical-align: middle; }

.dataframe tbody tr th {
    vertical-align: top;
}

.dataframe thead th {
    text-align: right;
}

</style>

	Unnamed: 0	business_id	cool	date	funny	review_id	stars	text	useful	user_id	Review_Word_Length

Creating Pivot Tables

#This transforms the data into a person by person spreadsheet and what stars they gave various restaurants
#Most values are NaN (null or missing) because people only review a few restaurants of those that exist
usr_reviews = df.pivot(index='user_id', columns='business_id', values='stars')
usr_reviews.head()

<style scoped> .dataframe tbody tr th:only-of-type { vertical-align: middle; }

.dataframe tbody tr th {
    vertical-align: top;
}

.dataframe thead th {
    text-align: right;
}

</style>

business_id	-GY2fx-8udXPY8qn2HVBCg	-LRlx2j9_LB3evsRRcC9MA	-MKWJZnMjSit406AUKf7Pg	-eIvRc3aEvufstBumpBTPQ	-lJtyCOTVInWusU9YF120A	01fuY2NNscttoTxOYbuZXw	04u-szAykldu-caSDHQaKA	07F9bkUm3cs83CzGvTi0TA	0QBFtNNj9RIggZGeivcbEg	0bbWKI1lA-bmEeeWOrDmSA	...	xmgLfJ5Jo6hHjY61hzO_EQ	y9hgPwF68tpWEp6onX-3TQ	yADOyFmSlHuixlQ4MtHhcQ	yQUXMWSA8H7wvkLa4iCD8g	yY3jNsrpCyKTqQuRuLV8gw	ynyDiLHzTdf4du9xMhscxg	zJGtD3y-pAIGNId4codEEg	zKw09ftu1730wEIZBZPoFg	zg5rJfgT4jhzg1d6r2twnA	ziv21pDfyrgdhlrlNIgDfg
user_id
-Zdxj4wuj4D_899B7tPE3g	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	...	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN
-ciBvC2RcMtt3JeRW0NDvg	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	...	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN
06LACu59TbIHOXYn14CkPA	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	...	NaN	NaN	5.0	NaN	NaN	NaN	NaN	NaN	NaN	NaN
0XKTRsa8Y1A3RHJB5LhuUg	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	...	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN
17YRCR1n1FcLAerv2qtmkw	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	...	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN

5 rows × 331 columns

Summary

In this brief introduction, you learned the acronym ETL and got to preview a few examples of ETL processes using pandas. In the upcoming lessons you'll get a much richer understanding of these and other techniques for wrangling your data!