1. Reading and Loading the Data

Very Very Easy Assignments ---------------------- 10 min.

598B

sales_data.csv

Open

6KB

sales_data.xlsx

Open

744B

employee_sales_data.json

Open

Click below and get the link for HTML data

List of Friends episodesWikipedia

8KB

tips.csv

Open

import pandas as pd

1. Load JSON data and store it into a dataframe.

Solution

df1=pd.read_json('employee_sales_data.json')
df1

# Output

    Employee_ID	Employee_Name	Department	Sales_Amount	Sales_Target	Achieved_Target
0	EMP001	John Doe	Sales	10000	12000	False
1	EMP002	Jane Smith	Marketing	15000	14000	True
2	EMP003	Sam Wilson	IT	7000	8000	False
3	EMP004	Linda Taylor	Finance	12000	10000	True
4	EMP005	Michael Brown	Sales	11000	13000	False

1. Load CSV data and store it into a dataframe.

Solution

df2=pd.read_csv('sales_data.csv')
df2

# Output


Order_ID	Product	Quantity	Price_per_Unit	Total_Sales	Sale_Date
0	ORD001	Laptop	2	800	1600.00	1/1/2023
1	ORD002	Mobile	[phone]
2	ORD003	Tablet	3	300	900.00	1/3/2023
3	ORD004	Headphones	1	100	100.00	1/4/2023
4	ORD005	Charger	5	20	100.00	1/5/2023
5	ORD006	Laptop	2	800	1600.00	1/6/2023
6	ORD007	Tablet	1	300	300.00	1/7/2023
7	ORD008	Mobile	[phone]/2023
8	ORD009	Headphones	2	100	200.00	1/9/2023
9	ORD010	Charger	4	20	80.00	1/10/2023
10	ORD011	Laptop	1	800	800.00	1/11/2023
11	ORD012	Mobile	[phone]/2023
12	ORD013	Tablet	2	300	600.00	1/13/2023
13	ORD014	Headphones	1	100	100.00	1/14/2023
14	ORD015	Charger	5	20	100.00	1/15/2023

Note: To load Excel file: pip install openpyxl

1. Load Excel data and store in a dataframe

Solution

df3=pd.read_excel('sales_data.xlsx')
df3

# Output


Order_ID	Product	Quantity	Price_per_Unit	Total_Sales	Sale_Date
0	ORD001	Laptop	2	800	1600	2023-01-01
1	ORD002	Mobile	[phone]
2	ORD003	Tablet	3	300	900	2023-01-03
3	ORD004	Headphones	1	100	100	2023-01-04
4	ORD005	Charger	5	20	100	2023-01-05
5	ORD006	Laptop	2	800	1600	2023-01-06
6	ORD007	Tablet	1	300	300	2023-01-07
7	ORD008	Mobile	[phone]-08
8	ORD009	Headphones	2	100	200	2023-01-09
9	ORD010	Charger	4	20	80	2023-01-10
10	ORD011	Laptop	1	800	800	2023-01-11
11	ORD012	Mobile	[phone]-12
12	ORD013	Tablet	2	300	600	2023-01-13
13	ORD014	Headphones	1	100	100	2023-01-14
14	ORD015	Charger	5	20	100	2023-01-15

1. Load data from HTML.

Solution

df=pd.read_html('https://en.wikipedia.org/wiki/List_of_Friends_episodes')
df

# Output

#It will give all the tables that are available in the webpage.

[         Season     Episodes                 Originally aired                \
          Season     Episodes   Episodes.1         First aired    Last aired   
 0             1           24           24  September 22, 1994  May 18, 1995   
 1             2           24           24  September 21, 1995  May 16, 1996   
 2             3           25           25  September 19, 1996  May 15, 1997   
 3             4           24           24  September 25, 1997   May 7, 1998   
 4             5           24           24  September 24, 1998  May 20, 1999   
 5             6           25           25  September 23, 1999  May 18, 2000   
 6             7           24           24    October 12, 2000  May 17, 2001   
 7             8           24           24  September 27, 2001  May 16, 2002   
 8             9           24           24  September 26, 2002  May 15, 2003   
 9            10           18           18  September 25, 2003   May 6, 2004   
 10  The Reunion  The Reunion  The Reunion        May 27, 2021  May 27, 2021   
 
               Rank Rating Viewers (millions)  
     Network   Rank Rating Viewers (millions)  
 0       NBC   8[3]   15.6               24.8  
 1       NBC   3[4]   18.7               31.7  
 2       NBC   4[5]   16.8               26.1  
 3       NBC   4[6]   16.1               25.0  
 4       NBC   2[7]   15.7               24.7  
 5       NBC   5[8]   14.0               22.6  
 6       NBC   5[9]   12.6               22.1  
 7       NBC  1[10]   15.0               26.4  
 8       NBC  2[11]   13.5               23.9  
...
 6             Season 7  "The One with Monica's Thunder" "The One with ...
 7             Season 8  "The One After 'I Do'" "The One with Rachel's ...
 8             Season 9  "The One Where No One Proposes" "The One with ...
 9            Season 10  "The One After Joey and Rachel Kiss" "The One ...
 10            Specials                               Friends: The Reunion]

1. Load Data from clipboard( Copy top 30 rows from tips file.)

Solution

df=pd.read_clipboard()
df

# Now practice on any random set of rows.

# Output

    total_bill	tip	gender	smoker	day	time	size
0	16.99	1.01	Female	No	Sun	Dinner	2
1	10.34	1.66	Male	No	Sun	Dinner	3
2	21.01	3.50	Male	No	Sun	Dinner	3
3	23.68	3.31	Male	No	Sun	Dinner	2
4	24.59	3.61	Female	No	Sun	Dinner	4
5	25.29	4.71	Male	No	Sun	Dinner	4
6	8.77	2.00	Male	No	Sun	Dinner	2
7	26.88	3.12	Male	No	Sun	Dinner	4
8	15.04	1.96	Male	No	Sun	Dinner	2
9	14.78	3.23	Male	No	Sun	Dinner	2
10	10.27	1.71	Male	No	Sun	Dinner	2
11	35.26	5.00	Female	No	Sun	Dinner	4
12	15.42	1.57	Male	No	Sun	Dinner	2
13	18.43	3.00	Male	No	Sun	Dinner	4
14	14.83	3.02	Female	No	Sun	Dinner	2
15	21.58	3.92	Male	No	Sun	Dinner	2
16	10.33	1.67	Female	No	Sun	Dinner	3
17	16.29	3.71	Male	No	Sun	Dinner	3
18	16.97	3.50	Female	No	Sun	Dinner	3
19	20.65	3.35	Male	No	Sat	Dinner	3
20	17.92	4.08	Male	No	Sat	Dinner	2
21	20.29	2.75	Female	No	Sat	Dinner	2
22	15.77	2.23	Female	No	Sat	Dinner	2
23	39.42	7.58	Male	No	Sat	Dinner	4
24	19.82	3.18	Male	No	Sat	Dinner	2
25	17.81	2.34	Male	No	Sat	Dinner	4
26	13.37	2.00	Male	No	Sat	Dinner	2
27	12.69	2.00	Male	No	Sat	Dinner	2
28	21.70	4.30	Male	No	Sat	Dinner	2

Easy Assignments -------------------------- 15 mins

54KB

sample_iowa_liquor_sales_sheets.xlsx

Open

1. Load the data of sheet 3 only from the above excel file.

Solution

df=pd.read_excel('sample_iowa_liquor_sales_sheets.xlsx',sheet_name='Sheet 3')
df

# 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)
0	S28868100001	2015-09-11 00:00:00	2555	Hy-Vee Food Store / Keokuk	3111 MAIN	KEOKUK	52632	3111 MAIN\nKEOKUK 52632\n(40.414975, -91.403338)	56	Lee	...	237	Knob Creek w/ Crystal Decanter	3	1750	35.55	53.34	3	160.02	5.25	1.39
1	S10699400047	02/20/2013	4794	Smokin' Joe's #17 Tobacco and Liquor	110 S ROOSEVELT HWY 61	BURLINGTON	52601	110 S ROOSEVELT HWY 61\nBURLINGTON 52601\n(40....	29	Des Moines	...	64000	Absente	12	750	21.67	32.50	4	130.00	3.00	0.79
2	S23647700049	01/26/2015	4742	No Frills Supermarkets #786 / Counci	1817, W BROADWAY	COUNCIL BLUFFS	51501	1817, W BROADWAY\nCOUNCIL BLUFFS 51501\n(41.26...	78	Pottawattamie	...	43128	Bacardi Superior Rum	6	1750	15.00	22.50	12	270.00	21.00	5.55
3	S08792800017	2012-06-11 00:00:00	2459	Reinhart Foods	200 STATE PO BOX 98	GUTHRIE CENTER	50115	200 STATE PO BOX 98\nGUTHRIE CENTER 50115\n	39	Guthrie	...	44499	Cruzan Mango Rum	12	750	6.82	10.24	3	30.72	2.25	0.59
4	S04854100048	2012-03-04 00:00:00	2487	Anamosa Family Foods	402 EAST MAIN	ANAMOSA	52205	402 EAST MAIN\nANAMOSA 52205\n(42.108289, -91....	53	Jones	...	80578	Arrow Peppermint Schnapps	6	1750	7.04	10.56	3	31.68	5.25	1.39
5	S13206600042	2013-08-07 00:00:00	3908	County Market #214 / Fort Madison	11802 AVENUE H	FORT MADISON	52627	11802 AVENUE H\nFORT MADISON 52627\n	56	Lee	...	41694	Uv Blue (raspberry) Vodka	12	1000	7.50	11.25	12	135.00	12.00	3.17
6	S29065400007	11/16/2015	2555	Hy-Vee Food Store / Keokuk	3111 MAIN	KEOKUK	52632	3111 MAIN\nKEOKUK 52632\n(40.414975, -91.403338)	56	Lee	...	37996	Smirnoff Vodka 80 Prf	12	750	8.25	12.38	12	148.56	9.00	2.38
7	S08900500035	11/13/2012	4509	A J'S LIQUOR II	2515 CHAMBERLAIN	AMES	50010	2515 CHAMBERLAIN\nAMES 50010\n(42.02146, -93.6...	85	Story	...	75210	Kinky Liqueur	6	750	10.00	15.00	12	180.00	9.00	2.38
8	S12302500025	05/21/2013	3929	Liquor And Tobacco Outlet	515 1/2 BROADWAY ST	WATERLOO	50703	515 1/2 BROADWAY ST\nWATERLOO 50703\n(42.51041...	7	Black Hawk	...	88290	Patron Silver Tequila	12	200	8.50	12.75	4	51.00	0.80	0.21
9	S17627300143	02/26/2014	2614	Hy-Vee #3 Food and Drugstore	1823 E KIMBERLY RD	DAVENPORT	52807	1823 E KIMBERLY RD\nDAVENPORT 52807\n(41.55678...	82	Scott	...	15776	Bushmills Irish Whiskey	12	750	13.00	19.50	4	78.00	3.00	0.79
10	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
11	S06855800032	07/31/2012	2522	Hy-Vee Wine and Spirits / Spirit Lak	HWY 9 & 71	SPIRIT LAKE	51360	HWY 9 & 71\nSPIRIT LAKE 51360\n	30	Dickinson	...	43125	Bacardi Superior Rum Pet	12	750	7.53	11.30	12	135.60	9.00	2.38
12	S26107400154	2015-09-06 00:00:00	2666	Hy-Vee #2 / Ankeny	2510 SW STATE ST	ANKENY	50023	2510 SW STATE ST\nANKENY 50023\n(41.704185, -9...	77	Polk	...	76478	Pama Pomegranate Liqueur	6	750	8.83	13.75	2	27.50	1.50	0.40
13	S18283700037	2014-07-04 00:00:00	2603	Hy-Vee Wine and Spirits / Bettendorf	2890 DEVILS GLEN ROAD	BETTENDORF	52722	2890 DEVILS GLEN ROAD\nBETTENDORF 52722\n(41.5...	82	Scott	...	30318	Gordon's Gin London Dry - Pet	6	1750	11.00	16.49	6	98.94	10.50	2.77
14	S08372100023	10/16/2012	3816	Swils	200 E OAK ST	RED OAK	51566	200 E OAK ST\nRED OAK 51566\n(41.016691, -95.2...	69	Montgomery	...	41693	Uv Blue (raspberry) Vodka	12	750	6.25	9.49	12	113.88	9.00	2.38
15	S11716100038	04/17/2013	3990	Cork and Bottle / Oskaloosa	309 A AVE WEST	OSKALOOSA	52577	309 A AVE WEST\nOSKALOOSA 52577\n(41.296228, -...	62	Mahaska	...	14192	Pendleton Canadian Whisky	12	750	12.10	18.65	2	37.30	1.50	0.40
16	S12585300015	2013-04-06 00:00:00	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.00	9.51
17	S05702000043	05/24/2012	3912	Smokin' Joe's #14 Tobacco and Liquor	225 EDGEWOOD RD	CEDAR RAPIDS	52405	225 EDGEWOOD RD\nCEDAR RAPIDS 52405\n(41.97341...	57	Linn	...	11296	Crown Royal	12	750	14.50	21.74	1	21.74	0.75	0.20
18	S05590100008	05/17/2012	3540	Super Target T-0533 / Davenport	5225 ELMORE AVE	DAVENPORT	52807	5225 ELMORE AVE\nDAVENPORT 52807\n(41.573626, ...	82	Scott	...	28866	Tanqueray Gin	12	750	12.00	17.99	12	215.88	9.00	2.38
19	S27992700007	09/21/2015	4898	Burlington Shell	130, S ROOSEVELT AVE	BURLINGTON	52601	130, S ROOSEVELT AVE\nBURLINGTON 52601\n(40.80...	29	Des Moines	...	37993	Smirnoff Vodka 80 Prf	48	200	2.75	4.13	10	41.30	2.00	0.53
20	S11403900036	2013-02-04 00:00:00	3858	Harolds Jack N Jill / Davenport	6723 NW BLVD UNIT 2	DAVENPORT	52806	6723 NW BLVD UNIT 2\nDAVENPORT 52806\n	82	Scott	...	40390	UV Candy Bar Mini	6	500	29.30	43.95	1	43.95	0.50	0.13
21	S07422400037	2012-12-10 00:00:00	3819	Quillins Decorah	915 SHORT ST	DECORAH	52101	915 SHORT ST\nDECORAH 52101\n(43.291013, -91.7...	96	Winneshiek	...	74476	Hiram Walker Pumpkin Spice	12	750	4.00	5.99	12	71.88	9.00	2.38
22	S08426400013	10/18/2012	4344	Spirits / Grimes	109 EAST 1ST ST STE B	GRIMES	50111	109 EAST 1ST ST STE B\nGRIMES 50111\n(41.6884,...	77	Polk	...	23278	Calvert Extra Bl Whiskey	6	1750	11.02	16.53	1	16.53	1.75	0.46
23	S19526900004	2014-12-06 00:00:00	4819	Super Stop 2 / Altoona	2635 ADVENTURELAND DR	ALTOONA	50009	2635 ADVENTURELAND DR\nALTOONA 50009\n(41.6585...	77	Polk	...	35318	Barton Vodka	6	1750	6.92	10.38	6	62.28	10.50	2.77
24	S19153100167	05/23/2014	2515	Hy-Vee Food Store #1 / Mason City	2400 4TH ST SW	MASON CITY	50401	2400 4TH ST SW\nMASON CITY 50401\n(43.148446, ...	17	Cerro Gordo	...	5036	Glenlivet 12 Yr Malt Scotch	12	750	22.07	33.11	2	66.22	1.50	0.40
25	S16168900009	2013-09-12 00:00:00	4110	Brothers Market, Inc.	706 HIGHWAY 57	PARKERSBURG	50665	706 HIGHWAY 57\nPARKERSBURG 50665\n(42.570361,...	12	Butler	...	43338	Captain Morgan Spiced Rum	6	1750	17.75	26.62	6	159.72	10.50	2.77
26	S06459500039	2012-09-07 00:00:00	4105	Fareway Stores #426 / Nevada	1505 SOUTH B AVE	NEVADA	50201	1505 SOUTH B AVE\nNEVADA 50201\n(42.009797, -9...	85	Story	...	31658	Paramount Gin	6	1750	7.10	10.65	2	21.30	3.50	0.92
27	S18205200042	2014-02-04 00:00:00	4167	Iowa Street Market, Inc.	1256 IOWA ST	DUBUQUE	52001	1256 IOWA ST\nDUBUQUE 52001\n(42.504958, -90.6...	31	Dubuque	...	24456	Kessler Blend Whiskey	12	750	5.51	8.26	12	99.12	9.00	2.38
28	S21679800033	2014-08-10 00:00:00	2642	Hy-Vee Wine and Spirits / Pella	512 E OSKALOOSA	PELLA	50219	512 E OSKALOOSA\nPELLA 50219\n(41.397023, -92....	63	Marion	...	12476	Canadian Mist Whiskey Pet	12	750	5.44	8.16	12	97.92	9.00	2.38
29	S29131400002	11/17/2015	4426	LIQUOR AND GROCERY DEPOT	114 CENTRAL ST N Suite 1	MARSHALLTOWN	50158	114 CENTRAL ST N Suite 1\nMARSHALLTOWN 50158\n	64	Marshall	...	23626	Five Star Pet	12	750	3.46	5.19	12	62.28	9.00	2.38
30	S11568600008	2013-10-04 00:00:00	3536	Geno's Liquor	111 S COMMERCIAL AVE	EAGLE GROVE	50533	111 S COMMERCIAL AVE\nEAGLE GROVE 50533\n(42.6...	99	Wright	...	43337	Captain Morgan Spiced Rum	12	1000	11.30	16.94	3	50.82	3.00	0.79
31	S22994400050	12/17/2014	4167	Iowa Street Market, Inc.	1256 IOWA ST	DUBUQUE	52001	1256 IOWA ST\nDUBUQUE 52001\n(42.504958, -90.6...	31	Dubuque	...	11777	Black Velvet	12	1000	6.63	9.95	48	477.60	48.00	12.68
32	S07934900009	09/25/2012	3774	Eagle Country Market / Dubuque	1800 ELM ST	DUBUQUE	52001	1800 ELM ST\nDUBUQUE 52001\n(42.511105, -90.66...	31	Dubuque	...	43336	Captain Morgan Original Spiced	12	750	8.50	12.74	12	152.88	9.00	2.38
33	S25525700025	2015-07-05 00:00:00	4958	Five Corners Liquor & Wine	809 E 18TH ST	CEDAR FALLS	50613	809 E 18TH ST\nCEDAR FALLS 50613\n(42.5214, -9...	7	Black Hawk	...	86796	Southern Host Whiskey Liqueur	12	750	4.87	7.56	3	22.68	2.25	0.59
34	S24499300036	2015-12-03 00:00:00	4847	Slagle Foods LeClaire	1301 EAGLE RIDGE RD	LECLAIRE	52753	1301 EAGLE RIDGE RD\nLECLAIRE 52753\n(41.58734...	82	Scott	...	19061	Jim Beam Mini	12	500	6.83	10.25	1	10.25	0.50	0.13
35	S23530700064	01/15/2015	4969	Lake Liquors Wine and Spirits	910 N 8TH ST W	CLEAR LAKE	50428	910 N 8TH ST W\nCLEAR LAKE 50428\n(43.142775, ...	17	Cerro Gordo	...	5486	Macallan 12 Yr Single Malt Scotch	12	750	30.07	45.11	3	135.33	2.25	0.59
36	S04414100075	2012-06-03 00:00:00	2532	Hy-Vee Food Store #2 / Des Moines	2540 E EUCLID	DES MOINES	50317	2540 E EUCLID\nDES MOINES 50317\n(41.627842, -...	77	Polk	...	65256	Jagermeister Liqueur	12	750	11.58	17.37	12	208.44	9.00	2.38
37	S20967300001	2014-02-09 00:00:00	4576	THE BOONEDOCKS	228 N HWY 71	ARNOLDS PARK	51331	228 N HWY 71\nARNOLDS PARK 51331\n(43.370811, ...	30	Dickinson	...	19066	Jim Beam	12	750	9.70	14.95	12	179.40	9.00	2.38
38	S04958400027	2012-10-04 00:00:00	4488	Casey's General Store #2762 / Cedar	20 MILLER AVE SW	CEDAR RAPIDS	52404	20 MILLER AVE SW\nCEDAR RAPIDS 52404\n(41.9245...	57	Linn	...	34061	Absolut Ruby Red	12	750	10.89	16.33	4	65.32	3.00	0.79
39	S20666600066	08/14/2014	2190	Central City Liquor, Inc.	1460 2ND AVE	DES MOINES	50314	1460 2ND AVE\nDES MOINES 50314\n(41.60566, -93...	77	Polk	...	27464	Cedar Ridge Wheat Whiskey	6	750	17.77	26.66	1	26.66	0.75	0.20
40	S04404400009	2012-06-03 00:00:00	3886	Wal-Mart 0646 / Anamosa	101 115 ST	ANAMOSA	52205	101 115 ST\nANAMOSA 52205\n(42.101677, -91.264...	53	Jones	...	19068	Jim Beam	6	1750	17.37	26.55	6	159.30	10.50	2.77
41	S27099100001	2015-03-08 00:00:00	5070	Jeff's Market / Wilton	101, E 4TH ST	WILTON	52778	101, E 4TH ST\nWILTON 52778\n(41.58873, -91.01...	70	Muscatine	...	80578	Arrow Peppermint Schnapps	6	1750	7.05	10.58	6	63.48	10.50	2.77
42	S10436800050	2013-05-02 00:00:00	2962	Dahl's / Johnston	5440 NW 86TH ST	JOHNSTON	50131	5440 NW 86TH ST\nJOHNSTON 50131\n(41.659093, -...	77	Polk	...	36969	New Amsterdam Vodka	12	750	6.90	10.35	3	31.05	2.25	0.59
43	S24009000053	2015-12-02 00:00:00	4269	Tipton Family Foods	610 CEDAR ST	TIPTON	52772	610 CEDAR ST\nTIPTON 52772\n(41.771753, -91.12...	16	Cedar	...	10626	Canadian Club Whisky	12	750	7.88	11.82	5	59.10	3.75	0.99
44	S09423900016	2012-12-12 00:00:00	4625	Kwik Shop #532 / Council Bluffs	1602 AVENUE G	COUNCIL BLUFFS	51501	1602 AVENUE G\nCOUNCIL BLUFFS 51501\n(41.26798...	78	Pottawattamie	...	34001	Absolut Swedish Vodka 80 Prf Mini	10	600	7.92	11.88	1	11.88	0.60	0.16
45	S11449100031	2013-03-04 00:00:00	3925	Johncy's Liquor Store Inc	585 HWY 965 S STE E	NORTH LIBERTY	52317	585 HWY 965 S STE E\nNORTH LIBERTY 52317\n(41....	48	Iowa	...	37997	Smirnoff Vodka 80 Prf	12	1000	8.95	13.43	12	161.16	12.00	3.17
46	S22189000027	2014-05-11 00:00:00	3932	Main Street Spirits / Mapleton	311 MAIN ST	MAPLETON	51034	311 MAIN ST\nMAPLETON 51034\n(42.165915, -95.7...	67	Monona	...	18198	Ezra Brooks Ky Sour Mash Bourbon	6	1750	14.34	21.51	2	43.02	3.50	0.92
47	S28774900013	2015-02-11 00:00:00	4689	Walgreens #11759 / Fort Madison	2639 AVENUE L	FORT MADISON	52627	2639 AVENUE L\nFORT MADISON 52627\n(40.626968,...	56	Lee	...	45886	Sailor Jerry Spiced Navy Rum	12	750	9.65	14.48	12	173.76	9.00	2.38
48	S11831600004	04/24/2013	4187	Jensen Liquor North	109 5TH ST	SIBLEY	51249	109 5TH ST\nSIBLEY 51249\n(43.403935, -95.759951)	72	Osceola	...	59037	Desert Island Long Island Ice Tea Cocktail	12	1000	3.85	6.03	12	72.36	12.00	3.17
49	S12668400023	2013-10-06 00:00:00	4276	Wj's Liquor	107 N MILL ST	LAKE MILLS	50450	107 N MILL ST\nLAKE MILLS 50450\n(43.419668, -...	95	Winnebago	...	75212	Kinky Blue Mini	6	500	4.96	7.44	1	7.44	0.50	0.13
50 rows × 24 columns

1. Load the data of sheet 4 only from the above excel file but it has 2 unused rows at the top.

Solution

df=pd.read_excel('sample_iowa_liquor_sales_sheets.xlsx',sheet_name='Sheet 4',skiprows=2)
df

# 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)
0	S08894500006	11/13/2012	4443	Palo Mini Mart	1204 1ST ST	PALO	52324	1204 1ST ST\nPALO 52324\n(42.070316, -91.795808)	57	Linn	...	43336	Captain Morgan Original Spiced	12	750	8.50	12.74	12	152.88	9.00	2.38
1	S06021200082	2012-12-06 00:00:00	3390	Okoboji Avenue Liquor	1610 OKOBOJI AVENUE	MILFORD	51351	1610 OKOBOJI AVENUE\nMILFORD 51351\n(43.331525...	30	Dickinson	...	21596	Ten High	12	750	4.00	5.99	12	71.88	9.00	2.38
2	S22996900012	12/17/2014	2413	Beecher Beverage	1691 ASBURY ROAD	DUBUQUE	52001	1691 ASBURY ROAD\nDUBUQUE 52001\n(42.500858, -...	31	Dubuque	...	11297	Crown Royal Canadian Whisky	12	1000	18.50	27.75	24	666.00	24.00	6.34
3	S23424900021	2015-12-01 00:00:00	4659	Casey's General Store #2802 / Conrad	409 E CENTER	CONRAD	50621	409 E CENTER\nCONRAD 50621\n	38	Grundy	...	37991	Smirnoff Vodka 80 Prf Mini	12	500	7.47	11.21	1	11.21	0.50	0.13
4	S15431000148	10/30/2013	3820	Charlie's Wine and Spirits,	507 W 19th St	SIOUX CITY	51103	507 W 19th St\nSIOUX CITY 51103\n(42.510535, -...	97	Woodbury	...	5516	Macallan 18yr Single Malt Scotch	12	750	99.95	149.92	2	299.84	1.50	0.40
5	S21795200005	10/15/2014	5045	Central Mart / Hamilton Blvd	800 HAMILTON BLVD	SIOUX CITY	51103	800 HAMILTON BLVD\nSIOUX CITY 51103\n(42.50438...	97	Woodbury	...	23827	Five Star	12	1000	4.40	6.60	12	79.20	12.00	3.17
6	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
7	S03642100028	01/18/2012	3928	Smokin' Joe's #12 Tobacco and Liquor	465 HWY 965 UNIT G	NORTH LIBERTY	52317	465 HWY 965 UNIT G\nNORTH LIBERTY 52317\n(41.7...	52	Johnson	...	37934	Skol Vodka	24	375	1.74	2.60	2	5.20	0.75	0.20
8	S15364700018	10/28/2013	4121	Newport Bp / Wapello	13410 STATE HWY 78	WAPELLO	52653	13410 STATE HWY 78\nWAPELLO 52653\n	58	Louisa	...	65253	Jagermeister Liqueur	48	200	3.50	5.26	3	15.78	0.60	0.16
9	S09409900025	2012-11-12 00:00:00	4670	Guppy's On The Go / Tripoli	600, 7TH AVE SW	TRIPOLI	50676	600, 7TH AVE SW\nTRIPOLI 50676\n(42.802375, -9...	9	Bremer	...	43126	Bacardi Superior Rum	12	750	7.53	11.30	2	22.60	1.50	0.40
10	S13570900020	07/25/2013	4819	Super Stop 2 / Altoona	2635 ADVENTURELAND DR	ALTOONA	50009	2635 ADVENTURELAND DR\nALTOONA 50009\n(41.6585...	77	Polk	...	43051	Bacardi Dragon Berry	12	750	7.33	11.49	4	45.96	3.00	0.79
11	S06441700180	2012-06-07 00:00:00	2544	Hy-Vee Food Store / Marshalltown	802 SOUTH CENTER	MARSHALLTOWN	50158	802 SOUTH CENTER\nMARSHALLTOWN 50158\n(42.0347...	64	Marshall	...	36301	Hawkeye Vodka 80 Prf Mini	12	500	3.73	5.59	3	16.77	1.50	0.40
12	S16937000033	01/21/2014	4277	Cork It!	769 PROSPECT ST SW	LE MARS	51031	769 PROSPECT ST SW\nLE MARS 51031\n(42.776296,...	75	Plymouth	...	89496	Margaritaville Gold Tequila	12	750	6.52	10.28	2	20.56	1.50	0.40
13	S05487200004	2012-10-05 00:00:00	3692	Wilkie Liquors	724 1ST ST E	MT VERNON	52314	724 1ST ST E\nMT VERNON 52314\n(41.91776, -91....	57	Linn	...	26827	Jack Daniels Old #7 Black Lbl	12	1000	16.92	25.37	24	608.88	24.00	6.34
14	S08845700025	2012-08-11 00:00:00	2508	Hy-Vee Food Store #1 / Cedar Rapids	1843 JOHNSON AVENUE, N.W.	CEDAR RAPIDS	52405	1843 JOHNSON AVENUE, N.W.\nCEDAR RAPIDS 52405\...	57	Linn	...	23626	Five Star Pet	12	750	3.46	5.19	12	62.28	9.00	2.38
15	S26656700011	2015-09-07 00:00:00	3647	Wal-Mart 2889 / Clinton	2715 SOUTH 25TH ST	CLINTON	52732	2715 SOUTH 25TH ST\nCLINTON 52732\n(41.818874,...	23	Clinton	...	38178	Titos Handmade Vodka	6	1750	18.52	27.78	6	166.68	10.50	2.77
16	S28184800058	09/29/2015	2465	Sid's Beverage Shop	2727 DODGE ST	DUBUQUE	52003	2727 DODGE ST\nDUBUQUE 52003\n(42.4918, -90.70...	31	Dubuque	...	86670	Jack Daniel's Tennessee Honey	12	750	15.07	22.61	2	45.22	1.50	0.40
17	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.00	9.51
18	S23819700011	2015-03-02 00:00:00	2660	HY-VEE FOOD STORE / SIOUX CENTER	1951 S MAIN AVE	SIOUX CENTER	51250	1951 S MAIN AVE\nSIOUX CENTER 51250\n(43.05603...	84	Sioux	...	36978	Nikolai Vodka	6	1750	6.92	10.38	2	20.76	3.50	0.92
19	S19553400063	06/16/2014	4829	Central City 2	1501 MICHIGAN AVE	DES MOINES	50314	1501 MICHIGAN AVE\nDES MOINES 50314\n(41.60556...	77	Polk	...	80687	Dekuyper Blustery Peppermint Burst Schnapps	12	1000	3.86	5.78	12	69.36	12.00	3.17
20	S18956900038	05/14/2014	2585	Hy-Vee Wine and Spirits / Algona	1516 HIGHWAY 169 NORTH	ALGONA	50511	1516 HIGHWAY 169 NORTH\nALGONA 50511\n(43.0812...	55	Kossuth	...	37997	Smirnoff Vodka 80 Prf	12	1000	9.25	13.87	12	166.44	12.00	3.17
21	S04408700074	2012-06-03 00:00:00	2624	Hy-Vee #2 / Dubuque	2395 NW ARTERIAL RD	DUBUQUE	52002	2395 NW ARTERIAL RD\nDUBUQUE 52002\n(42.516621...	31	Dubuque	...	49186	Remy Martin Vsop Cognac	12	750	20.83	31.25	2	62.50	1.50	0.40
22	S11500500065	2013-08-04 00:00:00	3704	Celtics Beverage Company	1122 BROADWAY	EMMETSBURG	50536	1122 BROADWAY\nEMMETSBURG 50536\n(43.109636, -...	74	Palo Alto	...	65126	Grand Marnier Cordon Rouge	12	750	23.31	34.96	1	34.96	0.75	0.20
23	S13670200015	07/31/2013	3644	Wal-Mart 2764 / Altoona	3501 8TH ST SW	ALTOONA	50009	3501 8TH ST SW\nALTOONA 50009\n(41.644195, -93...	77	Polk	...	34821	Svedka Vodka	6	1750	13.28	19.92	6	119.52	10.50	2.77
24	S05984500004	2012-11-06 00:00:00	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.00	9.51
25	S04846600029	2012-03-04 00:00:00	4509	A J'S LIQUOR II	2515 CHAMBERLAIN	AMES	50010	2515 CHAMBERLAIN\nAMES 50010\n(42.02146, -93.6...	85	Story	...	34433	Grey Goose Vodka	12	750	17.97	26.96	12	323.52	9.00	2.38
26	S22667200025	2014-02-12 00:00:00	4891	Schottsy's Liquor	109 5th ST	SIBLEY	51249	109 5th ST\nSIBLEY 51249\n(43.403935, -95.759951)	72	Osceola	...	58866	Jose Cuervo Authentic Strawberry Margarita	12	750	4.62	6.93	2	13.86	1.50	0.40
27	S06847200026	07/31/2012	3488	Bender's Foods	400 S SECOND	BELLEVUE	52031	400 S SECOND\nBELLEVUE 52031\n(42.255889, -90....	49	Jackson	...	86887	Southern Comfort	12	1000	11.82	17.74	12	212.88	12.00	3.17
28	S04197400002	02/22/2012	3906	Cigarette Outlet #17 / Burlington	412 N ROOSEVELT ST	BURLINGTON	52601	412 N ROOSEVELT ST\nBURLINGTON 52601\n(40.8150...	29	Des Moines	...	36306	Hawkeye Vodka	12	750	3.25	4.88	12	58.56	9.00	2.38
29	S26899700012	07/22/2015	2500	Hy-Vee Food Store #1 / Ames	3800 W LINCOLN WAY	AMES	50010	3800 W LINCOLN WAY\nAMES 50010\n(42.023145, -9...	85	Story	...	6196	Talisker Storm	6	750	34.49	51.74	1	51.74	0.75	0.20
30	S24892500005	2015-06-04 00:00:00	4673	Sonny's Super Market / West Point	327 5TH ST	WEST POINT	52656	327 5TH ST\nWEST POINT 52656\n(40.717867, -91....	56	Lee	...	64866	Fireball Cinnamon Whiskey	12	750	8.98	13.47	24	323.28	18.00	4.76
31	S21721200051	10/13/2014	2555	Hy-Vee Food Store / Keokuk	3111 MAIN	KEOKUK	52632	3111 MAIN\nKEOKUK 52632\n(40.414975, -91.403338)	56	Lee	...	89193	Jose Cuervo Especial Reposado Tequila	48	200	3.00	4.50	48	216.00	9.60	2.54
32	S09016600003	11/17/2012	3521	Ameristar Casino / Council Bluffs	2200 RIVER ROAD	COUNCIL BLUFFS	51501	2200 RIVER ROAD\nCOUNCIL BLUFFS 51501\n(41.242...	78	Pottawattamie	...	30528	Hawkeye Gin	6	1750	7.00	10.50	6	63.00	10.50	2.77
33	S15029400021	2013-09-10 00:00:00	2576	Hy-Vee Wine and Spirits / Storm Lake	1250 N LAKE ST	STORM LAKE	50588	1250 N LAKE ST\nSTORM LAKE 50588\n(42.653594, ...	11	Buena Vista	...	11786	Black Velvet Traveler	12	750	5.23	7.84	12	94.08	9.00	2.38
34	S08748300063	2012-05-11 00:00:00	2578	Hy-Vee / Charles City	901 KELLY ST	CHARLES CITY	50616	901 KELLY ST\nCHARLES CITY 50616\n(43.066994, ...	34	Floyd	...	11774	Black Velvet	24	375	3.07	4.60	24	110.40	9.00	2.38
35	S21231400112	09/17/2014	2624	Hy-Vee #2 / Dubuque	2395 NW ARTERIAL RD	DUBUQUE	52002	2395 NW ARTERIAL RD\nDUBUQUE 52002\n(42.516621...	31	Dubuque	...	77847	Smirnoff Vanilla	12	750	8.25	12.38	12	148.56	9.00	2.38
36	S05233900074	04/25/2012	3417	Big G Food Store	PO BOX 261 310 W DILLON	MARENGO	52301	PO BOX 261 310 W DILLON\nMARENGO 52301\n	48	Iowa	...	31666	Paramount Gin Traveler	12	750	3.39	5.09	2	10.18	1.50	0.40
37	S12909100007	06/18/2013	2528	Hy-Vee Food Store #3 / Des Moines	3221 SE 14TH ST	DES MOINES	50315	3221 SE 14TH ST\nDES MOINES 50315\n(41.553941,...	77	Polk	...	86796	Southern Host Whiskey Liqueur	12	750	5.37	8.05	12	96.60	9.00	2.38
38	S13555400025	07/24/2013	3943	Good and Quick Co	519 LINCOLNWAY	NEVADA	50201	519 LINCOLNWAY\nNEVADA 50201\n(42.022712, -93....	85	Story	...	67267	Yukon Jack Canadian Liqueur	12	1000	11.75	17.62	2	35.24	2.00	0.53
39	S11460200054	2013-04-04 00:00:00	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	...	35917	Five O'clock Vodka	12	1000	4.17	6.25	2	12.50	2.00	0.53
40	S11615400004	04/15/2013	4524	Casey's General Store #1139 / Nora S	14 E CONGRESS	NORA SPRINGS	50458	14 E CONGRESS\nNORA SPRINGS 50458\n(43.142731,...	34	Floyd	...	35918	Five O'clock Vodka	6	1750	7.20	10.79	18	194.22	31.50	8.32
41	S04958800091	2012-09-04 00:00:00	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	...	5289	J & B Rare Scotch	12	1000	14.98	22.47	12	269.64	12.00	3.17
42	S20329100034	07/31/2014	3698	Ingersoll Wine Merchants	1300 50TH ST	WEST DES MOINES	50266	1300 50TH ST\nWEST DES MOINES 50266\n(41.59085...	77	Polk	...	34605	Grey Goose La Poire Vodka	6	750	18.49	27.74	2	55.48	1.50	0.40
43	S09979300008	2013-10-01 00:00:00	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	...	23827	Five Star	12	1000	4.40	6.59	12	79.08	12.00	3.17
44	S11577700018	2013-10-04 00:00:00	3625	Wal-Mart 0892 / Ankeny	1002 SE NATIONAL DR	ANKENY	50021	1002 SE NATIONAL DR\nANKENY 50021\n(41.704864,...	77	Polk	...	43302	Captain Morgan Black Spiced	12	750	11.00	16.50	12	198.00	9.00	2.38
45	S12175200011	05/13/2013	2565	Hy-Vee Food Store / Spencer	819 N GRAND AVE	SPENCER	51301	819 N GRAND AVE\nSPENCER 51301\n(43.145897, -9...	21	Clay	...	36308	Hawkeye Vodka	6	1750	7.13	10.70	12	128.40	21.00	5.55
46	S13095400056	2013-01-07 00:00:00	2515	Hy-Vee Food Store #1 / Mason City	2400 4TH ST SW	MASON CITY	50401	2400 4TH ST SW\nMASON CITY 50401\n(43.148446, ...	17	Cerro Gordo	...	28867	Tanqueray Gin	12	1000	14.99	22.48	12	269.76	12.00	3.17
47	S13871400001	08/13/2013	4775	Casey's General Store #37 / Dakota C	502, MAIN	DAKOTA CITY	50529	502, MAIN\nDAKOTA CITY 50529\n(42.721391, -94....	46	Humboldt	...	43026	Admiral Nelson Spiced Rum	12	750	5.77	8.66	12	103.92	9.00	2.38
48	S24742300137	03/26/2015	5102	Wilkie Liquors	724, 1st ST SE	MOUNT VERNON	52314	724, 1st ST SE\nMOUNT VERNON 52314\n(41.91776,...	57	Linn	...	28890	Tanqueray Rangpur Gin	12	750	12.50	18.75	3	56.25	2.25	0.59
49	S05101300107	04/17/2012	3495	Great Pastimes	228 N MAIN ST	MONTICELLO	52310	228 N MAIN ST\nMONTICELLO 52310\n(42.240132, -...	53	Jones	...	11773	Black Velvet	48	200	1.56	2.34	12	28.08	2.40	0.63
50 rows × 24 columns

598B

sales_data_new.csv

Open

1. Load the data from the above CSV file with the separater '-'.

Solution

df=pd.read_csv('sales_data_new.csv',delimiter='-')
df

# Output


Order_ID	Product	Quantity	Price_per_Unit	Total_Sales	Sale_Date
0	ORD001	Laptop	2	800	1600.00	1/1/2023
1	ORD002	Mobile	[phone]
2	ORD003	Tablet	3	300	900.00	1/3/2023
3	ORD004	Headphones	1	100	100.00	1/4/2023
4	ORD005	Charger	5	20	100.00	1/5/2023
5	ORD006	Laptop	2	800	1600.00	1/6/2023
6	ORD007	Tablet	1	300	300.00	1/7/2023
7	ORD008	Mobile	[phone]/2023
8	ORD009	Headphones	2	100	200.00	1/9/2023
9	ORD010	Charger	4	20	80.00	1/10/2023
10	ORD011	Laptop	1	800	800.00	1/11/2023
11	ORD012	Mobile	[phone]/2023
12	ORD013	Tablet	2	300	600.00	1/13/2023
13	ORD014	Headphones	1	100	100.00	1/14/2023
14	ORD015	Charger	5	20	100.00	1/15/2023

1. Load the data from the html link. 1. Check the number of tables present on that page. 2. Print the sixth table.

Solution

df=pd.read_html('https://en.wikipedia.org/wiki/List_of_Friends_episodes')
len(df)

# Output-- 15

df[5]

# Output

No. overall	No. in season	Title	Directed by	Written by	Original air date	Prod. code	U.S. viewers (millions)	Rating/share (18–49)
0	147.0	1.0	"The One with Monica's Thunder"	Kevin S. Bright	Story by : Wil Calhoun Teleplay by : David Cra...	October 12, 2000	226401.0	25.54[163]	13.7/38[164]
1	148.0	2.0	"The One with Rachel's Book"	Michael Lembeck	Andrew Reich & Ted Cohen	October 12, 2000	226402.0	27.93[163]	15.4/39[164]
2	149.0	3.0	"The One with Phoebe's Cookies"	Gary Halvorson	Sherry Bilsing & Ellen Plummer	October 19, 2000	226405.0	22.72[165]	11.7/35[166]
3	150.0	4.0	"The One with Rachel's Assistant"	David Schwimmer	Brian Boyle	October 26, 2000	226403.0	22.66[167]	11.7/32[168]
4	151.0	5.0	"The One with the Engagement Picture"	Gary Halvorson	Story by : Earl Davis Teleplay by : Patty Lin	November 2, 2000	226404.0	24.43[169]	12.8/34[170]
5	152.0	6.0	"The One with the Nap Partners"	Gary Halvorson	Brian Buckner & Sebastian Jones	November 9, 2000	226406.0	22.01[171]	11.2/30[172]
6	153.0	7.0	"The One with Ross's Library Book"	David Schwimmer	Scott Silveri	November 16, 2000	226410.0	23.73[173]	12.1/32[174]
7	154.0	8.0	"The One Where Chandler Doesn't Like Dogs"	Kevin S. Bright	Patty Lin	November 23, 2000	226407.0	16.57[175]	7.3/22[176]
8	155.0	9.0	"The One with All the Candy"	David Schwimmer	Wil Calhoun	December 7, 2000	226408.0	21.08[177]	11.0/30[178]
9	156.0	10.0	"The One with the Holiday Armadillo"	Gary Halvorson	Gregory S. Malins	December 14, 2000	226409.0	23.26[179]	11.5/32[180]
10	157.0	11.0	"The One with All the Cheesecakes"	Gary Halvorson	Shana Goldberg-Meehan	January 4, 2001	226412.0	24.37[181]	12.4/32[182]
11	158.0	12.0	"The One Where They're Up All Night"	Kevin S. Bright	Zack Rosenblatt	January 11, 2001	226413.0	22.86[183]	11.6/30[184]
12	159.0	13.0	"The One Where Rosita Dies"†	Stephen Prime	Story by : Ellen Plummer & Sherry Bilsing Tele...	February 1, 2001	226415.0	22.24[185]	10.8/27[186]
13	160.0	14.0	"The One Where They All Turn Thirty"†	Ben Weiss	Story by : Vanessa McCarthy Teleplay by : Elle...	February 8, 2001	226411.0	22.40[187]	11.2/27[188]
14	161.0	15.0	"The One with Joey's New Brain"†	Kevin S. Bright	Story by : Sherry Bilsing & Ellen Plummer Tele...	February 15, 2001	226416.0	21.75[189]	10.6/27[190]
15	162.0	16.0	"The One with the Truth About London"†	David Schwimmer	Story by : Brian Buckner & Sebastian Jones Tel...	February 22, 2001	226417.0	21.22[191]	9.9/24[192]
16	163.0	17.0	"The One with the Cheap Wedding Dress"	Kevin S. Bright	Story by : Brian Buckner & Sebastian Jones Tel...	March 15, 2001	226414.0	20.84[193]	10.0/29[194]
17	164.0	18.0	"The One with Joey's Award"	Gary Halvorson	Story by : Sherry Bilsing & Ellen Plummer Tele...	March 29, 2001	226418.0	17.81[195]	8.1/21[196]
18	165.0	19.0	"The One with Ross and Monica's Cousin"	Gary Halvorson	Andrew Reich & Ted Cohen	April 19, 2001	226421.0	16.55[197]	7.7/22[198]
19	166.0	20.0	"The One with Rachel's Big Kiss"	Gary Halvorson	Scott Silveri & Shana Goldberg-Meehan	April 26, 2001	226419.0	16.30[199]	8.1/24[200]
20	167.0	21.0	"The One with the Vows"	Gary Halvorson	Doty Abrams	May 3, 2001	226424.0	15.65[201]	7.6/21[202]
21	168.0	22.0	"The One with Chandler's Dad"	Kevin S. Bright & Gary Halvorson	Story by : Gregory S. Malins Teleplay by : Bri...	May 10, 2001	226420.0	17.23[203]	8.6/27[204]
22	169.0	23.0	"The One with Monica and Chandler's Wedding"	Kevin S. Bright	Gregory S. Malins	May 17, 2001[b]	226422.0	30.05[205]	15.7/43[206]
23	170.0	24.0	"The One with Monica and Chandler's Wedding"	Kevin S. Bright	Marta Kauffman & David Crane	May 17, 2001[b]	226423.0	30.05[205]	15.7/43[206]
24	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN

1. Load the data from tips file.

Solution

df=pd.read_csv('tips.csv')
df

# Output

    total_bill	tip	gender	smoker	day	time	size
0	16.99	1.01	Female	No	Sun	Dinner	2
1	10.34	1.66	Male	No	Sun	Dinner	3
2	21.01	3.50	Male	No	Sun	Dinner	3
3	23.68	3.31	Male	No	Sun	Dinner	2
4	24.59	3.61	Female	No	Sun	Dinner	4
...	...	...	...	...	...	...	...
239	29.03	5.92	Male	No	Sat	Dinner	3
240	27.18	2.00	Female	Yes	Sat	Dinner	2
241	22.67	2.00	Male	Yes	Sat	Dinner	2
242	17.82	1.75	Male	No	Sat	Dinner	2
243	18.78	3.00	Female	No	Thur	Dinner	2
244 rows × 7 columns

Hard Assignments ------------------------------- 30 mins

1. Create a dictionary from user input where the keys are name, age, city and convert that dictionary into a data frame.

Solution

import pandas as pd

# Step 1: Take input from the user
name = input("Enter your name: ")
age = input("Enter your age: ")
city = input("Enter your city: ")

# Step 2: Create a dictionary with the input
data = {
    'Name': [name],
    'Age': [age],
    'City': [city]
}

# Step 3: Convert the dictionary into a DataFrame
df = pd.DataFrame(data)

# Step 4: Display the DataFrame
print(df)

1. Create a dictionary from user input where the keys are product, price, quantity and convert that dictionary into a data frame.

Solution

import pandas as pd

# Step 1: Take input from the user
product = input("Enter the product name: ")
price = input("Enter the product price: ")
quantity = input("Enter the product quantity: ")

# Step 2: Create a dictionary with the input
data = {
    'Product': [product],
    'Price': [price],
    'Quantity': [quantity]
}

# Step 3: Convert the dictionary into a DataFrame
df = pd.DataFrame(data)

# Step 4: Display the DataFrame
print(df)

1. Create a dictionary from user input where the keys are name, age, city, state (3 entries) and convert that dictionary into a data frame.

Solution

import pandas as pd


data = {
    'Name': [],
    'Age': [],
    'City': [],
    'state':[]
}

# Step 2: Loop through each person to get details
for i in range(3):
    name = input(f"Enter name of person {i+1}: ")
    age = input(f"Enter age of person {i+1}: ")
    city = input(f"Enter city of person {i+1}: ")
    state =input(f"Enter state of person {i+1}:")
    data['Name'].append(name)
    data['Age'].append(age)
    data['City'].append(city)
    data['state'].append(state)
    print()

# Step 3: Convert the dictionary into a DataFrame
df = pd.DataFrame(data)

# Step 4: Display the DataFrame
print(df)

1. Create a dictionary from user input where the keys are the employee's ID, name, salary, and department (5 entries) and convert that dictionary into a data frame.

Solution

import pandas as pd


employees_data = {
                'ID': [],
                'Name': [],
                'Salary': [],
                'Department':[]
                }

# Step 2: Loop through each person to get details
for i in range(5):
    id=int(input(f'Enter ID for employee {i+1}:'))
    name=input(f'Enter  name of employee {i+1}:')
    salary=input(f'Enter salary of employee {i+1}:')
    dept=input(f'Enter Department of employee {i+1}:')
    employees_data['ID'].append(id)
    employees_data['Name'].append(name)
    employees_data['Salary'].append(salary)
    employees_data['Department'].append(dept)
    print()

# Step 3: Convert the dictionary into a DataFrame
df = pd.DataFrame(employees_data)

# Step 4: Display the DataFrame
print(df)

1. Create a JSON structure that takes input from user and store product id,name, price, and qty for 3 products and converts it into a data frame.

Solution

import pandas as pd
# List to store product details
products = []

# Loop to take input for 3 products
for i in range(3):
    print(f"Enter details for product {i + 1}:")

    # Take inputs
    product_id = input("Product ID: ")
    product_name = input("Product Name: ")
    product_price = float(input("Product Price: "))
    product_qty = int(input("Product Quantity: "))

    # Create a dictionary for each product
    product = {
        "Product ID": product_id,
        "Product Name": product_name,
        "Product Price": product_price,
        "Product Quantity": product_qty
    }

    # Append the product to the list
    products.append(product)

df=pd.DataFrame(products)
print(df)