I have a dataframe
{'Author': {0: 1, 1: 1, 2: 2, 3: 2},
'Article': {0: 11, 1: 11, 2: 22, 3: 22},
'Year': {0: 2017, 1: 2018, 2: 2017, 3: 2018},
'First': {0: 1, 1: 0, 2: 0, 3: 0},
'Second': {0: 0, 1: 1, 2: 1, 3: 1}}
Want to convert from long to wide for Year, creating values columns based on First and Second.
Expected output
Author Article Year First Second First_2017 First_2018 Second_2017 Second_2018
1 11 2017 1 0 1 0 0 1
1 12 2018 0 1 1 0 0 1
2 22 2017 0 0 0 0 0 1
2 23 2018 0 1 0 0 0 1
>Solution :
If need test if exist at least one 1 in columns ['First','Second'] use DataFrame.pivot_table with any, flatten MultiIndex and append to original:
df1 = df.pivot_table(index='Author',
columns='Year',
values=['First','Second'],
aggfunc='any')
df1.columns = [f'{a}_{b}' for a, b in df1.columns]
df = df.join(df1.astype(int), on='Author')
print (df)
Author Article Year First Second First_2017 First_2018 Second_2017 \
0 1 11 2017 1 0 1 0 0
1 1 11 2018 0 1 1 0 0
2 2 22 2017 0 1 0 0 1
3 2 22 2018 0 1 0 0 1
Second_2018
0 1
1 1
2 1
3 1