Add Pandas Series as a Row to Pandas DataFrame

Question

I am trying to add a Pandas.Series as a new row to a Pandas.DataFrame. However, the Series always appear to be added with its index appearing as individual rows.

How can we append it as a single row?

import pandas as pd

df = pd.DataFrame([
    ('Tom', 'male', 10),
    ('Jane', 'female', 7),
    ('Peter', 'male', 9),
], columns=['name', 'gender', 'age'])
df.set_index(['name'], inplace=True)
print(df)

       gender  age
name              
Tom      male   10
Jane   female    7
Peter    male    9

s = pd.Series(('Jon', 'male', 12), index=['name', 'gender', 'age'])
print(s)

name       Jon
gender    male
age         12
dtype: object

Expected Result

       gender  age
name              
Tom      male   10
Jane   female    7
Peter    male    9
Jon      male   12

Attempt 1

df2 = df.append(pd.DataFrame(s))
print(df2)

           0   age  gender
Tom      NaN  10.0    male
Jane     NaN   7.0  female
Peter    NaN   9.0    male
name     Jon   NaN     NaN
gender  male   NaN     NaN
age       12   NaN     NaN

Attempt #2

df2 = pd.concat([df, s], axis=0)
print(df2)

           0   age  gender
Tom      NaN  10.0    male
Jane     NaN   7.0  female
Peter    NaN   9.0    male
name     Jon   NaN     NaN
gender  male   NaN     NaN
age       12   NaN     NaN

Attempt #3

df2 = pd.concat([df, pd.DataFrame(s)], axis=0)
print(df2)

           0   age  gender
Tom      NaN  10.0    male
Jane     NaN   7.0  female
Peter    NaN   9.0    male
name     Jon   NaN     NaN
gender  male   NaN     NaN
age       12   NaN     NaN

Alexander · Accepted Answer · 2019-11-08 01:21:32Z

9

This "works", but you may want to reconsider how you are building your dataframes in the first place. If you append data, do it all at once instead of row by row.

>>> pd.concat([df, s.to_frame().T.set_index('name')])
       gender age
name             
Tom      male  10
Jane   female   7
Peter    male   9
Jon      male  12

As a column of a dataframe, a Series is generally all the same data type (e.g. age). In this case, your series represents a single row of data for a given record, e.g. a row in a database with potentially mixed types. You may want to consider your series as a dataframe row instead.

row = pd.DataFrame({'gender': 'male', 'age': 12}, 
                   index=pd.Index(['Jon'], name='name'))
>>> pd.concat([df, row])
       gender  age
name              
Tom      male   10
Jane   female    7
Peter    male    9
Jon      male   12
>>> pd.concat([df, row])

edited Nov 8, 2019 at 1:21

answered Nov 8, 2019 at 1:06

Alexander

111k32 gold badges212 silver badges208 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

metasomite Over a year ago

Depending on the scenario, you could also use direct assignment: df.loc[s['name'], ['gender', 'age']] = s[['gender', 'age']].

Nyxynyx Over a year ago

Thanks, this works. Will df.append be preferred over df.concat?

Alexander Over a year ago

@Nyxynyx That is a preference. I've heard from others that they wish the append function was never exposed as a public function, thereby forcing concat and returning a new object.

Nyxynyx Over a year ago

Do you feel that the data represented in the Pandas Series is more appropriate if a single row Pandas DataFrame was used instead?

Alexander Over a year ago

Although not strictly accurate, it is useful to think of a Series as a column in a dataframe all holding the same datatype (e.g. int, float, object, etc.). Depending on your use case, you may want to consider using a namedtuple. docs.python.org/2/library/…

Collectives™ on Stack Overflow

Add Pandas Series as a Row to Pandas DataFrame

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related