I have a multi-index dataframe of the form below():
key nm c0 c1 c2 c3
bar one -0.42 0.56 0.27 -1.08
two -0.67 0.11 -1.47 0.52
baz one 0.40 0.57 -1.71 -1.03
two -0.37 -1.15 -1.34 0.84
I want to concatenate the numbers in the df based on the inner level index i.e. nm separated by \n (assuming the numbers are represented as strings) and get rid of that level nm ;like this below:
key c0 c1 c2 c3
bar -0.42\n-0.67 0.56\n0.11 0.27\n-1.47 -1.08\n0.52
baz 0.40\n-0.37 0.57\n-1.15 -1.71\n-1.34 -1.03\n0.84
Please guide on how it can be achieved.
You can groupby on "key" and join with "\n". NB. You first need to cast your data as string:
df.astype(str).groupby(level=0).agg('\n'.join)
output:
c0 c1 c2 c3
key
bar -0.42\n-0.67 0.56\n0.11 0.27\n-1.47 -1.08\n0.52
baz 0.4\n-0.37 0.57\n-1.15 -1.71\n-1.34 -1.03\n0.84