iterate through rows on condition pandas python

Question

I have an excel table that i read in by data = pd.read_excel('..data.xlsx')

Block   Concentration   Name    Replicate   Value
1            100          A          1        1446
1            100          A          2        25
1            100          A          3        12
1            33           A          1        111
1            33           A          2        222
1            33           A          3        1234
...
1            0            C          3         86
..
2            100          A          1        634
2            100          A          2        93
2            100          A          3        287
2            33           A          1        97234
2            33           A          2        1222
2            33           A          3        456  
...  
2            0            D          3         9800
...
...

24           0            E         3           93948

There are 24 blocks, 4 types of concentrations and many names. There are three replicates for each 'Block|Concentration|Name' combo, which points to unique 'Value' numbers.

I created a dictionary 'd' with hashes of :

 hash{Block|Concentration|Name|Replicate} -> value

pseudo code:

 for block 1-> 24:
     for each concentration:
          print (concentration)
          for each  name:
               for replicate 1-> 3:
                   print  key of hash{Block|Concentration|Name|Replicate}

my code:

for b in data.Block:
   for c in data.Concentration:
    print(c)
    for n in data.Name:
        for r in data.Replicate:
            print(d)

the result is a mess, i think it's printing out everything for each loop.

the output structure i have in mind (output doesn't need to be in the format):

 Block1         
Concentration            Name A                  Name B          Name C..   
  100                 1446   25  12          ..   ..    ..        ...
  33                  111    222  1234       ..   ..    ..
  10                  ..                     ..   ..    ..
  0                   ..                     ..   ..    ..


Block2         
Concentration            Name A                  Name B          Name C..   
  100                 634   93  287          ..   ..    ..        ...
  33                  97234 1222 456         ..   ..    ..
  10                  ..                     ..   ..    ..
  0                   ..                     ..   ..    ..

..
..
..
Block 24 ...

Andy Hayden · Accepted Answer · 2015-11-06 18:35:55Z

1

You can use pivot_table:

In [11]: df
Out[11]:
    Block  Con Name  Replicate  Mean
0       1  100    A          1    20
1       1  100    A          2    10
2       1  100    A          3    30
3       1  100    B          1    40
4       1  100    B          2    12
5       1  100    B          3    23
6       1   33    A          1    56
7       1   33    A          2   234
8       1   33    A          3   377
9       1   33    B          1   434
10      1   33    B          2  1232
11      1   33    B          3   233

In [12]: df.pivot_table(index=["Block", "Con"], columns=["Name", "Replicate"], values="Mean")
Out[12]:
Name        A              B
Replicate   1    2    3    1     2    3
Block Con
1     33   56  234  377  434  1232  233
      100  20   10   30   40    12   23

answered Nov 6, 2015 at 18:35

Andy Hayden

378k110 gold badges640 silver badges546 bronze badges

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

3 Comments

Jessica Over a year ago

thanks! can i outfile the pivot_table using data.to_excel ? or should i use something else?

Andy Hayden Over a year ago

@Jessica yup, easiest is to use to_excel.

Jessica Over a year ago

this has been giving me a headache for many days now! thank you so much! you saved my life :) :) :) @Andy Hayden

Collectives™ on Stack Overflow

iterate through rows on condition pandas python

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related