<jupyter_start><jupyter_text>The Year column seems to be fine. The first column we need to address is the 'Product line' column<jupyter_code>sales.groupby(['Product','line'])['Year'].count()<jupyter_output><empty_output><jupyter_text>This looks okay. But are there other columns whose values are flowing out into the next column? Let's have a look<jupyter_code>sales.groupby(['Product','line', 'Product.1'])['Year'].count()<jupyter_output><empty_output><jupyter_text>No such issue is visible right now. It seems like the 'Product line' column is easy to handle. We just have to join the 'Product' and 'line' column and we are good to go.<jupyter_code>sales['Product line'] = sales.apply(lambda x: x['Product'] +' '+ x['line'], axis = 1)
sales = sales.drop(['Product', 'line'], axis = 1)
sales.head()<jupyter_output><empty_output><jupyter_text>The next column - 'Product type' seems a bit tougher to handle. Let's experiment a bit before making changes to the actual sales DataFrame Let's create a temporary dataframe to experiment called tmp<jupyter_code>tmp = copy.deepcopy(sales)
tmp.groupby(['Product.1','type', 'Product.2'])['Year'].count()<jupyter_output><empty_output><jupyter_text>We are only interested in the following product types: 'Climbing Accessories',  'Cooking Gear', 'First Aid', 'Golf Accessories', 'Insect Repellents' and 'Sleeping Bags'<jupyter_code>tmp1 = copy.deepcopy(tmp[tmp['Product.1'].isin(['Climbing', 'Cooking', 'First', 'Golf', 'Insect', 'Sleeping'])])
tmp1.head()
tmp1.groupby(['Product.1'])['Year'].count()
tmp1.groupby(['Product.1','type', 'Product.2'])['Year'].count()
tmp1['Product type'] = tmp1['Product.1'] + ' '+ tmp1['type']
tmp1 = tmp1.drop(['Product.1', 'type'], axis = 1)
tmp1.head()<jupyter_output><empty_output><jupyter_text>We move on to analyzing the next column product for the tmp1 dataframe. It seems believable glancing at the first few rows that it is a combination of the Product.2, Order and method columns of the dataframe<jupyter_code>tmp1.groupby(['Product.2', 'Order'])['Year'].count()<jupyter_output><empty_output><jupyter_text>If we look at the hibernator class in Product.2, we realize that some elements of the "Order" column belong to the 'Order method type' column. Let's again further partition tmp1 into categories based on whether just Product.2 and Order columns make up their columns or not.<jupyter_code>tmp2 = copy.deepcopy(tmp1[~tmp1['Order'].isin(['E-mail', 'Fax', 'Mail', 'Sales', 'Special', 'Telephone', 'Web'])])
tmp2.head()
tmp2.groupby(['Product.2','Order', 'method'])['Year'].count()<jupyter_output><empty_output><jupyter_text>Most of the elements of tmp2 can be said to have the product column as the combination of the Product.2 and the Order column.Let's create tmp3 as those which don't have the same distribution.<jupyter_code>tmp3 = copy.deepcopy(tmp2[~tmp2['method'].isin(['E-mail', 'Fax', 'Mail', 'Sales', 'Special', 'Telephone', 'Web'])])
tmp3.head()
tmp3.groupby(['Product.2','Order', 'method', 'type.1'])['Year'].count()
tmp4 = copy.deepcopy(tmp3[~tmp3['type.1'].isin(['E-mail', 'Fax', 'Mail', 'Sales', 'Special', 'Telephone', 'Web'])])
tmp4.head()
tmp4.groupby(['Product.2','Order', 'method', 'type.1', 'Retailer'])['Year'].count()
tmp5 = copy.deepcopy(tmp4[~tmp4['Retailer'].isin(['E-mail', 'Fax', 'Mail', 'Sales', 'Special', 'Telephone', 'Web'])])
tmp5.head()
tmp5.groupby(['Product.2','Order', 'method', 'type.1', 'Retailer', 'country'])['Year'].count()
tmp6 = copy.deepcopy(tmp5[~tmp5['country'].isin(['E-mail', 'Fax', 'Mail', 'Sales', 'Special', 'Telephone', 'Web'])])
tmp6.head()
tmp6.groupby(['Product.2','Order', 'method', 'type.1', 'Retailer', 'country', 'Revenue'])['Year'].count()
tmp6['Product'] = (tmp6['Product.2'] + ' ' + tmp6['Order'] + ' ' + tmp6['method'] + ' ' + tmp6['type.1'] + 
                   ' ' + tmp6['Retailer'] + ' ' + tmp6['country'])
tmp6 = tmp6.drop(['Product.2', 'Order', 'method', 'type.1', 'Retailer', 'country'], axis = 1)
tmp6.head()<jupyter_output><empty_output><jupyter_text>The next column in our list is 'Order method type'<jupyter_code>tmp6.groupby(['Revenue', 'Planned'])['Year'].count()
tmp7 = copy.deepcopy(tmp6[tmp6['Planned']=='visit'])
tmp7.head()
tmp7['Order method type'] = tmp7['Revenue'] + ' ' + tmp7['Planned']
tmp7 = tmp7.drop(['Revenue', 'Planned'], axis = 1)
tmp7.head()<jupyter_output><empty_output><jupyter_text>This is followed by the 'Retailer country' column, let's see how that is distributed<jupyter_code>tmp7.groupby(['revenue', 'Product.3'])['Year'].count()
tmp8 = copy.deepcopy(tmp7[tmp7['Product.3'].isin(['Kingdom', 'States'])])
tmp8.head()
tmp8['Retailer country'] = tmp8['revenue'] + ' ' + tmp8['Product.3']
tmp8 = tmp8.drop(['revenue', 'Product.3'], axis = 1)
tmp8.head()<jupyter_output><empty_output><jupyter_text>The rest of the columns are now a straightforward translation<jupyter_code>tmp8["Revenue"] = tmp8['cost']
tmp8 = tmp8.drop(['cost'], axis = 1)

tmp8["Planned revenue"] = tmp8['Quantity'] 
tmp8 = tmp8.drop(['Quantity'], axis = 1)

tmp8["Product cost"] = tmp8['Unit'] 
tmp8 = tmp8.drop(['Unit'], axis = 1)

tmp8["Quantity"] = tmp8['cost.1'] 
tmp8 = tmp8.drop(['cost.1'], axis = 1)

tmp8["Unit cost"] = tmp8['Unit.1'] 
tmp8 = tmp8.drop(['Unit.1'], axis = 1)

tmp8["Unit price"] = tmp8['price'] 
tmp8 = tmp8.drop(['price'], axis = 1)

tmp8["Gross profit"] = tmp8['Gross']
tmp8 = tmp8.drop(['Gross'], axis = 1)

tmp8["Unit sale price"] = tmp8['profit']
tmp8 = tmp8.drop(['profit', 'Unit.2', 'sale', 'price.1'], axis = 1)

tmp8.head()<jupyter_output><empty_output><jupyter_text>tmp8 is now structured. Let's store this as the first structured subset str1 and proceed with other columns<jupyter_code>str1 = tmp8
temp = copy.deepcopy(tmp7[~tmp7.index.isin(tmp8.index.values)])
temp.head()
temp.groupby(['revenue', 'Product.3'])['Year'].count()
temp['Retailer country'] = temp['revenue']
temp = temp.drop('revenue', axis = 1)
temp.head()<jupyter_output><empty_output><jupyter_text>The remaining analysis is also pretty straightforward<jupyter_code>temp["Revenue"] = temp['Product.3']
temp = temp.drop(['Product.3'], axis = 1)

temp["Planned revenue"] = temp['cost'] 
temp = temp.drop(['cost'], axis = 1)

temp["Product cost"] = temp['Quantity'] 
temp = temp.drop(['Quantity'], axis = 1)

temp["Quantity"] = temp['Unit'] 
temp = temp.drop(['Unit'], axis = 1)

temp["Unit cost"] = temp['cost.1'] 
temp = temp.drop(['cost.1'], axis = 1)

temp["Unit price"] = temp['Unit.1'] 
temp = temp.drop(['Unit.1'], axis = 1)

temp["Gross profit"] = temp['price']
temp = temp.drop(['price'], axis = 1)

temp["Unit sale price"] = temp['Gross']
temp = temp.drop(['Gross', 'profit', 'Unit.2', 'sale', 'price.1'], axis = 1)

temp.head()<jupyter_output><empty_output><jupyter_text>Second structured dataset, so we call this str2<jupyter_code>str2 = temp<jupyter_output><empty_output><jupyter_text>str1 and str2 cover all of tmp7. Let's go back to tmp6<jupyter_code>temp = copy.deepcopy(tmp6[~tmp6.index.isin(tmp7.index.values)])
temp.head()
temp.groupby(['Revenue', 'Planned'])['Year'].count()
temp['Order method type'] = temp['Revenue']
temp = temp.drop('Revenue', axis = 1)
temp.head()
temp.groupby(['Planned', 'revenue'])['Year'].count()
temp1 = copy.deepcopy(temp[temp['revenue'].isin(['Kingdom', 'States'])])
temp1.head()
temp1.groupby(['Planned', 'revenue', 'Product.3'])['Year'].count()
temp1['Retailer country'] = temp1['Planned'] + ' ' + temp1['revenue']
temp1 = temp1.drop(['Planned', 'revenue'], axis = 1)
temp1.head()
temp1["Revenue"] = temp1['Product.3']
temp1 = temp1.drop(['Product.3'], axis = 1)

temp1["Planned revenue"] = temp1['cost'] 
temp1 = temp1.drop(['cost'], axis = 1)

temp1["Product cost"] = temp1['Quantity'] 
temp1 = temp1.drop(['Quantity'], axis = 1)

temp1["Quantity"] = temp1['Unit'] 
temp1 = temp1.drop(['Unit'], axis = 1)

temp1["Unit cost"] = temp1['cost.1'] 
temp1 = temp1.drop(['cost.1'], axis = 1)

temp1["Unit price"] = temp1['Unit.1'] 
temp1 = temp1.drop(['Unit.1'], axis = 1)

temp1["Gross profit"] = temp1['price']
temp1 = temp1.drop(['price'], axis = 1)

temp1["Unit sale price"] = temp1['Gross']
temp1 = temp1.drop(['Gross', 'profit', 'Unit.2', 'sale', 'price.1'], axis = 1)

temp1.head()
str3 = temp1
temp2 = copy.deepcopy(temp[~temp.index.isin(temp1.index.values)])
temp2.head()
temp2.groupby(['Planned', 'revenue'])['Year'].count()
temp2['Retailer country'] = temp2['Planned']
temp2 = temp2.drop('Planned', axis = 1)
temp2.head()
temp2["Revenue"] = temp2['revenue']
temp2 = temp2.drop(['revenue'], axis = 1)

temp2["Planned revenue"] = temp2['Product.3'] 
temp2 = temp2.drop(['Product.3'], axis = 1)

temp2["Product cost"] = temp2['cost'] 
temp2 = temp2.drop(['cost'], axis = 1)

temp2["Unit cost"] = temp2['Unit'] 
temp2 = temp2.drop(['Unit'], axis = 1)

temp2["Unit price"] = temp2['cost.1'] 
temp2 = temp2.drop(['cost.1'], axis = 1)

temp2["Gross profit"] = temp2['Unit.1']
temp2 = temp2.drop(['Unit.1'], axis = 1)

temp2["Unit sale price"] = temp2['price']
temp2 = temp2[['Year', 'Product line', 'Product type', 'Product', 'Order method type', 'Retailer country', 'Revenue', 
              'Planned revenue', 'Product cost', 'Quantity', 'Unit cost', 'Unit price', 'Gross profit', 'Unit sale price']]

temp2.head()
str4 = temp2<jupyter_output><empty_output><jupyter_text>As temp1 and temp2 are fully structured, temp is fully structured. When this temp is fully structured, we have structured values uptil tmp5, Let's move on to values that are in tmp4 but not in tmp5 and structure them<jupyter_code>temp = copy.deepcopy(tmp5[~tmp5.index.isin(tmp6.index.values)])
temp.head()
temp.groupby(['Product.2', 'Order', 'method', 'type.1', 'Retailer', 'country'])['Year'].count()
def check(k):
    if k==0:
        return '-'
    else:
        return k
    
temp['Product'] = temp['Product.2'] + ' ' + temp['Order'] + ' ' + temp['method'].map(check) +' ' + temp['type.1'] + ' ' + temp['Retailer']
temp = temp.drop(['Product.2', 'Order', 'method', 'type.1', 'Retailer'], axis = 1)
temp.head()
temp.groupby(['country', 'Revenue'])['Year'].count()
temp1 = copy.deepcopy(temp[temp['Revenue'] == 'visit'])
temp1.head()
temp1.groupby(['country', 'Revenue', 'Planned'])['Year'].count()
temp1['Order method type'] = temp1['country'] + ' ' + temp['Revenue']
temp1 = temp1.drop(['country', 'Revenue'], axis = 1)
temp1.head()
temp1.groupby(['Planned', 'revenue','Product.3'])['Year'].count()
temp2 = copy.deepcopy(temp1[temp1['revenue'].isin(['Kingdom', 'States'])])
temp2.head()
temp2["Retailer country"] = temp2["Planned"] + ' ' + temp2['revenue']
temp2 = temp2.drop(['Planned', 'revenue'], axis = 1)

temp2["Revenue"] = temp2['Product.3']
temp2 = temp2.drop(['Product.3'], axis = 1)

temp2["Planned revenue"] = temp2['cost'] 
temp2 = temp2.drop(['cost'], axis = 1)

temp2["Product cost"] = temp2['Quantity'] 
temp2 = temp2.drop(['Quantity'], axis = 1)

temp2["Quantity"] = temp2['Unit'] 
temp2 = temp2.drop(['Unit'], axis = 1)

temp2["Unit cost"] = temp2['cost.1'] 
temp2 = temp2.drop(['cost.1'], axis = 1)

temp2["Unit price"] = temp2['Unit.1'] 
temp2 = temp2.drop(['Unit.1'], axis = 1)

temp2["Gross profit"] = temp2['price']
temp2 = temp2.drop(['price'], axis = 1)

temp2["Unit sale price"] = temp2['Gross']
temp2 = temp2.drop(['Gross', 'profit', 'Unit.2', 'sale', 'price.1'], axis = 1)

temp2.head()
str5 = temp2
temp2 = copy.deepcopy(temp1[~temp1['revenue'].isin(['Kingdom', 'States'])])
temp2.head()
temp2.groupby(['Planned', 'revenue'])['Year'].count()
temp2["Retailer country"] = temp2['Planned']
temp2 = temp2.drop(['Planned'], axis = 1)

temp2["Revenue"] = temp2['revenue']
temp2 = temp2.drop(['revenue'], axis = 1)

temp2["Planned revenue"] = temp2['Product.3'] 
temp2 = temp2.drop(['Product.3'], axis = 1)

temp2["Product cost"] = temp2['cost'] 
temp2 = temp2.drop(['cost'], axis = 1)

temp2["Quantity"] = temp2['Quantity'] 
temp2 = temp2.drop(['Quantity'], axis = 1)

temp2["Unit cost"] = temp2['Unit'] 
temp2 = temp2.drop(['Unit'], axis = 1)

temp2["Unit price"] = temp2['cost.1'] 
temp2 = temp2.drop(['cost.1'], axis = 1)

temp2["Gross profit"] = temp2['Unit.1']
temp2 = temp2.drop(['Unit.1'], axis = 1)

temp2["Unit sale price"] = temp2['price']
temp2 = temp2.drop(['price', 'Gross', 'profit', 'Unit.2', 'sale', 'price.1'], axis = 1)

temp2.head()
str6 = temp2
temp1 = copy.deepcopy(temp[temp['Revenue'] != 'visit'])
temp1.head()
temp1.groupby(['country', 'Revenue'])['Year'].count()
temp1['Order method type'] = temp1['country']
temp1 = temp1.drop('country', axis = 1)
temp1.head()
temp2 = copy.deepcopy(temp1[temp1['Planned'].isin(['Kingdom', 'States'])])
temp2.groupby(['Revenue','Planned','revenue'])['Year'].count()
temp2["Retailer country"] = temp2["Revenue"] + ' ' + temp2['Planned']
temp2 = temp2.drop(['Revenue', 'Planned'], axis = 1)

temp2["Revenue"] = temp2['revenue']
temp2 = temp2.drop(['revenue'], axis = 1)

temp2["Planned revenue"] = temp2['Product.3'] 
temp2 = temp2.drop(['Product.3'], axis = 1)

temp2["Product cost"] = temp2['cost'] 
temp2 = temp2.drop(['cost'], axis = 1)

temp2["Quantity"] = temp2['Quantity'] 
temp2 = temp2.drop(['Quantity'], axis = 1)

temp2["Unit cost"] = temp2['Unit'] 
temp2 = temp2.drop(['Unit'], axis = 1)

temp2["Unit price"] = temp2['cost.1'] 
temp2 = temp2.drop(['cost.1'], axis = 1)

temp2["Gross profit"] = temp2['Unit.1']
temp2 = temp2.drop(['Unit.1'], axis = 1)

temp2["Unit sale price"] = temp2['price']
temp2 = temp2.drop(['price', 'Gross', 'profit', 'Unit.2', 'sale', 'price.1'], axis = 1)

temp2.head()
str7 = temp2
temp2 = copy.deepcopy(temp1[~temp1['Planned'].isin(['Kingdom', 'States'])])
temp2.groupby(['Revenue','Planned'])['Year'].count()
temp2["Retailer country"] = temp2["Revenue"]
temp2 = temp2.drop(['Revenue'], axis = 1)

temp2["Revenue"] = temp2['Planned']
temp2 = temp2.drop(['Planned'], axis = 1)

temp2["Planned revenue"] = temp2['revenue'] 
temp2 = temp2.drop(['revenue'], axis = 1)

temp2["Product cost"] = temp2['Product.3'] 
temp2 = temp2.drop(['Product.3'], axis = 1)

temp2["Quantity"] = temp2['cost'] 
temp2 = temp2.drop(['cost'], axis = 1)

temp2["Unit cost"] = temp2['Quantity'] 
temp2 = temp2.drop(['Quantity'], axis = 1)

temp2["Unit price"] = temp2['Unit'] 
temp2 = temp2.drop(['Unit'], axis = 1)

temp2["Gross profit"] = temp2['cost.1']
temp2 = temp2.drop(['cost.1'], axis = 1)

temp2["Unit sale price"] = temp2['Unit.1']
temp2 = temp2.drop(['Unit.1','price', 'Gross', 'profit', 'Unit.2', 'sale', 'price.1'], axis = 1)

temp2.head()
str8 = temp2<jupyter_output><empty_output><jupyter_text>With this value of temp2 we have structured all the possible tmp5 values for temp, we move on to tmp4 now<jupyter_code>temp = copy.deepcopy(tmp4[~tmp4.index.isin(tmp5.index.values)])
temp.head()
temp.groupby(['Product.2', 'Order', 'method', 'type.1', 'Retailer'])['Year'].count()
temp['Product'] = temp['Product.2'] + ' ' + temp['Order'] + ' ' + temp['method'] +' ' + temp['type.1']
temp = temp.drop(['Product.2', 'Order', 'method', 'type.1'], axis = 1)
temp.head()
temp.groupby(['Retailer', 'country'])['Year'].count()
temp1 = copy.deepcopy(temp[temp['country'] == 'visit'])
temp1.head()
temp1.groupby(['Retailer', 'country', 'Revenue'])['Year'].count()
temp1['Order method type'] = temp1['Retailer'] + ' ' + temp['country']
temp1 = temp1.drop(['Retailer', 'country'], axis = 1)
temp1.head()
temp1.groupby(['Revenue', 'Planned', 'revenue'])['Year'].count()
temp2 = copy.deepcopy(temp1[temp1['Planned'].isin(['Kingdom', 'States'])])
temp2.head()
temp2["Retailer country"] = temp2["Revenue"] + ' ' + temp2['Planned']
temp2 = temp2.drop(['Revenue', 'Planned'], axis = 1)

temp2["Revenue"] = temp2['revenue']
temp2 = temp2.drop(['revenue'], axis = 1)

temp2["Planned revenue"] = temp2['Product.3'] 
temp2 = temp2.drop(['Product.3'], axis = 1)

temp2["Product cost"] = temp2['cost'] 
temp2 = temp2.drop(['cost'], axis = 1)

temp2["Quantity"] = temp2['Quantity'] 
temp2 = temp2.drop(['Quantity'], axis = 1)

temp2["Unit cost"] = temp2['Unit'] 
temp2 = temp2.drop(['Unit'], axis = 1)

temp2["Unit price"] = temp2['cost.1'] 
temp2 = temp2.drop(['cost.1'], axis = 1)

temp2["Gross profit"] = temp2['Unit.1']
temp2 = temp2.drop(['Unit.1'], axis = 1)

temp2["Unit sale price"] = temp2['price']
temp2 = temp2.drop(['price', 'Gross', 'profit', 'Unit.2', 'sale', 'price.1'], axis = 1)

temp2.head()
str9 = temp2
temp3 = copy.deepcopy(temp1[~temp1.index.isin(temp2.index.values)])
temp3.head()
temp3.groupby(['Revenue', 'Planned'])['Year'].count()
temp3['Retailer country'] = temp3['Revenue']
temp3 = temp3.drop('Revenue', axis = 1)

temp3["Revenue"] = temp3['Planned']
temp3 = temp3.drop(['Planned'], axis = 1)

temp3["Planned revenue"] = temp3['revenue'] 
temp3 = temp3.drop(['revenue'], axis = 1)

temp3["Product cost"] = temp3['Product.3'] 
temp3 = temp3.drop(['Product.3'], axis = 1)

temp3["Unit cost"] = temp3['Quantity'] 
temp3 = temp3.drop(['Quantity'], axis = 1)

temp3["Quantity"] = temp3['cost'] 
temp3 = temp3.drop(['cost'], axis = 1)

temp3["Unit price"] = temp3['Unit'] 
temp3 = temp3.drop(['Unit'], axis = 1)

temp3["Gross profit"] = temp3['cost.1']
temp3 = temp3.drop(['cost.1'], axis = 1)

temp3["Unit sale price"] = temp3['Unit.1']
temp3 = temp3[['Year', 'Product line', 'Product type', 'Product', 'Order method type', 'Retailer country', 'Revenue', 
              'Planned revenue', 'Product cost', 'Quantity', 'Unit cost', 'Unit price', 'Gross profit', 'Unit sale price']]

temp3.head()
str10 = temp3
temp2 = copy.deepcopy(temp[~temp.index.isin(temp1.index.values)])
temp2
temp2.groupby(['Retailer', 'country'])['Year'].count()
temp2['Order method type'] = temp2['Retailer']
temp2 = temp2.drop('Retailer', axis = 1)
temp2.head()
temp2.groupby(['country', 'Revenue'])['Year'].count()
temp3 = copy.deepcopy(temp2[temp2['Revenue'].isin(['Kingdom', 'States'])])
temp3.head()
temp3['Retailer country'] = temp3['country'] + ' ' + temp3['Revenue']
temp3 = temp3.drop('Revenue', axis = 1)

temp3["Revenue"] = temp3['Planned']
temp3 = temp3.drop(['Planned'], axis = 1)

temp3["Planned revenue"] = temp3['revenue'] 
temp3 = temp3.drop(['revenue'], axis = 1)

temp3["Product cost"] = temp3['Product.3'] 
temp3 = temp3.drop(['Product.3'], axis = 1)

temp3["Unit cost"] = temp3['Quantity'] 
temp3 = temp3.drop(['Quantity'], axis = 1)

temp3["Quantity"] = temp3['cost'] 
temp3 = temp3.drop(['cost'], axis = 1)

temp3["Unit price"] = temp3['Unit'] 
temp3 = temp3.drop(['Unit'], axis = 1)

temp3["Gross profit"] = temp3['cost.1']
temp3 = temp3.drop(['cost.1'], axis = 1)

temp3["Unit sale price"] = temp3['Unit.1']
temp3 = temp3[['Year', 'Product line', 'Product type', 'Product', 'Order method type', 'Retailer country', 'Revenue', 
              'Planned revenue', 'Product cost', 'Quantity', 'Unit cost', 'Unit price', 'Gross profit', 'Unit sale price']]

temp3.head()
str11 = temp3
temp3 = copy.deepcopy(temp2[~temp2['Revenue'].isin(['Kingdom', 'States'])])
temp3.head()
temp3['Retailer country'] = temp3['country']
temp3 = temp3.drop('country', axis = 1)

temp3["Planned revenue"] = temp3['Planned'] 
temp3 = temp3.drop(['Planned'], axis = 1)

temp3["Product cost"] = temp3['revenue'] 
temp3 = temp3.drop(['revenue'], axis = 1)

temp3["Unit cost"] = temp3['cost'] 
temp3 = temp3.drop(['cost'], axis = 1)

temp3["Unit price"] = temp3['Quantity'] 
temp3 = temp3.drop(['Quantity'], axis = 1)

temp3["Quantity"] = temp3['Product.3'] 
temp3 = temp3.drop(['Product.3'], axis = 1)

temp3["Gross profit"] = temp3['Unit']
temp3 = temp3.drop(['Unit'], axis = 1)

temp3["Unit sale price"] = temp3['cost.1']
temp3 = temp3[['Year', 'Product line', 'Product type', 'Product', 'Order method type', 'Retailer country', 'Revenue', 
              'Planned revenue', 'Product cost', 'Quantity', 'Unit cost', 'Unit price', 'Gross profit', 'Unit sale price']]

temp3.head()
str12 = temp3<jupyter_output><empty_output><jupyter_text>Now temp has fully been structured, so we can go back to structuring tmp3<jupyter_code>temp = copy.deepcopy(tmp3[~tmp3.index.isin(tmp4.index.values)])
temp.head()
temp.groupby(['Product.2', 'Order', 'method', 'type.1'])['Year'].count()
temp['Product'] = temp['Product.2'] + ' ' + temp['Order'] + ' ' + temp['method']
temp = temp.drop(['Product.2', 'Order', 'method'], axis =1)
temp.head()
temp1 = copy.deepcopy(temp[temp['Retailer']=='visit'])
temp1.head()
temp1['Order method type'] = temp1['type.1'] + ' ' + temp1['Retailer']
temp1 = temp1.drop(['type.1', 'Retailer'], axis = 1)
temp1.head()
temp2 = copy.deepcopy(temp1[temp1['Revenue'].isin(['Kingdom', 'States'])])
temp2.head()
temp2['Retailer country'] = temp2['country'] + ' ' + temp2['Revenue']
temp2 = temp2.drop(['country', 'Revenue'], axis = 1)

temp2['Revenue'] = temp2['Planned'] 
temp2 = temp2.drop(['Planned'], axis = 1)

temp2["Planned revenue"] = temp2['revenue'] 
temp2 = temp2.drop(['revenue'], axis = 1)

temp2["Product cost"] = temp2['Product.3'] 
temp2 = temp2.drop(['Product.3'], axis = 1)

temp2["Unit cost"] = temp2['Quantity'] 
temp2 = temp2.drop(['Quantity'], axis = 1)

temp2["Unit price"] = temp2['Unit'] 
temp2 = temp2.drop(['Unit'], axis = 1)

temp2["Quantity"]  = temp2['cost']
temp2 = temp2.drop(['cost'], axis = 1)

temp2["Gross profit"] = temp2['cost.1']
temp2["Unit sale price"] = temp2['Unit.1']
temp2 = temp2[['Year', 'Product line', 'Product type', 'Product', 'Order method type', 'Retailer country', 'Revenue', 
              'Planned revenue', 'Product cost', 'Quantity', 'Unit cost', 'Unit price', 'Gross profit', 'Unit sale price']]

temp2.head()
str13 = temp2
temp2 = copy.deepcopy(temp1[~temp1['Revenue'].isin(['Kingdom', 'States'])])
temp2.head()
temp2['Retailer country'] = temp2['country']
temp2 = temp2.drop(['country'], axis = 1)

temp2["Planned revenue"] = temp2['Planned'] 
temp2 = temp2.drop(['Planned'], axis = 1)

temp2["Product cost"] = temp2['revenue'] 
temp2 = temp2.drop(['revenue'], axis = 1)

temp2["Unit cost"] = temp2['cost'] 
temp2 = temp2.drop(['cost'], axis = 1)

temp2["Unit price"] = temp2['Quantity'] 
temp2 = temp2.drop(['Quantity'], axis = 1)

temp2["Quantity"]  = temp2['Product.3']
temp2 = temp2.drop(['Product.3'], axis = 1)

temp2["Gross profit"] = temp2['Unit']
temp2["Unit sale price"] = temp2['cost.1']
temp2 = temp2[['Year', 'Product line', 'Product type', 'Product', 'Order method type', 'Retailer country', 'Revenue', 
              'Planned revenue', 'Product cost', 'Quantity', 'Unit cost', 'Unit price', 'Gross profit', 'Unit sale price']]

temp2.head()
str14 = temp2
temp1 = copy.deepcopy(temp[temp['Retailer']!='visit'])
temp1.head()
temp1.groupby(['type.1', 'Retailer'])['Year'].count()
temp1['Order method type'] = temp1['type.1']
temp1 = temp1.drop('type.1', axis = 1)
temp1.head()
temp2 = copy.deepcopy(temp1[temp1['country'].isin(['Kingdom', 'States'])])
temp2.head()
temp2['Retailer country'] = temp2['Retailer'] + ' ' + temp2['country']
temp2 = temp2.drop(['Retailer','country'], axis = 1)

temp2["Planned revenue"] = temp2['Planned'] 
temp2 = temp2.drop(['Planned'], axis = 1)

temp2["Product cost"] = temp2['revenue'] 
temp2 = temp2.drop(['revenue'], axis = 1)

temp2["Unit cost"] = temp2['cost'] 
temp2 = temp2.drop(['cost'], axis = 1)

temp2["Unit price"] = temp2['Quantity'] 
temp2 = temp2.drop(['Quantity'], axis = 1)

temp2["Quantity"]  = temp2['Product.3']
temp2 = temp2.drop(['Product.3'], axis = 1)

temp2["Gross profit"] = temp2['Unit']
temp2["Unit sale price"] = temp2['cost.1']
temp2 = temp2[['Year', 'Product line', 'Product type', 'Product', 'Order method type', 'Retailer country', 'Revenue', 
              'Planned revenue', 'Product cost', 'Quantity', 'Unit cost', 'Unit price', 'Gross profit', 'Unit sale price']]

temp2.head()
str15 = temp2
temp2 = copy.deepcopy(temp1[~temp1['country'].isin(['Kingdom', 'States'])])
temp2.head()
temp2['Retailer country'] = temp2['Retailer']
temp2 = temp2.drop(['Retailer'], axis = 1)

temp2["Planned revenue"] = temp2['country'] 
temp2 = temp2.drop(['country'], axis = 1)

temp2["Product cost"] = temp2['Planned'] 
temp2 = temp2.drop(['Planned'], axis = 1)

temp2["Unit cost"] = temp2['Product.3'] 
temp2 = temp2.drop(['Product.3'], axis = 1)

temp2["Unit price"] = temp2['cost'] 
temp2 = temp2.drop(['cost'], axis = 1)

temp2["Gross profit"] = temp2['Quantity']
temp2 = temp2.drop(['Quantity'], axis = 1)

temp2["Quantity"]  = temp2['revenue']
temp2 = temp2.drop(['revenue'], axis = 1)

temp2["Unit sale price"] = temp2['Unit']
temp2 = temp2[['Year', 'Product line', 'Product type', 'Product', 'Order method type', 'Retailer country', 'Revenue', 
              'Planned revenue', 'Product cost', 'Quantity', 'Unit cost', 'Unit price', 'Gross profit', 'Unit sale price']]

temp2.head()
str16 = temp2
temp = copy.deepcopy(tmp2[~tmp2.index.isin(tmp3.index.values)])
temp.head()
temp.groupby(['Product.2', 'Order', 'method'])['Year'].count()
temp['Product'] = temp['Product.2'] + ' ' + temp['Order']
temp = temp.drop(['Product.2','Order'], axis =1)
temp.head()
temp1 = copy.deepcopy(temp[temp['type.1'] == 'visit'])
temp1.head()
temp1['Order method type'] = temp1['method'] + ' ' + temp1['type.1']
temp1 = temp1.drop(['method', 'type.1'], axis = 1)
temp1.head()
temp2 = copy.deepcopy(temp1[temp1['country'].isin(['Kingdom', 'States'])])
temp2.head()
temp2['Retailer country'] = temp2['Retailer'] + ' ' + temp2['country']
temp2 = temp2.drop(['Retailer','country'], axis = 1)

temp2["Planned revenue"] = temp2['Planned'] 
temp2 = temp2.drop(['Planned'], axis = 1)

temp2["Product cost"] = temp2['revenue'] 
temp2 = temp2.drop(['revenue'], axis = 1)

temp2["Unit cost"] = temp2['cost'] 
temp2 = temp2.drop(['cost'], axis = 1)

temp2["Unit price"] = temp2['Quantity'] 
temp2 = temp2.drop(['Quantity'], axis = 1)

temp2["Quantity"]  = temp2['Product.3']
temp2 = temp2.drop(['Product.3'], axis = 1)

temp2["Gross profit"] = temp2['Unit']
temp2["Unit sale price"] = temp2['cost.1']
temp2 = temp2[['Year', 'Product line', 'Product type', 'Product', 'Order method type', 'Retailer country', 'Revenue', 
              'Planned revenue', 'Product cost', 'Quantity', 'Unit cost', 'Unit price', 'Gross profit', 'Unit sale price']]

temp2.head()
str17 = temp2
temp2 = copy.deepcopy(temp1[~temp1['country'].isin(['Kingdom', 'States'])])
temp2.head()
temp2['Retailer country'] = temp2['Retailer']
temp2 = temp2.drop(['Retailer'], axis = 1)

temp2["Planned revenue"] = temp2['country'] 
temp2 = temp2.drop(['country'], axis = 1)

temp2["Product cost"] = temp2['Planned'] 
temp2 = temp2.drop(['Planned'], axis = 1)

temp2["Unit cost"] = temp2['Product.3'] 
temp2 = temp2.drop(['Product.3'], axis = 1)

temp2["Unit price"] = temp2['cost'] 
temp2 = temp2.drop(['cost'], axis = 1)

temp2["Gross profit"] = temp2['Quantity']
temp2 = temp2.drop(['Quantity'], axis = 1)

temp2["Quantity"]  = temp2['revenue']
temp2 = temp2.drop(['revenue'], axis = 1)

temp2["Unit sale price"] = temp2['Unit']
temp2 = temp2[['Year', 'Product line', 'Product type', 'Product', 'Order method type', 'Retailer country', 'Revenue', 
              'Planned revenue', 'Product cost', 'Quantity', 'Unit cost', 'Unit price', 'Gross profit', 'Unit sale price']]

temp2.head()
str18 = temp2
temp1 = copy.deepcopy(temp[temp['type.1'] != 'visit'])
temp1.head()
temp1.groupby(["method", "type.1"])['Year'].count()
temp1['Order method type'] = temp1['method']
temp1 = temp1.drop('method', axis = 1)
temp1.head()
temp2 = copy.deepcopy(temp1[temp1['Retailer'].isin(['Kingdom', 'States'])])
temp2.head()
temp2['Retailer country'] = temp2['type.1'] + ' ' + temp2['Retailer']
temp2 = temp2.drop(['type.1', 'Retailer'], axis = 1)

temp2["Planned revenue"] = temp2['country'] 
temp2 = temp2.drop(['country'], axis = 1)

temp2["Product cost"] = temp2['Planned'] 
temp2 = temp2.drop(['Planned'], axis = 1)

temp2["Unit cost"] = temp2['Product.3'] 
temp2 = temp2.drop(['Product.3'], axis = 1)

temp2["Unit price"] = temp2['cost'] 
temp2 = temp2.drop(['cost'], axis = 1)

temp2["Gross profit"] = temp2['Quantity']
temp2 = temp2.drop(['Quantity'], axis = 1)

temp2["Quantity"]  = temp2['revenue']
temp2 = temp2.drop(['revenue'], axis = 1)

temp2["Unit sale price"] = temp2['Unit']
temp2 = temp2[['Year', 'Product line', 'Product type', 'Product', 'Order method type', 'Retailer country', 'Revenue', 
              'Planned revenue', 'Product cost', 'Quantity', 'Unit cost', 'Unit price', 'Gross profit', 'Unit sale price']]

temp2.head()
str19 = temp2
temp2 = copy.deepcopy(temp1[~temp1['Retailer'].isin(['Kingdom', 'States'])])
temp2.head()
temp2['Retailer country'] = temp2['type.1']
temp2 = temp2.drop(['type.1'], axis = 1)

temp2["Planned revenue"] = temp2['country'] 
temp2 = temp2.drop(['country'], axis = 1)

temp2["Product cost"] = temp2['Revenue'] 
temp2 = temp2.drop(['Revenue'], axis = 1)

temp2['Revenue'] = temp2['Retailer']
temp2 = temp2.drop('Retailer', axis = 1)

temp2["Unit cost"] = temp2['revenue'] 
temp2 = temp2.drop(['revenue'], axis = 1)

temp2["Unit price"] = temp2['Product.3'] 
temp2 = temp2.drop(['Product.3'], axis = 1)

temp2["Gross profit"] = temp2['cost']
temp2 = temp2.drop(['cost'], axis = 1)

temp2["Unit sale price"] = temp2['Quantity']
temp2 = temp2.drop(['Quantity'], axis = 1)

temp2["Quantity"]  = temp2['Planned']
temp2 = temp2.drop(['Planned'], axis = 1)

temp2 = temp2[['Year', 'Product line', 'Product type', 'Product', 'Order method type', 'Retailer country', 'Revenue', 
              'Planned revenue', 'Product cost', 'Quantity', 'Unit cost', 'Unit price', 'Gross profit', 'Unit sale price']]

temp2.head()
str20 = temp2
temp = copy.deepcopy(tmp1[~tmp1.index.isin(tmp2.index.values)])
temp.head()
temp.groupby(['Product.2', 'Order'])['Year'].count()
temp['Product'] = temp['Product.2']
temp = temp.drop('Product.2', axis =1)
temp.head()
temp.groupby(['Order', 'method', 'type.1'])['Year'].count()
temp['Order method type'] = temp['Order']
temp = temp.drop(['Order'], axis = 1)
temp.head()
temp1 = copy.deepcopy(temp[temp['type.1'].isin(['Kingdom', 'States'])])
temp1.groupby(['method', 'type.1'])['Year'].count()
temp1['Retailer country'] = temp1['method'] + ' ' + temp1['type.1']
temp1 = temp1.drop(['method','type.1'], axis = 1)

temp1["Planned revenue"] = temp1['country'] 
temp1 = temp1.drop(['country'], axis = 1)

temp1["Product cost"] = temp1['Revenue'] 
temp1 = temp1.drop(['Revenue'], axis = 1)

temp1['Revenue'] = temp1['Retailer']
temp1 = temp1.drop('Retailer', axis = 1)

temp1["Unit cost"] = temp1['revenue'] 
temp1 = temp1.drop(['revenue'], axis = 1)

temp1["Unit price"] = temp1['Product.3'] 
temp1 = temp1.drop(['Product.3'], axis = 1)

temp1["Gross profit"] = temp1['cost']
temp1 = temp1.drop(['cost'], axis = 1)

temp1["Unit sale price"] = temp1['Quantity']
temp1 = temp1.drop(['Quantity'], axis = 1)

temp1["Quantity"]  = temp1['Planned']
temp1 = temp1.drop(['Planned'], axis = 1)

temp1 = temp1[['Year', 'Product line', 'Product type', 'Product', 'Order method type', 'Retailer country', 'Revenue', 
              'Planned revenue', 'Product cost', 'Quantity', 'Unit cost', 'Unit price', 'Gross profit', 'Unit sale price']]

temp1.head()
str21 = temp1
temp1 = copy.deepcopy(temp[~temp['type.1'].isin(['Kingdom', 'States'])])
temp1.head()
temp1.groupby(['method', 'type.1'])['Year'].count()
temp1['Retailer country'] = temp1['method']
temp1 = temp1.drop(['method'], axis = 1)

temp1["Planned revenue"] = temp1['Retailer'] 
temp1 = temp1.drop(['Retailer'], axis = 1)

temp1["Product cost"] = temp1['country'] 
temp1 = temp1.drop(['country'], axis = 1)

temp1["Unit cost"] = temp1['Planned'] 
temp1 = temp1.drop(['Planned'], axis = 1)

temp1["Unit price"] = temp1['revenue'] 
temp1 = temp1.drop(['revenue'], axis = 1)

temp1["Gross profit"] = temp1['Product.3']
temp1 = temp1.drop(['Product.3'], axis = 1)

temp1["Unit sale price"] = temp1['cost']
temp1 = temp1.drop(['cost'], axis = 1)

temp1["Quantity"]  = temp1['Revenue']
temp1 = temp1.drop(['Revenue'], axis = 1)

temp1['Revenue'] = temp1['type.1']
temp1 = temp1.drop('type.1', axis = 1)

temp1 = temp1[['Year', 'Product line', 'Product type', 'Product', 'Order method type', 'Retailer country', 'Revenue', 
              'Planned revenue', 'Product cost', 'Quantity', 'Unit cost', 'Unit price', 'Gross profit', 'Unit sale price']]

temp1.head()
str22 = temp1
df = pd.concat([str1, str2, str3, str4, str5, str6, str7, str8, str9, str10, str11, str12, str13, str14, 
               str15, str16, str17, str18, str19, str20, str21], sort = True)[['Year', 'Product line',
               'Product type', 'Product', 'Order method type', 'Retailer country', 'Revenue', 'Planned revenue',
               'Product cost', 'Quantity', 'Unit cost', 'Unit price', 'Gross profit', 'Unit sale price']]

df.to_csv("../Lesson02/sales.csv", index = False)<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.22508896797153025, 'fraction_numerical': 0.045404344091299544, 'mean_word_length': 5.829457364341085, 'pattern_counts': {'":': 0, '<': 73, '<?xml version=': 0, '>': 73, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 128, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '18', 'dir': 'jupyter-structured-clean-dedup', 'id': '76231', 'license': 'MIT', 'n_tokens_mistral': 12255, 'n_tokens_neox': 11790, 'n_words': 2674, 'path': 'Lesson01/Activity 1.ipynb', 'repo_name': 'dwolfe1966/data-science-for-marketing-analytics', 'size': '32596'}