Pandas concatenation of multiple dataframes returns null values
<p>I have a dataframe (<code>df</code>), that I break down into 4 new dfs (<code>media</code>, <code>client</code>, <code>code_type</code>, and <code>date</code>). <code>media</code> has one column of null values, while the other three are only 1-dim dfs, each consisting of nulls. After replacing the nulls in each dataframe, I try to <code>pd.concat</code>to get a single df and get the result below.</p>

<pre><code> code_type
0   P
1   P
2   P
3   P
4   P
5   P

code_name   media_type  acq.    revenue
0   RASH    NaN         50.0     34004.0
1   100     NaN         10.0     1035.0
2   NEWS    NaN         61.0     3475.0
3   DR      NaN         53.0     4307.0
4   SPORTS  NaN         45.0     6503.0
5   DOUBL   NaN         13.0     4205.0

    client_id
0   2.0
1   2.0
2   2.0
3   2.0
4   2.0
5   2.0

    date
0   2016-08-15
1   2016-08-15
2   2016-08-15
3   2016-08-15
4   2016-08-15
5   2016-08-15
</code></pre>

<p>I <code>pd.merge</code> <code>media</code> with another a separate df to replace the NaNs under <code>media.media_type</code>, which appends a new <code>media_type_y</code></p>

<pre><code>code_name   media_type_x    acq.    revenue  media_type_y
0   RASH       NaN          282     34004.0  Radio
1   100        NaN          119     1035.0   NaN
2   NEWS       NaN           81     3475.0   SiriusXM
3   DR         NaN           33     4307.0   SiriusXM
4   SPORTS     NaN           25     6503.0   SiriusXM
5   DOUBL      NaN           23     4205.0   Podcast
</code></pre>

<p>I then drop <code>media_type_x</code> and rename <code>media_type_y</code> to just <code>media_type</code></p>

<pre><code>final = m.loc[:,('code_name','media_type_y', 'acquisition', 'revenue')]
final = final.rename(columns={'media_type_y': 'media_type'})
</code></pre>

<p>So that when I concatenate, I have a complete df.</p>

<pre><code>clean = pd.concat([media, client, code_type, date], axis=1)  

    code    media       acq.    revenue   client code_type  date
0   RASH    Radio       50.0    34004.0     NaN     NaN     NaT
1   100     NaN         10.0    1035.0      NaN     NaN     NaT
2   NEWS    SiriusXM    61.0    3475.0      NaN     NaN     NaT
3   DR      SiriusXM    53.0    4307.0      NaN     NaN     NaT
4   SPORTS  SiriusXM    45.0    6503.0      NaN     NaN     NaT
5   DOUBL   Podcast     13.0    4205.0      NaN     NaN     NaT
</code></pre>

<p><br><code>clean.client</code> is supposed to be all <code>2</code>
<br><code>clean.code_type</code> should be all <code>P</code>
<br><code>clean.date</code> should be all <code>08/15/2016</code> </p>

<p>The dfs by themselves show the data, it's only when I concatenate that I lose the information. I think it may be something with the indexes, but I'm not sure. Could also be something to do with the fact that I have a column with both <code>str</code> and <code>int</code> (see <code>clean.code</code> above) which might be why I get the runtime error listed below.</p>

<blockquote>
  <p>//anaconda/lib/python3.5/site-packages/pandas/indexes/api.py:71: RuntimeWarning: unorderable types: int() &lt; str(), sort order is undefined for incomparable objects
    result = result.union(other)</p>
</blockquote>

<p>Starting with this:</p>

<pre><code>  code_name media_type  acq.  revenue
0      RASH      Radio  50.0  34004.0
1       100        NaN  10.0   1035.0
2      NEWS   SiriusXM  61.0   3475.0
3        DR   SiriusXM  53.0   4307.0
4    SPORTS   SiriusXM  45.0   6503.0
5     DOUBL    Podcast  13.0   4205.0
</code></pre>

<p>Try this: </p>

<pre><code>df['client_id'] = 2
df['date']      = '08/15/2016'
df['code_type'] = 'P'
df

    code_name media_type  acq.  revenue  client_id        date code_type
0      RASH      Radio  50.0  34004.0          2  08/15/2016         P
1       100        NaN  10.0   1035.0          2  08/15/2016         P
2      NEWS   SiriusXM  61.0   3475.0          2  08/15/2016         P
3        DR   SiriusXM  53.0   4307.0          2  08/15/2016         P
4    SPORTS   SiriusXM  45.0   6503.0          2  08/15/2016         P
5     DOUBL    Podcast  13.0   4205.0          2  08/15/2016         P
</code></pre>

python pandas concatenation