title: Converting values of old columns into new columns, and using old values of paired columns as values in the new columns
body: <p>I've been tasked with cleaning data from a mobile application designed by a charity</p>
  <p>In one section, a users Q/A app use session is represented by a row. This section consists of repeated question answer field pairs, where a field represents the question asked and then the field next to it represents the corresponding answer. Together, each question/field and answer column pair represent a unique question with the answer.</p>
  <p>Starting data</p>
  <pre><code>_id                     answers.1.answer answers.0.fieldName answers.1.answer    answers.1.fieldName
  
  5f27f29c362a380d3f9a9e46    1.0           enjoyment              20.0        affectedkneeside           
  5f27f2ac362a380d3f9a9e4b    0.0           fatigue2weeks          40.0        avoidexercise             
  5f27f4d4362a380d3f9a9e52    1.0           height                 50.0        painlocationknee
  </code></pre>
  <p><a href="https://i.stack.imgur.com/gCyzI.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/gCyzI.png" alt="enter image description here" /></a></p>
  <p>I have been asked to reformat the section so each question forms a column, the corresponding answer a field in that column</p>
  <p>The ideal output</p>
  <pre><code>_id                    avoidexercise    enjoyment   fatigue2weeks   height
          
  5f27f29c362a380d3f9a9e46    1.0           yes            20.0       120.0
  5f27f2ac362a380d3f9a9e4b    0.0           no             40.0       180.0
  5f27f4d4362a380d3f9a9e52    1.0           yes            50.0       150.0
  </code></pre>
  <p>My plan is to create many pivot tables, from each other Q/A pairs of columns, then concaternate (outer join) then inner join to remove duplications</p>
  <p>However, the original dataframe contains a mixture of numeric and object datatypes</p>
  <p>Therefore, only some question/answer column pairs appear to be converting to pivot tables. I have tried using various aggregate functions</p>
  <pre><code>p1 = ur.pivot_table(index=['_id'],columns= ['answers.0.fieldName'],values=['answers.0.answer'],aggfunc=lambda x: ' '.join(x))
  p2 = ur.pivot_table(index=['_id'],columns= ['answers.1.fieldName'],values=['answers.1.answer'],aggfunc=lambda x: ' '.join(x))
  p3 = ur.pivot_table(index=['_id'],columns= ['answers.2.fieldName'],values=['answers.2.answer'],aggfunc=lambda x: ' '.join(x))
  </code></pre>
  <p>I have also tried another lambda function</p>
  <pre><code>p1 = ur.pivot_table(index=['_id'],columns= ['answers.0.fieldName'],values=['answers.0.answer'],aggfunc=lambda x: ' '.join(str(v) for v in x)
  </code></pre>
  <p>The furthest I have got so far is to run pivots with standard mean aggfunc</p>
  <pre><code>p1 = ur.pivot_table(index=['_id'],columns=['answers.0.fieldName'],values=['answers.0.answer'])
  ps = [p1,p2,p3]
  c = pd.concat(ps)
  </code></pre>
  <p>Then attempting to remove merge rows and columns</p>
  <pre><code>df = c.sum(axis=1, level=1, skipna=False)
  
  g = df.groupby('_id').agg(np.sum)
  </code></pre>
  <p>This returns a dataframe with the right shape</p>
  <p>However, it looses the values in the object columns, and I'm not sure how accurate all the numeric columns are</p>
  <p>To overcome this problem, I was considering converting as much data as I can into numeric</p>
  <pre><code>c4 = c.apply(pd.to_numeric, errors='ignore').info()
  </code></pre>
  <p>Then splitting the combined pivot table dataframe into numeric and object type</p>
  <pre><code>nu = ['int16', 'int32', 'int64', 'float16', 'float32', 'float64']
  cndf = c4.select_dtypes(include=nu)
  o = ['object', 'bool', 'datetime64', 'category']
  codf = c4.select_dtypes(include=o)
  </code></pre>
  <p>And running through the same .sum and groupby operations as above on the numeric dataframe</p>
  <pre><code>n1 = cndf.sum(axis=1, level=1, skipna=False)
  n2 = n1.groupby('_id').agg(np.sum)
  </code></pre>
  <p>However, this still leaves the challenge of dealing with the object columns</p>