0

I have a pandas dataframe structured like this:

Name Identifier Tags_0_Value Tags_1_Value Tags_2_Value Tags_3_Value Tags_4_Value Tags_5_Value Tags_6_Value
Bottle Nose Well 1345 A- Groundwater Aquifer WL - Water Level Network 104 - Area Wide Map 110 - Area Network 114 - Area Wide Monitoring
BMOs Adventure 3745 A - Groundwater Aquifer HR - Domestic Wells 15 - Baylor County Well Survey 20- Data collection 3 - Water Level Measurable 6 - Onsite 9 - Water Quality

but I want to collapse the columns starting with "Tags" into a single column and append every value to a row indexed by the Name/Identifier, like this:

Name Identifier Tags
Bottle Nose Well 1345 A- Groundwater Aquifer
Bottle Nose Well 1345 WL - Water Level Network
Bottle Nose Well 1345 104 - Area Wide Map
Bottle Nose Well 1345 110 - Area Network
Bottle Nose Well 1345 114 - Area Wide Monitoring
BMOs Adventure 3745 A - Groundwater Aquifer
BMOs Adventure 3745 HR - Domestic Wells
BMOs Adventure 3745 15 - Baylor County Well Survey
BMOs Adventure 3745 20- Data collection
BMOs Adventure 3745 3 - Water Level Measurable
BMOs Adventure 3745 6 - Onsite
BMOs Adventure 3745 9 - Water Quality

I've tried the transpose and pivot functions in Pandas, but already know that's not I need.

Mr. Geologist
  • 75
  • 8

1 Answers1

2

One way is to set_index + stack:

out = df.set_index(['Name','Identifier']).stack().droplevel(-1).reset_index(name='Tags')

Output:

                Name  Identifier                            Tags
0   Bottle Nose Well        1345          A- Groundwater Aquifer
1   Bottle Nose Well        1345        WL - Water Level Network
2   Bottle Nose Well        1345             104 - Area Wide Map
3   Bottle Nose Well        1345              110 - Area Network
4   Bottle Nose Well        1345      114 - Area Wide Monitoring
5     BMOs Adventure        3745         A - Groundwater Aquifer
6     BMOs Adventure        3745             HR - Domestic Wells
7     BMOs Adventure        3745  15 - Baylor County Well Survey
8     BMOs Adventure        3745             20- Data collection
9     BMOs Adventure        3745      3 - Water Level Measurable
10    BMOs Adventure        3745                      6 - Onsite
11    BMOs Adventure        3745               9 - Water Quality