I get a csv that if you read it, looks like:
import pandas as pd
df = pd.DataFrame([['de,ch,fr', '1,2,3'],['fr,ch,dk', '3,4,5']], columns=['countries', 'numbers'], index=['abc', 'bcd'])
I want to make it look like this:
df = pd.DataFrame([[1,2,3,0], [0,4,3,5]], columns=['de_number', 'ch_number', 'fr_number', 'dk_number'], index=['abc', 'bcd'])
Meaning exploding the countries column and getting for every value in index the number for every country in a separate column. I have the list of all the countries for this dataframe beforehand (meaning that I knew beforehand that I'm going to have the values ['de', 'ch', 'fr', 'dk'])
Is there a nice clean way of doing it? Everything that comes into my mind is quite messy.