How to melt the pd.DataFrame to organize the data? (toy example included)

Question

Issue

I am curious to know how to melt the data_df in the toy example provided below to the desired_df.

import pandas as pd

data_df = pd.DataFrame(data = [['FR','Aug',100], ['FR','Sep',170], ['FR','Oct',250],
                               ['KR','Aug',9], ['KR','Sep',12],['KR','Oct',19],
                               ['US','Aug',360], ['US','Sep',500], ['US','Oct',700]],
                       columns = ['country','time','covid19'])
data_df
>>>   country   time    covid19 
   0    FR       Aug      100
   1    FR       Sep      170
   2    FR       Oct      250
   3    KR       Aug       9
   4    KR       Sep      12
   5    KR       Oct      19
   6    US       Aug      360
   7    US       Sep      500
   8    US       Oct      700

My desired output desired_df is as follows, country names at columns, time at index, and number of Covid 19 patients in the dataframe as values.

desired_df
>>>     FR  KR  US
 Aug    100 9   360
 Sep    170 12  500
 Oct    250 19  700

I think pd.melt would help, but it does not create index and columns as I wanted.

David Collins · Accepted Answer · 2020-10-31T21:58:59.300

Try pivot:

data = data_df.pivot(index = 'time', columns = 'country')
print(data)

Which gives:

country      FR  KR   US
time                    
Aug         100   9  360
Oct         250  19  700
Sep         170  12  500

The indices are in alphabetical order. Reorder them as you like. For ordering them calendrically, I'd suggest Brad Solomon's answer to Sort a pandas's dataframe series by month name?, which uses the pd.Categorical.

How to melt the pd.DataFrame to organize the data? (toy example included)

Issue

1 Answers1