search

microsoft / data-formulator

🪄 Create rich visualizations with AI
https://arxiv.org/abs/2408.16119
MIT License
1.34k stars 73 forks source link

import json #50

Open nicho2 opened 2 weeks ago

nicho2 commented 2 weeks ago

I try to import a json file or clipboard with:

{ "timestamp": [ "2024-10-28T08:00:00", "2024-10-28T08:01:00", "2024-10-28T08:02:00", "2024-10-28T08:03:00", "2024-10-28T08:04:00", "2024-10-28T08:05:00", "2024-10-28T08:06:00", "2024-10-28T08:07:00", "2024-10-28T08:08:00", "2024-10-28T08:09:00", "2024-10-28T08:10:00", "2024-10-28T08:11:00", "2024-10-28T08:12:00", "2024-10-28T08:13:00", "2024-10-28T08:14:00", "2024-10-28T08:15:00", "2024-10-28T08:16:00", "2024-10-28T08:17:00", "2024-10-28T08:18:00", "2024-10-28T08:19:00", "2024-10-28T08:20:00", "2024-10-28T08:21:00", "2024-10-28T08:22:00", "2024-10-28T08:23:00", "2024-10-28T08:24:00", "2024-10-28T08:25:00", "2024-10-28T08:26:00", "2024-10-28T08:27:00", "2024-10-28T08:28:00", "2024-10-28T08:29:00", "2024-10-28T08:30:00", "2024-10-28T08:31:00", "2024-10-28T08:32:00", "2024-10-28T08:33:00", "2024-10-28T08:34:00", "2024-10-28T08:35:00", "2024-10-28T08:36:00", "2024-10-28T08:37:00", "2024-10-28T08:38:00", "2024-10-28T08:39:00", "2024-10-28T08:40:00", "2024-10-28T08:41:00", "2024-10-28T08:42:00", "2024-10-28T08:43:00", "2024-10-28T08:44:00", "2024-10-28T08:45:00", "2024-10-28T08:46:00", "2024-10-28T08:47:00", "2024-10-28T08:48:00", "2024-10-28T08:49:00", "2024-10-28T08:50:00", "2024-10-28T08:51:00", "2024-10-28T08:52:00", "2024-10-28T08:53:00", "2024-10-28T08:54:00", "2024-10-28T08:55:00", "2024-10-28T08:56:00", "2024-10-28T08:57:00", "2024-10-28T08:58:00", "2024-10-28T08:59:00", "2024-10-28T09:00:00", "2024-10-28T09:01:00", "2024-10-28T09:02:00", "2024-10-28T09:03:00", "2024-10-28T09:04:00", "2024-10-28T09:05:00", "2024-10-28T09:06:00", "2024-10-28T09:07:00", "2024-10-28T09:08:00", "2024-10-28T09:09:00", "2024-10-28T09:10:00", "2024-10-28T09:11:00", "2024-10-28T09:12:00", "2024-10-28T09:13:00", "2024-10-28T09:14:00", "2024-10-28T09:15:00", "2024-10-28T09:16:00", "2024-10-28T09:17:00", "2024-10-28T09:18:00", "2024-10-28T09:19:00", "2024-10-28T09:20:00", "2024-10-28T09:21:00", "2024-10-28T09:22:00", "2024-10-28T09:23:00", "2024-10-28T09:24:00", "2024-10-28T09:25:00", "2024-10-28T09:26:00", "2024-10-28T09:27:00", "2024-10-28T09:28:00", "2024-10-28T09:29:00", "2024-10-28T09:30:00", "2024-10-28T09:31:00", "2024-10-28T09:32:00", "2024-10-28T09:33:00", "2024-10-28T09:34:00", "2024-10-28T09:35:00", "2024-10-28T09:36:00", "2024-10-28T09:37:00", "2024-10-28T09:38:00", "2024-10-28T09:39:00", "2024-10-28T09:40:00", "2024-10-28T09:41:00", "2024-10-28T09:42:00", "2024-10-28T09:43:00", "2024-10-28T09:44:00", "2024-10-28T09:45:00", "2024-10-28T09:46:00", "2024-10-28T09:47:00", "2024-10-28T09:48:00", "2024-10-28T09:49:00", "2024-10-28T09:50:00", "2024-10-28T09:51:00", "2024-10-28T09:52:00", "2024-10-28T09:53:00", "2024-10-28T09:54:00", "2024-10-28T09:55:00", "2024-10-28T09:56:00", "2024-10-28T09:57:00", "2024-10-28T09:58:00", "2024-10-28T09:59:00", "2024-10-28T10:00:00", "2024-10-28T10:01:00", "2024-10-28T10:02:00", "2024-10-28T10:03:00", "2024-10-28T10:04:00", "2024-10-28T10:05:00", "2024-10-28T10:06:00", "2024-10-28T10:07:00", "2024-10-28T10:08:00", "2024-10-28T10:09:00", "2024-10-28T10:10:00", "2024-10-28T10:11:00", "2024-10-28T10:12:00", "2024-10-28T10:13:00", "2024-10-28T10:14:00", "2024-10-28T10:15:00", "2024-10-28T10:16:00", "2024-10-28T10:17:00", "2024-10-28T10:18:00", "2024-10-28T10:19:00", "2024-10-28T10:20:00", "2024-10-28T10:21:00", "2024-10-28T10:22:00", "2024-10-28T10:23:00", "2024-10-28T10:24:00", "2024-10-28T10:25:00", "2024-10-28T10:26:00", "2024-10-28T10:27:00", "2024-10-28T10:28:00", "2024-10-28T10:29:00", "2024-10-28T10:30:00", "2024-10-28T10:31:00", "2024-10-28T10:32:00", "2024-10-28T10:33:00", "2024-10-28T10:34:00", "2024-10-28T10:35:00", "2024-10-28T10:36:00", "2024-10-28T10:37:00", "2024-10-28T10:38:00", "2024-10-28T10:39:00", "2024-10-28T10:40:00", "2024-10-28T10:41:00", "2024-10-28T10:42:00", "2024-10-28T10:43:00", "2024-10-28T10:44:00", "2024-10-28T10:45:00", "2024-10-28T10:46:00", "2024-10-28T10:47:00", "2024-10-28T10:48:00", "2024-10-28T10:49:00", "2024-10-28T10:50:00", "2024-10-28T10:51:00", "2024-10-28T10:52:00", "2024-10-28T10:53:00", "2024-10-28T10:54:00", "2024-10-28T10:55:00", "2024-10-28T10:56:00", "2024-10-28T10:57:00", "2024-10-28T10:58:00", "2024-10-28T10:59:00", "2024-10-28T11:00:00", "2024-10-28T11:01:00", "2024-10-28T11:02:00", "2024-10-28T11:03:00", "2024-10-28T11:04:00", "2024-10-28T11:05:00", "2024-10-28T11:06:00", "2024-10-28T11:07:00", "2024-10-28T11:08:00", "2024-10-28T11:09:00", "2024-10-28T11:10:00", "2024-10-28T11:11:00", "2024-10-28T11:12:00", "2024-10-28T11:13:00", "2024-10-28T11:14:00", "2024-10-28T11:15:00", "2024-10-28T11:16:00", "2024-10-28T11:17:00", "2024-10-28T11:18:00", "2024-10-28T11:19:00", "2024-10-28T11:20:00", "2024-10-28T11:21:00", "2024-10-28T11:22:00", "2024-10-28T11:23:00", "2024-10-28T11:24:00", "2024-10-28T11:25:00", "2024-10-28T11:26:00", "2024-10-28T11:27:00", "2024-10-28T11:28:00", "2024-10-28T11:29:00", "2024-10-28T11:30:00", "2024-10-28T11:31:00", "2024-10-28T11:32:00", "2024-10-28T11:33:00", "2024-10-28T11:34:00", "2024-10-28T11:35:00", "2024-10-28T11:36:00", "2024-10-28T11:37:00", "2024-10-28T11:38:00", "2024-10-28T11:39:00", "2024-10-28T11:40:00", "2024-10-28T11:41:00", "2024-10-28T11:42:00", "2024-10-28T11:43:00", "2024-10-28T11:44:00", "2024-10-28T11:45:00", "2024-10-28T11:46:00", "2024-10-28T11:47:00", "2024-10-28T11:48:00", "2024-10-28T11:49:00", "2024-10-28T11:50:00", "2024-10-28T11:51:00", "2024-10-28T11:52:00", "2024-10-28T11:53:00", "2024-10-28T11:54:00", "2024-10-28T11:55:00", "2024-10-28T11:56:00", "2024-10-28T11:57:00", "2024-10-28T11:58:00" ], "co2": [ 407.0, 407.0, 407.0, 407.0, 407.0, 407.0, 407.0, 407.17, 408.0, 408.33, 409.0, 409.17, 410.0, 410.0, 410.5, 411.0, 411.0, 411.67, 412.0, 412.0, 412.0, 412.67, 413.0, 413.0, 412.0, 412.0, 412.0, 411.33, 411.0, 411.0, 410.0, 410.0, 409.5, 409.0, 409.0, 409.0, 408.0, 408.0, 408.0, 408.0, 408.0, 407.0, 412.5, 407.17, 408.0, 408.0, 407.33, 407.0, 407.0, 430.67, 407.0, 407.0, 407.0, 407.0, 407.0, 407.0, 407.0, 407.0, 407.0, 407.0, 407.0, 407.0, 407.5, 408.67, 410.17, 412.83, 416.67, 420.33, 424.33, 427.5, 430.5, 433.17, 435.67, 437.83, 439.67, 441.33, 442.33, 443.0, 443.5, 444.0, 444.0, 444.0, 444.0, 445.0, 445.0, 445.0, 445.0, 444.5, 444.0, 444.0, 443.0, 443.0, 442.5, 442.0, 441.67, 441.0, 441.0, 440.17, 440.0, 440.0, 439.5, 439.0, 439.0, 439.0, 439.0, 439.0, 439.0, 439.0, 439.0, 441.0, 438.0, 437.67, 437.0, 437.0, 436.0, 436.0, 435.17, 435.0, 434.5, 434.0, 433.5, 433.0, 432.17, 432.0, 432.0, 432.83, 435.5, 439.67, 444.5, 450.5, 457.5, 463.67, 470.67, 478.0, 485.33, 492.33, 500.0, 507.67, 514.33, 515.0, 515.0, 515.0, 515.0, 515.0, 515.0, 515.0, 515.0, 515.0, 515.0, 515.0, 515.0, 515.0, 515.0, 515.17, 521.0, 524.83, 524.83, 523.83, 522.5, 520.5, 518.0, 515.33, 511.83, 508.17, 504.0, 499.33, 494.5, 485.5, 485.5, 480.83, 476.0, 471.33, 466.83, 462.33, 458.0, 454.33, 450.33, 446.67, 443.5, 440.5, 437.67, 435.17, 433.17, 431.33, 429.67, 429.0, 428.0, 428.0, 429.33, 433.67, 439.5, 445.5, 451.83, 459.5, 467.5, 476.17, 484.67, 493.0, 501.0, 508.67, 515.83, 523.0, 531.33, 535.0, 535.0, 535.0, 535.0, 535.0, 535.0, 535.0, 535.0, 535.0, 535.0, 535.0, 535.0, 524.17, 535.0, 535.0, 535.0, 535.0, 535.0, 536.0, 544.33, 553.17, 560.0, 565.67, 570.33, 499.17, 577.83, 581.5, 584.83, 588.33, 592.0, 596.5, 601.83, 607.5, 613.83, 619.5, 623.0 ] }

and my table is empty:

Image

I think my json is correct:

df = pd.DataFrame(data) df timestamp co2 0 2024-10-28T08:00:00 407.00 1 2024-10-28T08:01:00 407.00 2 2024-10-28T08:02:00 407.00 3 2024-10-28T08:03:00 407.00 4 2024-10-28T08:04:00 407.00 .. ... ... 234 2024-10-28T11:54:00 601.83 235 2024-10-28T11:55:00 607.50 236 2024-10-28T11:56:00 613.83 237 2024-10-28T11:57:00 619.50 238 2024-10-28T11:58:00 623.00 [239 rows x 2 columns]

Chenglong-MS commented 2 weeks ago

Currently Data Formulator's supported json format is the json with "records" orientation:

For example, your dataset should be organized in this way (each record is a row in the table): This is equivelent as reading json in pandas with pandas.read_json(file_path, orient='records')

[
  {"index": 0, "timestamp": "2024-10-28T08:00:00", "value": 407.00},
  {"index": 1, "timestamp": "2024-10-28T08:01:00", "value": 407.00},
  {"index": 2, "timestamp": "2024-10-28T08:02:00", "value": 407.00},
  {"index": 3, "timestamp": "2024-10-28T08:03:00", "value": 407.00},
  {"index": 4, "timestamp": "2024-10-28T08:04:00", "value": 407.00},
  ...
  {"index": 234, "timestamp": "2024-10-28T11:54:00", "value": 601.83},
  {"index": 235, "timestamp": "2024-10-28T11:55:00", "value": 607.50},
  {"index": 236, "timestamp": "2024-10-28T11:56:00", "value": 613.83},
  {"index": 237, "timestamp": "2024-10-28T11:57:00", "value": 619.50},
  {"index": 238, "timestamp": "2024-10-28T11:58:00", "value": 623.00}
]

This is a good point though, maybe we should support both types of json to enter into data formulator.