Using Pandas to Iteratively Add Columns to a Dataframe

Question

I have some relatively simple code that I'm struggling to put together. I have a CSV that I've read into a dataframe. The CSV is panel data (i.e., unique company and year observations for each row). I have two columns that I want to perform a function on and then I want to create new variables based on the output of the function.

Here's what I have so far with code:

#Loop through rows in a CSV file
for index, rows in df.iterrows():
    #Start at column 6 and go to the end of the file
    for row in rows[6:]:
        data = perform_function1( row )
        output =  perform_function2(data)    
        df.ix[index, 'new_variable'] = output
        print output

I want this code to iterate starting in column 6 and then going to the end of the file (e.g., I have two columns I want to perform the function on Column6 and Column7) and then create new columns based on the functions that were performed (e.g., Output6 and Output7). The code above returns the output for Column7, but I can't figure out how to create a variable that allows me to capture the outputs from both columns (i.e., a new variable that isn't overwritten by loop). I searched Stackoverflow and didn't see anything that immediately related to my question (maybe because I'm too big of a noob?). I would really appreciate your help.

Thanks,

TT

P.S. I'm not sure if I've provided enough detail. Please let me know if I need to provide more.

Solution 1

Operating iteratively doesn't take advantage of Pandas' capabilities. Pandas' strength is in applying operations efficiently across the whole dataframe, rather than in iterating row by row. It's great for a task like this where you want to chain a few functions across your data. You should be able to accomplish your whole task in a single line.

df["new_variable"] = df.ix[6:].apply(perform_function1).apply(perform_function2)

perform_function1 will be applied to each row, and perform_function2 will be applied to the results of the first function.

Solution 2

If you want to apply function to certain columns in a dataframe

# Get the Series
colmun6 = df.ix[:, 5]  
# perform_function1 applied to each row
output6 = column6.apply(perform_function1)  
df["new_variable"] = output6

Comments

[TaterTots]

Thanks! I tried this approach and received the following error: TypeError: ('expected string or buffer', u'occurred at index CaseNum') I believe it's because my functions were written to handle individual strings (iterating down a column) rather than applying it to an entire row.

[ASGM]

Wait, are both functions meant to apply to individual strings instead of a whole row, or just perform_function1? Maybe it would help to include your functions (if they're not too complicated).

[TaterTots]

The functions are kind of complicated. They're meant to apply to individual strings.

[ASGM]

@TaterTots what do they return? Does perform_function1 return a single value or multiple values? How about perform_function2?

[TaterTots]

Both of the functions return single values. The answer to this problem was really simple. I just had to make a dynamic variable name by adding a counter and adding this code in the second for loop: name = df.columns[i+6] df.ix[index, 'new_var' + '_' + str(name)] = perform_function2 Thanks for the help!