I have the following two dataframes: The 1st column is the index and the last column is derived from the index by appending a '.txt' to it.
A 1 0.2 0.3 1.txt 2 0.4 0.6 2.txt B 1 0.1 0.8 1.txt 2 3.0 4.5 2.txt I would like to combine them so:
1 0.2 0.3 1.txt 2 0.4 0.6 2.txt 3 0.1 0.8 3.txt 4 3.0 4.5 4.txt I tried using pandas merge, but not sure of how to go about it without explicitly iterating using a for loop. Any suggestions?
Just concat them as a list and pass param ignore_index=true, then assign the index values to the 3rd column, convert to str dtype and then append the txt '.txt:
In [93]: merged = pd.concat([A,B], ignore_index=True) merged[3] = pd.Series(merged.index).astype(str) + '.txt' merged Out[93]: 1 2 3 0 0.2 0.3 0.txt 1 0.4 0.6 1.txt 2 0.1 0.8 2.txt 3 3.0 4.5 3.txt If you insist on the indexing being 1-based you can reassign to it and then run my code above:
In [100]: merged = pd.concat([A,B], ignore_index=True) merged.index = np.arange(1, len(merged) + 1) merged[3] = pd.Series(index=merged.index, data=merged.index.values).astype(str) + '.txt' merged Out[100]: 1 2 3 1 0.2 0.3 1.txt 2 0.4 0.6 2.txt 3 0.1 0.8 3.txt 4 3.0 4.5 4.txt As a side not I find it a little weird I have to specify the index values in the Series constructor in order for the alignment to be correct.
Here's one to go about it
In [207]: df1 Out[207]: col1 col2 txt 0 0.2 0.3 1.txt 1 0.4 0.6 2.txt In [208]: df2 Out[208]: col1 col2 txt 0 0.1 0.8 1.txt 1 3.0 4.5 2.txt In [209]: df1.append(df2, ignore_index=True) Out[209]: col1 col2 txt 0 0.2 0.3 1.txt 1 0.4 0.6 2.txt 2 0.1 0.8 1.txt 3 3.0 4.5 2.txt In [217]: dff = df1.append(df2, ignore_index=True) In [218]: dff['txt'] = dff.index.map(lambda x: '%d.txt' % (x+1)) In [219]: dff Out[219]: col1 col2 txt 0 0.2 0.3 1.txt 1 0.4 0.6 2.txt 2 0.1 0.8 3.txt 3 3.0 4.5 4.txt 
