Why isn't transpose working to allow me to concat dataframe with df.loc["Y"] row

Question

I have 2 dataframes:

df = pd.DataFrame({'A': [1, 2, 3],
                   'B': [4, 5, 6]},
                  index=['X', 'Y', 'Z'])

and

df1 = pd.DataFrame({'M': [10, 20, 30],
                   'N': [40, 50, 60]},
                  index=['S', 'T', 'U'])

i want to append the df1 with a row of from the df dataframe.

i use the following code to extract the row:

row = df.loc['Y']

when i print this i get:

A    2
B    5
Name: Y, dtype: int64

A and B are key values or column heading names. so i transpose this with

row_25 = row.transpose()

i print row_25 and get:

A    2
B    5
Name: Y, dtype: int64

this is the same as row, so it seems the transpose didn't happen

i then add this code to add the row to df1:

result = pd.concat([df1, row_25], axis=0, ignore_index=False)
print(result)

when i print df1 i get:

      M     N    0
S  10.0  40.0  NaN
T  20.0  50.0  NaN
U  30.0  60.0  NaN
A   NaN   NaN  2.0
B   NaN   NaN  5.0

i want A and B to be column headings (key values) and the name of row (Y) to be the row index.

what am i doing wrong?

Answer 1

Try

pd.concat([df1, df.loc[['Y']])

It generates:

    M       N       A      B
S   10.0    40.0    NaN    NaN
T   20.0    50.0    NaN    NaN
U   30.0    60.0    NaN    NaN
Y   NaN     NaN     2.0    5.0

Not sure if this is what you want.

To exclude column names 'M' and 'N' from the result you can rename the columns beforehand:

>>> df1.columns = ['A', 'B']
>>> pd.concat([df1, df.loc[['Y']])

    A   B
S  10  40
T  20  50
U  30  60
Y   2   5

The reason why you need double square brackets is that single square brackets return a 1D Series, that cannot be transposed. And double brackets return a 2D DataFrame (in general double brackets are used to reference several columns, like df1.loc[['X', 'Y']]; it is called 'fancy indexing' in NumPy).

If you are allergic to double brackets, use

pd.concat([df1.rename(columns={'M': 'A', 'N': 'B'}), 
           df.filter('Y', axis=0)])

Finally, if you really want to transpose something, you can convert the series to a frame and transpose it:

>>> df.loc['Y'].to_frame().T

   A  B
Y  2  5