Extract values from dataframe, put back into new dataframe, python

Question

I have the following data frame and want to extract the floats in the cells and perform calculations (in this case multiplications) and put them back into a new data frame (first row p000_1-p000_10, second row p100_1-p100_10). Is there a way to do this a simpler way instead of one after the other like I did? Here is what I did so far:

prob_all = {'prob_1': {1.0: 0.03409090909090909,
  2.0: 0.022727272727272728,
  3.0: 0.045454545454545456,
  4.0: 0.5340909090909091,
  5.0: 0.06818181818181818,
  6.0: 0.011363636363636364,
  7.0: 0.13636363636363635,
  8.0: 0.06818181818181818,
  9.0: 0.045454545454545456,
  10.0: 0.03409090909090909},
 'prob_2': {1.0: 0.045454545454545456,
  2.0: 0.011363636363636364,
  3.0: 0.03409090909090909,
  4.0: 0.4659090909090909,
  5.0: 0.11363636363636363,
  6.0: 0.045454545454545456,
  7.0: 0.1590909090909091,
  8.0: 0.045454545454545456,
  9.0: 0.03409090909090909,
  10.0: 0.045454545454545456},
 'prob_3': {1.0: 0.1111111111111111,
  2.0: nan,
  3.0: 0.06349206349206349,
  4.0: 0.3968253968253968,
  5.0: 0.07936507936507936,
  6.0: nan,
  7.0: 0.19047619047619047,
  8.0: 0.1111111111111111,
  9.0: nan,
  10.0: 0.047619047619047616},
 'prob_4': {1.0: nan,
  2.0: nan,
  3.0: 0.043478260869565216,
  4.0: 0.391304347826087,
  5.0: 0.13043478260869565,
  6.0: nan,
  7.0: 0.08695652173913043,
  8.0: 0.2608695652173913,
  9.0: nan,
  10.0: 0.08695652173913043}}

prob_all = pd.DataFrame.from_dict(prob_all)

p000_1 = prob_all.iat[0,0]
p000_2 = prob_all.iat[1,0]
p000_3 = prob_all.iat[2,0]
p000_4 = prob_all.iat[3,0]
p000_5 = prob_all.iat[4,0]
p000_6 = prob_all.iat[5,0]
p000_7 = prob_all.iat[6,0]
p000_8 = prob_all.iat[7,0]
p000_9 = prob_all.iat[8,0]
p000_10 = prob_all.iat[9,0]

p100_1 = p000_1*prob_all.iat[0,1]
p100_2 = p000_1*prob_all.iat[1,1]
p100_3 = p000_1*prob_all.iat[2,1]
p100_4 = p000_1*prob_all.iat[3,1]
p100_5 = p000_1*prob_all.iat[4,1]
p100_6 = p000_1*prob_all.iat[5,1]
p100_7 = p000_1*prob_all.iat[6,1]
p100_8 = p000_1*prob_all.iat[7,1]
p100_9 = p000_1*prob_all.iat[8,1]
p100_10 = p000_1*prob_all.iat[9,1]
p200_1 = p000_2*prob_all.iat[0,1]
p200_2 = p000_2*prob_all.iat[1,1]
p200_3 = p000_2*prob_all.iat[2,1]
p200_4 = p000_2*prob_all.iat[3,1]
p200_5 = p000_2*prob_all.iat[4,1]
p200_6 = p000_2*prob_all.iat[5,1]
p200_7 = p000_2*prob_all.iat[6,1]
p200_8 = p000_2*prob_all.iat[7,1]
p200_9 = p000_2*prob_all.iat[8,1]
p200_10 = p000_2*prob_all.iat[9,1]
p300_1 = p000_3*prob_all.iat[0,1]
p300_2 = p000_3*prob_all.iat[1,1]
p300_3 = p000_3*prob_all.iat[2,1]
p300_4 = p000_3*prob_all.iat[3,1]
p300_5 = p000_3*prob_all.iat[4,1]
p300_6 = p000_3*prob_all.iat[5,1]
p300_7 = p000_3*prob_all.iat[6,1]
p300_8 = p000_3*prob_all.iat[7,1]
p300_9 = p000_3*prob_all.iat[8,1]
p300_10 = p000_3*prob_all.iat[9,1]

p110_1 = p100_1*prob_all.iat[0,2]
p110_2 = p100_1*prob_all.iat[1,2]
p110_3 = p100_1*prob_all.iat[2,2]
p110_4 = p100_1*prob_all.iat[3,2]
p110_5 = p100_1*prob_all.iat[4,2]
p110_6 = p100_1*prob_all.iat[5,2]
p110_7 = p100_1*prob_all.iat[6,2]
p110_8 = p100_1*prob_all.iat[7,2]
p110_9 = p100_1*prob_all.iat[8,2]
p110_10 = p100_1*prob_all.iat[9,2]

p120_1 = p100_2*prob_all.iat[0,2]
p120_2 = p100_2*prob_all.iat[1,2]
p120_3 = p100_2*prob_all.iat[2,2]
p120_4 = p100_2*prob_all.iat[3,2]
p120_5 = p100_2*prob_all.iat[4,2]
p120_6 = p100_2*prob_all.iat[5,2]
p120_7 = p100_2*prob_all.iat[6,2]
p120_8 = p100_2*prob_all.iat[7,2]
p120_9 = p100_2*prob_all.iat[8,2]
p120_10 = p100_2*prob_all.iat[9,2]

p130_1 = p100_3*prob_all.iat[0,2]
p130_2 = p100_3*prob_all.iat[1,2]
p130_3 = p100_3*prob_all.iat[2,2]
p130_4 = p100_3*prob_all.iat[3,2]
p130_5 = p100_3*prob_all.iat[4,2]
p130_6 = p100_3*prob_all.iat[5,2]
p130_7 = p100_3*prob_all.iat[6,2]
p130_8 = p100_3*prob_all.iat[7,2]
p130_9 = p100_3*prob_all.iat[8,2]
p130_10 = p100_3*prob_all.iat[9,2]

p210_1 = p200_1*prob_all.iat[0,2]
p210_2 = p200_1*prob_all.iat[1,2]
p210_3 = p200_1*prob_all.iat[2,2]
p210_4 = p200_1*prob_all.iat[3,2]
p210_5 = p200_1*prob_all.iat[4,2]
p210_6 = p200_1*prob_all.iat[5,2]                                                                                                                          
p210_7 = p200_1*prob_all.iat[6,2]
p210_8 = p200_1*prob_all.iat[7,2]
p210_9 = p200_1*prob_all.iat[8,2]
p210_10 = p200_1*prob_all.iat[9,2]

#probability of third job being 1,2,3,4,5,6,7,8,9,10 - given 1st job was 2, 2nd job was 2 - 2-2-0
p220_1 = p200_2*prob_all.iat[0,2]
p220_2 = p200_2*prob_all.iat[1,2]
p220_3 = p200_2*prob_all.iat[2,2]
p220_4 = p200_2*prob_all.iat[3,2]
p220_5 = p200_2*prob_all.iat[4,2]
p220_6 = p200_2*prob_all.iat[5,2]
p220_7 = p200_2*prob_all.iat[6,2]
p220_8 = p200_2*prob_all.iat[7,2]
p220_9 = p200_2*prob_all.iat[8,2]
p220_10 = p200_2*prob_all.iat[9,2]

#probability of third job being 1,2,3,4,5,6,7,8,9,10 - given 1st job was 2, 2nd job was 3 - 2-3-0
p230_1 = p200_3*prob_all.iat[0,2]
p230_2 = p200_3*prob_all.iat[1,2]
p230_3 = p200_3*prob_all.iat[2,2]
p230_4 = p200_3*prob_all.iat[3,2]
p230_5 = p200_3*prob_all.iat[4,2]
p230_6 = p200_3*prob_all.iat[5,2]
p230_7 = p200_3*prob_all.iat[6,2]
p230_8 = p200_3*prob_all.iat[7,2]
p230_9 = p200_3*prob_all.iat[8,2]
p230_10 = p200_3*prob_all.iat[9,2]

p000 = [p000_1,p000_2,p000_3,p000_4,p000_5,p000_6,p000_7,p000_8,p000_9,p000_10]
p100 = [p100_1, p100_2, p100_3, p100_4, p100_5, p100_6, p100_7, p100_8, p100_9, p100_10]
p200 = [p200_1, p200_2, p200_3, p200_4, p200_5, p200_6, p200_7, p200_8, p200_9, p200_10]
p300 = [p300_1, p300_2, p300_3, p300_4, p300_5, p300_6, p300_7, p300_8, p300_9, p300_10]
p110 = [p110_1, p110_2, p110_3, p110_4, p110_5, p110_6, p110_7, p110_8, p110_9, p110_10]
p120 = [p120_1, p120_2, p120_3, p120_4, p120_5, p120_6, p120_7, p120_8, p120_9, p120_10]
p130 = [p130_1, p130_2, p130_3, p130_4, p130_5, p130_6, p130_7, p130_8, p130_9, p130_10]
p210 = [p210_1, p210_2, p210_3, p210_4, p210_5, p210_6, p210_7, p210_8, p210_9, p210_10]
p220 = [p220_1, p220_2, p220_3, p220_4, p220_5, p220_6, p220_7, p220_8, p220_9, p220_10]
p230 = [p230_1, p230_2, p230_3, p230_4, p230_5, p230_6, p230_7, p230_8, p230_9, p230_10]

df = pd.DataFrame.from_records([p000,p100, p200, p300, p110, p120, p130, p210, p220, p230])

iat is used for dataframe not for dictionaries. Please edit your question and make it more clear — Prakash Dahal
– Prakash Dahal, Commented Jul 1, 2021 at 7:28
I tried to make a reproducible example out of my data frame, is there another way to do this? — Theresa_S
– Theresa_S, Commented Jul 1, 2021 at 7:34

Anurag Dabas · Accepted Answer · 2021-07-01 15:23:05Z

1

Try:

via list comprehension,concat(),DataFrame() method and T attribute:

out=[prob_all['prob_1']]+[prob_all['prob_2']*prob_all['prob_1'].iloc[x] for x in range(0,3)]
out=pd.concat(out,axis=1)
out=(out.join(pd.concat([prob_all['prob_3']*out.iloc[x,1] for x in range(0,3)],axis=1))
        .join(pd.concat([prob_all['prob_3']*out.iloc[x,2] for x in range(0,3)],axis=1),rsuffix='x')).values
out=pd.DataFrame(out).T

Now if you print out you will get your desired output

edited Jul 1, 2021 at 15:23

answered Jul 1, 2021 at 8:26

Anurag Dabas

24.3k9 gold badges25 silver badges41 bronze badges

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

9 Comments

Theresa_S Over a year ago

Thanks a lot, it worked fine for the sample data I gave but how do I do it if I want to add more rows? I updated the sample data and included more

Anurag Dabas Over a year ago

@Theresa_S updated answer....kindly have a look :)

Theresa_S Over a year ago

Thank you so much, just one more question, I am struggling to apply this to my whole dataset: what if I want to have p110_1-p110_10, p120_1-p120_10, p130_1- p130_10 as the next rows (see updated question about how they are calculated, they take p100_1, p100_2, p100_3 and multiply with the value in the dataframe)

Anurag Dabas Over a year ago

@Theresa_S I saw your updated question but don't find any change In previous version and this version(that you updated)

Theresa_S Over a year ago

I added p110_1-p110_10, p120_1-p120_10, p130_1- p130_10

|

Alperen Enes Bayar · Accepted Answer · 2021-07-01 07:29:36Z

0

p000 = []
for i in range(dataframe_row_count):
   for j in range(dataframe_column_count):
      p000.append(prob_all.iat[j,i])

Does this kind logic solve your problem?

answered Jul 1, 2021 at 7:29

Alperen Enes Bayar

899 bronze badges

3 Comments

Theresa_S Over a year ago

It looks helpful but I'm not sure what dataframe_row_count in your example is

Alperen Enes Bayar Over a year ago

They are integers. Where you specify how many rows and how many columns you will process. You can get them by Dataframe.shape[0] or np_array.shape[0].

Theresa_S Over a year ago

Ok thanks! This creates a list but I want to do it for many rows and then have all lists as rows in a dataframe. Is this also possible in this way?

Collectives™ on Stack Overflow

Extract values from dataframe, put back into new dataframe, python

2 Answers 2

9 Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

9 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related