I have the following dataframe
t input type value
0 0 2 A 0.1
1 1 2 A 0.2
2 2 2 A 0.3
3 0 2 B 1.0
4 1 2 B 2.0
5 2 2 B 3.0
6 0 4 A 1.0
7 2 4 A 2.0
8 0 4 B 1.0
9 1 4 B 1.0
and I want to transform it to the following form
t input A B
0 0 2 0.1 1
1 1 2 0.2 2
2 2 2 0.3 3
3 0 4 1 1
4 1 4 1
5 2 4 1
I started by grouping the contents of the dataframe based on 'type'. I am not sure how to proceed next.
Code:
import pandas as pd
from pprint import pprint
if __name__ == '__main__':
d = {
't': [0, 1, 2, 0, 1, 2, 0, 2, 0, 1],
'input': [2, 2, 2, 2, 2, 2, 4, 4, 4, 4],
'type': ['A', 'A', 'A', 'B', 'B', 'B', 'A', 'A', 'B', 'B'],
'value': [0.1, 0.2, 0.3, 1, 2, 3, 1, 2, 1, 1],
}
df = pd.DataFrame(d)
pprint(df)
frames = {}
for c in df.type.unique():
frames[c] = df.copy()[df.type == c]
pprint(frames)
# expected output
d_out = {
't': [0, 1, 2, 0, 1, 2],
'input': [2, 2, 2, 4, 4, 4],
'A': [0.1, 0.2, 0.3, 1, '', 1],
'B': [1, 2, 3, 1, 1, ''],
}
df_out = pd.DataFrame(d_out)
pprint(df_out)
Suggestions on how to transform df to df_out will be really helpful.
EDIT: I tried
df_piv = df.pivot(['t', 'input'], 'type', 'value')
df_piv >
type A B
t input
0 2 0.1 1.0
4 1.0 1.0
1 2 0.2 2.0
4 NaN 1.0
2 2 0.3 3.0
4 2.0 NaN
I would like to know how to change the above to
t input A B
0 2 0.1 1.0
0 4 1.0 1.0
1 2 0.2 2.0
1 4 NaN 1.0
2 2 0.3 3.0
2 4 2.0 NaN
