Joining hundreds of columns and column indexes into a string
<p>I've got what I hope is a unique/interesting problem for my first question on Stack Overflow!</p>

<p>I have data on skills assessments, currently in a very large pandas dataframe. Each row represents a student, and each column contains their scores for a particular skills assessment. There are about 200 skill assessments in total, with each student having a score in only a small subset of these assessments (1 - 20 scores is typical, but some students have more).</p>

<p>Example dataframe structure:</p>

<pre><code>id    skill1  skill2  skill3  skill4  skill5 ....
1     10      50      NaN     3       NaN
2     Nan     10      2       70      NaN
3     23      NaN     45      NaN     5
</code></pre>

<p>I am attempting to get this data transformed into a space-delimited string for each student, in the following format, so that we can import it into a different datastore: </p>

<pre><code>skill1:10 skill2:50 skill4:3
skill2:10 skill3:2 skill4:70
</code></pre>

<p>(notice how skills without assessments scores don't get added to the list)</p>

<p>I've created a lambda function to join all of these skill values with their column labels:</p>

<pre><code>skillmerge = lambda row: ' '.join([str(row.index[i])+':'+str(row[i]) for i in range(0,len(row)) if row[i]!=np.nan])
</code></pre>

<p>When I created a single series (1 student) to test on, the lambda function takes less than a second to create the output string in my desired format. However, when I create a dataframe with just 2 rows (again for testing purposes), the function takes several minutes just to complete those 2 rows:</p>

<pre><code>testing_df['combined_skills'] = testing_df.apply(skillmerge, axis=1)
</code></pre>

<p>Seeing as how I have a couple million students in this dataset, I am looking for way to make this process reliably work faster. Any thoughts on where I can fix this?</p>

<p>Thanks in advance for helping with my first SO question! :D</p>

<p>Try this: </p>

<pre><code>ld = df.set_index('id').fillna("").to_dict(orient='records')
ll = [' '.join([ k +":"+ str(v) for k,v in x.iteritems() if v != "" ]) for x in ld ]
ll 

 ['skill2:50.0 skill1:10.0 skill4:3.0',
 'skill3:2.0 skill2:10.0 skill4:70.0',
 'skill3:45.0 skill1:23.0 skill5:5.0']
</code></pre>

python pandas dataframe