CODEWITHSUNDEEP
pythonpandasdataframedata-analysis
J. herrington
J. herrington

Reputation: 33

Merging and transforming two pandas dataframes

I have two pandas data frames:

One in the format:

type sum date
x1   12  01/01/12
x2   10  01/01/12
x3   8   01/01/12
x1   13  02/01/12
x2   12  02/01/12
x3   55  02/01/12
x1   11  03/01/12
x2   10  03/01/12
x3   8   03/01/12

and another in the format

total date
122   01/01/12
133   02/01/12
144   03/01/12

What is the simplest way to combine these to so i could get the following output:

date     x1 x2 x3 total
01/01/12 12 10 8  122
02/01/12 13 12 55 133
03/01/12 11 10 8  144

I have tried a lot of functions that are getting very messy, very quickly and do not seem to work.

Any help would be greatly appreciated.

Upvotes: 3

Views: 35

Answers (1)

jezrael
jezrael

Reputation: 863401

You can use pivot with df1, set_index with df2 and then concat them together. Last you can remove columns name and reset_index:

print df1.pivot(index='date', columns='type', values='sum')
type        x1  x2  x3
date                  
2012-01-01  12  10   8
2012-02-01  13  12  55
2012-03-01  11  10   8

print df2.set_index('date')
            total
date             
2012-01-01    122
2012-02-01    133
2012-03-01    144

df = pd.concat([df1.pivot(index='date', columns='type', values='sum'), 
                df2.set_index('date')], axis=1)
df.columns.name = None
df = df.reset_index()
print df
        date  x1  x2  x3  total
0 2012-01-01  12  10   8    122
1 2012-02-01  13  12  55    133
2 2012-03-01  11  10   8    144

And maybe before you can convert columns date to_datetime of both DataFrames:

df1['date'] = pd.to_datetime(df1['date'])
df2['date'] = pd.to_datetime(df2['date'])
print df1
  type  sum       date
0   x1   12 2012-01-01
1   x2   10 2012-01-01
2   x3    8 2012-01-01
3   x1   13 2012-02-01
4   x2   12 2012-02-01
5   x3   55 2012-02-01
6   x1   11 2012-03-01
7   x2   10 2012-03-01
8   x3    8 2012-03-01

print df2
   total       date
0    122 2012-01-01
1    133 2012-02-01
2    144 2012-03-01

Upvotes: 3

Related Questions