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
Advertisement
Answer
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