Pandas Dataframe looping

Question

I'm stuck with the code below. Basically, I am looping through the first column of 2 dataframes with the loop variables 'analyticalname' and 'fedbatchname'. If these two variables find a match in each dataframe, then I want to access that element of the dataframe based on the value of the loop variable. When I run the code, however, I get a key error that originates at the line after the if statement. In essence, I am asking how to access an element of a dataframe based on a loop variable. Any suggestions?

import pandas as pd
analyticaldata = pd.read_csv('SE-HPLC.csv', usecols = ['Sample ID','% 
Aggregate','% Monomer','% Fragment']) #loading data into pandas DataFrame
fedbatchdata = pd.read_csv('Culture Day Sheet.csv',usecols = 
['RUN_NUMBER','% Aggregate','% Monomer','% Fragment'])

for analyticalname in analyticaldata['Sample ID']: #for each element in 
first column of SE-HPLC worksheet
for fedbatchname in fedbatchdata['RUN_NUMBER']: #for each element in first column of Culture Day worksheet
    if analyticalname == fedbatchname: #if any of the names match
        fedbatchdata.ix[fedbatchdata.fedbatchname,'% Aggregate'] = analyticaldata[analyticalname]['% Aggregate']
        fedbatchdata.ix[fedbatchdata.fedbatchname,'% Monomer'] = analyticaldata.ix[analyticalname,'% Monomer']                  
        fedbatchdata.ix[fedbatchdata.fedbatchname,'% Fragment'] = analyticaldata.ix[analyticalname,'% Fragment']

EDIT: Here is some sample data for both dataframes, I apologize for leaving that out. The fedbatch dataframe is meant to be empty, I am trying to copy the values from the analytical dataframe into the fedbatch dataframe.

Analytical dataframe:
SAMPLE_ID:    % Aggregate    % Monomer    % Fragment
A               2             4             1.5
B               1             4             6
C               5             5             2.1
D               3             7.1           10

Fed Batch Dataframe:
RUN_NUMBER:    % Aggregate    % Monomer    % Fragment
B                                        
A                                                                                   
C
D

Can you add some data sample? It looks like need analyticaldata.merge(fedbatchdata, left_on=['Sample ID'], right_on=['RUN_NUMBER']) — jezrael
– jezrael, Commented Oct 15, 2018 at 13:16
Would I have to merge the two dataframes just to simply access an element of a dataframe? I also don't understand how to add sample data, since it is in excel and when I paste the cells, it loses the excel format — A.P
– A.P, Commented Oct 15, 2018 at 13:25
Check how to provide a great pandas example and minimal, complete, and verifiable example — jezrael
– jezrael, Commented Oct 15, 2018 at 13:26
So analyticaldata.merge(fedbatchdata, left_on=['Sample ID'], right_on=['RUN_NUMBER'], how='left') working? — jezrael
– jezrael, Commented Oct 15, 2018 at 13:37

jezrael · Accepted Answer · 2018-10-15 14:04:30Z

1

Use merge with rename column for avoid it in output DataFrame with select only column RUN_NUMBER from fedbatchdata:

df = (fedbatchdata[['RUN_NUMBER']]
                    .merge(analyticaldata.rename(columns={'Sample ID':'RUN_NUMBER'}), 
                          on=['RUN_NUMBER'], 
                          how='left'))
print (df)
  RUN_NUMBER  % Aggregate  % Monomer  % Fragment
0          B            1        4.0         6.0
1          A            2        4.0         1.5
2          C            5        5.0         2.1
3          D            3        7.1        10.0

edited Oct 15, 2018 at 14:04

answered Oct 15, 2018 at 13:42

jezrael

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

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

12 Comments

A.P Over a year ago

The code would work if the values in the RUN_NUMBERS and SAMPLE_ID's were in the same order, but they are not, so I believe I have to loop through the column to find matches, then copy the data, no?

A.P Over a year ago

I am getting another error: KeyError: 'RUN_NUMBER'. Any suggestions @jezrael

jezrael Over a year ago

What is print (fedbatchdata.columns) ?

A.P Over a year ago

This is what the complier is giving me: Index(['RUN_NUMBER', '% Aggregate', '% Monomer', '% Fragment'], dtype='object')

A.P Over a year ago

Ok thank you very much! I learned a lot about csv and pandas!

|

Collectives™ on Stack Overflow

Pandas Dataframe looping

1 Answer 1

12 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

12 Comments

Your Answer

Sign up or log in

Post as a guest

Related