import pandas as pd

data_dir = "/Users/seddont/Dropbox/Tom/MIDS/W209_work/project/"

"""
Explanation: Processing the open food databse to extract a small number of representative items to use as demonstration for the visualization.
End of explanation
"""


# Get sample of the full database to understand what columns we want
smp = pd.read_csv(data_dir+"en.openfoodfacts.org.products.csv", sep = "\t", nrows = 100)

for c in smp.columns:
    print(c)

# Specify what columns we need for the demonstration visualizations

demo_cols = ['code', 'creator', 'product_name', 'generic_name', 'quantity',
       'brands', 'brands_tags', 'categories', 'categories_tags', 'serving_size',
       'serving_size', 'energy_100g', 'energyfromfat_100g', 'fat_100g',
       'saturatedfat_100g', 'monounsaturatedfat_100g',
       'polyunsaturatedfat_100g', 'omega3fat_100g', 'omega6fat_100g',
       'omega9fat_100g', 'oleicacid_100g', 'transfat_100g', 'cholesterol_100g',
       'carbohydrates_100g', 'sugars_100g', 'sucrose_100g', 'glucose_100g',
       'fructose_100g', 'lactose_100g', 'maltose_100g', 'starch_100g',
       'fiber_100g', 'proteins_100g', 'salt_100g', 'sodium_100g',
       'alcohol_100g', 'vitamina_100g', 'betacarotene_100g', 'vitamind_100g',
       'vitamine_100g', 'vitamink_100g', 'vitaminc_100g', 'vitaminb1_100g',
       'vitaminb2_100g', 'vitaminpp_100g', 'vitaminb6_100g', 'vitaminb9_100g',
       'folates_100g', 'vitaminb12_100g', 'bicarbonate_100g', 'potassium_100g',
       'chloride_100g', 'calcium_100g', 'iron_100g', 'fluoride_100g',
       'iodine_100g', 'caffeine_100g', 'cocoa_100g',
       'ingredients_list']

# Create a list of columns to drop
drop_cols = [c for c in smp.columns if c not in demo_cols]
print(drop_cols)

# Pull in full dataset
df = pd.read_csv(data_dir+"en.openfoodfacts.org.products.csv", sep = "\t")

# Drop unwanted columns
df.drop(drop_cols, axis = 1, inplace = True)

# Take a quick look
df

# Drop all rows that are not from the usda ndb import
df = df[df.creator == "usda-ndb-import"]

df

"""
Explanation: Working from the full database, because the usda_imports_filtered.csv file in the shared drive does not have brand information, which will be useful for displaying.
End of explanation
"""


df[df["product_name"].str.lower().str.contains("donut", na = False)]

df[df["product_name"].str.lower().str.contains("cracker", na = False)]

df[df["product_name"].str.lower().str.contains("cereal", na = False)]

"""
Explanation: Now down to a manageable number of rows and columns.  Going to explore for a few typical items to use as demo data.  Let's take a look at donuts, crackers and cereal -- the three categories used in the paper prototype.
End of explanation
"""


# reminder on column names remaining
df.columns

"""
Explanation: Looks like there are plenty of options for these.  For demo purposes I want to pick 12 of each with a reasonable range of variation on the key factors of sugar, fat, sodium, protein, so that I can have one plus up to 11 comparison products.
End of explanation
"""


# Words we want to find that indicate product type
cat_words = ["donut", "cracker", "cereal"]

# Some of these generate confusion, so also have an 'exclude' dictionary
# This is pretty crude, but seems ok for generating demo 

exclude_dict = {"donut": "coffee",
                "cracker": "Nut",
                "cereal": "Bar"}

# What we want to get variation on
pick_factors = ['fat_100g', 'sugars_100g', 'proteins_100g', 'sodium_100g']

# Points we want to pick (percentiles).  Can tune this to get more or fewer picks.

pick_percentiles = [0.1, 0.5, 0.9]
# pick_percentiles = [0, 0.25, 0.5, 0.75, 1.0]

demo_picks = []

for cat in cat_words:
    
    # first get all the items containing the cat word
    catf = df[df["product_name"].str.lower().str.contains(cat, na = False)]
    
    # then exclude any of these that contain the relevant exclude word
    catf = catf[~catf["product_name"].str.lower().str.contains(exclude_dict[cat], na = False)]
    
    # Identify what rank each product is in that category, for each main factor
    for p in pick_factors:
        
        catf[p + "_rank"] = catf[p].rank(method = "first")
        
        # Select five products, at quintiles on each
        
        high = catf[p + "_rank"].max()
        
        pick_index = [max(1, round(n * high)) for n in pick_percentiles]
        
        demo_picks.extend(catf[catf[p+"_rank"].isin(pick_index)].code)
        
    
demo_df = df[df.code.isin(demo_picks)]

# Add in category identifier
demo_df["demo_cat"] = "None"
for w in cat_words:
    is_cat = demo_df.product_name.str.lower().str.contains(w)
    demo_df["demo_cat"][is_cat] = w

# Take a look at what we built
demo_df

# Now write it out to disk
outfile = "demo_food_data.csv"
demo_df.to_csv(data_dir+outfile)

"""
Explanation: Now going to go through and find items that have certain category words in the product name.  Then filter these to exclude the most often word that is confused in there (e.g. donut flavor coffee gets picked up under donut).
Then going to sort each of these based on the rank of items on key factors like sugar.  And for each factor, going to pick items that are at specified percentiles, so we get a wide range on those factors.
End of explanation
"""


