merging two dataframes together with similar column values

Question

I have two dfs, one is longer than the other but they both have one column that contain the same values.

Here is my first df called weather:

        DATE    AWND    PRCP    SNOW    WT01    WT02    TAVG
 0  2017-01-01  5.59    0.00    0.0     NaN     NaN     46
 1  2017-01-02  9.17    0.21    0.0     1.0     NaN     40
 2  2017-01-03  10.74   0.58    0.0     1.0     NaN     42
 3  2017-01-04  8.05    0.00    0.0     1.0     NaN     47
 4  2017-01-05  7.83    0.00    0.0     NaN     NaN     34

Here is my 2nd df called bike:

    DATE    LENGTH      ID      AMOUNT  
 0  2017-01-01  3       1       5       
 1  2017-01-01  6       2       10  
 2  2017-01-02  9       3       100     
 3  2017-01-02  12      4       250 
 4  2017-01-03  15      5       45

So I want my df to copy over all rows from the weather df based upon the shared DATE column and copy it over.

  DATE    LENGTH      ID      AMOUNT  AWND   SNOW  TAVG
0  2017-01-01  3       1       5       5.59  0     46
1  2017-01-01  6       2       10      5.59  0     46
2  2017-01-02  9       3       100     9.17  0     40
3  2017-01-02  12      4       250     9.17  0     40
4  2017-01-03  15      5       45      10.74 0     42

Please help! Maybe some type of join can be used.

score 0 · Answer 1 · answered Jul 11 '18 at 16:52

Use merge

In [93]: bike.merge(weather[['DATE', 'AWND', 'SNOW', 'TAVG']], on='DATE')
Out[93]:
         DATE  LENGTH  ID  AMOUNT   AWND  SNOW  TAVG
0  2017-01-01       3   1       5   5.59   0.0    46
1  2017-01-01       6   2      10   5.59   0.0    46
2  2017-01-02       9   3     100   9.17   0.0    40
3  2017-01-02      12   4     250   9.17   0.0    40
4  2017-01-03      15   5      45  10.74   0.0    42

score 0 · Answer 2 · answered Jul 11 '18 at 16:56

0

Just use the same indexes and simple slicing

df2 = df2.set_index('DATE')
df2[['SNOW', 'TAVG']] = df.set_index('DATE')[['SNOW', 'TAVG']]

answered Jul 11 '18 at 16:56

rafaelc

57,686
15
58
82

score 0 · Answer 3 · edited Jul 11 '18 at 19:39

If you check the pandas docs, they explain all the different types of "merges" (joins) that you can do between two dataframes.

The common syntax for a merge looks like: pd.merge(weather, bike, on= 'DATE')

You can also make the merge more fancy by adding any of the arguments to your merge function that I listed below: (e.g specifying whether your want an inner vs right join)

Here are the arguments the function takes based on the current pandas docs:

pandas.merge(left, right, how='inner', on=None, left_on=None, right_on=None, left_index=False, right_index=False, sort=False, suffixes=('_x', '_y'), copy=True, indicator=False, validate=None)

Source

Hope it helps!

merging two dataframes together with similar column values

3 Answers3