Pandas combine dataframes

Question

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?

Answer 1

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.

Answer 2

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