Using for loop for reading a json dict and write into pandas dataframe

Question

I am trying to iterate through a JSON dict and write chosen objects into a pandas Dataframe and then at the and want to calculate the correlation between the two columns.

The Dataframe should look like this:

calc  val
20.1  20
20.2  20
19.8  20
...   ...
10.1  10
10.3  10
9.8   10
...   ...
5.2   5
5.1   5
5.0   5
...   ...

My JSON dict looks like this:

{
    "20um PSL": [
            {
                "imgsize": 20.886688245888603,
                "trigsize": 20.87416236786009,
                ...
            }
                "imgsize": 20.886688245888603,
                "trigsize": 20.87416236786009,
                ...
            {...}
            ...
    ]
        
    "10um PSL": [
        {
                "imgsize": 10.886688245888603,
                "trigsize": 10.87416236786009,
                  ....
        }
        {...}
            ...
    ]
            
    "5um PSL": [
        {
                "imgsize": 5.886688245888603,
                "trigsize": 5.87416236786009,
                  ....
        }
        {...}
        ...
    ]
}

this is my code so far:

    sizes = ['20um PSL', '10um PSL', '5um PSL']
    for file in json_data[sizes[0]]:
        particles_0 = pd.DataFrame({'calc': file['trigsize'], 'val': sizes_list[0]})
    for file in json_data[sizes[1]]:
        particles_1 = pd.DataFrame({'calc': file['trigsize'], 'val': sizes_list[1]})
    for file in json_data[sizes[2]]:
        particles_2 = pd.DataFrame({'calc': file['trigsize'], 'val': sizes_list[2]})
    df = particles_0.append([particles_1, particles_2])
    df.reset_index(drop=True, inplace=True)  # reorder index

My difficulty is now that somehow always only the last 'trigsize' value is loaded into the data frame. I am aware that this is somehow a fault with my loop and I need to change the iteration. As I am a Python beginner I seem not to find the logical solution to this problem. In the end i need to calculate the correlation on how the numbers in Calc correlate with val (which is in this case always 20, 10, 5). Might there be a better solution for this? (instead of creating a dataframe?)

Dataframe so far:

calc  val
20.1  20
20.1  20
20.1  20
...   ...
10.1  10
10.1  10
10.1   10
...   ...
5.0   5
5.0   5
5.0   5
...   ...

Christian · Accepted Answer · 2021-04-19 12:27:02Z

1

As far as I see it, you are overwriting your particles_0, -_1, -_2 with every iteration of the loop. This also explains why only the last row remains.

You would have to probably first collect this data you are iterating through in a dict or something similar and then build up your data-frame once you are through everything.

Edit:

data = []
w = 0
sizes = ['20um PSL', '10um PSL', '5um PSL']
for size in sizes:
    for file in json_data[size]:
        data.append((file['trigsize'], y_verifcsize[w]))
        w += 1

df = pd.DataFrame(data, columns=["calc", "val"])

edited Apr 19, 2021 at 12:27

answered Apr 19, 2021 at 9:10

Christian

1,5712 gold badges13 silver badges17 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Lyoner Over a year ago

Hei Christian. Yes, I was aware that I am somehow overwriting my last data. But I seem to come to a point where my brain stopped being able to build logical loops. do you have a suggestion on how to do this?

Christian Over a year ago

I'm no pro myself yet but I would do it like this:

data = []  sizes = ['20um PSL', '10um PSL', '5um PSL']  for size in sizes:      for file in json_data[size]:          data.append((file['trigsize'], sizes_list[0])   df = pd.DataFrame(data, index=["calc", "val"])

Christian Over a year ago

I'm sorry - I have no idea how to tag this code so that it is properly readable. I added it as edit to my main answer.

Lyoner Over a year ago

Thanks! it worked. i had to add a counter variable in sizes list so it append to the proper size:

data = []     sizes = ['20um PSL', '10um PSL', '5um PSL']     w = 0     for size in sizes:         for file in json_data[size]:             data.append((file['trigsize'], y_verifcsize[w]))         w += 1     df = pd.DataFrame(data, columns=["calc", "val"])

(if you can add the counter i will mark it for you as answerd :) )

Christian Over a year ago

That's true - I've overlooked the index of the other variable. Thank you, I added it :)

Collectives™ on Stack Overflow

Using for loop for reading a json dict and write into pandas dataframe

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related