How to append rows with concat to a Pandas DataFrame

In [19]:

import pandas as pd
import requests
import json

For this example, I will load the data from the following api. This is stocks data. Let us not worry about the technical aspect of this data.

In [20]:

data = json.loads(requests.get('https://tradestie.com/api/v1/apps/ttm-squeeze-stocks?date=2022-11-17').text)

In [21]:

data[0]

Out[21]:

{'date': 'Thu, 17 Nov 2022 00:00:00 GMT',
 'in_squeeze': False,
 'no_of_days_in_squeeze': 0,
 'no_of_days_out_of_squeeze': 1,
 'out_of_squeeze': True,
 'ticker': 'AA'}

In [22]:

df = pd.DataFrame(data)

Let us look at our data.

In [23]:

df.head(1)

Out[23]:

	date	in_squeeze	no_of_days_in_squeeze	no_of_days_out_of_squeeze	out_of_squeeze	ticker
0	Thu, 17 Nov 2022 00:00:00 GMT	False	0	1	True	AA

This data is huge. But for our purpose, I will just slice the first 10 rows of this dataframe.

In [24]:

df10rows = df.head(10)

In [25]:

len(df10rows)

Out[25]:

In [26]:

df10rows.head(2)

Out[26]:

	date	in_squeeze	no_of_days_in_squeeze	no_of_days_out_of_squeeze	out_of_squeeze	ticker
0	Thu, 17 Nov 2022 00:00:00 GMT	False	0	1	True	AA
1	Thu, 17 Nov 2022 00:00:00 GMT	True	0	0	False	AADI

As we know, there are 10 rows in this data. Let us now append another row (row11) to this dataframe.

In [27]:

row11 = {'date': 'Thu, 17 Nov 2022 00:00:00 GMT',
 'in_squeeze': False,
 'no_of_days_in_squeeze': 0,
 'no_of_days_out_of_squeeze': 1,
 'out_of_squeeze': True,
 'ticker': 'INTC'}

First we need to convert this data in to a dataframe.

In [28]:

dfrow11 = pd.DataFrame([row11])

In [29]:

dfrow11

Out[29]:

	date	in_squeeze	no_of_days_in_squeeze	no_of_days_out_of_squeeze	out_of_squeeze	ticker
0	Thu, 17 Nov 2022 00:00:00 GMT	False	0	1	True	INTC

Concat row to a Pandas Dataframe

In [30]:

pd.concat([df10rows,pd.DataFrame([row11])])

Out[30]:

	date	in_squeeze	no_of_days_in_squeeze	no_of_days_out_of_squeeze	out_of_squeeze	ticker
0	Thu, 17 Nov 2022 00:00:00 GMT	False	0	1	True	AA
1	Thu, 17 Nov 2022 00:00:00 GMT	True	0	0	False	AADI
2	Thu, 17 Nov 2022 00:00:00 GMT	True	0	0	False	AAL
3	Thu, 17 Nov 2022 00:00:00 GMT	True	1	0	False	AAME
4	Thu, 17 Nov 2022 00:00:00 GMT	False	0	1	True	AATC
5	Thu, 17 Nov 2022 00:00:00 GMT	False	0	1	True	AAU
6	Thu, 17 Nov 2022 00:00:00 GMT	True	1	0	False	AAWW
7	Thu, 17 Nov 2022 00:00:00 GMT	False	0	1	True	ABCB
8	Thu, 17 Nov 2022 00:00:00 GMT	True	1	0	False	ABEO
9	Thu, 17 Nov 2022 00:00:00 GMT	False	0	1	True	ABIO
0	Thu, 17 Nov 2022 00:00:00 GMT	False	0	1	True	INTC

As we see above the row is appended to the last. Note the index of the last row is same as it is of first row.

We can fix it using pd.index command.

In [31]:

dfrow11.index = range(10,11)

In [32]:

dfrow11

Out[32]:

	date	in_squeeze	no_of_days_in_squeeze	no_of_days_out_of_squeeze	out_of_squeeze	ticker
10	Thu, 17 Nov 2022 00:00:00 GMT	False	0	1	True	INTC

Let us try to append again.

In [33]:

pd.concat([df10rows,dfrow11])

Out[33]:

	date	in_squeeze	no_of_days_in_squeeze	no_of_days_out_of_squeeze	out_of_squeeze	ticker
0	Thu, 17 Nov 2022 00:00:00 GMT	False	0	1	True	AA
1	Thu, 17 Nov 2022 00:00:00 GMT	True	0	0	False	AADI
2	Thu, 17 Nov 2022 00:00:00 GMT	True	0	0	False	AAL
3	Thu, 17 Nov 2022 00:00:00 GMT	True	1	0	False	AAME
4	Thu, 17 Nov 2022 00:00:00 GMT	False	0	1	True	AATC
5	Thu, 17 Nov 2022 00:00:00 GMT	False	0	1	True	AAU
6	Thu, 17 Nov 2022 00:00:00 GMT	True	1	0	False	AAWW
7	Thu, 17 Nov 2022 00:00:00 GMT	False	0	1	True	ABCB
8	Thu, 17 Nov 2022 00:00:00 GMT	True	1	0	False	ABEO
9	Thu, 17 Nov 2022 00:00:00 GMT	False	0	1	True	ABIO
10	Thu, 17 Nov 2022 00:00:00 GMT	False	0	1	True	INTC

As we the index of last row is 10 now.

If the index is unique, we can also use pd.combine_first method of pandas as shown below.

In [34]:

df10rows.combine_first(dfrow11).head(11)

Out[34]:

	date	in_squeeze	no_of_days_in_squeeze	no_of_days_out_of_squeeze	out_of_squeeze	ticker
0	Thu, 17 Nov 2022 00:00:00 GMT	False	0	1	True	AA
1	Thu, 17 Nov 2022 00:00:00 GMT	True	0	0	False	AADI
2	Thu, 17 Nov 2022 00:00:00 GMT	True	0	0	False	AAL
3	Thu, 17 Nov 2022 00:00:00 GMT	True	1	0	False	AAME
4	Thu, 17 Nov 2022 00:00:00 GMT	False	0	1	True	AATC
5	Thu, 17 Nov 2022 00:00:00 GMT	False	0	1	True	AAU
6	Thu, 17 Nov 2022 00:00:00 GMT	True	1	0	False	AAWW
7	Thu, 17 Nov 2022 00:00:00 GMT	False	0	1	True	ABCB
8	Thu, 17 Nov 2022 00:00:00 GMT	True	1	0	False	ABEO
9	Thu, 17 Nov 2022 00:00:00 GMT	False	0	1	True	ABIO
10	Thu, 17 Nov 2022 00:00:00 GMT	False	0	1	True	INTC

How to append rows with concat to a Pandas DataFrame

Concat row to a Pandas Dataframe

Related Notebooks