Pandas DataFrame add column by two columns from other DataFrame

Question

I'm studying Data Science with Python Pandas. But, I'm not good at handling data. So, I need some help.

df1

>>> df1
    stamp  id  col1  col2
0  100000   1   100    60
1  100000   2   100    30
2  100001   1    10    10
3  100001   1    20    30
4  100001   2    20    10
5  100001   2    20    90
6  100001   3    30    10
7  100002   1   300    30
8  100002   4    40    60

df2

>>> df2
    start     end  id  val
0  100000  100001   1    1
1  100002  100003   4    1

Using python3 pandas dataframe, How do I add a new column to the data that has the same 'id' value and 'stamp' values between 'start' and 'end' values?

@ Want to make result like this

>>> result
    stamp  id  col1  col2  val
0  100000   1   100    60    1
1  100000   2   100    30    0
2  100001   1    10    10    1
3  100001   1    20    30    1
4  100001   2    20    10    0
5  100001   2    20    90    0
6  100001   3    30    10    0
7  100002   1   300    30    0
8  100002   4    40    60    1

How can i make it?

user13779898 · Accepted Answer · 2020-06-20 07:21:27Z

2

Try

result = pd.DataFrame()

df2['stamp'] = df2['start']

result = pd.merge(df1,df2, on='stamp')

answered Jun 20, 2020 at 7:21

user13779898

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

1 Comment

김민섭 Over a year ago

pd.merge(df1,df2, on='stamp') | pd.merge(df1,df2, on=['stamp', 'id'], how='left') ==> Both ways are not correct.

sammywemmy · Accepted Answer · 2020-06-20 07:19:02Z

0

melt the second dataframe(df2), so that start and end columns are 'melted' into one column; then proceed to merge the new dataframe with df1, with stamp and id as the merge columns, and fill the null values with 0

df2 = df2.melt(["id", "val"], value_name="stamp")

df1.merge(df2.drop("variable", axis=1), on=["stamp", "id"], how="left").fillna(0)


stamp   id  col1    col2    val
0   100000  1   100 60  1.0
1   100000  2   100 30  0.0
2   100001  1   10  10  1.0
3   100001  1   20  30  1.0
4   100001  2   20  10  0.0
5   100001  2   20  90  0.0
6   100001  3   30  10  0.0
7   100002  1   300 30  0.0
8   100002  4   40  60  1.0

answered Jun 20, 2020 at 7:19

sammywemmy

28.9k4 gold badges21 silver badges35 bronze badges

Collectives™ on Stack Overflow

Pandas DataFrame add column by two columns from other DataFrame

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related