python - Transpose 3 column excel with K:V into column Pandas

Question

I have a 3 column excel file I'm reading into pandas with basically k:v pairs in columns

                        stuff     Unnamed: 1            Unnamed: 2
0                    animal:dog            NaN                   NaN
1                   color:brown            NaN                   NaN
2                        age:12            NaN                   NaN
3                  height: 60cm   doctor: mike  visit date: 01/12/21
4   unique animal id:3597230857   doctor id: 2                   NaN
5                           NaN            NaN                   NaN
6                   animal: cat            NaN                   NaN
7                  color: orage            NaN                   NaN
8                        age:21            NaN                   NaN
9                  height: 40cm   doctor: mike                   NaN
10   unique animal id: 95760203   doctor id: 2   visit date:05/21/20
11                          NaN            NaN                   NaN
12                animal: snake            NaN                   NaN
13                 color: orage            NaN                   NaN
14                        age:3  doctor:sally                    NaN
15     unique animal id: 325125    doctor id:1   visit date:05/21/20
16                          NaN            NaN                   NaN
17                          NaN            NaN                   NaN
18                 animal: bird            NaN                   NaN
19                  color: blue            NaN                   NaN
20                 height: 40cm  doctor:sally                    NaN
21  unique animal id: 496701275    doctor id:1   visit date:05/21/20

Process finished with exit code 0

I need to not only tie the information in unnamed:1 & unnamed:2 to the unique animal ID as this is how I will track the animal but also transpose these columns where everything to the left of the ":" is the column header w/ appropriate values.

  animal   color   age  height(cm)  unique animal id doctor  dictir id visit date
0    dog   brown  12.0        60.0        3597230857   mike          2 2021-01-12
1    cat   orage  21.0        40.0          95760203   mike          2 2020-05-21
2  snake  orange   3.0         NaN            325125  sally          1 2020-05-21
3   bird    blue   NaN        40.0         496701275  sally          1 2020-05-21

Currently working off of

    for source in sources:
        df = pd.read_excel(source)
    df['new_heading'] = df['stuff'].str.split(':').str[0]
    df['new_value'] = df['stuff'].str.split(':').str[1]
    df.loc[df['new_heading'] == "animal", 'new_aninmal'] = df['new_value']
    df.loc[df['new_heading'] == "color", 'new_color'] = df['new_value']
    df.loc[df['new_heading'] == "age", 'new_age'] = df['new_value']
    df.loc[df['new_heading'] == "height", 'new_height'] = df['new_value']
    df.loc[df['new_heading'] == "unique animal id", 'new_uid'] = df['new_value']

but it gives this waterfall output (expectedly so)

                          stuff     Unnamed: 1            Unnamed: 2       new_heading   new_value new_aninmal new_color new_age new_height     new_uid
0                    animal:dog            NaN                   NaN            animal         dog         dog       NaN     NaN        NaN         NaN
1                   color:brown            NaN                   NaN             color       brown         NaN     brown     NaN        NaN         NaN
2                        age:12            NaN                   NaN               age          12         NaN       NaN      12        NaN         NaN
3                  height: 60cm   doctor: mike  visit date: 01/12/21            height        60cm         NaN       NaN     NaN       60cm         NaN
4   unique animal id:3597230857   doctor id: 2                   NaN  unique animal id  3597230857         NaN       NaN     NaN        NaN  3597230857
5                           NaN            NaN                   NaN               NaN         NaN         NaN       NaN     NaN        NaN         NaN
6                   animal: cat            NaN                   NaN            animal         cat         cat       NaN     NaN        NaN         NaN
7                  color: orage            NaN                   NaN             color       orage         NaN     orage     NaN        NaN         NaN
8                        age:21            NaN                   NaN               age          21         NaN       NaN      21        NaN         NaN
9                  height: 40cm   doctor: mike                   NaN            height        40cm         NaN       NaN     NaN       40cm         NaN
10   unique animal id: 95760203   doctor id: 2   visit date:05/21/20  unique animal id    95760203         NaN       NaN     NaN        NaN    95760203
11                          NaN            NaN                   NaN               NaN         NaN         NaN       NaN     NaN        NaN         NaN
12                animal: snake            NaN                   NaN            animal       snake       snake       NaN     NaN        NaN         NaN
13                 color: orage            NaN                   NaN             color       orage         NaN     orage     NaN        NaN         NaN
14                        age:3  doctor:sally                    NaN               age           3         NaN       NaN       3        NaN         NaN
15     unique animal id: 325125    doctor id:1   visit date:05/21/20  unique animal id      325125         NaN       NaN     NaN        NaN      325125
16                          NaN            NaN                   NaN               NaN         NaN         NaN       NaN     NaN        NaN         NaN
17                          NaN            NaN                   NaN               NaN         NaN         NaN       NaN     NaN        NaN         NaN
18                 animal: bird            NaN                   NaN            animal        bird        bird       NaN     NaN        NaN         NaN
19                  color: blue            NaN                   NaN             color        blue         NaN      blue     NaN        NaN         NaN
20                 height: 40cm  doctor:sally                    NaN            height        40cm         NaN       NaN     NaN       40cm         NaN
21  unique animal id: 496701275    doctor id:1   visit date:05/21/20  unique animal id   496701275         NaN       NaN     NaN        NaN   496701275

question from:https://stackoverflow.com/questions/65876633/transpose-3-column-excel-with-kv-into-column-pandas

Answer 1

Each section starts with animal, so create a counter that acknowledges that. the counter will be used in pivoting the data:

reshape = (
    df.assign(counter=np.where(df.stuff.str.startswith("animal:"), 1, 0).cumsum())
    .melt("counter")
    .dropna()
    .drop(columns="variable")
)

Here we split the column with : and pivot the data; the counter ensures a unique index, which is required for a successful pivot:

    (
    reshape.value.str.split(":", expand=True)
    .assign(counter=reshape.counter)
    .pivot("counter", 0, 1)
)
    

        age     animal  color   doctor  doctor id   height  unique animal id    visit date
counter                                 
1       12      dog     brown   mike      2        60cm     3597230857  01/12/21
3       21      cat     orage   mike      2        40cm     95760203    05/21/20
5       3       snake   orage   sally     1         NaN     325125  05/21/20
8      NaN      bird    blue    sally     1        40cm     496701275   05/21/20