Creating a multilevel columns from this dataframe

Question

This is the data frame I have:

  data = {
          'names': ['Rose', 'Rose', 'Tulip', 'Tulip', 'Lily'],
           'f': [10, 20, 5,15, 30],
           'h': [5, 8, 6, 12, 10],
           'date':['2023-01-01', '2023-02-01', '2023-02-01', '2023-03-01', '2023-04-01']
        }

How can I create this?

              Rose          Tulip          Lily                   
              f  h        f        h        f        h
2023-01-01   10  5        0        0        0        0
2023-02-01   20  8        5        6        0        0
2023-03-01   0   0        15       12       0        0
2023-04-01   0   0        0         0       30       10

Answer 1

I think you want to pivot the data:

data = {
    "names": ["Rose", "Rose", "Tulip", "Tulip", "Lily"],
    "f": [10, 20, 5, 15, 30],
    "h": [5, 8, 6, 12, 10],
    "date": ["2023-01-01", "2023-02-01", "2023-02-01", "2023-03-01", "2023-04-01"],
}

df = pd.DataFrame(data)
df = (
    df.pivot(index="date", columns=["names"], values=["f", "h"])
    .swaplevel(axis=1)
    .sort_index(axis=1)
    .fillna(0)
)
print(df)

Prints:

names       Lily        Rose      Tulip      
               f     h     f    h     f     h
date                                         
2023-01-01   0.0   0.0  10.0  5.0   0.0   0.0
2023-02-01   0.0   0.0  20.0  8.0   5.0   6.0
2023-03-01   0.0   0.0   0.0  0.0  15.0  12.0
2023-04-01  30.0  10.0   0.0  0.0   0.0   0.0

Answer 2

Code

I agree with @Andrej Kesely answer. but if questioner wants to sort in the order of names in names column(Rose->Tulip->Lily), then use this code.

m = {name : num for num, name in enumerate(df['names'].unique())}
df.pivot_table(values=['f', 'h'], index='date', columns='names')\
  .swaplevel(axis=1)\
  .sort_index(key=lambda x: x.get_level_values(0).map(m), axis=1)\
  .fillna(0)

output:

    names   Rose            Tulip           Lily
            f       h       f       h       f       h
date                        
2023-01-01  10.0    5.0     0.0     0.0     0.0     0.0
2023-02-01  20.0    8.0     5.0     6.0     0.0     0.0
2023-03-01  0.0     0.0     15.0    12.0    0.0     0.0
2023-04-01  0.0     0.0     0.0     0.0     30.0    10.0