#🔒 .append() and pandas dataframe

43 messages · Page 1 of 1 (latest)

glass shore
#

Hi,
I make an api call using the requests module, then I append this raw https get request to this list variable. The raw data is JSON-like, not true JSON. However, getting to my problem. When I create a dataframe with this .append(raw_response.json()) then I have an additional [ ] square brackets and this is causing my dataframe to be broken. I checked some of my previous work where I did not use the .append() method and it did not have additional square brackets. I will post code snippets shorty.

fossil iglooBOT
#

@glass shore

Python help channel opened

Remember to:

  • Ask your Python question, not if you can ask or if there's an expert who can help.
  • Show a code sample as text (rather than a screenshot) and the error message, if you've got one.
  • Explain what you expect to happen and what actually happens.

:warning: Do not pip install anything that isn't related to your question, especially if asked to over DMs.

Closes after a period of inactivity, or when you send !close.
glass shore
#

Relevant code section, the pounds are not actually there.

data = []

for serial in serialNumbers:

   url = f"https://admin.*****************/trackers/{serial}/diagnostics?DateFrom={time_from}T22%3A00%3A00.000Z&DateTo={time_to}T14%3A53%3A54.485Z"
   raw_response = requests.get(url=url, headers=headers)
   # display(raw_response.status_code)
   data.append(raw_response.json())
   # data = raw_response.json()
   # display(data)

display(data)
#

The ouput snippet for this code. Note the double square brackets at the start

[[{'trackerId': 5300242,
   'time': '2024-01-02T00:00:00+00:00',
   'latitude': -24.865015029907227,
   'longitude': 30.32404136657715,
   'uptimeInDays': 106,
   'temperatureInDegreesCelcius': 21,
   'accelerometer': [0, 0, -1],
   'chargeStatus': 2,
...
#

Now, creating the dataframe

# 
df_main = pd.DataFrame(data)
pd.set_option('display.max_columns', 20)
df_main
#

And its (hideous) output

# 
0    {'trackerId': 5300242, 'time': '2024-01-02T00:...    {'trackerId': 5300242, 'time': '2024-01-02T06:...    {'trackerId': 5300242, 'time': '2024-01-02T06:...    {'trackerId': 5300242, 'time': '2024-01-02T06:...    {'trackerId': 5300242, 'time': '2024-01-02T06:...    {'trackerId': 5300242, 'time': '2024-01-02T06:...    {'trackerId': 5300242, 'time': '2024-01-02T06:...    {'trackerId': 5300242, 'time': '2024-01-02T06:...    {'trackerId': 5300242, 'time': '2024-01-02T06:...    {'trackerId': 5300242, 'time': '2024-01-02T06:...    ...    None    None    None    None    None    None    None    None    None    None
1    {'trackerId': 5400126, 'time': '2024-01-02T00:...    None    None    None    None    None    None    None    None    None```
#

You can see the columns are numbered 0 1 2 and so on

#

Now the reference from some previous work I did

#

It's code

df_main_1 = pd.DataFrame(data1)
pd.set_option('display.max_columns', 20)
df_main_1
#

and the resulting dataframe

0    5300242    2024-01-02T00:00:00+00:00    -24.865015    30.324041    106    21    [0, 0, -1]    2    51    54    Connected    0    0    -128    4078    -128    -1    0    -1.00    -1    {'apn': 'gigsky-02', 'username': '', 'password...    6593520fbd0cf00001f6ae83    2024-01-02T00:00:15Z
1    5300242    2024-01-
#

You can see the columns are trackerId time latitude and so on

#

Here is the raw response of the same https get request but without using .append

  'time': '2024-01-02T00:00:00+00:00',
  'latitude': -25.093399047851562,
  'longitude': 31.019685745239258,
  'uptimeInDays': 25,```
#

Why am I using .append now and did not previously? Because previously I had only 1 trackerId but now I have many more trackerId's and want to call the request for each tracker and in a for loop and then append that to a variable that I can use to create a dataframe

molten flicker
#

because there's a [ in front, suggesting what you have is a list

glass shore
molten flicker
#

and now you want to combine multiple trackers' data into 1 dataframe

glass shore
#

yes, if possible?

molten flicker
# glass shore yes, if possible?

well the problem right now is you have a list of lists, where each list represents a tracker data
flatten it so you just have 1 big list, then pass it to dataframe

glass shore
#

how do I flatten it?

#

If you don't mind showing me

molten flicker
#

one way is

>>> l1 = [{'a': 1, 'b': 2}, {'a': 12, 'b': 3}]
>>> l2 = [{'a': 13, 'b': 100}]
>>> import itertools
>>> merged = list( itertools.chain.from_iterable( [l1, l2] ) )
>>> list(merged)
[{'a': 1, 'b': 2}, {'a': 12, 'b': 3}, {'a': 13, 'b': 100}]
#

another

>>> all_lists = [l1, l2]
>>> flat = [value for l in all_lists for value in l]
>>> flat
[{'a': 1, 'b': 2}, {'a': 12, 'b': 3}, {'a': 13, 'b': 100}]
>>>
#

another

>>> pd.concat( pd.DataFrame(l) for l in all_lists )
    a    b
0   1    2
1  12    3
0  13  100
>>>
glass shore
#

ok, how do I access my individual lists like your example. If I understand, currently my varable data is the list of lists. So can I say?

pd.concat(pd.Dataframe(data) for l in data)
molten flicker
glass shore
#

ah

molten flicker
#

think if it like

result = []
for l in data:
    result.append(pd.DataFrame(l))
pd.concat(result)
#

the syntax's called "comprehension"

glass shore
#

interesting

#

I will try this, thank you

#

will post if I succeed or not

#

You are brilliant!

#

Excuse the sceenshot but is easiest since the result that I want to show is too large for discord

#

this look correct

#

2053 rows × 23 columns

#

I only work with python like 2 weeks in a year or when I need to write a report with data fromt he db and our front end cannot provide the info in my required way, so this is quite fun for me. I am in the embedded firmware space

fossil iglooBOT
#
Python help channel closed

This help channel has been closed and it's no longer possible to send messages here. If your question wasn't answered, feel free to create a new post in #1035199133436354600. To maximize your chances of getting a response, check out this guide on asking good questions.