Search code examples
pythonpandasconcatenation

Why does my output dataframe have two columns with same indexes?

I have two dataframes

results:

0  2211 E Winston Rd Ste B, 92806, CA  33.814547 -117.886028   4  
1            P.O. Box 5601, 29304, SC  34.945855  -81.930035   6    
2           4113 Darius Dr, 17025, PA  40.287768  -76.967292   8

acctypeDF:

0    rooftop
1    place
2    rooftop

I wanted to combine both these dataframes into one so i did:

import pandas as pd
resultsfinal = pd.concat([results, acctypeDF], axis=1)

But the output is:

resultsfinal
Out[155]: 
                                    0          1           2   3        0
0  2211 E Winston Rd Ste B, 92806, CA  33.814547 -117.886028   4  rooftop
1            P.O. Box 5601, 29304, SC  34.945855  -81.930035   6    place
2           4113 Darius Dr, 17025, PA  40.287768  -76.967292   8  rooftop

As you can see the output is repeating the index number 0.Why does this happen? My objective is to drop the first index(first column) which has addresses, but I am getting this error:

resultsfinal.drop(columns='0')
 raise KeyError('{} not found in axis'.format(labels))

KeyError: "['0'] not found in axis"

I also tried:

resultsfinal = pd.concat([results, acctypeDF], axis=1,ignore_index=True)

resultsfinal
Out[158]: 
                                    0          1  ...        4        5
0  2211 E Winston Rd Ste B, 92806, CA  33.814547  ...  rooftop  rooftop
1            P.O. Box 5601, 29304, SC  34.945855  ...    place    place

But as you see above, even though the issue of index 0 repeating goes away, it creates a duplicate column(5)

If i do:

resultsfinal = results[results.columns[1:]]

resultsfinal
Out[161]: 
           1           2  ...                                   0        0
0  33.814547 -117.886028  ...  2211 E Winston Rd Ste B, 92806, CA  rooftop
1  34.945855  -81.930035  ...            P.O. Box 5601, 29304, SC    place

print(resultsfinal.info())
<class 'pandas.core.frame.DataFrame'>
RangeIndex: 10 entries, 0 to 9
Data columns (total 5 columns):
0    10 non-null object
1    10 non-null float64
2    10 non-null float64
3    10 non-null int64
4    10 non-null object
dtypes: float64(2), int64(1), object(2)
memory usage: 480.0+ bytes
Solution

  • Use ingnore_index=True:

    resultsfinal = pd.concat([results, acctypeDF], axis=1,ignore_index=True)

    or 

    resultsfinal = pd.concat([results, acctypeDF], axis=1)
resultsfinal.columns=range(len(resultsfinal.columns))
print(resultfinal)

    remove the first column:

    resultsfinal[resultsfinal.columns[1:]]