Kalimantan Kalimantan - 2 months ago 9
Python Question

Tranpose Rows to Columns while keeping part of dataframe in tact

I am trying to move some of my rows and make the them columns, but keep a large portion of the dataframe the same.

My Dataframe looks something like this:

ID Thing Level1 Level2 Time OAttribute IsTrue Score Value
1 bicycle value value 9:30 whatever yes 1 type1
2 non-cycle value value 1:30 whatever no 2 type2
3 bicycle value value 2:30 whatever yes
4 bicycle value value 3:30 whatever no 4 type3


And I want something like this:

ID Thing Level1 Level2 Time OAttribute IsTrue Type1 Type2 Type 3
1 bicycle value value 9:30 whatever yes 1
2 non-cycle value value 1:30 whatever no 2
3 bicycle value value 2:30 whatever yes
4 bicycle value value 3:30 whatever no 4

Answer

Option 1
Use merge

df_ = df[['ID', 'Value']].dropna().set_index('Value', append=True).ID.unstack()

df.drop('Value', 1).merge(df_, right_index=True, left_index=True, how='left').fillna('')

enter image description here

Option 2
Use pd.concat

df_ = df[['ID', 'Value']].dropna().set_index('Value', append=True).ID.unstack()

pd.concat([df, df_], axis=1).drop('Value', 1).fillna('')