let's say i have the following two dataframes X1 and X2. I would like to merge those 2 dataframes by row so that each index from each dataframe being the same combines the corresponding rows from both dataframes.
A B C D
DATE1 a1 b1 c1 d1
DATE2 a2 b2 c2 d2
DATE3 a3 b3 c3 d3
A B C D
DATE1 f1 g1 h1 i1
DATE2 f2 g2 h2 i2
DATE3 f3 g3 h3 i3
how would i combine them to get
A B C D
DATE1 A1 B1 C1 D1
f1 g1 h1 i1
DATE2 A2 B2 C2 D2
f2 g2 h2 i2
DATE3 A3 B3 C3 D3
f3 g3 h3 i3
I have tried this so far but this does not seem to work:
d= pd.concat( { idx : [ X1[idx], X2[idx]] for idx, value in appended_data1.iterrows() } , axis =1}
thanks
Option 1
df3 = df1.stack().to_frame('df1')
df3.loc[:, 'df2'] = df2.stack().values
df3 = df3.stack().unstack(1)
df3
Option 2 Generalized
idx = df1.stack().index
dfs = [df1, df2]
dflabels = ['df1', 'df2']
a = np.stack([d.values.flatten() for d in dfs], axis=1)
df3 = pd.DataFrame(a, index=idx, columns=dflabels).stack().unstack(1)
from StringIO import StringIO
import pandas as pd
df1_text = """ A B C D
DATE1 a1 b1 c1 d1
DATE2 a2 b2 c2 d2
DATE3 a3 b3 c3 d3"""
df2_text = """ F G H I
DATE1 f1 g1 h1 i1
DATE2 f2 g2 h2 i2
DATE3 f3 g3 h3 i3"""
df1 = pd.read_csv(StringIO(df1_text), delim_whitespace=True)
df2 = pd.read_csv(StringIO(df2_text), delim_whitespace=True)
df1
df2
maybe this solution too could solve your problem:
df3 = pd.concat([df1,df2]).sort_index()
print df3
Out[42]:
A B C D
DATE1 a1 b1 c1 d1
DATE1 f1 g1 h1 i1
DATE2 a2 b2 c2 d2
DATE2 f2 g2 h2 i2
DATE3 a3 b3 c3 d3
DATE3 f3 g3 h3 i3
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With