Merging content of two rows in Pandas

Question

I have a data frame, where I would like to merge the content of two rows, and have it separated by underscore, within the same cell. If this is the original DF:

0   eye-right   eye-right   hand
1   location    location    position
2   12          27.7        2
3   14          27.6        2.2

I would like it to become:

0   eye-right_location   eye-right_location   hand_position
1   12                   27.7                 2
2   14                   27.6                 2.2

Eventually I would like to translate row 0 to become header, and reset indexes for the entire df.

Answer 1

You can set your column labels, slice via iloc, then reset_index:

print(df)
#            0          1         2
# 0  eye-right  eye-right      hand
# 1   location   location  position
# 2         12       27.7         2
# 3         14       27.6       2.2

df.columns = (df.iloc[0] + '_' + df.iloc[1])
df = df.iloc[2:].reset_index(drop=True)

print(df)
#   eye-right_location eye-right_location hand_position
# 0                 12               27.7             2
# 1                 14               27.6           2.2

Answer 2

I like jpp's answer a lot. Short and sweet. Perfect for quick analysis.

Just one quibble: The resulting DataFrame is generically typed. Because strings were in the first two rows, all columns are considered type object. You can see this with the info method.

For data analysis, it's often preferable that columns have specific numeric types. This can be tidied up with one more line:

df.columns = df.iloc[0] + '_' + df.iloc[1]
df = df.iloc[2:].reset_index(drop=True)
df = df.apply(pd.to_numeric)

The third line here applies Panda's to_numeric function to each column in turn, leaving a more-typed DataFrame:

While not essential for simple usage, as soon as you start performing math on DataFrames, or start using very large data sets, column types become something you'll need to pay attention to.