CODEWITHSUNDEEP
pythonpandasdataframenan
Mark M
Mark M

Reputation: 31

Eliminate several Nan using Pandas Pivot from list

My python list is:

prop = [('City', ' Python Town'),
 ('Property Type', ' Townhouse'),
 ('Square Feet', ' 1400'),
 ('Zip Code', ' 5000'),
 ('Bathrooms', ' 3'),
 ('Bedrooms', ' 3'),
 ('MLS', ' 0202'),
 ('Price1', ' 1295000'),
 ('Posted On', ' Dec 1, 2019')]

If I index it, an example would be :

prop[0][0] = City
prop[0][1] = Python Town

Converted to dataframe:

df = pd.DataFrame(prop)
df.pivot(columns = 0, values = 1)

Get this:

    Bathrooms   Bedrooms    City         MLS    Posted On   Price1  Property Type   Square Feet Zip Code
0   NaN         NaN         Python Town NaN     NaN         NaN     NaN             NaN         NaN
1   NaN         NaN         NaN         NaN     NaN         NaN     Townhouse       NaN         NaN
2   NaN         NaN         NaN         NaN     NaN         NaN     NaN             1485        NaN
3   NaN         NaN         NaN         NaN     NaN         NaN     NaN             NaN         5000
4   3           NaN         NaN         NaN     NaN         NaN     NaN             NaN         NaN
5   NaN           3         NaN         NaN     NaN         NaN     NaN             NaN         NaN
6   NaN         NaN         NaN         0202    NaN         NaN     NaN             NaN         NaN
7   NaN         NaN         NaN         NaN     NaN         1295000 NaN             NaN         NaN
8   NaN         NaN         NaN         NaN     Dec 10, 2019NaN     NaN             NaN         NaN

How to I eliminate the row index and make it 1 row while eliminated all the unnecessary Nan's?

Upvotes: 2

Views: 91

Answers (2)

oppressionslayer
oppressionslayer

Reputation: 7214

Before the pivot, You could transform it, to get the output like this:

df.T 

              0              1            2         3          4         5      6         7             8
0          City  Property Type  Square Feet  Zip Code  Bathrooms  Bedrooms    MLS    Price1     Posted On
1   Python Town      Townhouse         1400      5000          3         3   0202   1295000   Dec 1, 2019

You can also make those column headers with this:

df1 = df.T
new_columns = df1.T[:][0]
df1 = df1[1:]
df1.columns = new_columns
df1 = df1.reset_index(drop=True)
df1 = df1.rename_axis(columns=None)

           City Property Type Square Feet Zip Code Bathrooms Bedrooms    MLS    Price1     Posted On
0   Python Town     Townhouse        1400     5000         3        3   0202   1295000   Dec 1, 2019

depending on how you want it formatted.

Upvotes: 0

ambitiousdonut
ambitiousdonut

Reputation: 394

I wouldn't use pivot for something this small. You can just transpose either before or after making the dataFrame

>> import numpy as np
>> df = pd.DataFrame(np.transpose(prop))
>> print(df)

              0              1            2  ...      6         7             8
0          City  Property Type  Square Feet  ...    MLS    Price1     Posted On
1   Python Town      Townhouse         1400  ...   0202   1295000   Dec 1, 2019

If you want the first row as the index: 

>> df.columns = df.loc[0]
>> df.drop(0, axis=0, inplace=True)
>> df.reset_index(drop=True, inplace=True)
>> print(df)


           City  Property Type  ...   Price1        Posted On
0   Python Town      Townhouse  ...   1295000      Dec 1, 2019

Upvotes: 1

Related Questions