If possible, convert the sqlite3 query below into a python script that does the exact same thing (can use any python libraries). The Python script would be referencing a Pandas Data Frame with the same data and structure as the database the sql refers to. The original assignment question read as follows: "Report the three (3) most frequently purchased product categories by the gender of "adult 1" using only the data for the active customers. For each category, report the total spend in dollars, the total number of products ('items')purchased. Finally, report the number of adults in each gender category."

Here is the SQLITE3 query that should be converted into a python script.

############################################ # # # START CODE # # # ############################################

cur.execute("""select a.adult1_g as Gender, a.deptdescr as category, frequency, TotalAmount, quantity, individual from (select c.adult1_g, i.deptdescr, sum(totamt) as TotalAmount from customer c, item i where c.acctno = i.acctno group by c.adult1_g, i.deptdescr) a, (select c.adult1_g, i.deptdescr, count(*) as frequency from customer c, item i where c.acctno = i.acctno group by c.adult1_g, i.deptdescr) b, (select c.adult1_g, i.deptdescr, sum(qty) as quantity from customer c, item i where c.acctno = i.acctno group by c.adult1_g, i.deptdescr) c, (select c.adult1_g, i.deptdescr, count(distinct(c.acctno)) as individual from customer c, item i where c.acctno = i.acctno group by c.adult1_g, i.deptdescr) d where a.adult1_g = b.adult1_g and a.deptdescr = b.deptdescr and b.adult1_g = c.adult1_g and c.adult1_g = d.adult1_g and b.deptdescr= c.deptdescr and c.deptdescr = d.deptdescr order by frequency desc, TotalAmount desc, a.adult1_g""") #cur.fetchall() will fetche the rows from the cursor df_FrequentBuyer = pd.DataFrame(cur.fetchall()) #get the column names from cursor using cur.description and add to the dataframe. field_names = [i[0] for i in cur.description] df_FrequentBuyer.columns = field_names df_FrequentBuyer.iloc[:6]

############################################ # # # END CODE # # # ############################################

SCREENSHOT

#part5 #write a query to get the gender grouped by category and sum the quantity cur.execute(" "select a.adult1_g as Gender, a.deptdescr as category, frequency, TotalAmount, quantity, individual from (select c.adult1_g, i.deptdescr, sum(totamt) as TotalAmount from customer c, item i where c.acctn= i.acctno group by c.adult1-g, i.deptdescr) a, (select c.adult1_g, i.deptdescr, count(*) as frequency from customer c, item i where c.acctn= i.acctno group by c.adult1-g, i.deptdescr) b, (select c.adult1_g, i.deptdescr, sum(qty) as quantity from customer c, item i where c.acctno -i.acctno group by c.adult1_g, i.deptdescr) c, (select c.adulti_g, i.deptdescr, count (distinct(c.acctno)) as individual from customer c, item 1 where c.acctno 1.acctno group by c.adult1 g, i.deptdescr) d where a.adult1g b.adult1-g and a.deptdescr = b.deptdescr and b.adult1-g = c.adult1-g and c .adult1-g d.adult1-g and b.deptdescr- c.deptdescr and c.deptdescr d.deptdescr order by frequency desc, TotalAmount desc, a.adult1_g""") #cur , fetchaLL() will fetche the rows from the cursor df_FrequentBuyer = pd . DataFrame(cur. fetcha11()) #get the column names from cursor using cur.description and add to the dataframe field names [iel for i in cur.description] df.FrequentBuyer.columns = field-name s df_FrequentBuyer.iloc[:6] Gender category frequency TotalAmount quantity individual 760369.65 18255 5828 956301.03 14622 6207 800757.84 13974 4696 2171664.39 70883853 745500.27 6104 2920 150226.02 3544 1303 0 F Mobile Electronic Accessories 17164 Small Appliances Mobile Electronics Home Audio Portable ElectronicS Mobile Electronic Accessories 3379 14106 12443 6336 5155 2 F 4 F 5 M #part5 #write a query to get the gender grouped by category and sum the quantity cur.execute(" "select a.adult1_g as Gender, a.deptdescr as category, frequency, TotalAmount, quantity, individual from (select c.adult1_g, i.deptdescr, sum(totamt) as TotalAmount from customer c, item i where c.acctn= i.acctno group by c.adult1-g, i.deptdescr) a, (select c.adult1_g, i.deptdescr, count(*) as frequency from customer c, item i where c.acctn= i.acctno group by c.adult1-g, i.deptdescr) b, (select c.adult1_g, i.deptdescr, sum(qty) as quantity from customer c, item i where c.acctno -i.acctno group by c.adult1_g, i.deptdescr) c, (select c.adulti_g, i.deptdescr, count (distinct(c.acctno)) as individual from customer c, item 1 where c.acctno 1.acctno group by c.adult1 g, i.deptdescr) d where a.adult1g b.adult1-g and a.deptdescr = b.deptdescr and b.adult1-g = c.adult1-g and c .adult1-g d.adult1-g and b.deptdescr- c.deptdescr and c.deptdescr d.deptdescr order by frequency desc, TotalAmount desc, a.adult1_g""") #cur , fetchaLL() will fetche the rows from the cursor df_FrequentBuyer = pd . DataFrame(cur. fetcha11()) #get the column names from cursor using cur.description and add to the dataframe field names [iel for i in cur.description] df.FrequentBuyer.columns = field-name s df_FrequentBuyer.iloc[:6] Gender category frequency TotalAmount quantity individual 760369.65 18255 5828 956301.03 14622 6207 800757.84 13974 4696 2171664.39 70883853 745500.27 6104 2920 150226.02 3544 1303 0 F Mobile Electronic Accessories 17164 Small Appliances Mobile Electronics Home Audio Portable ElectronicS Mobile Electronic Accessories 3379 14106 12443 6336 5155 2 F 4 F 5 M