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
