How do you convert the below dataframe to:
State Feature 2020Q1 2020Q2 2020Q3 2020Q4.....2030Q4
AL Population 100000 100020 100030 100040 100050
AL InterestRate 1.5 1.5 1.5 1.6 1.6
AZ Population 200001 200002 200003 200004..... 300002
AZ InterestRate 2.0 2.0 2.0 2.0 ..........3.0
Need output like:
State YearQuarter Population InterestRate
AL 2020Q1 100000 1.5
AL 2020Q2 100020 1.5
AL 2020Q3 100030 1.5
AL 2020Q4 100040 1.6
.
.
AL 2030Q3 100050 1.6
CodePudding user response：
Use pivot and some axis operations:
out = (df.pivot(index='State', columns='Feature')
.rename_axis(columns=['YearQuarter', None])
.stack('YearQuarter').reset_index())
print(out)
# Output
State YearQuarter InterestRate Population
0 AL 2020Q1 1.5 100000.0
1 AL 2020Q2 1.5 100020.0
2 AL 2020Q3 1.5 100030.0
3 AL 2020Q4 1.6 100040.0
4 AL 2030Q4 1.6 100050.0
5 AZ 2020Q1 2.0 200001.0
6 AZ 2020Q2 2.0 200002.0
7 AZ 2020Q3 2.0 200003.0
8 AZ 2020Q4 2.0 200004.0
9 AZ 2030Q4 3.0 300002.0
CodePudding user response：
here is one way to do it
df.melt(
['State','Feature'], var_name='Quarters'
).pivot_table(
index=['State','Quarters'],
columns='Feature',
values='value' ).reset_index()
Feature State Quarters InterestRate Population
0 AL 2020Q1 1.5 100000.0
1 AL 2020Q2 1.5 100020.0
2 AL 2020Q3 1.5 100030.0
3 AL 2020Q4 1.6 100040.0
4 AL 2030Q4 1.6 100050.0
5 AZ 2020Q1 2.0 200001.0
6 AZ 2020Q2 2.0 200002.0
7 AZ 2020Q3 2.0 200003.0
8 AZ 2020Q4 2.0 200004.0
9 AZ 2030Q4 3.0 300002.0