# http://stackoverflow.com/questions/18642402/populate-a-dense-dataframe-given-a-key-value-dataframe
import pandas as pd
import numpy as np
vals = pd.DataFrame(columns=['X','Y','val'],data= [['a','z',5],['b','g',3],['b','y',6],['e','r',9]])
df = pd.DataFrame(index=vals.X.unique(), columns=vals.Y.unique())
def lookup_index(self, row_labels, col_labels):
values = self.values
ridx = self.index.get_indexer(row_labels)
cidx = self.columns.get_indexer(col_labels)
if (ridx == -1).any():
raise ValueError('One or more row labels was not found')
if (cidx == -1).any():
raise ValueError('One or more column labels was not found')
flat_index = ridx * len(self.columns) + cidx
return flat_index
flat_index = lookup_index(df, vals.X, vals.Y)
np.put(df.values, flat_index, vals.val.values)
df
z | g | y | r | |
---|---|---|---|---|
a | 5 | NaN | NaN | NaN |
b | NaN | 3 | 6 | NaN |
e | NaN | NaN | NaN | 9 |