How extract data from dataframe and join with an another dataframe

Question

I have two dataframes df and df1. I want to join the both dataframes and get the output in different ways

df
City    Date        Wind    Temperature
London  5/11/2019   14        5
London  6/11/2019   28        6
London  7/11/2019   10        5
Berlin  5/11/2019   23        12
Berlin  6/11/2019   24        12
Berlin  7/11/2019   16        16
Munich  5/11/2019   12        10
Munich  6/11/2019   33        11
Munich  7/11/2019   44        13
Paris   5/11/2019   27        6
Paris   6/11/2019   16        7
Paris   7/11/2019   14        8
Paris   8/11/2019   10        6

df1
ID     City   Delivery_Date Provider
1456223 London  7/11/2019   Amazon
1456345 London  6/11/2019   Amazon
2345623 Paris   8/11/2019   Walmart
1287456 Paris   7/11/2019   Amazon
4568971 Munich  7/11/2019   Amazon
3456789 Berlin  6/11/2019   Walmart

Output1

ID  City    Delivery_Date   Wind    Temperature
1456223 London  7/11/2019   10        5
1456345 London  6/11/2019   28        6
2345623 Paris   8/11/2019   10        6
1287456 Paris   7/11/2019   14        8
4568971 Munich  7/11/2019   44       13

Output 2

Here the weather details of the Item should displayed till its delivery date is met

ID  City    Delivery_Date   Wind    Temperature
1456223 London  5/11/2019   14  5
1456223 London  6/11/2019   28  6
1456223 London  7/11/2019   10  5
1287456 Paris   5/11/2019   27  6
1287456 Paris   6/11/2019   16  7
1287456 Paris   7/11/2019   14  8

How can this be done.

Does this answer your question? How to join (merge) data frames (inner, outer, left, right) — pissall
– pissall, Commented Nov 5, 2019 at 4:56
@kirangadhe, I am trying to extract all the avialable weather feeds from df for the particular shipment id for that location which less than to the delivery date.For eg.`1287456 is having planned delivery date of 7/11/2018.I so want to display the weather details for 5,6,7 november for that record. — aeapen
– aeapen, Commented Nov 5, 2019 at 6:16
@kirangadhe, I want also the grouping to be done based id . For eg .2345623 you will have 4 records showing weather from 5 to 8 November' and `1287456 you will have 3 records from 5 to7 nov — aeapen
– aeapen, Commented Nov 5, 2019 at 6:44

kiran gadhe · Accepted Answer · 2019-11-05 06:25:49Z

1

considering DF and DF1 as data frames as you explained.

import pandas as pd 
output1 = pd.merge(DF1, DF,left_on = ['City','Date'] ,right_on = ['City','Delivery_Date'], how='inner' ) 
res1 = df1.groupby('City').max() [['Delivery_Date']]
result1 = pd.merge(df,res1, on ='City')
output2 = result1 [result1['Date'] <= result1['Delivery_Date']]

edited Nov 5, 2019 at 6:25

answered Nov 5, 2019 at 5:43

kiran gadhe

7433 silver badges11 bronze badges

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

Comments

Khakhar Shyam · Accepted Answer · 2019-11-05 07:21:18Z

0

You can use df.merge

import pandas as pd
df.merge(df1[['City','Delivery_Date','ID']],left_on = ['City','Date'] ,right_on = ['City','Delivery_Date'],how='inner')

answered Nov 5, 2019 at 7:21

Khakhar Shyam

5093 silver badges12 bronze badges

Collectives™ on Stack Overflow

How extract data from dataframe and join with an another dataframe

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related