CODEWITHSUNDEEP
pythonpython-2.7pandas
Sai Rajesh
Sai Rajesh

Reputation: 1982

How to merge dataframes using pandas python?

I have following two dataframes

data1

company,values
a,76
b,56
c,78
d,56

dataframe2

company,comp_id
a,a1
b,b1

I need output like following format

company,value,comp_id
 a,76,a1
 b,56,b1
 c,78
 d,56

how to merge dataframes like following format?

Upvotes: 1

Views: 954

Answers (4)

Gil Baggio
Gil Baggio

Reputation: 13963

You can use pd.concat() for this.

I am adding an example for a better understanding.

In [25]: grouped_data
Out[25]: 
                                     Yield
monthyear Facility Date                   
Dec 15    CCM2     2015-12-01  2550.000000
Feb 16    CCM2     2016-02-01  4250.000000
Jan 16    CCM1     2016-01-01  1540.000000
          CCM2     2016-01-01  6800.000000
Nov 15    CCM1     2015-11-01   921.458157
          CCM2     2015-11-01  1750.310038
Sep 15    CCM2     2015-09-01  5191.197065


In [27]: grouped_data1
Out[27]: 
                               Planned
monthyear Facility Date               
Nov 15    CCM1     2015-11-01    880.0
          CCM2     2015-11-01   3000.0
Sep 15    CCM2     2015-09-01   8000.0

Now merging these two dataframes using pd.concat()

In [28]: result = pd.concat([grouped_data,grouped_data1],axis=1)

In [29]: result
Out[29]: 
                                     Yield  Planned
monthyear Facility Date                            
Dec 15    CCM2     2015-12-01  2550.000000      NaN
Feb 16    CCM2     2016-02-01  4250.000000      NaN
Jan 16    CCM1     2016-01-01  1540.000000      NaN
          CCM2     2016-01-01  6800.000000      NaN
Nov 15    CCM1     2015-11-01   921.458157    880.0
          CCM2     2015-11-01  1750.310038   3000.0
Sep 15    CCM2     2015-09-01  5191.197065   8000.0

Upvotes: 2

jezrael
jezrael

Reputation: 862581

Use merge with fillna:

#fillna only column comp_id
print (pd.merge(df1,df2,on='company', how='outer').fillna({'comp_id':''}))

#fillna all dataframe
#print (pd.merge(df1,df2,on='company', how='outer').fillna(''))

  company  values comp_id
0       a      76      a1
1       b      56      b1
2       c      78        
3       d      56

Sample:

df1 = pd.DataFrame({'values': {0: 76, 1: 56, 2: 78, 3: 56}, 
                    'company': {0: 'a', 1: 'b', 2: 'c', 3: 'd'}})
print (df1)
  company  values
0       a      76
1       b      56
2       c      78
3       d      56

df2 = pd.DataFrame({'comp_id': {0: 'a1', 1: 'b1'}, 'company': {0: 'a', 1: 'b'}},
                   columns=['company','comp_id'])
print (df2)
  company comp_id
0       a      a1
1       b      b1

print (pd.merge(df1,df2,on='company', how='outer'))
  company  values comp_id
0       a      76      a1
1       b      56      b1
2       c      78     NaN
3       d      56     NaN

#fillna only column comp_id
print (pd.merge(df1,df2,on='company', how='outer').fillna({'comp_id':''}))
  company  values comp_id
0       a      76      a1
1       b      56      b1
2       c      78        
3       d      56

Upvotes: 5

o-90
o-90

Reputation: 17585

Use pd.merge()

>>> pd.merge(dataframe1, dataframe2, on='company', how='outer')

Upvotes: 3

sobek
sobek

Reputation: 1426

You're looking for the pandas.concat() function. Have a look at the documentation here.

Upvotes: 1

Related Questions