How to concatenate pairs of row elements into a new column in a pandas dataframe?

Question

I have this DataFrame where the columns are coordinates (e.g. x1,y1,x2,y2...). The coordinate columns start from the 8th column (the previous ones are irrelevant for the question)
I have a larger example sample here, but here's a sample:

start_column = 8    
df = pd.DataFrame(columns = ['x1','y1','x2','y2'],
                 data = [(0,0,1,0),(0,1,2,3),(-1,-2,None,None)])
for i in range(7):
    df.insert(0,'c'+str(7-i),'x')
df

I want to create a new column in the DataFrame as a list of xy pairs, as in: df["coordinates"]=[[x1,y1],[x2,y2],[x3,y3]....]

What I've tried so far:

for row in df.iterrows():
   for i in range(1,total_count_of_xy_rows):
      df["coordinates"]= 
             df[["x{}".format(i),"y{}".format(i)]].values.tolist()
   print(df)

Is there a better way to do this?

I need to add one more column in the df which can contains the list of the row [[x1,y1],[x2,y2]....] — A.D, Sep 25 '19 at 06:06
And I am unable to count the number of x and y columns containing the data . Because some rows has x y upto 60 measn x60 and y60 and some row having the data upto x25 y25 hence the other are showing NAN . — A.D, Sep 25 '19 at 06:09
A dataframe snapshot is no good. Since your problem is not related to reading from the Excel file, you should provide a sample dataframe (even small), as either a generating code (like I added in edit) or a copy-paste. To learn more, please visit: https://stackoverflow.com/questions/20109391/how-to-make-good-reproducible-pandas-examples — Itamar Mushkin, Sep 25 '19 at 06:12

Itamar Mushkin · Accepted Answer · 2019-09-25T08:24:21.837

1

You can create the new column by .apply-ing a custom list comprehension function across the different rows:

start_column = 8    
coordinates_list = list(zip(df.columns[(start_column-1):-1:2],df.columns[start_column::2]))
df['coordinates'] = df.apply(lambda row: [(row[x], row[y]) 
                                          for x,y in coordinates_list if not any((pd.isna(row[x]), pd.isna(row[y])))], axis=1)

Using this example input, with the coordinate columns starting from the 8th column, as you stated in a comment:

df = pd.DataFrame(columns = ['x1','y1','x2','y2'],
                 data = [(0,0,1,0),(0,1,2,3),(-1,-2,None,None)])
for i in range(start_column-1):
    df.insert(0,'c'+str(start_column-1-i),'x')
df

    c1  c2  c3  c4  c5  c6  c7  x1  y1  x2  y2
0   x   x   x   x   x   x   x   0   0   1.0 0.0
1   x   x   x   x   x   x   x   0   1   2.0 3.0
2   x   x   x   x   x   x   x   -1  -2  NaN NaN

This will produce this output:

c1  c2  c3  c4  c5  c6  c7  x1  y1  x2  y2  coordinates
0   x   x   x   x   x   x   x   0   0   1.0 0.0 [(0, 0), (1.0, 0.0)]
1   x   x   x   x   x   x   x   0   1   2.0 3.0 [(0, 1), (2.0, 3.0)]
2   x   x   x   x   x   x   x   -1  -2  NaN NaN [(-1, -2)]

This deals with the unequal number of coordinates in each row. Hope that helps!

edited Sep 25 '19 at 08:24

answered Sep 25 '19 at 06:10

Itamar Mushkin

2,803
2
16
32

I need to start the list zip from the 8th column means from x and y are starting from 8th column . How to for this ? – A.D Sep 25 '19 at 06:40
Please assist, I am unable to provide the above conditon where x y are starting from the 8 th column number – A.D Sep 25 '19 at 07:00
I've edited your question again to include this new condition, and my answer to accommodate it. In the future, please make sure that your answer includes all the information needed to answer the question. I've edited your question to make sure the example data includes this new condition. – Itamar Mushkin Sep 25 '19 at 07:22
Related to this answer I have posted one question kindly suggest for the issue https://stackoverflow.com/questions/58109579/how-to-create-the-subcolumns-in-the-dataframe-and-add-the-data-using-pandas-in-p?noredirect=1#comment102609387_58109579 – A.D Sep 26 '19 at 04:57
Kindly suggest for the issue posted at https://stackoverflow.com/questions/58112213/how-to-read-the-sublist-from-the-main-list-and-add-the-sublist-in-the-another-da – A.D Sep 26 '19 at 13:05

How to concatenate pairs of row elements into a new column in a pandas dataframe?

1 Answers1

Linked