0
$\begingroup$

I have recently started working on some ETL work and wanted some guidance in this area related to data cleaning from CSV to JSON mapping using AWS Glue, Python (pandas, pyspark).

Question

Need a recommendation ASAP to know if I am on the right track or if there is a better way to do this. Perhaps, there is a tool I am not aware of in which you can specify input and expected output examples and some ML or AI can learn mapping based on examples? Looking forward to responses!

Goal

Clean input CSV data to be able to map to JSON via AWS Glue. In order to do so effectively I should be able to handle repeating phone numbers and visited places (combination of columns: visited state, visited country) and map easily to a JSON array like the following:

Planned Steps to achieve this

Input CSV File:

id, name, phone number, phone number.1, visited state, visited country, visited state.1, visited country.2
1, Migz, "9731234444", "9731234888", "NJ", "USA", "Porto", "Portugal"
2, John, "9173334444", "9179994444", "NY", "USA", "Barcelona", "Spain"
  1. Group repeating single/pair of columns into one column and then take all values as an array.
    • concat visited state, visited country using JSON format "{ state: , country: }" into new column called visited_places. This function should also do the same for visited state.N, visited country.N where N is repeating column
    • use pandas melt in order to take all visited_places.N (repeating columns) and have them all under one column 'visited_places'
    • group by unique column (id for example), and run collect_set for unique values using something like this: (df .groupby("id") .agg(F.collect_set("visited_places"), F.collect_set("phone_numbers")) .show()) Should have an dataframe that looks something like this:
id, name, phone_numbers, visited_places
1, Migz, ["9731234444", "9731234888"], [{ "state": "NJ", "country": "USA"}, { "state": "Porto", "country": "Portugal"}]
2, John, ["9173334444", "9179994444"], [{ "state": "NJ", "country": "USA"}, { "state": "Barcelona", "country": "Spain"}]
  1. After using AWS Glue Crawler to build schema and table, with above clean data output, AWS GLU ETL job (script) should be able to map phone_numbers to an array column and visited_places.array>. Assumption is that during mapping, pyspark will be able to translate array to array>.

  2. Final expected JSON file is the below which we should be able to query using AWS Athena!

[
{
  "id": 1,
  "name": "Migz",
  "phone_number": [
     "9731234444",
     "9731234888"
  ],
  "visited_places": [
    {
      "state": "NJ",
      "country": "USA"
    },
    {
      "state": "Porto",
      "country": "Portugal"
    }
  ]
},
{
  "id": 2,
  "name": "John",
  "phone_number": [
     "9173334444",
     "9179994444"
  ],
  "visited_places": [
    {
      "state": "NY",
      "country": "USA"
    },
    {
      "state": "Barcelona",
      "country": "Spain"
    }
  ]
}
$\endgroup$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.