How to create new columns using .loc syntax?

user_8461786 0
Viewed 1167

I have a list of names of columns (cols) that exist in one dataframe.

I want to insert columns by those names in another dataframe.

So I am using a for loop to iterate the list and create the columns one by one:

cols = ['DEPTID', 'ACCOUNT', 'JRNL LINE DESCRIPTION', 'JRNL DATE', 'BASE AMOUNT', 'FOREIGN CURRENCY', 'FOREIGN AMOUNT', 'JRNL SOURCE']
for col in cols:
    # "summary" and "obiee" are dataframes
    summary.loc[obiee['mapid'], col] = obiee[col].tolist()

I would like to get rid of the for loop, however.

So I have tried multiple column assignment using the .loc syntax:

cols = ['DEPTID', 'ACCOUNT', 'JRNL LINE DESCRIPTION', 'JRNL DATE', 'BASE AMOUNT', 'FOREIGN CURRENCY', 'FOREIGN AMOUNT', 'JRNL SOURCE']
summary.loc[obiee['mapid'], cols] = obiee[cols]

but Pandas will throw an error:

KeyError: "['DEPTID' 'ACCOUNT' 'JRNL LINE DESCRIPTION' 'JRNL DATE' 'BASE AMOUNT'\n 'FOREIGN CURRENCY' 'FOREIGN AMOUNT' 'JRNL SOURCE'] not in index"

Is it not possible with this syntax? How can I do this otherwise?

pandas dataframe python
2 Answers

join

You can create a new dataframe and then join. From your problem description and sample code, 'mapid' represents index values in the summary dataframe. join is made to merge on index. So by setting obiee's index to 'mapid' then taking the the appropriate columns, we can just use join.

summary.join(obiee.set_index('mapid')[cols])
user_23366540

you have a dataFrame df1 .. with some columns...

And you want those in a df2 ... all you need to do is just equate them as show below

df2 = pd.DataFrame({ 'A' : 1.,
   ....:                      'B' : pd.Timestamp('20130102'),
   ....:                      'C' : pd.Series(1,index=list(range(4)),dtype='float32'),
   ....:                      'D' : np.array([3] * 4,dtype='int32'),
   ....:                      'E' : pd.Categorical(["test","train","test","train"]),
   ....:                      'F' : 'foo' })
df1 = pd.DataFrame({ 'G' : 1.,
   ....:                      'H' : pd.Timestamp('20130102'),
   ....:                      'I' : pd.Series(1,index=list(range(4)),dtype='float32'),
   ....:                      'J' : np.array([3] * 4,dtype='int32'),
   ....:                      'K' : pd.Categorical(["test","train","test","train"]),
   ....:                      'L' : 'foo' })
df2['G'],df2['F'] = df1['G'],df1['H']
user_66169630
Related