CODEWITHSUNDEEP
pythonpython-3.x
Vin Bono
Vin Bono

Reputation: 25

How to concat based on a condition in python

I would like to concat 2 Dataframes based on the same date to identify when a product is bought in a linear fashion.

Here's my code:

s = pd.Series(['01-2020', '02-2020', '03-2020', '04-2020', '05-2020', '06-2020', '07-2020', '08-2020', '09-2020', '10-2020', '11-2020', '12-2020'], name=Date)
data = [['01-2020', 5], ['02-2020', 3], ['03-2020', 1], ['05-2020', 4], ['06-2020', 8], ['08-2020', 3], ['09-2020', 11], ['10-2020', 5], ['12-2020', 3]]
df = pd.DataFrame(data, columns = ['Date Bought', 'Amount_Bought'])
result = pd.concat([df, s], axis=1, join="outer")

When I try to concat these dataframes the result is out of order.

I wish the output too look like this

  Date    Date_Bought Amount_Bought
01-2020     01-2020     5
02-2020     02-2020     3
03-2020     03-2020     1
04-2020       NaN       0
05-2020     05-2020     4
06-2020     06-2020     8
07-2020        NaN      0
08-2020     08-2020     3
09-2020     09-2020     11
10-2020     10-2020     5
11-2020        NaN      0
12-2020     12-2020     3

Upvotes: 1

Views: 153

Answers (1)

Mattheus Sant'Anna
Mattheus Sant'Anna

Reputation: 134

Use merge instead of concat - the latter will combine the columns from the set formed by the series and the data frame, and this is not what you would like to have. Also, remove the NaN using fillna on the column 'Amount_Bought'.

results = pd.merge(left = s, right = df, left_on = 'Date', right_on = 'Date Bought', how = 'left')
results[['Amount_Bought']] = results[['Amount_Bought']].fillna(value=0)

Upvotes: 2

Related Questions