sos sos - 5 months ago 7
Python Question

Make a new pandas.dataframe column as filtered other column

I have a pandas.Dataframe object in the following format:

Symb Bid Ask
ESU6 207000 207025
YMU6 12793 12797
ESU6 207025 207050
YMU6 12795 12797


But I want to re-configure the object to look like the following format:

Bid.ESU6 Ask.ESU6 Bid.YMU6 Ask.YMU6
207000 207025
12793 12797
207025 207050
12795 12797


Is there an easy way to re-configure the data without a for-loop that goes through each element?

Thank you so much in advance!

Answer

You can use pivot_table:

df1 = df.pivot_table(columns='Symb', values=['Bid','Ask'], index=df.index)
df1.sort_index(axis=1, level=1, inplace=True)
df1.columns = ['.'.join(col) for col in df1.columns]
df1.fillna('', inplace=True)
print (df1)
  Bid.ESU6 Ask.ESU6 Bid.YMU6 Ask.YMU6
0   207000   207025                  
1                      12793    12797
2   207025   207050                  
3                      12795    12797