How do I store results of a loop into one (1) dataframe?

Question

I have some python code which runs a loop across different "databases" and stores each database separately. How do I save the content from he ten (10) loops into one daataframe that I can save / export or push to Google Sheets?

Here's a preview of my code below:

USERNAME = 'email'
PASSWORD = 'password'
DATABASES = [ 'DB1', 'DB2', 'DB3', 'DB4', 'DB5', 'DB6', 'DB7', 'DB8', 'DB9', 'DB10' ]

def get_dataframe(db):
    client = my.API(username=USERNAME, password=PASSWORD, database=db)
    client.authenticate()

    device_status = client.get("DeviceStatusInfo")
    device_serial = client.get("Device")


    df = pandas.DataFrame(device_serial)
    ids = list(df['id'])
    sn = list(df['serialNumber'])
    nm = list(df['name'])
    cm = list(df['comment'])
    dt = list(df['deviceType'])

    di = pandas.DataFrame(device_status)
    device_ids = di['device']

    dev_id = []
    for device_id in device_ids:
        dev_id.append(device_id['id'])

    di['id'] = pandas.Series(dev_id)

    d = {
        'id':ids,
        'serialNumber':sn,
        'name':nm,
        'comment':cm,
        'deviceType':dt
    }


    df2 = pandas.DataFrame(d)
    dfo = pandas.merge(di, df2, on='id')

    print(dfo)

    return dfo

In addition, here's the loop below:

def main():

    for db in DATABASES:
        df = get_dataframe(db)

        gc = gspread.service_account(filename='creds.json')
        sh = gc.open_by_key('1vToay2IueDcV0gzu6MCehcMUU6Frea1fa3lqjpoKdSs')
        worksheet = sh.get_worksheet(0)

        dfo = pandas.DataFrame(df)
        set_with_dataframe(worksheet, dfo)

if __name__ == '__main__':
    main()

Any help from anyone would be greatly appreciated! Thanks in advance!

EldinAyal · Accepted Answer · 2021-05-20 07:25:08Z

1

Im not sure i understood your question completely and I can't add a comment still but have you tried creating an empty data frame outside of the loop and then using concat, for example:

def main():
final_df=pandasd.DataFrame(columns=['your column names'])
for db in DATABASES:
    df = get_dataframe(db)
    final_df=pandas.concat([df,final_df])

gc = gspread.service_account(filename='creds.json')
sh = gc.open_by_key('1vToay2IueDcV0gzu6MCehcMUU6Frea1fa3lqjpoKdSs')
worksheet = sh.get_worksheet(0)
set_with_dataframe(worksheet, final_df)


if __name__ == '__main__':
    main()

P.S: this method is slow and a better way would be always to store your databases as numpy arrays and only creating the data frame after the loops ,as working with pandas data frame is slow.

answered May 20, 2021 at 7:25

EldinAyal

965 bronze badges

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

1 Comment

saff1901 Over a year ago

Thanks! I got it to work using the following concat method: def main(): dfo = pd.concat([get_dataframe(db) for db in DATABASES]) gc = gspread.service_account(filename='creds.json') sh = gc.open_by_key('1vToay2IueDcV0gzu6MCehcMUU6Frea1fa3lqjpoKdSs') worksheet = sh.get_worksheet(0) set_with_dataframe(worksheet, dfo)

Collectives™ on Stack Overflow

How do I store results of a loop into one (1) dataframe?

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related