Add new column elements to explicitly to existing row in Pandas DataFrame (Python 2)

Question

I'm trying to add columns to a row like in Adding new column to existing DataFrame in Python pandas

I want to go from:
   a  b  c
A  1  2  3
B -1 -2 -3


   a  b  c  d  e  f  g
A  1  2  3  4  5  6  7
B -1 -2 -3 -4 -5 -6 -7

I have defined values that I want for the columns/row nodes. I want to explicitly add to a row with a given index to increase the number of columns (e.g. DF.ix["A"] = DF.ix["A"] + pd.Series([4,5,6,7],name="A",index=list("defg"))

The code below works, but how can I do this WITHOUT creating a new DataFrame and concatenating them? My real values are HUGE and I'd like to avoid creating an unncecessary DataFrame object if I can.

#!/usr/bin/python
import pandas as pd

X = [[1,2,3],[-1,-2,-3]]

r_labels = ["A","B"]         
c_labels = ["a","b","c"]

DF = pd.DataFrame(X)
DF.index = r_labels
DF.columns = c_labels

#I want to add columns [d,e,f,g] to each row
row1 = [('d', 4), ('e', 5), ('f', 6), ('g', 7)]
row2 = [('d', -4), ('e', -5), ('f', -6), ('g', -7)]
DF_tmp = pd.DataFrame()
for r_label,row in zip(DF.index,[row1,row2]):
    SR_row = pd.Series([x[1] for x in row],name=r_label,index=[x[0] for x in row])
    DF_tmp = DF_tmp.append(SR_row)

DF = pd.concat([DF, DF_tmp], axis=1, join_axes=[DF.index])#, how='outer', on=DF_tmp.columns)

EdChum · Answer 1 · 2015-11-03T22:04:19.193

1

If all you're wanting to do is add some random ints with some new columns then you can just do:

In [16]:
pd.concat([DF, pd.DataFrame(columns=list('defg'), data=np.random.randint(0, 4,(2,4)), index=DF.index)], axis=1)

Out[16]:
   a  b  c  d  e  f  g
A  1  2  3  0  2  1  3
B -1 -2 -3  3  0  3  3

EDIT

Make a list of list of your desired row values and construct a df and concat them:

In [20]:
new_A = [4,5,6,7]
new_B = [-4,-5,-6,-7]
l=[new_A,new_B]
l

Out[20]:
[[4, 5, 6, 7], [-4, -5, -6, -7]]

In [21]:
pd.concat([DF, pd.DataFrame(columns=list('defg'), data=l, index=DF.index)], axis=1)

Out[21]:
   a  b  c  d  e  f  g
A  1  2  3  4  5  6  7
B -1 -2 -3 -4 -5 -6 -7

edited Nov 03 '15 at 22:04

answered Nov 03 '15 at 21:41

EdChum

376,765
198
813
562

My apologies, I thought I made mentioned that the values are actual predetermined values. I changed that part of the question to make it less confusing. I have specific entries for the new columns that need to go to a specific row label. – O.rka Nov 03 '15 at 21:58
Thanks for your edit, how can I explicitly add it to row A instead of assuming that the matrices are ordered correctly? Is there anyway you can `DF.ix[r_label] = DF.ix[r_label].append(SR_row)` where `SR_row.index = list("defg")`? – O.rka Nov 03 '15 at 22:16
1

You'd have to construct a new df and merge on the index to do that and I advise against it for any large dfs, besides adding a row at a time is non-performant. It's better to construct the entire df and concat them as I've shown – EdChum Nov 03 '15 at 22:38

wflynny · Answer 2 · 2015-11-03T21:53:27.703

1

Since you're already looping, why not just:

In [1]: df = pd.DataFrame([[1,2,3],[-1,-2,-3]], columns=list('abc'), index=list('AB'))

In [2]: for c in 'defg':
            df[c] = np.random.rand(len(df))

In [3]: df
Out[3]: 
   a  b  c         d         e         f         g
A  1  2  3  0.173682  0.332771  0.151931  0.250421
B -1 -2 -3  0.916427  0.664965  0.961631  0.512067

Edit:

In light of your comment, you could always change it to be something like:

In [2]: for c, data in zip('defg', (d_data, e_data, f_data, g_data):
            df[c] = pd.Series(data, index=df.index)

edited Nov 03 '15 at 21:53

answered Nov 03 '15 at 21:42

wflynny

18,065
5
46
67

I like this, how does it specify A or B ? I'm not actually using random values, they are defined and pretty complicated but I can fix that in my question. Sorry for the confusion. – O.rka Nov 03 '15 at 21:48
I'm trying your edit, and I'm still not sure where row "A" and row "B" are being specified for the assignment – O.rka Nov 03 '15 at 22:02

score 1 · Accepted Answer · answered Nov 03 '15 at 22:37

You can also create an other DataFrame DF2 with your predetermined values and add the columns of DF2 to your original DataFrame DF.

DF2 = pd.DataFrame(
    [[11,12,13,14],
    [15,16,17,18]], 
    columns = ('x', 'y', 'z', 'u'), index=['A', 'B'])
DF2
    x   y   z   u
A   11  12  13  14
B   15  16  17  18
DF[['d', 'e', 'f', 'g']] = DF2[['x', 'y', 'z', 'u']]
DF
a   b   c   d   e   f   g
A   1   2   3   11  12  13  14
B   -1  -2  -3  15  16  17  18

Add new column elements to explicitly to existing row in Pandas DataFrame (Python 2)

3 Answers3