Why does concat Series to DataFrame with index matching columns not work?

Question

I want to append a Series to a DataFrame where Series's index matches DataFrame's columns using pd.concat, but it gives me surprises:

df = pd.DataFrame(columns=['a', 'b'])
sr = pd.Series(data=[1,2], index=['a', 'b'], name=1)
pd.concat([df, sr], axis=0)
Out[11]: 
     a    b    0
a  NaN  NaN  1.0
b  NaN  NaN  2.0

What I expected is of course:

df.append(sr)
Out[14]: 
   a  b
1  1  2

It really surprises me that pd.concat is not index-columns aware. So is it true that if I want to concat a Series as a new row to a DF, then I can only use df.append instead?

@juanpa.arrivillaga didn't work either. As it turns out it has the same output as when axis=0. — Vim, Nov 03 '17 at 07:15

score 10 · Accepted Answer · answered Nov 03 '17 at 07:14

10

Need DataFrame from Series by to_frame and transpose:

a = pd.concat([df, sr.to_frame(1).T])
print (a)
   a  b
1  1  2

Detail:

print (sr.to_frame(1).T)
   a  b
1  1  2

Or use setting with enlargement:

df.loc[1] = sr
print (df)
   a  b
1  1  2

answered Nov 03 '17 at 07:14

jezrael

822,522
95
1,334
1,252

score 1 · Answer 2 · answered Apr 08 '22 at 12:46

1

"df.loc[1] = sr" will drop the column if it isn't in df

df = pd.DataFrame(columns = ['a','b'])
sr = pd.Series({'a':1,'b':2,'c':3})
df.loc[1] = sr

df will be like:

   a  b
1  1  2

answered Apr 08 '22 at 12:46

abcdehc

11
3

Why does concat Series to DataFrame with index matching columns not work?

2 Answers2

Linked