Add column to DataFrame in a loop

Question

Let's say I have a very simple pandas dataframe, containing a single indexed column with "initial values". I want to read in a loop N other dataframes to fill a single "comparison" column, with matching indices.

For instance, with my inital dataframe as

   Initial
0        a
1        b
2        c
3        d

and the following two dataframes to read in a loop

   Comparison
0           e
1           f
   Comparison
2           g
3           h
4           i  <= note that this index doesn't exist in Initial so won't be matched

I would like to produce the following result

    Initial Comparison
0        a           e
1        b           f
2        c           g
3        d           h

Using merge, concat or join, I only ever seem to be able to create a new column for each iteration of the loop, filling the blanks with NaN.

What's the most pandas-pythonic way of achieving this?

Below an example from the proposed duplicate solution:

import pandas as pd
import numpy as np
df1 = pd.DataFrame(np.array([['a'],['b'],['c'],['d']]), columns=['Initial'])
print df1
df2 = pd.DataFrame(np.array([['e'],['f']]), columns=['Compare'])
print df2
df3 = pd.DataFrame(np.array([[2,'g'],[3,'h'],[4,'i']]), columns=['','Compare'])
df3 = df3.set_index('')
print df3
print df1.merge(df2,left_index=True,right_index=True).merge(df3,left_index=True,right_index=True)
>>
      Initial
0       a
1       b
2       c
3       d
  Compare
0       e
1       f
  Compare

2       g
3       h
4       i
Empty DataFrame
Columns: [Initial, Compare_x, Compare_y]
Index: []

Second edit: @W-B, the following seems to work, but it can't be the case that there isn't a simpler option using proper pandas methods. It also requires turning off warnings, which might be dangerous...

pd.options.mode.chained_assignment = None
df1["Compare"]=pd.Series()
for ind in df1.index.values:
    if ind in df2.index.values:
        df1["Compare"][ind]=df2.T[ind]["Compare"]
    if ind in df3.index.values:
        df1["Compare"][ind]=df3.T[ind]["Compare"]
print df1
>>
      Initial Compare
0           a       e
1           b       f
2           c       g
3           d       h

Thanks for the link @W-B, but I don't think it's the same problem... The reduce+merge solution outlined there is to add several columns to a dataframe. Here, I want to merge several columns in one, based on the indexing. — Demosthene, Jan 28 '19 at 17:44
@W-B Sorry, but I still don't see what you mean (I've edited the post). I'm very new to this, so there's probably something obvious I'm missing... — Demosthene, Jan 28 '19 at 17:57
@W-B further edit, found a working solution, but it's really ugly and inefficient... suggestions? — Demosthene, Jan 28 '19 at 19:04
Would you like me reopen the question and provide the solution ? — BENY, Jan 28 '19 at 19:20

score 1 · Accepted Answer · answered Jan 28 '19 at 19:24

Ok , since Op need more info

Data input

import functools
df1 = pd.DataFrame(np.array([['a'],['b'],['c'],['d']]), columns=['Initial'])
df1['Compare']=np.nan
df2 = pd.DataFrame(np.array([['e'],['f']]), columns=['Compare'])
df3 = pd.DataFrame(np.array(['g','h','i']), columns=['Compare'],index=[2,3,4])

Solution

newdf=functools.reduce(lambda x,y: x.fillna(y),[df1,df2,df3])
newdf
Out[639]: 
  Initial Compare
0       a       e
1       b       f
2       c       g
3       d       h

Thanks a lot! That _is_ a much nicer way :) – Demosthene Jan 28 '19 at 19:29 — Demosthene, Jan 28 '19 at 19:29

Add column to DataFrame in a loop

1 Answers1