Pivoting a repeating Time Series Data

Question

I am trying to pivot this data in such a way that I get columns like eg: AK_positive AK_probableCases, AK_negative, AL_positive.. and so on.

You can get the data here, df = pd.read_csv('https://covidtracking.com/api/states/daily.csv')

Does this answer your question? [Pandas - How to flatten a hierarchical index in columns](https://stackoverflow.com/questions/14507794/pandas-how-to-flatten-a-hierarchical-index-in-columns) — Bill Huang, Nov 06 '20 at 04:52

score 0 · Answer 1 · answered Nov 06 '20 at 05:11

Just flatten the original MultiIndex column into tuples using .to_flat_index(), and rearrange tuple elements into a new column name.

df_pivoted.columns = [f"{i[1]}_{i[0]}" for i in df_pivoted.columns.to_flat_index()]

Result:

# start from April
df_pivoted[df_pivoted.index >= 20200401].head(5)

          AK_positive  AL_positive  AR_positive  ...  WI_grade  WV_grade  WY_grade
date                                             ...                              
20200401        133.0       1077.0        584.0  ...       NaN       NaN       NaN
20200402        143.0       1233.0        643.0  ...       NaN       NaN       NaN
20200403        157.0       1432.0        704.0  ...       NaN       NaN       NaN
20200404        171.0       1580.0        743.0  ...       NaN       NaN       NaN
20200405        185.0       1796.0        830.0  ...       NaN       NaN       NaN

Pivoting a repeating Time Series Data

1 Answers1