4. Filtering in Pandas

1. Display only customer ID, name, email, phone, and address columns of the top 3 rows.

Solution

import pandas as pd
df=pd.read_csv('retail_data.csv')
df.iloc[0:3,1:6]
# this can also be done by df.head(3)[['Customer_ID', 'Name', 'Email', 'Phone', 'Address']]

# Output
    Customer_ID	Name	Email	Phone	Address
0	37249	Michelle Harrington	[e-mail]	1414786801	3959 Amanda Burgs
1	69749	Kelsey Hill	[e-mail]	6852899987	82072 Dawn Centers
2	30192	Scott Jensen	[e-mail]	8362160449	4133 Young Canyon

1. Display the last 5 rows and first 5 columns of the dataset.

Solution

df.iloc[-5:,0:5]

# Output

        Transaction_ID	Customer_ID	Name	Email	Phone
293906	4246475	12104	Meagan Ellis	[e-mail]	7466353743
293907	1197603	69772	Mathew Beck	[e-mail]	5754304957
293908	7743242	28449	Daniel Lee	[e-mail]	9382530370
293909	9301950	45477	Patrick Wilson	[e-mail]	9373222023
293910	2882826	53626	Dustin Merritt	[e-mail]	9518926645

1. Display the last 7 rows and last 7 columns.

Solution

df.iloc[-7:,-7:]

# Output

Product_Type	Feedback	Shipping_Method	Payment_Method	Order_Status	Ratings	products
293904	Tablet	Average	Same-Day	Cash	Pending	2	Amazon Fire Tablet
293905	Shorts	Excellent	Standard	Cash	Delivered	4	Chino shorts
293906	Fiction	Bad	Same-Day	Cash	Processing	1	Historical fiction
293907	Laptop	Excellent	Same-Day	Cash	Processing	5	LG Gram
293908	Jacket	Average	Express	Cash	Shipped	2	Parka
293909	Furniture	Good	Standard	Cash	Shipped	4	TV stand
293910	Decorations	Average	Same-Day	Cash	Shipped	2	Clocks

1. Retrieve the first 3 rows

Solution

df.iloc[:3]

# Output

Transaction_ID	Customer_ID	Name	Email	Phone	Address	City	State	Zipcode	Country	...	Total_Amount	Product_Category	Product_Brand	Product_Type	Feedback	Shipping_Method	Payment_Method	Order_Status	Ratings	products
0	8691788	37249	Michelle Harrington	[e-mail]	1414786801	3959 Amanda Burgs	Dortmund	Berlin	77985	Germany	...	324.086270	Clothing	Nike	Shorts	Excellent	Same-Day	Debit Card	Shipped	5	Cycling shorts
1	2174773	69749	Kelsey Hill	[e-mail]	6852899987	82072 Dawn Centers	Nottingham	England	99071	UK	...	806.707815	Electronics	Samsung	Tablet	Excellent	Standard	Credit Card	Processing	4	Lenovo Tab
2	6679610	30192	Scott Jensen	[e-mail]	8362160449	4133 Young Canyon	Geelong	New South Wales	75929	Australia	...	1063.432799	Books	Penguin Books	Children's	Average	Same-Day	Credit Card	Processing	2	Sports equipment

1. Display the rows where the order status is shipped.

Solution

shipped_orders=df.loc[df['Order_Status]=='Shipped']
shipped_orders

# Output
 
Transaction_ID	Customer_ID	Name	Email	Phone	Address	City	State	Zipcode	Country	...	Total_Amount	Product_Category	Product_Brand	Product_Type	Feedback	Shipping_Method	Payment_Method	Order_Status	Ratings	products
0	8691788	37249	Michelle Harrington	[e-mail]	1414786801	3959 Amanda Burgs	Dortmund	Berlin	77985	Germany	...	324.086270	Clothing	Nike	Shorts	Excellent	Same-Day	Debit Card	Shipped	5	Cycling shorts
4	4983775	27901	Debra Coleman	[e-mail]	9098267635	5813 Lori Ports Suite 269	Bristol	England	48704	UK	...	248.553049	Grocery	Nestle	Chocolate	Bad	Standard	Cash	Shipped	1	Chocolate cookies
10	8493213	19136	Jonathan Eaton	[e-mail]	2996714102	9772 Sosa Coves	Portsmouth	England	59280	UK	...	363.927479	Home Decor	Home Depot	Tools	Average	Standard	Credit Card	Shipped	2	Screwdriver set
14	2401331	98300	Andrew Guzman	[e-mail]	2923044936	470 Rodriguez Estate Suite 564	Portsmouth	England	5259	UK	...	1786.356235	Clothing	Adidas	T-shirt	Bad	Same-Day	Cash	Shipped	1	V-neck tee
16	6681000	69939	Jessica Harrison	[e-mail]	2540232555	93806 Murphy Avenue Apt. 919	Portsmouth	England	84007	UK	...	147.271643	Grocery	Pepsi	Water	Average	Same-Day	Debit Card	Shipped	2	Flavored water
...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...
293899	7349938	50236	Faith Pugh	[e-mail]	8786031174	669 Melinda Park Suite 448	Kitchener	Ontario	31602	Canada	...	501.674654	Books	Penguin Books	Children's	Good	Express	Cash	Shipped	3	Puzzles
293900	8032917	31242	Danielle Anderson	[e-mail]	3445479425	369 Barnes Tunnel	San Antonio	Florida	32598	USA	...	413.734856	Electronics	Samsung	Tablet	Excellent	Same-Day	Cash	Shipped	5	iPad
293908	7743242	28449	Daniel Lee	[e-mail]	9382530370	407 Aaron Crossing Suite 495	Brighton	England	88038	UK	...	182.105285	Clothing	Adidas	Jacket	Average	Express	Cash	Shipped	2	Parka
293909	9301950	45477	Patrick Wilson	[e-mail]	9373222023	3204 Baird Port	Halifax	Ontario	67608	Canada	...	120.834784	Home Decor	IKEA	Furniture	Good	Standard	Cash	Shipped	4	TV stand
293910	2882826	53626	Dustin Merritt	[e-mail]	9518926645	143 Amanda Crescent	Tucson	West Virginia	25242	USA	...	2382.233417	Home Decor	Home Depot	Decorations	Average	Same-Day	Cash	Shipped	2	Clocks

63275 rows × 30 columns

1. Display the data of Berlin state.

Solution

berlin_state=df.loc[df['State']=='Berlin']
berlin_state

# Output
Transaction_ID	Customer_ID	Name	Email	Phone	Address	City	State	Zipcode	Country	...	Total_Amount	Product_Category	Product_Brand	Product_Type	Feedback	Shipping_Method	Payment_Method	Order_Status	Ratings	products
0	8691788	37249	Michelle Harrington	[e-mail]	1414786801	3959 Amanda Burgs	Dortmund	Berlin	77985	Germany	...	324.086270	Clothing	Nike	Shorts	Excellent	Same-Day	Debit Card	Shipped	5	Cycling shorts
7	2344675	26603	Angela Fields	[e-mail]	3668096144	237 Young Curve	Munich	Berlin	86862	Germany	...	46.588070	Clothing	Zara	Shirt	Bad	Same-Day	Cash	Processing	1	Dress shirt
9	4926148	31878	Lori Bell	[e-mail]	6004895059	6225 William Lodge	Cologne	Berlin	64317	Germany	...	3976.112295	Home Decor	Home Depot	Decorations	Excellent	Standard	Cash	Delivered	4	Candles
17382	3680043	78484	Mr. Zachary Marks	[e-mail]	2831499082	085 Nguyen Highway	Cologne	Berlin	49173	Germany	...	2474.858288	Home Decor	Home Depot	Tools	Good	Express	PayPal	Delivered	4	Drill
17390	5206426	66989	Angel Fitzpatrick	[e-mail]	7490023454	548 Gonzalez Camp	Leipzig	Berlin	95501	Germany	...	171.435242	Grocery	Coca-Cola	Water	Excellent	Standard	Debit Card	Delivered	5	Flavored water
...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...
293884	7666480	29139	Jane Ball	[e-mail]	8579604153	57855 Julie Divide	Bonn	Berlin	5083	Germany	...	212.446853	Grocery	Pepsi	Soft Drink	Bad	Same-Day	Cash	Delivered	1	Ginger ale
293890	1554068	72401	Matthew Miller	[e-mail]	7507492899	8471 Dana Station	Bielefeld	Berlin	34228	Germany	...	1729.573700	Clothing	Zara	Dress	Good	Express	Cash	Shipped	4	Fit and flare dress
293896	5346939	12465	Daniel Miller	[e-mail]	8981601821	235 Reed Glens	Bochum	Berlin	66351	Germany	...	258.497099	Grocery	Pepsi	Juice	Excellent	Same-Day	Cash	Shipped	5	Orange juice
293897	5781099	41133	Cheryl Collins	[e-mail]	6851286333	6806 Buchanan Place Apt. 569	Nuremberg	Berlin	91049	Germany	...	102.938408	Clothing	Adidas	T-shirt	Excellent	Standard	Cash	Processing	4	Scoop neck tee
293907	1197603	69772	Mathew Beck	[e-mail]	5754304957	52809 Mark Forges	Hanover	Berlin	16852	Germany	...	285.137301	Electronics	Apple	Laptop	Excellent	Same-Day	Cash	Processing	5	LG Gram
51433 rows × 30 columns

1. Display the data for the electronics and clothing category only.

Solution

specific_categories= df.loc[(df['Product_Category']=='Electronics) | (df['Product_Category']=='Clothing')]
specific_categories

# Output

Transaction_ID	Customer_ID	Name	Email	Phone	Address	City	State	Zipcode	Country	...	Total_Amount	Product_Category	Product_Brand	Product_Type	Feedback	Shipping_Method	Payment_Method	Order_Status	Ratings	products
0	8691788	37249	Michelle Harrington	[e-mail]	1414786801	3959 Amanda Burgs	Dortmund	Berlin	77985	Germany	...	324.086270	Clothing	Nike	Shorts	Excellent	Same-Day	Debit Card	Shipped	5	Cycling shorts
1	2174773	69749	Kelsey Hill	[e-mail]	6852899987	82072 Dawn Centers	Nottingham	England	99071	UK	...	806.707815	Electronics	Samsung	Tablet	Excellent	Standard	Credit Card	Processing	4	Lenovo Tab
5	6095326	41289	Ryan Johnson	[e-mail]	3292677006	532 Ashley Crest Suite 014	Brisbane	New South Wales	74430	Australia	...	1185.167224	Electronics	Apple	Tablet	Good	Express	PayPal	Pending	4	Lenovo Tab
6	5434096	97285	Erin Lewis	[e-mail]	1578355423	600 Brian Prairie Suite 497	Kitchener	Ontario	47545	Canada	...	630.115295	Electronics	Samsung	Television	Bad	Standard	Cash	Processing	1	QLED TV
7	2344675	26603	Angela Fields	[e-mail]	3668096144	237 Young Curve	Munich	Berlin	86862	Germany	...	46.588070	Clothing	Zara	Shirt	Bad	Same-Day	Cash	Processing	1	Dress shirt
...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...
293900	8032917	31242	Danielle Anderson	[e-mail]	3445479425	369 Barnes Tunnel	San Antonio	Florida	32598	USA	...	413.734856	Electronics	Samsung	Tablet	Excellent	Same-Day	Cash	Shipped	5	iPad
293904	2844206	18799	Angel Hood	[e-mail]	2825444712	7593 Joseph Trace Suite 382	Cairns	New South Wales	39837	Australia	...	2384.717299	Electronics	Apple	Tablet	Average	Same-Day	Cash	Pending	2	Amazon Fire Tablet
293905	4833982	94117	Kara Hart	[e-mail]	7108672468	872 Robinson Harbors Apt. 328	Charlotte	Missouri	65301	USA	...	2362.120301	Clothing	Nike	Shorts	Excellent	Standard	Cash	Delivered	4	Chino shorts
293907	1197603	69772	Mathew Beck	[e-mail]	5754304957	52809 Mark Forges	Hanover	Berlin	16852	Germany	...	285.137301	Electronics	Apple	Laptop	Excellent	Same-Day	Cash	Processing	5	LG Gram
293908	7743242	28449	Daniel Lee	[e-mail]	9382530370	407 Aaron Crossing Suite 495	Brighton	England	88038	UK	...	182.105285	Clothing	Adidas	Jacket	Average	Express	Cash	Shipped	2	Parka
122647 rows × 30 columns

1. Display the data of Germany where ratings are between 3-5(inclusive).

Solution

germany_high_ratings = df.loc[(df['Country'] == 'Germany') & ((df['Ratings']>=3) & (df['Ratings']<=5))]
germany_high_ratings

# Output

Transaction_ID	Customer_ID	Name	Email	Phone	Address	City	State	Zipcode	Country	...	Total_Amount	Product_Category	Product_Brand	Product_Type	Feedback	Shipping_Method	Payment_Method	Order_Status	Ratings	products
0	8691788	37249	Michelle Harrington	[e-mail]	1414786801	3959 Amanda Burgs	Dortmund	Berlin	77985	Germany	...	324.086270	Clothing	Nike	Shorts	Excellent	Same-Day	Debit Card	Shipped	5	Cycling shorts
9	4926148	31878	Lori Bell	[e-mail]	6004895059	6225 William Lodge	Cologne	Berlin	64317	Germany	...	3976.112295	Home Decor	Home Depot	Decorations	Excellent	Standard	Cash	Delivered	4	Candles
17382	3680043	78484	Mr. Zachary Marks	[e-mail]	2831499082	085 Nguyen Highway	Cologne	Berlin	49173	Germany	...	2474.858288	Home Decor	Home Depot	Tools	Good	Express	PayPal	Delivered	4	Drill
17390	5206426	66989	Angel Fitzpatrick	[e-mail]	7490023454	548 Gonzalez Camp	Leipzig	Berlin	95501	Germany	...	171.435242	Grocery	Coca-Cola	Water	Excellent	Standard	Debit Card	Delivered	5	Flavored water
17394	2270795	60004	Rachel Diaz	[e-mail]	7377024547	201 Gonzalez Burg Suite 926	Düsseldorf	Berlin	65763	Germany	...	1266.702996	Home Decor	Bed Bath & Beyond	Bathroom	Good	Standard	Credit Card	Delivered	3	Towel rack
...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...
293873	4839324	78561	Daniel Miller	[e-mail]	1952313262	9347 Chad Shore	Essen	Berlin	86830	Germany	...	274.060651	Electronics	Sony	Headphones	Good	Express	Cash	Shipped	3	In-ear headphones
293890	1554068	72401	Matthew Miller	[e-mail]	7507492899	8471 Dana Station	Bielefeld	Berlin	34228	Germany	...	1729.573700	Clothing	Zara	Dress	Good	Express	Cash	Shipped	4	Fit and flare dress
293896	5346939	12465	Daniel Miller	[e-mail]	8981601821	235 Reed Glens	Bochum	Berlin	66351	Germany	...	258.497099	Grocery	Pepsi	Juice	Excellent	Same-Day	Cash	Shipped	5	Orange juice
293897	5781099	41133	Cheryl Collins	[e-mail]	6851286333	6806 Buchanan Place Apt. 569	Nuremberg	Berlin	91049	Germany	...	102.938408	Clothing	Adidas	T-shirt	Excellent	Standard	Cash	Processing	4	Scoop neck tee
293907	1197603	69772	Mathew Beck	[e-mail]	5754304957	52809 Mark Forges	Hanover	Berlin	16852	Germany	...	285.137301	Electronics	Apple	Laptop	Excellent	Same-Day	Cash	Processing	5	LG Gram
33888 rows × 30 columns

1. Display the data of Adidas and Nike with excellent feedback.

Solution

excellent_feedback = df.loc[df['Product_Brand'].isin(['Adidas', 'Nike']) & (df['Feedback'] == 'Excellent')]
excellent_feedback

# Output

Transaction_ID	Customer_ID	Name	Email	Phone	Address	City	State	Zipcode	Country	...	Total_Amount	Product_Category	Product_Brand	Product_Type	Feedback	Shipping_Method	Payment_Method	Order_Status	Ratings	products
0	8691788	37249	Michelle Harrington	[e-mail]	1414786801	3959 Amanda Burgs	Dortmund	Berlin	77985	Germany	...	324.086270	Clothing	Nike	Shorts	Excellent	Same-Day	Debit Card	Shipped	5	Cycling shorts
39	2458233	96840	Susan Thomas	[e-mail]	9540868010	48415 Ferguson Passage	Portsmouth	England	85074	UK	...	230.604085	Clothing	Adidas	Shoes	Excellent	Same-Day	Credit Card	Pending	4	Running shoes
84	6573179	55594	Tara Hawkins	[e-mail]	7609311698	2856 Steven Crossroad Apt. 393	Portsmouth	England	50743	UK	...	2534.323563	Clothing	Adidas	Jacket	Excellent	Express	Credit Card	Delivered	5	Puffer jacket
140	6094546	12223	Michael Howe	[e-mail]	7829418212	027 Matthew Walks	Portsmouth	England	60278	UK	...	1005.138159	Clothing	Adidas	Jacket	Excellent	Express	Debit Card	Processing	4	Varsity jacket
150	4201893	93072	Brandy Cruz	[e-mail]	8167796506	6928 Scott Motorway	Portsmouth	England	20819	UK	...	649.831335	Clothing	Adidas	Jacket	Excellent	Express	PayPal	Processing	4	Peacoat
...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...
293731	6253211	64721	Michael Rogers	[e-mail]	6755507113	0605 Joseph Land Apt. 719	Brighton	England	6404	UK	...	634.058940	Clothing	Nike	Shorts	Excellent	Same-Day	Cash	Processing	5	Bermuda shorts
293745	3383565	20132	Samantha Haney	[e-mail]	3434154738	1679 Tiffany Mountains	Baltimore	Texas	79404	USA	...	962.196438	Clothing	Nike	Shoes	Excellent	Express	Cash	Processing	5	High heels
293809	3721605	30246	Kyle Brown	[e-mail]	2008530901	59440 Peter Alley Apt. 069	Birmingham	England	8581	UK	...	3415.046026	Clothing	Adidas	T-shirt	Excellent	Same-Day	Cash	Delivered	5	Off-the-shoulder tee
293897	5781099	41133	Cheryl Collins	[e-mail]	6851286333	6806 Buchanan Place Apt. 569	Nuremberg	Berlin	91049	Germany	...	102.938408	Clothing	Adidas	T-shirt	Excellent	Standard	Cash	Processing	4	Scoop neck tee
293905	4833982	94117	Kara Hart	[e-mail]	7108672468	872 Robinson Harbors Apt. 328	Charlotte	Missouri	65301	USA	...	2362.120301	Clothing	Nike	Shorts	Excellent	Standard	Cash	Delivered	4	Chino shorts
12466 rows × 30 columns

1. Display orders shipped to Canada with a rating of 3 or higher.

Solution

canada_orders=df.loc[(df['Country'] == 'Canada') & (df['Ratings'] >= 3)]
canada_orders

# Output

Transaction_ID	Customer_ID	Name	Email	Phone	Address	City	State	Zipcode	Country	...	Total_Amount	Product_Category	Product_Brand	Product_Type	Feedback	Shipping_Method	Payment_Method	Order_Status	Ratings	products
3	7232460	62101	Joseph Miller	[e-mail]	2776751724	8148 Thomas Creek Suite 100	Edmonton	Ontario	88420	Canada	...	2466.854021	Home Decor	Home Depot	Tools	Excellent	Standard	PayPal	Processing	4	Utility knife
17383	1146137	72157	Martha Haney	[e-mail]	7718175311	477 Johnny Stravenue Apt. 089	Hamilton	Ontario	42293	Canada	...	497.463192	Clothing	Zara	Dress	Excellent	Express	Credit Card	Delivered	5	Sundress
17397	6377555	39106	Patricia Brown	[e-mail]	1446133658	9995 Lindsay Radial	Halifax	Ontario	96341	Canada	...	2111.591093	Clothing	Adidas	Jacket	Good	Express	Cash	Delivered	3	Windbreaker
17398	1631755	20420	Danny Carter	[e-mail]	6019921813	2208 Lisa Causeway Suite 946	Montreal	Ontario	36830	Canada	...	591.434580	Home Decor	Bed Bath & Beyond	Bedding	Good	Same-Day	PayPal	Delivered	3	Blanket
17409	2091151	87076	Joseph Watson	[e-mail]	3536240293	054 Charles Pass	Toronto	Ontario	9599	Canada	...	1053.004466	Grocery	Coca-Cola	Juice	Good	Same-Day	Debit Card	Delivered	4	Mango juice
...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...
293868	2368392	23573	Steven Manning	[e-mail]	8686211920	215 Hill Prairie	Quebec City	Ontario	98747	Canada	...	1768.614257	Electronics	Sony	Smartphone	Good	Express	Cash	Processing	4	Xiaomi Mi
293893	7937210	77698	Cassandra Mcmillan	[e-mail]	2894630531	5042 Howe Streets	Kitchener	Ontario	37919	Canada	...	874.027896	Books	HarperCollins	Fiction	Good	Standard	Cash	Processing	4	Dystopian
293899	7349938	50236	Faith Pugh	[e-mail]	8786031174	669 Melinda Park Suite 448	Kitchener	Ontario	31602	Canada	...	501.674654	Books	Penguin Books	Children's	Good	Express	Cash	Shipped	3	Puzzles
293903	8961631	79479	Jason Welch	[e-mail]	6279294104	764 Garcia Flat	Hamilton	Ontario	61218	Canada	...	2659.976987	Home Decor	Home Depot	Tools	Excellent	Express	Cash	Pending	5	Level
293909	9301950	45477	Patrick Wilson	[e-mail]	9373222023	3204 Baird Port	Halifax	Ontario	67608	Canada	...	120.834784	Home Decor	IKEA	Furniture	Good	Standard	Cash	Shipped	4	TV stand
30163 rows × 30 columns

1. Find orders where the total amount is greater than $1,000, the payment method is "Credit Card," and the product category is "Electronics."

Solution

new_df=df.loc[(df['Total_Amount'] > 1000) & (df['Payment_Method'] == 'Credit Card') & (df['Product_Category'] == 'Electronics')]
new_df

# Output

Transaction_ID	Customer_ID	Name	Email	Phone	Address	City	State	Zipcode	Country	...	Total_Amount	Product_Category	Product_Brand	Product_Type	Feedback	Shipping_Method	Payment_Method	Order_Status	Ratings	products
27	5230876	23097	Michael Cole	[e-mail]	6487484685	0777 Rebecca Junctions Apt. 015	Portsmouth	England	14668	UK	...	2357.410660	Electronics	Apple	Tablet	Bad	Same-Day	Credit Card	Shipped	1	Amazon Fire Tablet
66	5161746	95925	Jesus Cole	[e-mail]	5224669316	100 Jasmine Summit Apt. 567	Portsmouth	England	56239	UK	...	3134.881143	Electronics	Sony	Headphones	Average	Same-Day	Credit Card	Processing	2	Sports headphones
73	6126111	63629	Mary Lawson	[e-mail]	2830391284	63515 Ross Corner Apt. 945	Portsmouth	England	41990	UK	...	1823.483710	Electronics	Apple	Laptop	Bad	Same-Day	Credit Card	Shipped	1	LG Gram
112	6298651	50555	Steven Riddle	[e-mail]	2470177640	46650 Melissa Plains Apt. 683	Portsmouth	England	22156	UK	...	3738.363983	Electronics	Samsung	Smartphone	Bad	Standard	Credit Card	Pending	1	Xiaomi Mi
132	4946535	73735	Kathleen Garcia	[e-mail]	6374505488	0699 Ramirez Brooks Suite 096	Portsmouth	England	82804	UK	...	2959.809090	Electronics	Samsung	Television	Good	Standard	Credit Card	Shipped	4	LCD TV
...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...
292457	7254765	80548	Tami Fritz	[e-mail]	9265636979	733 Susan Land	Adelaide	New South Wales	23411	Australia	...	1095.266187	Electronics	Sony	Television	Bad	Same-Day	Credit Card	Delivered	1	Android TV
292471	2485225	22964	David Robinson	[e-mail]	6214410854	9105 Terri Via Apt. 790	Oshawa	Ontario	37887	Canada	...	1376.736659	Electronics	Sony	Television	Average	Same-Day	Credit Card	Delivered	2	HDR TV
292481	1258760	76225	Christine Walker	[e-mail]	8338585445	524 Sarah Villages Apt. 059	Washington	Alabama	35270	USA	...	1695.319501	Electronics	Sony	Smartphone	Good	Same-Day	Credit Card	Processing	4	Samsung Galaxy
292486	5813724	23578	Kristin Johnson	[e-mail]	9307294081	1073 Andrea Dam Suite 744	Kelowna	Ontario	68239	Canada	...	3723.996697	Electronics	Samsung	Television	Average	Standard	Credit Card	Pending	2	4K TV
292488	1230240	76122	James Adams	[e-mail]	8092122150	9920 Cynthia Mills	San Antonio	Kansas	67338	USA	...	2098.408533	Electronics	Samsung	Television	Bad	Same-Day	Credit Card	Pending	1	QLED TV
11807 rows × 30 columns

1. Find the companies that have excellent feedback for electronics in the American market.

Solution

excellent_company=df.loc[(df['Product_Category']=='Electronics') & (df['Country']=='USA') & (df['Feedback']=='Excellent')]
excellent_company

# Output

Transaction_ID	Customer_ID	Name	Email	Phone	Address	City	State	Zipcode	Country	...	Total_Amount	Product_Category	Product_Brand	Product_Type	Feedback	Shipping_Method	Payment_Method	Order_Status	Ratings	products
17556	7523534	85060	Kim Allen	[e-mail]	6870867693	0870 Everett Fort Apt. 518	Fort Worth	New Mexico	74707	USA	...	663.793609	Electronics	Sony	Television	Excellent	Standard	PayPal	Delivered	4	QLED TV
17575	8520504	70725	Theresa Brooks	[e-mail]	8595376895	136 Wilson Springs	Fort Worth	New Mexico	35198	USA	...	3976.636173	Electronics	Apple	Laptop	Excellent	Same-Day	Credit Card	Delivered	5	Razer Blade
17577	1878922	96452	David Ray	[e-mail]	7545702592	5194 Martinez Parkways	Fort Worth	New Mexico	8314	USA	...	613.908979	Electronics	Apple	Tablet	Excellent	Express	Credit Card	Delivered	4	Asus ZenPad
17578	1124609	57337	Andrew Berger	[e-mail]	8723566636	9041 Whitney Harbors Apt. 636	Fort Worth	New Mexico	94476	USA	...	2253.611337	Electronics	Samsung	Tablet	Excellent	Same-Day	Debit Card	Delivered	5	Microsoft Surface
17584	6662281	91476	Amanda Martinez	[e-mail]	1460630585	20359 Margaret Trail Apt. 346	Fort Worth	New Mexico	48933	USA	...	1244.001356	Electronics	Apple	Smartphone	Excellent	Same-Day	Credit Card	Delivered	4	Huawei P
...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...
293485	5402881	19879	Lauren Hunt	[e-mail]	5200922601	25935 Bailey Valleys Apt. 975	New Orleans	Pennsylvania	18810	USA	...	36.044168	Electronics	Apple	Smartphone	Excellent	Same-Day	Cash	Shipped	5	Motorola Moto
293634	8531256	80073	Nicole Curry	[e-mail]	8208624472	4063 Payne Road	Washington	Louisiana	70706	USA	...	1778.525554	Electronics	Apple	Tablet	Excellent	Same-Day	Cash	Delivered	4	Acer Iconia Tab
293754	9386765	57609	Peter Pena PhD	[e-mail]	7407045571	58178 Roberts Villages	Washington	Alaska	99542	USA	...	1841.242537	Electronics	Apple	Smartphone	Excellent	Same-Day	Cash	Shipped	5	Huawei P
293762	2741435	81818	David Dean	[e-mail]	1661986121	041 Brianna Crescent Apt. 924	Seattle	Iowa	52540	USA	...	539.383266	Electronics	Samsung	Television	Excellent	Express	Cash	Pending	5	QLED TV
293900	8032917	31242	Danielle Anderson	[e-mail]	3445479425	369 Barnes Tunnel	San Antonio	Florida	32598	USA	...	413.734856	Electronics	Samsung	Tablet	Excellent	Same-Day	Cash	Shipped	5	iPad
5881 rows × 30 columns

242KB

sample_iowa_liquor_sales.csv

1. Create a sample of 50 rows from the above dataset.

Solution

df.sample(50)

# Output

#Random data of 50 rows × 24 columns will generate

1. Display the first 10 rows of the last 10 columns.

Solution

df.iloc[:10,15:]

# Output

Item Description	Pack	Bottle Volume (ml)	State Bottle Cost	State Bottle Retail	Bottles Sold	Sale (Dollars)	Volume Sold (Liters)	Volume Sold (Gallons)
0	Forbidden Secret Coffee Pack	6	1500	11.62	17.43	6	104.58	9.0	2.38
1	Laphroaig w/ Whiskey Stones	12	750	19.58	29.37	4	117.48	3.0	0.79
2	Forbidden Secret Coffee Pack	6	1500	11.62	17.43	1	17.43	1.5	0.40
3	Rumchata "GoChatas"	1	6000	99.00	148.50	1	148.50	6.0	1.59
4	Forbidden Secret Coffee Pack	6	1500	11.62	17.43	6	104.58	9.0	2.38
5	Forbidden Secret Coffee Pack	6	1500	11.62	17.43	3	52.29	4.5	1.19
6	Forbidden Secret Coffee Pack	6	1500	11.62	17.43	6	104.58	9.0	2.38
7	Forbidden Secret Coffee Pack	6	1500	11.62	17.43	2	34.86	3.0	0.79
8	Laphroaig w/ Whiskey Stones	12	750	19.58	29.37	36	1057.32	27.0	7.13
9	Forbidden Secret Coffee Pack	6	1500	11.62	17.43	12	209.16	18.0	4.76

1. Display the sale records of Polk County but the sale value must be above 500.

Solution

polk_sale=df.loc[(df['County'] == 'Polk') & (df['Sale (Dollars)'] > 500)]
polk_sale

# Output

Invoice/Item Number	Date	Store Number	Store Name	Address	City	Zip Code	Store Location	County Number	County	...	Item Number	Item Description	Pack	Bottle Volume (ml)	State Bottle Cost	State Bottle Retail	Bottles Sold	Sale (Dollars)	Volume Sold (Liters)	Volume Sold (Gallons)
8	S29191200001	11/19/2015	2248	Ingersoll Liquor and Beverage	3500 INGERSOLL AVE	DES MOINES	50312	3500 INGERSOLL AVE\nDES MOINES 50312\n(41.5863...	77	Polk	...	173	Laphroaig w/ Whiskey Stones	12	750	19.58	29.37	36	1057.32	27.0	7.13
85	S13536700014	07/24/2013	2238	Adventureland Inn	3200 Adventureland Dr	ALTOONA	50009	3200 Adventureland Dr\nALTOONA 50009\n(41.6585...	77	Polk	...	43127	Bacardi Superior Rum	12	1000	9.43	14.14	36	509.04	36.0	9.51
145	S19385800095	06-05-2014	4829	Central City 2	1501 MICHIGAN AVE	DES MOINES	50314	1501 MICHIGAN AVE\nDES MOINES 50314\n(41.60556...	77	Polk	...	82607	Dekuyper Sour Apple	12	1000	7.62	11.43	48	548.64	48.0	12.68
167	S15256700116	10/21/2013	2633	Hy-Vee #3 / BDI / Des Moines	3221 SE 14TH ST	DES MOINES	50320	3221 SE 14TH ST\nDES MOINES 50320\n(41.554101,...	77	Polk	...	66936	Grangala Triple Orange Liqueur	12	750	10.99	16.49	36	593.64	27.0	7.13
338	S26714700062	07/13/2015	4829	Central City 2	1501 MICHIGAN AVE	DES MOINES	50314	1501 MICHIGAN AVE\nDES MOINES 50314\n(41.60556...	77	Polk	...	34422	Grey Goose Vodka	6	1000	22.50	33.75	60	2025.00	60.0	15.85
441	S19779600060	06/26/2014	2633	Hy-Vee #3 / BDI / Des Moines	3221 SE 14TH ST	DES MOINES	50320	3221 SE 14TH ST\nDES MOINES 50320\n(41.554101,...	77	Polk	...	34422	Grey Goose Vodka	6	1000	22.00	33.00	480	15840.00	480.0	126.80
655	S22949600003	12/15/2014	2478	Prairie Meadows	ONE PRAIRIE MEADOWS DRIVE	ALTOONA	50009	ONE PRAIRIE MEADOWS DRIVE\nALTOONA 50009\n	77	Polk	...	26827	Jack Daniels Old #7 Black Lbl	12	1000	17.90	26.85	36	966.60	36.0	9.51
668	S14885200007	10-01-2013	4349	Southside Food Mart	1101 ARMY POST RD	DES MOINES	50315	1101 ARMY POST RD\nDES MOINES 50315\n(41.52650...	77	Polk	...	37346	Phillips Vodka	12	750	3.57	5.35	156	834.60	117.0	30.91
669	S22117400165	10/30/2014	2633	Hy-Vee #3 / BDI / Des Moines	3221 SE 14TH ST	DES MOINES	50320	3221 SE 14TH ST\nDES MOINES 50320\n(41.554101,...	77	Polk	...	68037	Bailey's Original Irish Cream	12	1000	17.25	25.88	24	621.12	24.0	6.34
9 rows × 24 columns

1. Find out the sales in Linn county but the bottle volume must be more than 1000ml.

Solution

linn_sales=df.loc[(df['Bottle Volume (ml)'] > 1000) & (df['County'] == 'Linn')]
linn_sales

# Output

Invoice/Item Number	Date	Store Number	Store Name	Address	City	Zip Code	Store Location	County Number	County	...	Item Number	Item Description	Pack	Bottle Volume (ml)	State Bottle Cost	State Bottle Retail	Bottles Sold	Sale (Dollars)	Volume Sold (Liters)	Volume Sold (Gallons)
74	S07441800045	08/30/2012	2529	Hy-Vee Drugstore #4 / Cedar Rapids	4825 JOHNSON AVE NW	CEDAR RAPIDS	52405	4825 JOHNSON AVE NW\nCEDAR RAPIDS 52405\n(41.9...	57	Linn	...	57148	Chi-Chi's Margarita W/tequila	6	1750	6.49	9.74	12	116.88	21.00	5.55
221	S25865900045	05/27/2015	4860	Jims Foods	812 6TH ST SW	CEDAR RAPIDS	52404	812 6TH ST SW\nCEDAR RAPIDS 52404\n(41.968294,...	57	Linn	...	64858	Fireball Cinnamon Whiskey Mini Dispenser	1	3000	29.72	44.58	1	44.58	3.00	0.79
244	S28271800008	10-06-2015	4443	Palo Mini Mart	1204 1ST ST	PALO	52324	1204 1ST ST\nPALO 52324\n(42.070316, -91.795808)	57	Linn	...	11788	Black Velvet	6	1750	9.45	14.68	12	176.16	21.00	5.55
348	S08262100013	10-11-2012	3666	Target Store T-1771 / Cedar Rapids	3400 EDGEWOOD RD SW	CEDAR RAPIDS	52404	3400 EDGEWOOD RD SW\nCEDAR RAPIDS 52404\n(41.9...	57	Linn	...	12408	Canadian Ltd Whisky	6	1750	7.79	12.29	6	73.74	10.50	2.77
406	S04692900036	03/22/2012	4180	Smokin' Joe's #10 Tobacco and Liquor	480 7TH AVE	MARION	52302	480 7TH AVE\nMARION 52302\n(42.033308, -91.60546)	57	Linn	...	35918	Five O'clock Vodka	6	1750	6.92	10.38	2	20.76	3.50	0.92
533	S28313800043	10-06-2015	2509	Hy-Vee / Drugtown #1 / Cedar Rapids	1520 6TH ST SW	CEDAR RAPIDS	52404	1520 6TH ST SW\nCEDAR RAPIDS 52404\n(41.96233,...	57	Linn	...	42984	Trader Vics Private Selection Spiced Rum	6	1750	10.00	15.00	6	90.00	10.50	2.77
546	S20035900017	07-10-2014	2846	CVS Pharmacy #8443 / Cedar Rapids	3419 16th AVE SW	CEDAR RAPIDS	52404	3419 16th AVE SW\nCEDAR RAPIDS 52404\n(41.9635...	57	Linn	...	52318	Christian Bros Brandy	6	1750	11.83	17.74	1	17.74	1.75	0.46
547	S25327100009	04/28/2015	4297	Discount Liquor / Cedar Rapids	2933 1ST AVE SE	CEDAR RAPIDS	52402	2933 1ST AVE SE\nCEDAR RAPIDS 52402\n(42.00653...	57	Linn	...	41692	Uv Blue (raspberry) Vodka	6	1750	10.99	16.49	6	98.94	10.50	2.77
597	S08859700020	11-08-2012	3890	Smokin' Joe's #7 Tobacco and Liquor	904 1ST AVE NW	CEDAR RAPIDS	52405	904 1ST AVE NW\nCEDAR RAPIDS 52405\n(41.972773...	57	Linn	...	48102	Hennessy Vs Cognac 100ml	8	1200	36.00	54.00	1	54.00	1.20	0.32
637	S24199900020	02/24/2015	4297	Discount Liquor / Cedar Rapids	2933 1ST AVE SE	CEDAR RAPIDS	52402	2933 1ST AVE SE\nCEDAR RAPIDS 52402\n(42.00653...	57	Linn	...	37938	Skol Vodka	6	1750	7.11	10.67	6	64.02	10.50	2.77
694	S12904300018	06/18/2013	3692	Wilkie Liquors	724 1ST ST E	MT VERNON	52314	724 1ST ST E\nMT VERNON 52314\n(41.91776, -91....	57	Linn	...	26828	Jack Daniels Old #7 Black Lbl	6	1750	27.69	41.54	12	498.48	21.00	5.55
732	S14726100027	09/23/2013	2605	Hy-Vee Drugstore #5 / Cedar Rapids	2001 BLAIRS FERRY ROAD NE	CEDAR RAPIDS	52402	2001 BLAIRS FERRY ROAD NE\nCEDAR RAPIDS 52402\...	57	Linn	...	54448	Paramount Apricot Flavored Brandy	6	1750	11.28	16.93	6	101.58	10.50	2.77
755	S16318000105	12/18/2013	2560	Hy-Vee Food Store / Marion	3600 BUSINESS HWY 151 EAST	MARION	52302	3600 BUSINESS HWY 151 EAST\nMARION 52302\n	57	Linn	...	81208	Paramount Peppermint Schnapps	6	1750	7.08	10.62	6	63.72	10.50	2.77
782	S03771900051	01/26/2012	2552	Hy-Vee Food Store #3 / Cedar Rapids	20 WILSON AVENUE WEST	CEDAR RAPIDS	52404	20 WILSON AVENUE WEST\nCEDAR RAPIDS 52404\n(41...	57	Linn	...	35948	Fleischmann's Royal Vodka 80 Prf	6	1750	7.14	10.70	12	128.40	21.00	5.55
841	S07593800004	09-06-2012	4155	Fareway Stores #949 / Marion	3300 10TH AVE	MARION	52302	3300 10TH AVE\nMARION 52302\n(42.036023, -91.5...	57	Linn	...	36308	Hawkeye Vodka	6	1750	7.13	10.70	18	192.60	31.50	8.32
872	S09094000005	11/20/2012	2841	CVS Pharmacy #8532 / Cedar Rapids	2711 MT VERNON RD	CEDAR RAPIDS	52403	2711 MT VERNON RD\nCEDAR RAPIDS 52403\n(41.976...	57	Linn	...	37998	Smirnoff Vodka 80 Prf	6	1750	14.50	21.74	12	260.88	21.00	5.55
886	S11212600005	03/20/2013	2843	CVS Pharmacy #8526 / Cedar Rapids	4116 CENTER POINTE RD	CEDAR RAPIDS	52402	4116 CENTER POINTE RD\nCEDAR RAPIDS 52402\n(42...	57	Linn	...	36908	Mccormick Vodka Pet	6	1750	7.46	11.19	6	67.14	10.50	2.77
17 rows × 24 columns

1. Display sales records where "Bottle Volume (ml)" is either 500 or 1000, and the "Sale (Dollars)" is over $700.

Solution

df.loc[df['Bottle Volume (ml)'].isin([500, 1000]) & (df['Sale (Dollars)'] > 700)]


# Output

Invoice/Item Number	Date	Store Number	Store Name	Address	City	Zip Code	Store Location	County Number	County	...	Item Number	Item Description	Pack	Bottle Volume (ml)	State Bottle Cost	State Bottle Retail	Bottles Sold	Sale (Dollars)	Volume Sold (Liters)	Volume Sold (Gallons)
34	S12585300015	06-04-2013	3390	Okoboji Avenue Liquor	1610 OKOBOJI AVENUE	MILFORD	51351	1610 OKOBOJI AVENUE\nMILFORD 51351\n(43.331525...	30	Dickinson	...	28867	Tanqueray Gin	12	1000	14.99	22.48	36	809.28	36.0	9.51
92	S05984500004	06-11-2012	3692	Wilkie Liquors	724 1ST ST E	MT VERNON	52314	724 1ST ST E\nMT VERNON 52314\n(41.91776, -91....	57	Linn	...	11297	Crown Royal Canadian Whisky	12	1000	17.76	26.65	36	959.40	36.0	9.51
269	S15979000005	11/26/2013	3773	Benz Distributing	501 7TH AVE SE	CEDAR RAPIDS	52401	501 7TH AVE SE\nCEDAR RAPIDS 52401\n(41.975739...	57	Linn	...	26827	Jack Daniels Old #7 Black Lbl	12	1000	17.41	26.12	48	1253.76	48.0	12.68
338	S26714700062	07/13/2015	4829	Central City 2	1501 MICHIGAN AVE	DES MOINES	50314	1501 MICHIGAN AVE\nDES MOINES 50314\n(41.60556...	77	Polk	...	34422	Grey Goose Vodka	6	1000	22.50	33.75	60	2025.00	60.0	15.85
441	S19779600060	06/26/2014	2633	Hy-Vee #3 / BDI / Des Moines	3221 SE 14TH ST	DES MOINES	50320	3221 SE 14TH ST\nDES MOINES 50320\n(41.554101,...	77	Polk	...	34422	Grey Goose Vodka	6	1000	22.00	33.00	480	15840.00	480.0	126.80
476	S26237300004	06/16/2015	2629	Hy-Vee Food Store #2 / Council Bluff	1745 MADISON AVE	COUNCIL BLUFFS	51503	1745 MADISON AVE\nCOUNCIL BLUFFS 51503\n(41.24...	78	Pottawattamie	...	11297	Crown Royal Canadian Whisky	12	1000	18.50	27.75	36	999.00	36.0	9.51
655	S22949600003	12/15/2014	2478	Prairie Meadows	ONE PRAIRIE MEADOWS DRIVE	ALTOONA	50009	ONE PRAIRIE MEADOWS DRIVE\nALTOONA 50009\n	77	Polk	...	26827	Jack Daniels Old #7 Black Lbl	12	1000	17.90	26.85	36	966.60	36.0	9.51
773	S25375100038	04/29/2015	2625	Hy-Vee Wine and Spirits #2	3301 W KIMBERLY RD	DAVENPORT	52804	3301 W KIMBERLY RD\nDAVENPORT 52804\n	82	Scott	...	69637	Dr. Mcgillicuddy's Cherry Schnapps	12	1000	11.00	16.50	60	990.00	60.0	15.85
823	S08161000006	10-10-2012	3621	Jensen Liquors, Ltd.	615 2ND AVE	SHELDON	51201	615 2ND AVE\nSHELDON 51201\n(43.18463, -95.854...	71	O'Brien	...	43127	Bacardi Superior Rum	12	1000	9.43	14.14	96	1357.44	96.0	25.36
916	S12278000057	05/22/2013	3385	Sam's Club 8162 / Cedar Rapids	2605 BLAIRS FERRY RD NE	CEDAR RAPIDS	52402	2605 BLAIRS FERRY RD NE\nCEDAR RAPIDS 52402\n(...	57	Linn	...	34029	Absolut Citron (lemon Vodka)	12	1000	15.00	22.49	60	1349.40	60.0	15.85
10 rows × 24 columns

1. Show the sales of happened on 10-10-2012 and 11/26/2013 only.

Solution

df.loc[df['Date'].isin(['10-10-2012','11/26/2013'])]

# Output

Invoice/Item Number	Date	Store Number	Store Name	Address	City	Zip Code	Store Location	County Number	County	...	Item Number	Item Description	Pack	Bottle Volume (ml)	State Bottle Cost	State Bottle Retail	Bottles Sold	Sale (Dollars)	Volume Sold (Liters)	Volume Sold (Gallons)
28	S15983300014	11/26/2013	2551	Hy-Vee Food Store / Chariton	2001 WEST COURT	CHARITON	50049	2001 WEST COURT\nCHARITON 50049\n	59	Lucas	...	77472	Sweet Revenge	6	750	10.98	16.47	6	98.82	4.50	1.19
269	S15979000005	11/26/2013	3773	Benz Distributing	501 7TH AVE SE	CEDAR RAPIDS	52401	501 7TH AVE SE\nCEDAR RAPIDS 52401\n(41.975739...	57	Linn	...	26827	Jack Daniels Old #7 Black Lbl	12	1000	17.41	26.12	48	1253.76	48.00	12.68
468	S08226100006	10-10-2012	4557	Hometown Foods / Traer	420 SECOND ST	TRAER	50675	420 SECOND ST\nTRAER 50675\n(42.193386, -92.46...	86	Tama	...	69706	Dr. Mcgillicuddy's Root Beer Schnapps	12	750	8.32	12.48	3	37.44	2.25	0.59
823	S08161000006	10-10-2012	3621	Jensen Liquors, Ltd.	615 2ND AVE	SHELDON	51201	615 2ND AVE\nSHELDON 51201\n(43.18463, -95.854...	71	O'Brien	...	43127	Bacardi Superior Rum	12	1000	9.43	14.14	96	1357.44	96.00	25.36
4 rows × 24 columns