In [1]:
import pandas as pd
import numpy as np
from pandas import DataFrame, Series
In [3]:
df1 = DataFrame({'key' : list('bbacaab'),
'data1' : np.arange(7)})
df1
Out[3]:
In [6]:
df2 = DataFrame({'key' : list('abd'),
'data2' : np.arange(3)})
df2
Out[6]:
In [7]:
pd.merge(df1, df2)
Out[7]:
In [8]:
pd.merge(df1, df2, on ='key')
Out[8]:
In [10]:
df3 = DataFrame({'lkey' : list('bbacaab'),
'data1' : np.arange(7)})
df4 = DataFrame({'rkey' : list('abd'),
'data2' : np.arange(3)})
pd.merge(df3, df4, left_on = 'lkey', right_on = 'rkey')
Out[10]:
In [11]:
pd.merge(df1, df2, how = 'outer')
Out[11]:
In [12]:
df1 = DataFrame({'key' : list('bbacab'),
'data1' : np.arange(6)})
df2 = DataFrame({'key' : list('ababd'),
'data2' : np.arange(5)})
pd.merge(df1, df2, on = 'key', how = 'left')
Out[12]:
In [13]:
pd.merge(df1, df2, on = 'key', how = 'inner')
Out[13]:
In [14]:
left = DataFrame({'key1': ['foo', 'foo', 'bar'],
'key2': ['one', 'two', 'one'],
'lval': [1, 2, 3]})
right = DataFrame({'key1': ['foo', 'foo', 'bar', 'bar'],
'key2': ['one', 'one', 'one', 'two'],
'rval': [4, 5, 6, 7]})
pd.merge(left, right, on = ['key1', 'key2'], how = 'outer')
Out[14]:
In [15]:
pd.merge(left, right, on = 'key1')
Out[15]:
In [16]:
pd.merge(left, right, on = 'key1', suffixes=('_left', '_right'))
Out[16]:
In [17]:
left1 = DataFrame({'key': ['a', 'b', 'a', 'a', 'b', 'c'],
'value': range(6)})
right1 = DataFrame({'group_val': [3.5, 7]}, index=['a', 'b'])
print(left1)
print(right1)
In [19]:
pd.merge(left1, right1, left_on = 'key', right_index = True)
Out[19]:
In [20]:
pd.merge(left1, right1, left_on = 'key', right_index = True, how = 'outer')
Out[20]:
In [21]:
lefth = DataFrame({'key1': ['Ohio', 'Ohio', 'Ohio', 'Nevada', 'Nevada'],
'key2': [2000, 2001, 2002, 2001, 2002],
'data': np.arange(5.)})
righth = DataFrame(np.arange(12).reshape((6, 2)),
index=[['Nevada', 'Nevada', 'Ohio', 'Ohio', 'Ohio', 'Ohio'],
[2001, 2000, 2000, 2000, 2001, 2002]],
columns=['event1', 'event2'])
lefth
Out[21]:
In [23]:
righth
Out[23]:
In [24]:
pd.merge(lefth, righth, left_on = ['key1', 'key2'], right_index = True)
Out[24]:
In [25]:
pd.merge(lefth, righth, left_on = ['key1', 'key2'], right_index = True, how = 'outer')
Out[25]:
In [26]:
left2 = DataFrame([[1., 2.], [3., 4.], [5., 6.]], index=['a', 'c', 'e'],
columns=['Ohio', 'Nevada'])
right2 = DataFrame([[7., 8.], [9., 10.], [11., 12.], [13, 14]],
index=['b', 'c', 'd', 'e'], columns=['Missouri', 'Alabama'])
left2
Out[26]:
In [27]:
right2
Out[27]:
In [28]:
pd.merge(left2, right2, how = 'outer', left_index = True, right_index = True)
Out[28]:
In [29]:
left2.join(right2, how = 'outer')
Out[29]:
In [30]:
left1.join(right1, on = 'key')
Out[30]:
In [31]:
another = DataFrame([[7., 8.], [9., 10.], [11., 12.], [16., 17.]],
index=['a', 'c', 'e', 'f'], columns=['New York', 'Oregon'])
left2.join([right2, another])
Out[31]:
In [32]:
left2.join([right2, another], how = 'outer')
Out[32]:
In [34]:
arr = np.arange(12).reshape((3, 4))
arr
Out[34]:
In [35]:
np.concatenate([arr, arr], axis = 1)
Out[35]:
In [36]:
s1 = Series([0, 1], index=['a', 'b'])
s2 = Series([2, 3, 4], index=['c', 'd', 'e'])
s3 = Series([5, 6], index=['f', 'g'])
pd.concat([s1, s2, s3])
Out[36]:
In [37]:
pd.concat([s1, s2, s3], axis = 1)
Out[37]:
In [38]:
s4 = pd.concat([s1 * 5, s3])
pd.concat([s1, s4], axis = 1)
Out[38]:
In [40]:
pd.concat([s1, s4], axis = 1, join = 'inner')
Out[40]:
In [41]:
pd.concat([s1, s4], axis = 1, join_axes = [['a', 'c', 'b', 'e']])
Out[41]:
In [43]:
result = pd.concat([s1, s1, s3], keys = ['one', 'two', 'three'])
result
Out[43]:
In [44]:
result.unstack()
Out[44]:
In [47]:
result = pd.concat([s1, s2, s3], axis = 1, keys=['one', 'two', 'three'])
result
Out[47]:
In [48]:
df1 = DataFrame(np.arange(6).reshape(3, 2), index=['a', 'b', 'c'],
columns=['one', 'two'])
df2 = DataFrame(5 + np.arange(4).reshape(2, 2), index=['a', 'c'],
columns=['three', 'four'])
pd.concat([df1, df2], axis=1, keys=['level1', 'level2'])
Out[48]:
In [49]:
pd.concat({'level1': df1, 'level2': df2}, axis=1)
Out[49]:
In [50]:
pd.concat([df1, df2], axis=1, keys=['level1', 'level2'],
names=['upper', 'lower'])
Out[50]:
In [51]:
df1 = DataFrame(np.random.randn(3, 4), columns=['a', 'b', 'c', 'd'])
df2 = DataFrame(np.random.randn(2, 3), columns=['b', 'd', 'a'])
df1
Out[51]:
In [52]:
df2
Out[52]:
In [53]:
pd.concat([df1, df2], ignore_index = True)
Out[53]:
In [54]:
a = Series([np.nan, 2.5, np.nan, 3.5, 4.5, np.nan],
index=['f', 'e', 'd', 'c', 'b', 'a'])
b = Series(np.arange(len(a), dtype=np.float64),
index=['f', 'e', 'd', 'c', 'b', 'a'])
b[-1] = np.nan
a
Out[54]:
In [55]:
b
Out[55]:
In [56]:
np.where(pd.isnull(a), b, a)
Out[56]:
In [57]:
b[:-2].combine_first(a[2:])
Out[57]:
In [58]:
df1 = DataFrame({'a': [1., np.nan, 5., np.nan],
'b': [np.nan, 2., np.nan, 6.],
'c': range(2, 18, 4)})
df2 = DataFrame({'a': [5., 4., np.nan, 3., 7.],
'b': [np.nan, 3., 4., 6., 8.]})
df1.combine_first(df2)
Out[58]:
In [59]:
data = DataFrame(np.arange(6).reshape((2, 3)),
index=pd.Index(['Ohio', 'Colorado'], name='state'),
columns=pd.Index(['one', 'two', 'three'], name='number'))
data
Out[59]:
In [61]:
result = data.stack()
result
Out[61]:
In [62]:
result.unstack()
Out[62]:
In [63]:
result.unstack(0)
Out[63]:
In [64]:
result.unstack('state')
Out[64]:
In [65]:
s1 = Series([0, 1, 2, 3], index=['a', 'b', 'c', 'd'])
s2 = Series([4, 5, 6], index=['c', 'd', 'e'])
data2 = pd.concat([s1, s2], keys=['one', 'two'])
data2
Out[65]:
In [66]:
data2.unstack()
Out[66]:
In [67]:
data2.unstack().stack()
Out[67]:
In [68]:
data2.unstack().stack(dropna = False)
Out[68]:
In [69]:
df = DataFrame({'left': result, 'right': result + 5},
columns=pd.Index(['left', 'right'], name='side'))
df
Out[69]:
In [70]:
df.unstack('state')
Out[70]:
In [71]:
df.unstack('state').stack('state')
Out[71]:
In [72]:
data = pd.read_csv('macrodata.csv')
periods = pd.PeriodIndex(year=data.year, quarter=data.quarter, name='date')
data = DataFrame(data.to_records(),
columns=pd.Index(['realgdp', 'infl', 'unemp'], name='item'),
index=periods.to_timestamp('D', 'end'))
ldata = data.stack().reset_index().rename(columns={0: 'value'})
wdata = ldata.pivot('date', 'item', 'value')
In [73]:
ldata[:10]
Out[73]:
In [74]:
pivoted = ldata.pivot('date', 'item', 'value')
pivoted.head()
Out[74]:
In [75]:
ldata['value2'] = np.random.randn(len(ldata))
ldata[:10]
Out[75]:
In [77]:
pivoted = ldata.pivot('date', 'item')
pivoted
Out[77]:
In [78]:
pivoted['value'][:5]
Out[78]:
In [81]:
unstacked = ldata.set_index(['date', 'item']).unstack('item')
unstacked[:7]
Out[81]:
In [ ]: