Vfrdtyky

I have a pandas dataframe like this below. This gives me the distances in degrees from individual points to the following cities,viz, Fargo,Orange and Jersey City. But each column in the below dataframe like 'Fargo' has its row numbers 0 through 3 populated by the shortest 4 distances to any of the points and for the rest of the 8 rows it is getting populated because we are finding out the 4 shortest distances to another city 'Orange' and so on. To summarise from the below dataframe

Points = ['Point1','Point4','Point5','Point2','Point2','Point5','Point1','Point4','Point3','Point6','Point4','Point1']

Fargo = [2.90300755828,3.91961324034,21.9825588597,24.3141420303,24.3141420303,21.9825588597,2.90300755828,3.91961324034,25.3599772676,25.8509998739,3.91961324034,2.90300755828]

Orange = [25.5464458592,27.1527975618,6.17298387907,4.80214941294,4.80214941294,6.17298387907,25.5464458592,27.1527975618,46.4066249652,45.8853687976,27.1527975618,25.5464458592]

Jersey_City = [21.1030418227,19.6763385681,39.3194029761,41.8121131045,41.8121131045,39.3194029761,21.1030418227,19.6763385681,2.09632277264,2.67885042284,19.6763385681,21.1030418227]

toy_data=pd.DataFrame(index=Points,columns=['Fargo','Orange','Jersey_City'])

toy_data['Fargo']= Fargo

toy_data['Orange']=Orange

toy_data['Jersey_City']=Jersey_City

Lets say for the column Fargo the first 4 rows : rows 0 through 3 represent the points which have the shortest distances from Fargo. Similarly in the column Orange rows 4 through 7 represent the points which have the shortest distances to Orange and now in rows 4 through 7 the column Fargo gets populated with the distances from the nearest four points to Orange. But I want a frame where I get the 4 points having the shortest distances to each City in one single dataframe. So what you see here in the column Fargo rows 0-3 are its 4 nearest points,in column Orange, rows 4-7 are its nearest 4 points, in column Jersey City the rows 8-11 are its 4 nearest points. I want to keep those 4 nearest points for each city and remove the remaining as I have done below.
What I want is this:

Fargo = [2.9030075582789885,3.919613240342197,21.982558859743925,24.314142030334484,'NAN','NAN','NAN','NAN','NAN','NAN','NAN','NAN']

Orange = ['NAN','NAN','NAN','NAN',4.802149412942695,6.172983879065276,25.546445859236265,27.15279756182145,'NAN','NAN','NAN','NAN']

Jersey_City = ['NAN','NAN','NAN','NAN','NAN','NAN','NAN','NAN',2.096322772642856,2.67885042283533,19.676338568056806,21.10304182269932]

result_wanted_data =pd.DataFrame(index= Points,columns = ['Fargo','Orange','Jersey_City'])

result_wanted_data['Fargo']=Fargo

result_wanted_data['Orange']=Orange

result_wanted_data['Jersey_City']=Jersey_City

What you can do is not exactly that what I guess you wanted but I think this will solve the purpose:

newdf=np.empty([12])



for i in range(12):

    newdf[i]=data.iloc[i,[(math.ceil((i+1)/4))]]

newdf1=

cities=list(data.columns.values[1:])

for i in range(12):

     newdf1.append(cities[(math.ceil((i+1)/4)-1)])

strs = ["" for x in range(12)]  

for i in range(12):



    strs[i]=data.iloc[i,0]



final_data=pd.DataFrame(columns=['city','point','distance' ])

final_data['city']=newdf1

final_data['distance']=newdf

final_data['point']=strs 

You can use the following:

intervals = np.array_split(np.arange(toy_data.shape[0]), 3)

df = pd.DataFrame(columns=['Distances'], index=toy_data.reset_index().index)

for i, j in zip(range(toy_data.shape[1]), intervals):

    df.loc[j,'Distances'] = toy_data.reset_index(drop=True).iloc[j,i]



print(df)



    Distances

0    2.90301

1    3.91961

2    21.9826

3    24.3141

4    4.80215

5    6.17298

6    25.5464

7    27.1528

8    2.09632

9    2.67885

10   19.6763

11    21.103

You can use np.split() and a for loop:

x = 0

split = 

for num in range(len(toy_data.columns)-1):

    split.append(x+4)

    x+=4



dfs = np.split(toy_data, split)



data = 

for i in range(len(dfs)):

    data.append(pd.DataFrame(dfs[i][dfs[i].columns[i]]))

pd.concat(data, sort=False)



          Fargo   Orange    Jersey_City

Point1  2.903008    NaN         NaN

Point4  3.919613    NaN         NaN

Point5  21.982559   NaN         NaN

Point2  24.314142   NaN         NaN

Point2     NaN     4.802149     NaN

Point5     NaN     6.172984     NaN

Point1     NaN     25.546446    NaN

Point4     NaN     27.152798    NaN

Point3     NaN       NaN      2.096323

Point6     NaN       NaN      2.678850

Point4     NaN       NaN      19.676339

Point1     NaN       NaN      21.103042