14

I can create a new row in a dataframe using .loc():

>>> df = pd.DataFrame({'a':[10, 20], 'b':[100,200]}, index='1 2'.split())
>>> df
    a    b
1  10  100
2  20  200
>>> df.loc[3, 'a'] = 30
>>> df
      a      b
1  10.0  100.0
2  20.0  200.0
3  30.0    NaN

But how can I create more than one row using the same method?

>>> df.loc[[4, 5], 'a'] = [40, 50]
...
KeyError: '[4 5] not in index'

I'm familiar with .append() but am looking for a way that does NOT require constructing a new row into a Series before having it appended to df.

Desired input:

>>> df.loc[[4, 5], 'a'] = [40, 50]

Desired output

      a      b
1  10.0  100.0
2  20.0  200.0
3  30.0    NaN
4  40.0    NaN
5  50.0    NaN

Where last 2 rows are newly added.

Henry Ecker
  • 34,399
  • 18
  • 41
  • 57
Zhang18
  • 4,800
  • 10
  • 50
  • 67

3 Answers3

6

Admittedly, this is a very late answer, but I have had to deal with a similar problem and think my solution might be helpful to others as well.

After recreating your data, it is basically a two-step approach:

  1. Recreate data:

    import pandas as pd
df = pd.DataFrame({'a':[10, 20], 'b':[100,200]}, index='1 2'.split())
df.loc[3, 'a'] = 30

  2. Extend the df.index using .reindex:

    idx = list(df.index)
new_rows = list(map(str, range(4, 6)))  # easier extensible than new_rows = ["4", "5"]
idx.extend(new_rows)
df = df.reindex(index=idx)

  3. Set the values using .loc:

    df.loc[new_rows, "a"] = [40, 50]

    giving you

    >>> df
      a      b
1  10.0  100.0
2  20.0  200.0
3  30.0    NaN
4  40.0    NaN
5  50.0    NaN
apitsch
  • 1,532
  • 14
  • 31
1

Example data 

>>> data = pd.DataFrame({
    'a': [10, 6, -3, -2, 4, 12, 3, 3], 
    'b': [6, -3, 6, 12, 8, 11, -5, -5], 
    'id': [1, 1, 1, 1, 6, 2, 2, 4]})

Case 1 Note that range can be altered to whatever it is that you desire.

>>> for i in range(10):
...     data.loc[i, 'a'] = 30
... 
>>> data
      a     b   id
0  30.0   6.0  1.0
1  30.0  -3.0  1.0
2  30.0   6.0  1.0
3  30.0  12.0  1.0
4  30.0   8.0  6.0
5  30.0  11.0  2.0
6  30.0  -5.0  2.0
7  30.0  -5.0  4.0
8  30.0   NaN  NaN
9  30.0   NaN  NaN

Case 2 Here we are adding a new column to a data frame that had 8 rows to begin with. As we extend our new column c to be of length 10 the other columns are extended with NaN.

>>> for i in range(10):
...     data.loc[i, 'c'] = 30
... 
>>> data
      a     b   id     c
0  10.0   6.0  1.0  30.0
1   6.0  -3.0  1.0  30.0
2  -3.0   6.0  1.0  30.0
3  -2.0  12.0  1.0  30.0
4   4.0   8.0  6.0  30.0
5  12.0  11.0  2.0  30.0
6   3.0  -5.0  2.0  30.0
7   3.0  -5.0  4.0  30.0
8   NaN   NaN  NaN  30.0
9   NaN   NaN  NaN  30.0
spies006
  • 2,867
  • 2
  • 19
  • 28
  • 2
    I forgot to mention looping is an obvious choice but highly inefficient. I'm looking for ways to leverage Pandas's ability to operate on matrix/DataFrames without going through one row at a time. – Zhang18 May 18 '17 at 13:14
1

Also somewhat late, but my solution was similar to the accepted one:

import pandas as pd
df = pd.DataFrame({'a':[10, 20], 'b':[100,200]}, index=[1,2])

# single index assignment always works
df.loc[3, 'a'] = 30

# multiple indices
new_rows = [4,5]

# there should be a nicer way to add more than one index/row at once,
# but at least this is just one extra line:
df = df.reindex(index=df.index.append(pd.Index(new_rows))) # note: Index.append() doesn't accept non-Index iterables?

# multiple new rows now works:
df.loc[new_rows, "a"] = [40, 50]
print(df)

... which yields:

      a      b
1  10.0  100.0
2  20.0  200.0
3  30.0    NaN
4  40.0    NaN
5  50.0    NaN

This also works now (useful when performance on aggregating dataframes matters):

# inserting whole rows:
df.loc[new_rows] = [[41, 51], [61,71]]
print(df)

      a      b
1  10.0  100.0
2  20.0  200.0
3  30.0    NaN
4  41.0   51.0
5  61.0   71.0
Jan
  • 81
  • 5