Python Append Data from Loop into Data frame

user_12189569 0
Viewed 92

I created this code where I am able to pull the data I want but not able to sort it as it should be. I am guessing it has to do with the way I am appending each item by ignoring index but I can't find my way around it.

This is my code:

import json 
import pandas as pd 


#load json object
with open("c:\Sample.json","r",encoding='utf-8') as file:
    data = file.read()
    data2 = json.loads(data)    
    print("Type:", type(data2))
    
cls=['Image', 'Email', 'User', 'Members', 'Time']
df = pd.DataFrame(columns = cls )
for d in data2['mydata']:  
    for k,v in d.items():
        #print(k)
        if k == 'attachments':
            #print(d.get('attachments')[0]['id'])
            image = (d.get('attachments')[0]['id'])            
            df=df.append({'Image':image},ignore_index = True)
            #df['Message'] = image
            
        if k == 'author_user_email':
            #print(d.get('author_user_email'))
            email = (d.get('author_user_email'))           
            df=df.append({'Email':email}, ignore_index = True)
            #df['Email'] = email
            
        if k == 'author_user_name':
            #print(d.get('author_user_name'))
            user = (d.get('author_user_name'))            
            df=df.append({'User':user}, ignore_index = True)
            #df['User'] = user
        
        if k == 'room_name':
            #print(d.get('room_name'))
            members = (d.get('room_name'))            
            df=df.append({'Members':members}, ignore_index = True)
            #df['Members'] = members
            
        if k == 'ts_iso':
            #print(d.get('ts_iso'))
            time = (d.get('ts_iso'))
            df=df.append({'Time':time}, ignore_index = True)
            #df['Time'] = time
df

print('Finished getting Data')
df1 = (df.head())
print(df)
print(df.head())
df.to_csv(r'c:\sample.csv', encoding='utf-8')

The code gives me this as the result

Result

I am looking to get this

Desired Result

Data of the file is this:

{
 "mydata": [
    {
    "attachments": [
      {
        "filename": "image.png",
        "id": "888888888"
      }
    ],
    "author_user_email": "email@email.com",
    "author_user_id": "91",
    "author_user_name": "Marlone",
    "message": "",
    "room_id": "999",
    "room_members": [
      {
        "room_member_id": "91",
        "room_member_name": "Marlone"
      },
      {
        "room_member_id": "9191",
        "room_member_name": " +16309438985"
      }
    ],
    "room_name": "SMS [Marlone] [ +7777777777]",
    "room_type": "sms",
    "ts": 55,
    "ts_iso": "2021-06-13T18:17:32.877369+00:00"
  },
  {
    "author_user_email": "email@email.com",
    "author_user_id": "21",
    "author_user_name": "Chris",
    "message": "Hi",
    "room_id": "100",
    "room_members": [
      {
        "room_member_id": "21",
        "room_member_name": "Joe"
      },
      {
        "room_member_id": "21",
        "room_member_name": "Chris"
      }
    ],
    "room_name": "Direct [Chris] [Joe]",
    "room_type": "direct",
    "ts": 12345678910,
    "ts_iso": "2021-06-14T14:42:07.572479+00:00"
  }]}

Any help would be appreciated. I am new to python and am learning on my own.

pandas dataframe append python json
3 Answers

Try:

import json
import pandas as pd

with open("your_data.json", "r") as f_in:
    data = json.load(f_in)

tmp = []
for d in data["mydata"]:
    image = d.get("attachments", [{"id": None}])[0]["id"]
    email = d.get("author_user_email")
    user = d.get("author_user_name")
    members = d.get("room_name")
    time = d.get("ts_iso")
    tmp.append((image, email, user, members, time))

df = pd.DataFrame(tmp, columns=["Image", "Email", "User", "Members", "Time"])
print(df)

Prints:

       Image            Email     User                       Members                              Time
0  888888888  email@email.com  Marlone  SMS [Marlone] [ +7777777777]  2021-06-13T18:17:32.877369+00:00
1       None  email@email.com    Chris          Direct [Chris] [Joe]  2021-06-14T14:42:07.572479+00:00
user_100359850

Although the other answer does work, pandas has a built in reader for json files pd.read_json: https://pandas.pydata.org/pandas-docs/version/1.1.3/reference/api/pandas.read_json.html

It has the benefit of being able to handle very large datasets via chunking, as well as processing quite a few different formats. The other answer would not be performant for a large dataset.

This would get you started:

import pandas as pd

df = pd.read_json("c:\Sample.json")
user_91906400

The probblem is that append() adds a new row. So, you have to use at[] https://pandas.pydata.org/docs/reference/api/pandas.DataFrame.at.html specifying the index/row. Se below. Some print/debug messages were left and path to input and output files was changed a little because I'm on Linux.

import json 
import pandas as pd
import pprint as pp


#load json object
with open("Sample.json","r",encoding='utf-8') as file:
    data = file.read()
    data2 = json.loads(data)    
    #pp.pprint(data2)
    
cls=['Image', 'Email', 'User', 'Members', 'Time']
df = pd.DataFrame(columns = cls )
pp.pprint(df)
index = 0
for d in data2['mydata']:  
    for k,v in d.items():
        #print(k)
        if k == 'attachments':
            #print(d.get('attachments')[0]['id'])
            image = (d.get('attachments')[0]['id'])            
            df.at[index, 'Image'] = image
            #df['Message'] = image
            
        if k == 'author_user_email':
            #print(d.get('author_user_email'))
            email = (d.get('author_user_email'))           
            df.at[index, 'Email'] = email
            #df['Email'] = email
            
        if k == 'author_user_name':
            #print(d.get('author_user_name'))
            user = (d.get('author_user_name'))            
            df.at[index, 'User'] = user
            #df['User'] = user
        
        if k == 'room_name':
            #print(d.get('room_name'))
            members = (d.get('room_name'))            
            df.at[index, 'Members'] = members
            #df['Members'] = members
            
        if k == 'ts_iso':
            #print(d.get('ts_iso'))
            time = (d.get('ts_iso'))
            df.at[index, 'Time'] = time
            #df['Time'] = time
    index += 1

# start indexing from 0
df.reset_index()
# replace empty str/cells witn None
df.fillna('None', inplace=True)
pp.pprint(df)



print('Finished getting Data')
df1 = (df.head())
print(df)
print(df.head())
df.to_csv(r'sample.csv', encoding='utf-8')
user_109193700
Related