Building Dataframe from For Loop using Pandas

Question

I have a for-loop that returns the data below and I am trying to put it into a single data frame using Pandas. The issue is that each time I try I get separate data frames for each row instead of one single data frame. I'm sure the solution is simple but I just can't work it out.

For-Loop:

    for runner in i.runners:
        print(i.market_id, runner.selection_id, runner.runner_name)

Output:

1.193218924 36476468 Immortal Fame
1.193218924 27794475 After The Fox
1.193218924 21214404 Banana Joe
1.193218924 4177156 Bird On The Wire
1.193218924 24462868 Vinnies Getaway
1.193218924 28007020 Veshenskaya
1.193218924 10159485 Green Or Black
1.193218924 21001423 Definite Wisdom
1.193218924 26997545 Ribeye
1.193218924 21822171 Dorado Dollar
1.193218924 4678460 My Keepsake
1.193218924 8481071 Ballyfarsoon
1.193219097 38432851 Dynamite Kentucky
1.193219097 37413708 Voyburg
1.193219097 39371721 Dans Chosen
1.193219097 26548202 Terrierman
1.193219097 15684272 Daydream Aulmes
1.193219097 18570232 Johnny B
1.193219097 16519452 Chloes Court
1.193219097 28093620 Itacare

Attempt 1:

    for runner in i.runners:
        print(i.market_id, runner.selection_id, runner.runner_name)
        df = pd.DataFrame({
            'Market ID': i.market_id,
            'Selection ID': runner.selection_id,
            'Selection Name': runner.runner_name,
        }, index=[0])
        print(df)

Attempt 2:

    for runner in i.runners:
        d.append(
            {
                'Market ID': i.market_id,
                'Selection ID': runner.selection_id,
                'Selection Name': runner.runner_name
            }
        )

    pd.DataFrame(d)
    print(d)

Desired Output:

With for Loop to get Match IDs:

    for i in results:
         race_ids.append(i.market_id)

         for runner in i.runners:
             print(i.market_id, runner.selection_id, runner.runner_name)
             df = pd.DataFrame({
                 'Market ID': i.market_id,
                 'Selection ID': runner.selection_id,
                 'Selection Name': runner.runner_name,
             }, index=[0])
             print(df)

Thanks for the responses. They are an improvement but all seem to group by Market ID giving separate dataframes for each Market ID. Or it may be they repeat the headers in the same dataframe. Is there a way of having just one dataframe? — Robsmith
– Robsmith, Commented Jan 11, 2022 at 9:59
Is there a way of having just one dataframe? - Solutions not return one DataFrame? Is there some another loop, not posted in question? — jezrael
– jezrael, Commented Jan 11, 2022 at 10:23
Hi @jezrael , yes, there's a loop to get the Market IDs. I've edited the question to show it. — Robsmith
– Robsmith, Commented Jan 11, 2022 at 10:44
Hi @jezrael , thanks for your help. Your question sparked the solution. — Robsmith
– Robsmith, Commented Jan 11, 2022 at 10:59

score 2 · Accepted Answer · 2022-01-11 11:11:05Z

2

Use list comprehension to collect all your data and cast it to a DataFrame:

data = [[i.market_id, runner.selection_id, runner.runner_name] for i in results for runner in i.runners]
df = pd.DataFrame(data, columns = ['Market ID', 'Selection ID', 'Selection Name'])

edited Jan 11, 2022 at 11:11

answered Jan 11, 2022 at 9:45

user7864386

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

1 Comment

user7864386 Over a year ago

@Robsmith I saw your edit and edited my answer to include the outer loop as well. I think it will work now.

jezrael · Accepted Answer · 2022-01-11 11:11:49Z

1

Create list of dicts anmd pass to DataFrame constructor:

L = [ {'Market ID': i.market_id, 
       'Selection ID': runner.selection_id, 
       'Selection Name': runner.runner_name } for runner in i.runners]

Edited answer:

L = [ {'Market ID': i.market_id, 
       'Selection ID': runner.selection_id, 
       'Selection Name': runner.runner_name }  for i in results for runner in i.runners]

df = pd.DataFrame(L)

edited Jan 11, 2022 at 11:11

answered Jan 11, 2022 at 9:45

jezrael

868k102 gold badges1.4k silver badges1.3k bronze badges

Comments

Bemwa Malak · Accepted Answer · 2022-01-11 09:48:16Z

1

You can do it using a 2D list then converting this 2D list into a single data frame like this:

main_lst = list()
for runner in i.runners:
        lst = list()
        lst.append(i.market_id)
        lst.append(runner.selection_id)
        lst.append(runner.runner_name)
        main_lst.append(lst)
df = pd.DataFrame(main_lst, columns =['Market ID', 'Selection ID', 'Selection Name']) 
print(df)

answered Jan 11, 2022 at 9:48

Bemwa Malak

1,3591 gold badge10 silver badges22 bronze badges

Collectives™ on Stack Overflow

Building Dataframe from For Loop using Pandas

3 Answers 3

1 Comment

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Related