Flatten data from JSON to a relational format

[singhish]

As it currently stands, the data is spread out across multiple folders in JSON format in the following structure:

    out_dir
    └── user
        └── spec_id
            └── key
                └── {start_ts}_{end_ts}.json

Note that all of the PhoneView-related data is in the following format:

{
        "_id": {
            "$oid": "..."
        },
        "metadata": {
            "key": "...",
            "platform": "...",
            "read_ts": ...,
            "time_zone": "...",
            "type": "...",
            "write_ts": ...
        },
        "user_id": {
            "$uuid": "..."
        },
        "data": {
            <keys are dependent on key specified in metadata>
        }
    }

Thus, I propose a series of Pandas DataFrames with the following columns:

1. A DataFrame consisting of metadata

• user
• spec_id
• key
• start_ts
• end_ts
• _id
• platform
• read_ts
• time_zone
• type
• write_ts
• user_id

2. A DataFrame for each key. The fields here correspond to the fields in the data sub-object of the PhoneView data files. For instance, a DataFrame for the background/battery key for android devices can have these columns:

• _id
• android_health
• android_plugged
• android_technology
• android_temperature
• android_voltage
• battery_level_pct
• battery_status
• ts
• write_ts

With the amount of data there is, though, Pandas DataFrames might be insufficient and clunky. A SQLite database might be more appropriate, as things such as primary/foreign keys (which _id can take the role of) can be used to better organize the data as a whole.

When all is said and done, we would end up with these tables:

• Metadata
• BackgroundBattery
• BackgroundFilteredLocation
• BackgroundLocation
• BackgroundMotionActivity
• ManualEvaluationTransition
• StatemachineTransition

that would encompass all our data.

Thoughts? @shankari

[shankari]

There are multiple JSON files; are you proposing a single dataframe or multiple dataframes for each type?
e.g. for battery, I count 8 JSON files

$ find  ~/e-mission/mobilitynet-analysis-scripts/bin/data/ -name \*battery\* | wc -l
       8

Will there be 8 dataframes or one?
And if there will be one, how do you propose to capture the differences between the different JSON files?

Concretely, why didn't we just save all the JSON files to one giant battery.json?
If we needed multiple JSON files, why don't we need multiple csv files/dataframes?

Note also that creating csv files is only the first part of the puzzle.
As I indicated, you also need to be able to load and navigate the csv files - e.g. change the examples in the data exploration template to work with the new structure.

[shankari]

Just to clarify, I think that your overall direction is correct. But you need to think through the details.
The trajectory evaluation notebook can actually give you a hint

[shankari]

creating csv representations of the files is good, but I am not sure it adds that much to the ease of exploration because people still have to navigate the tree to access individual data frames. In general, data scientists don't seem to like to work with trees - they don't know what the keys are, etc.

They want to load everything into a dataframe and then just work with dataframes forever.
So I think that the flattening requirement is actually driven by the need to load a dataframe at the beginning of the notebook and then use it forever.

[singhish]

My initial understanding of this task was to re-represent the data in tabular format, as this would make it easier for users to deal with rather than JSON files. I initially thought that the reason why you were mentioning using Pandas DataFrames was because they are inherently tabular.

This would for sure change how the data is currently accessed, but I believe that having the data tabularized would make it easier to navigate. For example, you would be able to replace the tree traversals with simple queries.

[shankari]

For example, you would be able to replace the tree traversals with simple queries.

This is exactly what I had in mind, but I am not sure your proposed solution actually fully supports this.
Concretely, in the data exploration template, "Work with a single trip" section, we traverse the tree to display the battery drain comparison for a single trip. How do you propose we do that with the battery_df or battery.csv structure?

If you want hints on how I did this before myself, look at the trajectory evaluation notebooks, in which I did create a dataframe of the locations, but with a few small additions.

[singhish]

I spent today studying Data_exploration_template and trajectory_evaluation to get an idea of how to answer this question. The general way of data is being accessed for trips is by indexing directly into a portion of the phone view map. For instance, this is accomplished for battery_df in Data_exploration_template as such:

curr_range = phone_detail_map["evaluation_ranges"][2]
...
battery_df = curr_range["battery_df"]

and likewise, in trajectory_evaluation, this is done directly in the test cases by picking out a section:

"section": pv_ucb.map()["android"]["ucb-sdb-android-3"]["evaluation_ranges"][0]["evaluation_trip_ranges"][0]["evaluation_section_ranges"][2]

Also, a (probably obvious) observation is that the dfs are stored in the phone view maps and are directly referenced to build up the spatial error dfs (eg. the location_df key is directly referenced in get_spatial_errors => sr['location_df'])

Not sure if this is on the right track, but maybe each of these dfs can be appended upon each other during the tree traversals to directly return data in .csv/tabular format? That approach is a lot more straightforward and works better with the current tree traversals than the original approach I suggested.

[shankari]

Not sure if this is on the right track, but maybe each of these dfs can be appended upon each other during the tree traversals to directly return data in .csv/tabular format? That approach is a lot more straightforward and works better with the current tree traversals than the original approach I suggested.

May be on the right track, but could you give some additional details? pseudocode is fine, PR is also fine if you can quickly backtrack from an approach that doesn't work.

Concretely, if you append the dfs to each other, what will the columns look like?
how will you implement the "Work with a single trip"? Will you still traverse a tree?

[singhish]

Of course, we would need to add the path to a specific entry as column values in each df to uniquely identify records in the dataframes.

[shankari]

I would suggest the following:

• create an alternate class that can read data from the directory file structure and generate pandas dataframes with the additional columns representing the path through the tree.
• change the data exploration template to use the new class and method of reading the data. This will serve as an existence proof that it actually works

In the upcoming days, we can replace phoneview in the notebooks with the new class. And then we are done.

[shankari]

@singhish One challenge you may run into is that the file directory structure does not currently have all the information required to add the additional columns - e.g.

bin/data//ucb-sdb-ios-4/train_bus_ebike_mtv_ucb/background~battery

which implies that users can't load only the battery dataframe and work with it, for example.

[singhish]

Added a notebook here that successfully does what we discussed: https://github.com/singhish/mobilitynet-analysis-scripts/blob/flatten_json/bin/Flatten_JSON_to_tabular.ipynb. Let me know your thoughts! I can turn this into a script tomorrow. @shankari

[shankari]

This looks fine to me overall.
I assume it won't be a script at the end, but rather, a module (similar to phoneview) that will basically embed the df_map, right?

But I still don't see how you can accomplish the original goal "Work with a single trip" section," from the data exploration template. Can you elaborate?

[shankari]

One other thing (not sure it is important) is that people may want to load one CSV manually (e.g. only battery or only location). but I guess they can just do it pandas directly. Let's revisit this after getting this iteration to work fully.

[shankari]

Concretely, the requirement is "I want to pick the third repetition of the berkeley timeline and visualize it (either battery or location)"

As an example of how this works with the trajectory (that I posted to the chat yesterday):

st_errors_df.query("phone_os == 'android' & (quality == 4) & section_id == 'light_rail_below_above_ground'").boxplot(column="error", by="run")

That gives me the android HAMFDC (quality == 4) data for the "light_rail_below_above_ground" leg.

[shankari]

In other words, you have got to the point where you can concatenate dataframes, but then you are just printing them. I want to see how you query the dataframes so that you can replicate the behavior in the data exploration template. where we visualize one trip or one section at a time.