0

Let me start by saying I am very novice and this code is probably ugly. I'm trying to append dataframe data to a json file without deleting the previous content of the json data at each subsequent run.

import json
import pandas as pd
import datetime

json_backup = 'temp.json'
df_store = pd.DataFrame(columns=["Time", "Average Rate"])

while True:
   #doing some data gathering (code not included here) at each loop
   df_store = df_store.append({
             "Time": datetime.datetime.now(),
            "Average Rate": average_rate
             }, ignore_index=True)
  df_store.to_json(json_backup)

backup = pd.read_json(json_backup)
print (backup)

So this works as intended with all new data added to the json until I restart the script and the json data gets deleted. How should I proceed so that this data is kept and all new data is just appended to the json file?

Tamis
  • 35
  • 1
  • 1
  • 8
  • 1
    As far as I've seen, you cannot. One work-around would be to load your current file (`df_old = pd.read_json(json_backup)`) and then concatenate the two dataframes (probably with something along the lines of `df_old.append(df_store)`, and finally saving the concatenated dataframe (`df_old.to_json(json_backup)`) Maybe you could also take a look at: https://stackoverflow.com/a/30230394/6655150 – Kostas Mouratidis Jun 03 '18 at 10:45
  • Thanks Kostas Mouratidis, you are right it cannot be done. – Tamis Jun 03 '18 at 12:26

2 Answers2

0

I think you should read the 'temp.json' before the start of your while loop into df_store variable, if the 'temp.json' file exists.

Evgeny
  • 4,173
  • 2
  • 19
  • 39
0

@Tamis, please try the below code Just copy the code(Python3), paste, run:

Note: I have taken the initial value of average_rate as the microseconds part of datetime.now() just for the purpose to test the code. You can calculate its value based on your logic so please change it.

You don't need to create any temp.json file. It will be automatically created.

Just copy the contents of append_data_to_json-v2-py3.py file, paste then run.

Press Y/y to continue after each updation of temp.json else press any other key to come out from while loop.

» append_data_to_json-v2-py3.py

import json
import pandas as pd
import datetime
import time # {Added}

json_backup = 'temp.json'
df_store = pd.DataFrame(columns=["Time", "Average Rate"])

average_rate = 50  # {Added}
count = 1          # {Added}

while True:
    # Doing some data gathering (code not included here) at each loop
    time_data = str(datetime.datetime.now())

    # Store micro seconds of datetime as average 
    # (For test only, use your own logic to calculate it)
    # In case of 2018-06-03 18:44:56.220778 => 220778
    average_rate = int((time_data.split()[1]).split('.')[1]) 

    try:
        df_store = pd.read_json(json_backup)

        df_store = df_store.append({
            "Time": time_data,
            "Average Rate": average_rate
        }, ignore_index=True)
        
        df_store.to_json(json_backup)
        print (df_store)
        print ("***********************************************")       
    except Exception as e:
        df_store = df_store.append({
            "Time": time_data,
            "Average Rate": average_rate
            }, ignore_index=True)

        df_store.to_json(json_backup)
        print(df_store)
        print("***********************************************")

    # time.sleep(30.0 - ((time.time() - starttime) % 30.0))  # {Commented}
    print(count, "temp.json updated")

    # If user presses Y/y then continue otherwise exit from loop
    choice = input("\n" + str(count) + " Do you want to continue the operation (Y/N): ")
    if choice == 'y' or choice == 'Y':
        count = count + 1
        continue
    else:
        break

» First run

(py3.6) H:\RishikeshAgrawani\Projects\Python3\Pandas>python append_data_to_json-v2-py3.py
                         Time Average Rate
0  2018-06-03 18:51:57.506959       506959
***********************************************
1 temp.json updated

1 Do you want to continue the operation (Y/N): y
                         Time  Average Rate
0  2018-06-03 18:51:57.506959        506959
1  2018-06-03 18:52:00.925554        925554
***********************************************
2 temp.json updated

2 Do you want to continue the operation (Y/N): y
                         Time  Average Rate
0  2018-06-03 18:51:57.506959        506959
1  2018-06-03 18:52:00.925554        925554
2  2018-06-03 18:52:02.325613        325613
***********************************************
3 temp.json updated

3 Do you want to continue the operation (Y/N): y
                         Time  Average Rate
0  2018-06-03 18:51:57.506959        506959
1  2018-06-03 18:52:00.925554        925554
2  2018-06-03 18:52:02.325613        325613
3  2018-06-03 18:52:03.508673        508673
***********************************************
4 temp.json updated

4 Do you want to continue the operation (Y/N): y
                         Time  Average Rate
0  2018-06-03 18:51:57.506959        506959
1  2018-06-03 18:52:00.925554        925554
2  2018-06-03 18:52:02.325613        325613
3  2018-06-03 18:52:03.508673        508673
4  2018-06-03 18:52:07.772553        772553
***********************************************
5 temp.json updated

5 Do you want to continue the operation (Y/N): n

» Second run:

(py3.6) H:\RishikeshAgrawani\Projects\Python3\Pandas>python append_data_to_json-v2-py3.py
                       Time  Average Rate
0  2018-06-03 18:51:57.506959        506959
1  2018-06-03 18:52:00.925554        925554
2  2018-06-03 18:52:02.325613        325613
3  2018-06-03 18:52:03.508673        508673
4  2018-06-03 18:52:07.772553        772553
5  2018-06-03 18:53:52.484954        484954
***********************************************
1 temp.json updated

1 Do you want to continue the operation (Y/N): y
                       Time  Average Rate
0  2018-06-03 18:51:57.506959        506959
1  2018-06-03 18:52:00.925554        925554
2  2018-06-03 18:52:02.325613        325613
3  2018-06-03 18:52:03.508673        508673
4  2018-06-03 18:52:07.772553        772553
5  2018-06-03 18:53:52.484954        484954
6  2018-06-03 18:53:54.733274        733274
***********************************************
2 temp.json updated

2 Do you want to continue the operation (Y/N): y
                       Time  Average Rate
0  2018-06-03 18:51:57.506959        506959
1  2018-06-03 18:52:00.925554        925554
2  2018-06-03 18:52:02.325613        325613
3  2018-06-03 18:52:03.508673        508673
4  2018-06-03 18:52:07.772553        772553
5  2018-06-03 18:53:52.484954        484954
6  2018-06-03 18:53:54.733274        733274
7  2018-06-03 18:53:57.037358         37358
***********************************************
3 temp.json updated

3 Do you want to continue the operation (Y/N): y
                       Time  Average Rate
0  2018-06-03 18:51:57.506959        506959
1  2018-06-03 18:52:00.925554        925554
2  2018-06-03 18:52:02.325613        325613
3  2018-06-03 18:52:03.508673        508673
4  2018-06-03 18:52:07.772553        772553
5  2018-06-03 18:53:52.484954        484954
6  2018-06-03 18:53:54.733274        733274
7  2018-06-03 18:53:57.037358         37358
8  2018-06-03 18:53:58.437644        437644
***********************************************
4 temp.json updated

4 Do you want to continue the operation (Y/N): y
                       Time  Average Rate
0  2018-06-03 18:51:57.506959        506959
1  2018-06-03 18:52:00.925554        925554
2  2018-06-03 18:52:02.325613        325613
3  2018-06-03 18:52:03.508673        508673
4  2018-06-03 18:52:07.772553        772553
5  2018-06-03 18:53:52.484954        484954
6  2018-06-03 18:53:54.733274        733274
7  2018-06-03 18:53:57.037358         37358
8  2018-06-03 18:53:58.437644        437644
9  2018-06-03 18:53:59.181472        181472
***********************************************
5 temp.json updated

5 Do you want to continue the operation (Y/N): y
                        Time  Average Rate
0   2018-06-03 18:51:57.506959        506959
1   2018-06-03 18:52:00.925554        925554
2   2018-06-03 18:52:02.325613        325613
3   2018-06-03 18:52:03.508673        508673
4   2018-06-03 18:52:07.772553        772553
5   2018-06-03 18:53:52.484954        484954
6   2018-06-03 18:53:54.733274        733274
7   2018-06-03 18:53:57.037358         37358
8   2018-06-03 18:53:58.437644        437644
9   2018-06-03 18:53:59.181472        181472
10  2018-06-03 18:53:59.805276        805276
***********************************************
6 temp.json updated

6 Do you want to continue the operation (Y/N): y
                        Time  Average Rate
0   2018-06-03 18:51:57.506959        506959
1   2018-06-03 18:52:00.925554        925554
2   2018-06-03 18:53:59.805276        805276
3   2018-06-03 18:52:02.325613        325613
4   2018-06-03 18:52:03.508673        508673
5   2018-06-03 18:52:07.772553        772553
6   2018-06-03 18:53:52.484954        484954
7   2018-06-03 18:53:54.733274        733274
8   2018-06-03 18:53:57.037358         37358
9   2018-06-03 18:53:58.437644        437644
10  2018-06-03 18:53:59.181472        181472
11  2018-06-03 18:54:00.436774        436774
***********************************************
7 temp.json updated

7 Do you want to continue the operation (Y/N): y
                        Time  Average Rate
0   2018-06-03 18:51:57.506959        506959
1   2018-06-03 18:52:00.925554        925554
2   2018-06-03 18:53:59.181472        181472
3   2018-06-03 18:54:00.436774        436774
4   2018-06-03 18:53:59.805276        805276
5   2018-06-03 18:52:02.325613        325613
6   2018-06-03 18:52:03.508673        508673
7   2018-06-03 18:52:07.772553        772553
8   2018-06-03 18:53:52.484954        484954
9   2018-06-03 18:53:54.733274        733274
10  2018-06-03 18:53:57.037358         37358
11  2018-06-03 18:53:58.437644        437644
12  2018-06-03 18:54:00.997659        997659
***********************************************
8 temp.json updated

8 Do you want to continue the operation (Y/N): y
                        Time  Average Rate
0   2018-06-03 18:51:57.506959        506959
1   2018-06-03 18:52:00.925554        925554
2   2018-06-03 18:53:57.037358         37358
3   2018-06-03 18:53:58.437644        437644
4   2018-06-03 18:54:00.997659        997659
5   2018-06-03 18:53:59.181472        181472
6   2018-06-03 18:54:00.436774        436774
7   2018-06-03 18:53:59.805276        805276
8   2018-06-03 18:52:02.325613        325613
9   2018-06-03 18:52:03.508673        508673
10  2018-06-03 18:52:07.772553        772553
11  2018-06-03 18:53:52.484954        484954
12  2018-06-03 18:53:54.733274        733274
13  2018-06-03 18:54:01.549498        549498
***********************************************
9 temp.json updated

9 Do you want to continue the operation (Y/N): y
                        Time  Average Rate
0   2018-06-03 18:51:57.506959        506959
1   2018-06-03 18:52:00.925554        925554
2   2018-06-03 18:52:07.772553        772553
3   2018-06-03 18:53:52.484954        484954
4   2018-06-03 18:53:54.733274        733274
5   2018-06-03 18:54:01.549498        549498
6   2018-06-03 18:53:57.037358         37358
7   2018-06-03 18:53:58.437644        437644
8   2018-06-03 18:54:00.997659        997659
9   2018-06-03 18:53:59.181472        181472
10  2018-06-03 18:54:00.436774        436774
11  2018-06-03 18:53:59.805276        805276
12  2018-06-03 18:52:02.325613        325613
13  2018-06-03 18:52:03.508673        508673
14  2018-06-03 18:54:02.061568         61568
***********************************************
10 temp.json updated

10 Do you want to continue the operation (Y/N): y
                        Time  Average Rate
0   2018-06-03 18:51:57.506959        506959
1   2018-06-03 18:52:00.925554        925554
2   2018-06-03 18:54:00.436774        436774
3   2018-06-03 18:53:59.805276        805276
4   2018-06-03 18:52:02.325613        325613
5   2018-06-03 18:52:03.508673        508673
6   2018-06-03 18:54:02.061568         61568
7   2018-06-03 18:52:07.772553        772553
8   2018-06-03 18:53:52.484954        484954
9   2018-06-03 18:53:54.733274        733274
10  2018-06-03 18:54:01.549498        549498
11  2018-06-03 18:53:57.037358         37358
12  2018-06-03 18:53:58.437644        437644
13  2018-06-03 18:54:00.997659        997659
14  2018-06-03 18:53:59.181472        181472
15  2018-06-03 18:54:03.420695        420695
***********************************************
11 temp.json updated

11 Do you want to continue the operation (Y/N): n

» Third run

(py3.6) H:\RishikeshAgrawani\Projects\Python3\Pandas>python append_data_to_json-v2-py3.py
                          Time  Average Rate
0   2018-06-03 18:51:57.506959        506959
1   2018-06-03 18:52:00.925554        925554
2   2018-06-03 18:54:01.549498        549498
3   2018-06-03 18:53:57.037358         37358
4   2018-06-03 18:53:58.437644        437644
5   2018-06-03 18:54:00.997659        997659
6   2018-06-03 18:53:59.181472        181472
7   2018-06-03 18:54:03.420695        420695
8   2018-06-03 18:54:00.436774        436774
9   2018-06-03 18:53:59.805276        805276
10  2018-06-03 18:52:02.325613        325613
11  2018-06-03 18:52:03.508673        508673
12  2018-06-03 18:54:02.061568         61568
13  2018-06-03 18:52:07.772553        772553
14  2018-06-03 18:53:52.484954        484954
15  2018-06-03 18:53:54.733274        733274
16  2018-06-03 18:55:39.415698        415698
***********************************************
1 temp.json updated

1 Do you want to continue the operation (Y/N): y
                          Time  Average Rate
0   2018-06-03 18:51:57.506959        506959
1   2018-06-03 18:52:00.925554        925554
2   2018-06-03 18:52:02.325613        325613
3   2018-06-03 18:52:03.508673        508673
4   2018-06-03 18:54:02.061568         61568
5   2018-06-03 18:52:07.772553        772553
6   2018-06-03 18:53:52.484954        484954
7   2018-06-03 18:53:54.733274        733274
8   2018-06-03 18:55:39.415698        415698
9   2018-06-03 18:54:01.549498        549498
10  2018-06-03 18:53:57.037358         37358
11  2018-06-03 18:53:58.437644        437644
12  2018-06-03 18:54:00.997659        997659
13  2018-06-03 18:53:59.181472        181472
14  2018-06-03 18:54:03.420695        420695
15  2018-06-03 18:54:00.436774        436774
16  2018-06-03 18:53:59.805276        805276
17  2018-06-03 18:55:41.861641        861641
***********************************************
2 temp.json updated

2 Do you want to continue the operation (Y/N): y
                          Time  Average Rate
0   2018-06-03 18:51:57.506959        506959
1   2018-06-03 18:52:00.925554        925554
2   2018-06-03 18:53:57.037358         37358
3   2018-06-03 18:53:58.437644        437644
4   2018-06-03 18:54:00.997659        997659
5   2018-06-03 18:53:59.181472        181472
6   2018-06-03 18:54:03.420695        420695
7   2018-06-03 18:54:00.436774        436774
8   2018-06-03 18:53:59.805276        805276
9   2018-06-03 18:55:41.861641        861641
10  2018-06-03 18:52:02.325613        325613
11  2018-06-03 18:52:03.508673        508673
12  2018-06-03 18:54:02.061568         61568
13  2018-06-03 18:52:07.772553        772553
14  2018-06-03 18:53:52.484954        484954
15  2018-06-03 18:53:54.733274        733274
16  2018-06-03 18:55:39.415698        415698
17  2018-06-03 18:54:01.549498        549498
18  2018-06-03 18:55:44.381318        381318
***********************************************
3 temp.json updated

3 Do you want to continue the operation (Y/N): y
                          Time  Average Rate
0   2018-06-03 18:51:57.506959        506959
1   2018-06-03 18:52:00.925554        925554
2   2018-06-03 18:52:02.325613        325613
3   2018-06-03 18:52:03.508673        508673
4   2018-06-03 18:54:02.061568         61568
5   2018-06-03 18:52:07.772553        772553
6   2018-06-03 18:53:52.484954        484954
7   2018-06-03 18:53:54.733274        733274
8   2018-06-03 18:55:39.415698        415698
9   2018-06-03 18:54:01.549498        549498
10  2018-06-03 18:55:44.381318        381318
11  2018-06-03 18:53:57.037358         37358
12  2018-06-03 18:53:58.437644        437644
13  2018-06-03 18:54:00.997659        997659
14  2018-06-03 18:53:59.181472        181472
15  2018-06-03 18:54:03.420695        420695
16  2018-06-03 18:54:00.436774        436774
17  2018-06-03 18:53:59.805276        805276
18  2018-06-03 18:55:41.861641        861641
19  2018-06-03 18:55:45.181318        181318
***********************************************
4 temp.json updated

4 Do you want to continue the operation (Y/N): y
                          Time  Average Rate
0   2018-06-03 18:51:57.506959        506959
1   2018-06-03 18:52:00.925554        925554
2   2018-06-03 18:55:44.381318        381318
3   2018-06-03 18:53:57.037358         37358
4   2018-06-03 18:53:58.437644        437644
5   2018-06-03 18:54:00.997659        997659
6   2018-06-03 18:53:59.181472        181472
7   2018-06-03 18:54:03.420695        420695
8   2018-06-03 18:54:00.436774        436774
9   2018-06-03 18:53:59.805276        805276
10  2018-06-03 18:55:41.861641        861641
11  2018-06-03 18:55:45.181318        181318
12  2018-06-03 18:52:02.325613        325613
13  2018-06-03 18:52:03.508673        508673
14  2018-06-03 18:54:02.061568         61568
15  2018-06-03 18:52:07.772553        772553
16  2018-06-03 18:53:52.484954        484954
17  2018-06-03 18:53:54.733274        733274
18  2018-06-03 18:55:39.415698        415698
19  2018-06-03 18:54:01.549498        549498
20  2018-06-03 18:55:45.765547        765547
***********************************************
5 temp.json updated

5 Do you want to continue the operation (Y/N): n
Community
  • 1
  • 1
hygull
  • 8,464
  • 2
  • 43
  • 52
  • why is there so much duplicate code and bare exception? people might actually learn doing that from your post! – Evgeny Jun 03 '18 at 16:32
  • **@Evgeny**, I didn't get, you're talking about which part of code. – hygull Jun 03 '18 at 16:57
  • the duplicates? most of code in try and except, if you notice – Evgeny Jun 03 '18 at 17:02
  • Yea, that is correct. We can design a single block of code and place it inside that but it's just 3-4 lines of code so I thought, not to make problem more complicated with 2 function calls. So it's totally up to the programmer how he/she designs the code. Using function to remove code duplication is nice. Thanks. – hygull Jun 03 '18 at 17:07
  • Better make good programming decisions, right? Bare exception? Perhaps you know what kind of error might occur? – Evgeny Jun 03 '18 at 17:16