Pandas: Three listed columns to wide format

Solution

Split the strings in attribute like columns around delimiter , to convert into lists, then explode to convert lists into individual rows, then pivot with columns=attributes to reshape, finally flatten the multindex using map + join

y = x.set_index(['id', 'count'])
y = y.apply(lambda s: s.str.split(',')).explode([*y])
y = y.pivot(columns='attributes')
y.columns = y.columns.map('_'.join)
y = y.reset_index()

>>> y
     id  count attribute_values_MIN attribute_values_TEMP attribute_values_VIS attribute_years_MIN attribute_years_TEMP attribute_years_VIS
0    11      7                    2                     4                    0                2002                 2001                2000
1   998      7                    2                     0                    3                2001                 2003                2002
2  3923      7                    0                   NaN                    9                2008                  NaN                2009

I guess there is no native function in pandas to split strings and create new columns from them. However, you can easily write your own little function. Assuming your example

import pandas as pd

x = pd.DataFrame({'id': [1,2,3],
'attributes': ['VIS,TEMP,MIN','MIN,VIS,TEMP','MIN,VIS'],
'attribute_values': ['0,4,2','2,3,0','0,9'],
'attribute_years': ['2000,2001,2002','2001,2002,2003','2008,2009']})

You can just loop over the rows, split the strings in the columns x['attributes'], x['attribute_values'], and x['attribute_years'], split the strings (always assuming that we don't need to fear different length and that ',' indicates the character to split the strings) and create a new dictionary. Collecting all dictionaries with the new key-value pairs, you can just build a new pandas.DataFrame and assign it to the original if you like:

data = []
for i, row in x.iterrows():
    # extract data
    att = row['attributes'].split(',')
    val = list(map(int, row['attribute_values'].split(',')))
    yrs = list(map(int, row['attribute_years'].split(',')))
    # create new dictionaries
    row_new = {f'attribute_values_{a}': v for a, v in zip(att, val)}
    row_new.update({f'attribute_years_{a}': y for a, y in zip(att, yrs)})
    # concatenate dictionaries and append to list
    data.append(row_new)
# create table from list of rows
pd.DataFrame(data, index=x.index)

output: