3. Series - Basic Operations

75MB

retail_data.csv

1. Load the data and access name series.

Solution

import pandas as pd

df=pd.read_csv('retail_data.csv')
print(df['Name'])

#  Output

0         Michelle Harrington
1                 Kelsey Hill
2                Scott Jensen
3               Joseph Miller
4               Debra Coleman
                 ...         
293906           Meagan Ellis
293907            Mathew Beck
293908             Daniel Lee
293909         Patrick Wilson
293910         Dustin Merritt
Name: Name, Length: 293911, dtype: object

1. Display address, zipcode, city, state and country column.

Solution

df[['Address','Zipcode','City','State','Country']]

# Output

	Address	Zipcode	City	State	Country
0	3959 Amanda Burgs	77985	Dortmund	Berlin	Germany
1	82072 Dawn Centers	99071	Nottingham	England	UK
2	4133 Young Canyon	75929	Geelong	New South Wales	Australia
3	8148 Thomas Creek Suite 100	88420	Edmonton	Ontario	Canada
4	5813 Lori Ports Suite 269	48704	Bristol	England	UK
...	...	...	...	...	...
293906	389 Todd Path Apt. 159	4567	Townsville	New South Wales	Australia
293907	52809 Mark Forges	16852	Hanover	Berlin	Germany
293908	407 Aaron Crossing Suite 495	88038	Brighton	England	UK
293909	3204 Baird Port	67608	Halifax	Ontario	Canada
293910	143 Amanda Crescent	25242	Tucson	West Virginia	USA
293911 rows × 5 columns

1. Display all the columns that are related to products.

Solution

df[['Product_Category','Product_Brand','Product_Type','products']]

# Output

Product_Category	Product_Brand	Product_Type	products
0	Clothing	Nike	Shorts	Cycling shorts
1	Electronics	Samsung	Tablet	Lenovo Tab
2	Books	Penguin Books	Children's	Sports equipment
3	Home Decor	Home Depot	Tools	Utility knife
4	Grocery	Nestle	Chocolate	Chocolate cookies
...	...	...	...	...
293906	Books	Penguin Books	Fiction	Historical fiction
293907	Electronics	Apple	Laptop	LG Gram
293908	Clothing	Adidas	Jacket	Parka
293909	Home Decor	IKEA	Furniture	TV stand
293910	Home Decor	Home Depot	Decorations	Clocks
293911 rows × 4 columns

1. Create a new column with 100 stored in it as value.

Solution

df['new_col']=100
df

# Output
Transaction_ID	Customer_ID	Name	Email	Phone	Address	City	State	Zipcode	Country	...	Product_Category	Product_Brand	Product_Type	Feedback	Shipping_Method	Payment_Method	Order_Status	Ratings	products	new_col
0	8691788	37249	Michelle Harrington	[e-mail]	1414786801	3959 Amanda Burgs	Dortmund	Berlin	77985	Germany	...	Clothing	Nike	Shorts	Excellent	Same-Day	Debit Card	Shipped	5	Cycling shorts	100
1	2174773	69749	Kelsey Hill	[e-mail]	6852899987	82072 Dawn Centers	Nottingham	England	99071	UK	...	Electronics	Samsung	Tablet	Excellent	Standard	Credit Card	Processing	4	Lenovo Tab	100
2	6679610	30192	Scott Jensen	[e-mail]	8362160449	4133 Young Canyon	Geelong	New South Wales	75929	Australia	...	Books	Penguin Books	Children's	Average	Same-Day	Credit Card	Processing	2	Sports equipment	100
3	7232460	62101	Joseph Miller	[e-mail]	2776751724	8148 Thomas Creek Suite 100	Edmonton	Ontario	88420	Canada	...	Home Decor	Home Depot	Tools	Excellent	Standard	PayPal	Processing	4	Utility knife	100
4	4983775	27901	Debra Coleman	[e-mail]	9098267635	5813 Lori Ports Suite 269	Bristol	England	48704	UK	...	Grocery	Nestle	Chocolate	Bad	Standard	Cash	Shipped	1	Chocolate cookies	100
...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...
293906	4246475	12104	Meagan Ellis	[e-mail]	7466353743	389 Todd Path Apt. 159	Townsville	New South Wales	4567	Australia	...	Books	Penguin Books	Fiction	Bad	Same-Day	Cash	Processing	1	Historical fiction	100
293907	1197603	69772	Mathew Beck	[e-mail]	5754304957	52809 Mark Forges	Hanover	Berlin	16852	Germany	...	Electronics	Apple	Laptop	Excellent	Same-Day	Cash	Processing	5	LG Gram	100
293908	7743242	28449	Daniel Lee	[e-mail]	9382530370	407 Aaron Crossing Suite 495	Brighton	England	88038	UK	...	Clothing	Adidas	Jacket	Average	Express	Cash	Shipped	2	Parka	100
293909	9301950	45477	Patrick Wilson	[e-mail]	9373222023	3204 Baird Port	Halifax	Ontario	67608	Canada	...	Home Decor	IKEA	Furniture	Good	Standard	Cash	Shipped	4	TV stand	100
293910	2882826	53626	Dustin Merritt	[e-mail]	9518926645	143 Amanda Crescent	Tucson	West Virginia	25242	USA	...	Home Decor	Home Depot	Decorations	Average	Same-Day	Cash	Shipped	2	Clocks	100
293911 rows × 31 columns

1. Create a new column that holds serial number 1 to last row.

Solution

rows=df.shape[0]

df['Sr_No.']=range(1,rows+1)
df

# Output

Transaction_ID	Customer_ID	Name	Email	Phone	Address	City	State	Zipcode	Country	...	Product_Category	Product_Brand	Product_Type	Feedback	Shipping_Method	Payment_Method	Order_Status	Ratings	products	Sr_No.
0	8691788	37249	Michelle Harrington	[e-mail]	1414786801	3959 Amanda Burgs	Dortmund	Berlin	77985	Germany	...	Clothing	Nike	Shorts	Excellent	Same-Day	Debit Card	Shipped	5	Cycling shorts	1
1	2174773	69749	Kelsey Hill	[e-mail]	6852899987	82072 Dawn Centers	Nottingham	England	99071	UK	...	Electronics	Samsung	Tablet	Excellent	Standard	Credit Card	Processing	4	Lenovo Tab	2
2	6679610	30192	Scott Jensen	[e-mail]	8362160449	4133 Young Canyon	Geelong	New South Wales	75929	Australia	...	Books	Penguin Books	Children's	Average	Same-Day	Credit Card	Processing	2	Sports equipment	3
3	7232460	62101	Joseph Miller	[e-mail]	2776751724	8148 Thomas Creek Suite 100	Edmonton	Ontario	88420	Canada	...	Home Decor	Home Depot	Tools	Excellent	Standard	PayPal	Processing	4	Utility knife	4
4	4983775	27901	Debra Coleman	[e-mail]	9098267635	5813 Lori Ports Suite 269	Bristol	England	48704	UK	...	Grocery	Nestle	Chocolate	Bad	Standard	Cash	Shipped	1	Chocolate cookies	5
...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...
293906	4246475	12104	Meagan Ellis	[e-mail]	7466353743	389 Todd Path Apt. 159	Townsville	New South Wales	4567	Australia	...	Books	Penguin Books	Fiction	Bad	Same-Day	Cash	Processing	1	Historical fiction	293907
293907	1197603	69772	Mathew Beck	[e-mail]	5754304957	52809 Mark Forges	Hanover	Berlin	16852	Germany	...	Electronics	Apple	Laptop	Excellent	Same-Day	Cash	Processing	5	LG Gram	293908
293908	7743242	28449	Daniel Lee	[e-mail]	9382530370	407 Aaron Crossing Suite 495	Brighton	England	88038	UK	...	Clothing	Adidas	Jacket	Average	Express	Cash	Shipped	2	Parka	293909
293909	9301950	45477	Patrick Wilson	[e-mail]	9373222023	3204 Baird Port	Halifax	Ontario	67608	Canada	...	Home Decor	IKEA	Furniture	Good	Standard	Cash	Shipped	4	TV stand	293910
293910	2882826	53626	Dustin Merritt	[e-mail]	9518926645	143 Amanda Crescent	Tucson	West Virginia	25242	USA	...	Home Decor	Home Depot	Decorations	Average	Same-Day	Cash	Shipped	2	Clocks	293911
293911 rows × 31 columns

1. Create a new column with random values between 1,1000.

Solution

df['RandomInt'] = np.random.randint(1, 1001, df.shape[0])
df

# Output

Transaction_ID	Customer_ID	Name	Email	Phone	Address	City	State	Zipcode	Country	...	Product_Category	Product_Brand	Product_Type	Feedback	Shipping_Method	Payment_Method	Order_Status	Ratings	products	RandomInt
0	8691788	37249	Michelle Harrington	[e-mail]	1414786801	3959 Amanda Burgs	Dortmund	Berlin	77985	Germany	...	Clothing	Nike	Shorts	Excellent	Same-Day	Debit Card	Shipped	5	Cycling shorts	717
1	2174773	69749	Kelsey Hill	[e-mail]	6852899987	82072 Dawn Centers	Nottingham	England	99071	UK	...	Electronics	Samsung	Tablet	Excellent	Standard	Credit Card	Processing	4	Lenovo Tab	391
2	6679610	30192	Scott Jensen	[e-mail]	8362160449	4133 Young Canyon	Geelong	New South Wales	75929	Australia	...	Books	Penguin Books	Children's	Average	Same-Day	Credit Card	Processing	2	Sports equipment	104
3	7232460	62101	Joseph Miller	[e-mail]	2776751724	8148 Thomas Creek Suite 100	Edmonton	Ontario	88420	Canada	...	Home Decor	Home Depot	Tools	Excellent	Standard	PayPal	Processing	4	Utility knife	694
4	4983775	27901	Debra Coleman	[e-mail]	9098267635	5813 Lori Ports Suite 269	Bristol	England	48704	UK	...	Grocery	Nestle	Chocolate	Bad	Standard	Cash	Shipped	1	Chocolate cookies	45
...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...
293906	4246475	12104	Meagan Ellis	[e-mail]	7466353743	389 Todd Path Apt. 159	Townsville	New South Wales	4567	Australia	...	Books	Penguin Books	Fiction	Bad	Same-Day	Cash	Processing	1	Historical fiction	670
293907	1197603	69772	Mathew Beck	[e-mail]	5754304957	52809 Mark Forges	Hanover	Berlin	16852	Germany	...	Electronics	Apple	Laptop	Excellent	Same-Day	Cash	Processing	5	LG Gram	986
293908	7743242	28449	Daniel Lee	[e-mail]	9382530370	407 Aaron Crossing Suite 495	Brighton	England	88038	UK	...	Clothing	Adidas	Jacket	Average	Express	Cash	Shipped	2	Parka	356
293909	9301950	45477	Patrick Wilson	[e-mail]	9373222023	3204 Baird Port	Halifax	Ontario	67608	Canada	...	Home Decor	IKEA	Furniture	Good	Standard	Cash	Shipped	4	TV stand	134
293910	2882826	53626	Dustin Merritt	[e-mail]	9518926645	143 Amanda Crescent	Tucson	West Virginia	25242	USA	...	Home Decor	Home Depot	Decorations	Average	Same-Day	Cash	Shipped	2	Clocks	556
293911 rows × 31 columns

1. Create a column for total sales.

Solution

df['total_sale']=df['Amount'] * df['Total_Purchases']
df

# Output

Transaction_ID	Customer_ID	Name	Email	Phone	Address	City	State	Zipcode	Country	...	Product_Category	Product_Brand	Product_Type	Feedback	Shipping_Method	Payment_Method	Order_Status	Ratings	products	total_sale
0	8691788	37249	Michelle Harrington	[e-mail]	1414786801	3959 Amanda Burgs	Dortmund	Berlin	77985	Germany	...	Clothing	Nike	Shorts	Excellent	Same-Day	Debit Card	Shipped	5	Cycling shorts	324.086270
1	2174773	69749	Kelsey Hill	[e-mail]	6852899987	82072 Dawn Centers	Nottingham	England	99071	UK	...	Electronics	Samsung	Tablet	Excellent	Standard	Credit Card	Processing	4	Lenovo Tab	806.707815
2	6679610	30192	Scott Jensen	[e-mail]	8362160449	4133 Young Canyon	Geelong	New South Wales	75929	Australia	...	Books	Penguin Books	Children's	Average	Same-Day	Credit Card	Processing	2	Sports equipment	1063.432799
3	7232460	62101	Joseph Miller	[e-mail]	2776751724	8148 Thomas Creek Suite 100	Edmonton	Ontario	88420	Canada	...	Home Decor	Home Depot	Tools	Excellent	Standard	PayPal	Processing	4	Utility knife	2466.854021
4	4983775	27901	Debra Coleman	[e-mail]	9098267635	5813 Lori Ports Suite 269	Bristol	England	48704	UK	...	Grocery	Nestle	Chocolate	Bad	Standard	Cash	Shipped	1	Chocolate cookies	248.553049
...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...
293906	4246475	12104	Meagan Ellis	[e-mail]	7466353743	389 Todd Path Apt. 159	Townsville	New South Wales	4567	Australia	...	Books	Penguin Books	Fiction	Bad	Same-Day	Cash	Processing	1	Historical fiction	973.962985
293907	1197603	69772	Mathew Beck	[e-mail]	5754304957	52809 Mark Forges	Hanover	Berlin	16852	Germany	...	Electronics	Apple	Laptop	Excellent	Same-Day	Cash	Processing	5	LG Gram	285.137301
293908	7743242	28449	Daniel Lee	[e-mail]	9382530370	407 Aaron Crossing Suite 495	Brighton	England	88038	UK	...	Clothing	Adidas	Jacket	Average	Express	Cash	Shipped	2	Parka	182.105284
293909	9301950	45477	Patrick Wilson	[e-mail]	9373222023	3204 Baird Port	Halifax	Ontario	67608	Canada	...	Home Decor	IKEA	Furniture	Good	Standard	Cash	Shipped	4	TV stand	120.834784
293910	2882826	53626	Dustin Merritt	[e-mail]	9518926645	143 Amanda Crescent	Tucson	West Virginia	25242	USA	...	Home Decor	Home Depot	Decorations	Average	Same-Day	Cash	Shipped	2	Clocks	2382.233416
293911 rows × 31 columns

1. Find out the birth year of the customers.

Solution

df['birth_year']=df['Year']-df['Age']
df[['Year','Age','birth_year']]

# Output

	Year	Age	birth_year
0	2023	21	2002
1	2023	19	2004
2	2023	48	1975
3	2023	56	1967
4	2024	22	2002
...	...	...	...
293906	2024	31	1993
293907	2023	35	1988
293908	2024	41	1983
293909	2023	41	1982
293910	2024	28	1996
293911 rows × 3 columns

1. Update the address column and add the city name along with the address.

Solution

df['Address']=df['Address']+' '+df['City']
df['Address']

# Output

0                    3959 Amanda Burgs Dortmund
1                 82072 Dawn Centers Nottingham
2                     4133 Young Canyon Geelong
3          8148 Thomas Creek Suite 100 Edmonton
4             5813 Lori Ports Suite 269 Bristol
                          ...                  
293906        389 Todd Path Apt. 159 Townsville
293907                52809 Mark Forges Hanover
293908    407 Aaron Crossing Suite 495 Brighton
293909                  3204 Baird Port Halifax
293910               143 Amanda Crescent Tucson
Name: Address, Length: 293911, dtype: object

242KB

sample_iowa_liquor_sales.csv

1. Load the data and display all the invoice numbers.

Solution

import pandas as pd

df=pd.read_csv('sample_iowa_liquor_sales.csv')
df['Invoice/Item Number']

# Output

0      S28865700001
1      S29339300091
2      S28866900001
3      S29134300126
4      S29282800048
           ...     
914    S26164400020
915    S19675100022
916    S12278000057
917    S05694100030
918    S23309100006
Name: Invoice/Item Number, Length: 919, dtype: object

1. Display the details and address with zipcode of all the stores.

Solution

df[['Store Number','Store Name','Address','Zip Code']]

# Output

	Store Number	Store Name	Address	Zip Code
0	2538	Hy-Vee Food Store #3 / Waterloo	1422 FLAMMANG DR	50702
1	2662	Hy-Vee Wine & Spirits / Muscatine	522 MULBERRY, SUITE A	52761
2	3650	Spirits, Stogies and Stuff	118 South Main St.	51025
3	3723	J D Spirits Liquor	1023 9TH ST	51040
4	2642	Hy-Vee Wine and Spirits / Pella	512 E OSKALOOSA	50219
...	...	...	...	...
914	3944	Sam's Club 4973 / Dubuque	4400 ASBURY RD	52002
915	4008	Sioux Valley Spirits	116 E MAIN ST	51004
916	3385	Sam's Club 8162 / Cedar Rapids	2605 BLAIRS FERRY RD NE	52402
917	2487	Anamosa Family Foods	402 EAST MAIN	52205
918	4559	Osage Payless Foods	633, CHASE ST	50461

1. Create a new column Beverage Type with values 'LIQUOR'. Store columns like category, category name, item description, and beverage type in a variable and display all values.

Solution

df['Beverage Type']='Liquor'
ndf=df[['Category', 'Category Name','Item Description','Beverage Type']]
ndf

# Output

Category	Category Name	Item Description	Beverage Type
0	1701100	DECANTERS & SPECIALTY PACKAGES	Forbidden Secret Coffee Pack	Liquor
1	1701100	DECANTERS & SPECIALTY PACKAGES	Laphroaig w/ Whiskey Stones	Liquor
2	1701100	DECANTERS & SPECIALTY PACKAGES	Forbidden Secret Coffee Pack	Liquor
3	1081200	CREAM LIQUEURS	Rumchata "GoChatas"	Liquor
4	1701100	DECANTERS & SPECIALTY PACKAGES	Forbidden Secret Coffee Pack	Liquor
...	...	...	...	...
914	1031200	VODKA FLAVORED	Uv Red (cherry) Vodka	Liquor
915	1042100	IMPORTED DRY GINS	Tanqueray Rangpur Gin	Liquor
916	1032200	IMPORTED VODKA - MISC	Absolut Citron (lemon Vodka)	Liquor
917	1012100	CANADIAN WHISKIES	Black Velvet	Liquor
918	1031080	VODKA 80 PROOF	Five O'clock PET Vodka	Liquor
919 rows × 4 columns

1. Create a new column Liquor Type with random values among (whiskey, rum, vodka). Store columns like category, category name, item description, and beverage type in a variable and display all values.

Solution

df['Liquor Type']=np.random.choice(['whiskey','vodka','rum'],919)
ndf=df[['Category', 'Category Name','Item Description','Liquor Type']]
ndf

# Output

Category	Category Name	Item Description	Liquor Type
0	1701100	DECANTERS & SPECIALTY PACKAGES	Forbidden Secret Coffee Pack	vodka
1	1701100	DECANTERS & SPECIALTY PACKAGES	Laphroaig w/ Whiskey Stones	vodka
2	1701100	DECANTERS & SPECIALTY PACKAGES	Forbidden Secret Coffee Pack	vodka
3	1081200	CREAM LIQUEURS	Rumchata "GoChatas"	whiskey
4	1701100	DECANTERS & SPECIALTY PACKAGES	Forbidden Secret Coffee Pack	whiskey
...	...	...	...	...
914	1031200	VODKA FLAVORED	Uv Red (cherry) Vodka	rum
915	1042100	IMPORTED DRY GINS	Tanqueray Rangpur Gin	whiskey
916	1032200	IMPORTED VODKA - MISC	Absolut Citron (lemon Vodka)	whiskey
917	1012100	CANADIAN WHISKIES	Black Velvet	vodka
918	1031080	VODKA 80 PROOF	Five O'clock PET Vodka	rum
919 rows × 4 columns

1. Calculate margin earned by retailers on each bottle.

Solution

df['margin']=df['State Bottle Retail']-df['State Bottle Cost']
df[['State Bottle Retail','State Bottle Cost','margin']]

# Output

State Bottle Retail	State Bottle Cost	margin
0	17.43	11.62	5.81
1	29.37	19.58	9.79
2	17.43	11.62	5.81
3	148.50	99.00	49.50
4	17.43	11.62	5.81
...	...	...	...
914	11.25	7.50	3.75
915	18.74	12.50	6.24
916	22.49	15.00	7.49
917	4.60	3.07	1.53
918	5.06	3.37	1.69
919 rows × 3 columns

1. Update the values of the column Volume Sold (Liters) into ml.

Solution

df['Volume Sold (Liters)']=df['Volume Sold (Liters)']*1000
df['Volume Sold (Liters)']

# Output

0       9000.0
1       3000.0
2       1500.0
3       6000.0
4       9000.0
        ...   
914    12000.0
915     1500.0
916    60000.0
917    18000.0
918     9000.0
Name: Volume Sold (Liters), Length: 919, dtype: float64