📑 Extracting & Transforming USDA's FoodData Central Data

2024-11-04 705 words 4 minutes

Contents

USDA’s FoodData Central is a comprehensive database of detailed information on food. Each food item has a detailed breakdown of nutrients, including macronutrients (like carbohydrates, fats, and proteins), micro-nutrients (vitamins and minerals), and other components (like water, caffeine, and alcohol). The site offers a download of the datasets in JSON format. I will write a script to transform the data to comport with the purposes of my recipe management system Formulation

Steps

Create new C# .NET Core Console Application
- .NET Core 8
Establish connection to the database
- I create a service connection, which is just a way to let Visual Studio automatically write the connection string to my database and store it in user secrets file.

using Microsoft.Data.SqlClient;
using Microsoft.Extensions.Configuration;

var config = new ConfigurationBuilder().AddUserSecrets().Build();
var connectionString = config.GetConnectionString("FormulationDBSA");

Model the data in the JSON file in C#
- I need to match Model property names with JSON property names

public class FoundationFoodData
{
    [JsonPropertyName("FoundationFoods")]
    public List FoundationFoods { get; set; }
}

public class FoodItem
{
    [JsonPropertyName("fdcId")]
    public int FdcId { get; set; }

    [JsonPropertyName("description")]
    public string Description { get; set; }

    [JsonPropertyName("foodNutrients")]
    public List FoodNutrients { get; set; }
}

public class FoodNutrient
{

    [JsonPropertyName("nutrient")]
    public Nutrient Nutrient { get; set; }

    [JsonPropertyName("amount")]
    public double Amount { get; set; }
}

public class Nutrient
{
    [JsonPropertyName("number")]
    public string Number { get; set; }
    [JsonPropertyName("unitName")]
    public string UnitName { get; set; }
}

I am interested in only a few of the data model properties:
- fdcid - unique identifier, use this as primary key in the database
- description - essentially the name of the food item
- foodNutrients - array of nutrient objects
I build the data model to only include those properties I am interested in:

    public class Ingredient
    {
      public int Id { get; set; }
      public string Name { get; set; }
      public List Nutrients { get; set; }
    }
    public class IngredientNutrient
    {
      public string Name { get; set; }
      public double Amount { get; set; }
      public string Unit { get; set; }
    }

Read the JSON file

var data = JsonSerializer.Deserialize(jsonData);

Create Dictionary for nutrient filtering and renaming

Dictionary NutrientFilter = new Dictionary
        {
            { "Sodium", 307 },
            { "Sugar", 269 },
            { "Water", 255 },
            { "Fat", 204 },
            { "Fiber", 291 },
            { "Protein", 203 },
            { "Carbohydrate", 205 },
            { "Calories", 957 }
        };

Transform the FoundationalFoods object

var ingredients = new List();
foreach (var food in data.FoundationFoods)
{
    ingredients.Add(new Ingredient
    {
        Id = food.FdcId,
        Name = food.Description,
        Nutrients = food.FoodNutrients
        .Where(nutrient => NutrientFilter.Any(n => n.Value.ToString() == nutrient.Nutrient.Number))
        .Select(nutrient => new IngredientNutrient
        {
            Name = NutrientFilter.FirstOrDefault(filter => filter.Value.ToString() == nutrient.Nutrient.Number).Key,
            Amount = nutrient.Amount,
            Unit = nutrient.Nutrient.UnitName,
        }).ToList()
    });
}

Results

I print formatted data to the console.

Write the data to SQL Database
- The last step is to write the data to the database. This must wait until I sort out how to structure the database tables.
- For now, I add the fdc_id and water columns to my existing ingredient table, to see if this works.
- Once again, I use Add-Migration and Update-Database to automatically update my table schema.

    foreach (var food in data.FoundationFoods)
{
    ingredients.Add(new Ingredient
    {
        Id = Guid.NewGuid(),
        FdcId = food.FdcId,
        Name = food.Description,
        Sodium = food.FoodNutrients.Where(fn => fn.Nutrient.Number == NutrientFilter["Sodium"].ToString()).Select(n => n.Amount).FirstOrDefault(),
        Sugar = food.FoodNutrients.Where(fn => fn.Nutrient.Number == NutrientFilter["Sugar"].ToString()).Select(n => n.Amount).FirstOrDefault(),
        Water = food.FoodNutrients.Where(fn => fn.Nutrient.Number == NutrientFilter["Water"].ToString()).Select(n => n.Amount).FirstOrDefault(),
        Fat = food.FoodNutrients.Where(fn => fn.Nutrient.Number == NutrientFilter["Fat"].ToString()).Select(n => n.Amount).FirstOrDefault(),
        Fiber = food.FoodNutrients.Where(fn => fn.Nutrient.Number == NutrientFilter["Fiber"].ToString()).Select(n => n.Amount).FirstOrDefault(),
        Protein = food.FoodNutrients.Where(fn => fn.Nutrient.Number == NutrientFilter["Protein"].ToString()).Select(n => n.Amount).FirstOrDefault(),
        Carbohydrate = food.FoodNutrients.Where(fn => fn.Nutrient.Number == NutrientFilter["Carbohydrate"].ToString()).Select(n => n.Amount).FirstOrDefault(),
        Kilocalorie = food.FoodNutrients.Where(fn => fn.Nutrient.Number == NutrientFilter["Calories"].ToString()).Select(n => n.Amount).FirstOrDefault(),
        IsFormulation = false
    });
}

DataTable ingredientData = new DataTable();

ingredientData.Columns.Add(new DataColumn("id", typeof(Guid)));
ingredientData.Columns.Add(new DataColumn("name", typeof(string)));
ingredientData.Columns.Add(new DataColumn("is_formulation", typeof(bool)));
ingredientData.Columns.Add(new DataColumn("kilocalorie", typeof(double)));
ingredientData.Columns.Add(new DataColumn("protein", typeof(double)));
ingredientData.Columns.Add(new DataColumn("fat", typeof(double)));
ingredientData.Columns.Add(new DataColumn("carbohydrate", typeof(double)));
ingredientData.Columns.Add(new DataColumn("fiber", typeof(double)));
ingredientData.Columns.Add(new DataColumn("sugar", typeof(double)));
ingredientData.Columns.Add(new DataColumn("sodium", typeof(double)));
ingredientData.Columns.Add(new DataColumn("fdc_id", typeof(int)));
ingredientData.Columns.Add(new DataColumn("water", typeof(double)));

foreach (var ingredient in ingredients)
{
    ingredientData.Rows.Add(
        ingredient.Id,
        ingredient.Name,
        ingredient.IsFormulation,
        ingredient.Kilocalorie,
        ingredient.Protein,
        ingredient.Fat,
        ingredient.Carbohydrate,
        ingredient.Fiber,
        ingredient.Sugar,
        ingredient.Sodium,
        ingredient.FdcId.HasValue ? ingredient.FdcId.Value : DBNull.Value,
        ingredient.Water
    );
}

using (var connection = new SqlConnection(connectionString))
{
    await connection.OpenAsync();
    using (SqlBulkCopy bulkCopy = new SqlBulkCopy(connection))
    {
        bulkCopy.DestinationTableName = "ingredient";
        await bulkCopy.WriteToServerAsync(ingredientData);
    }
}

Done.