How to convert multiple set of column to single column in pandas?

Question

i want to convert a columns(Azi_0 to Azi_47,Dist_0 to Dist_47) in dataframe(df) to a two column(Azimuth,Distance) as in new_df?

Azi = [f"Azi_{i}" for i in range(47)]  
dist = [f"Dist_{i}" for i in range(47)]

sample dataframe,df:

expected output,new_df:

Current_Sim_Az_obj1	Current_Sim_distance_r_obj1	Azimuth	Distance
-60	3.950372041	-59.73007665	3.07
-60	3.950372041	-59.73007665	3.07
-60	6.950372041	-59.4701257	7.89
-60	6.950372041	-59.89004647	7.765
-60	8.950372041	-59.64009363	8.345
-60	8.950372041	-59.58010495	8.425
-60	8.950372041	-59.58010495	8.425
-55	2.38397709	-55.06095763	3.14
-55	2.38397709	-55.21092934	3.065
-55	2.38397709	-55.21092934	3.065
-55	2.38397709	-55.2609199	3.03
-55	2.38397709	-55.2609199	3.03
-55	2.38397709	-55.2609199	3.03
-55	2.38397709	-55.2609199	3.03
-55	2.38397709	-55.03096329	3.105
-55	2.38397709	-55.03096329	3.105
-55	2.38397709	-55.32090858	3
-55	2.38397709	-55.32090858	3
-55	2.38397709	-55.27091802	3.12
-55	2.38397709	-55.27091802	3.12
-55	2.38397709	-55.8508086	3.09
-55	2.38397709	-55.8508086	3.09
-55	2.38397709	-55.57086142	3.065
-55	2.38397709	-55.57086142	3.065

How to combine several columns to a single column?

Check this out https://pandas.pydata.org/docs/reference/api/pandas.wide_to_long.html — Chris, Apr 27 '22 at 13:09
I would try to write a functional example, but since you posted photos of your data instead of pasting the actual data, I cannot — Chris, Apr 27 '22 at 13:10
@Chris,i added the expected out put as table.As the table will be very big,i couldn't add the sample data — Mageo, Apr 27 '22 at 13:27
@BigBen i want the data as expected output.I mean for **Azi_0 to Azi_46** need to convert to a single column **Azimuth** and similarly for distance — Mageo, Apr 27 '22 at 13:37
always put code, data and full error message as text (not screenshot, not link) in question (not in comment). It will be more readable and easier to use in answer, and more people will see it - so more people can help you. — furas, Apr 27 '22 at 20:16

score 1 · Accepted Answer · answered Apr 27 '22 at 13:26

You are essentially asking how to coalesce a values of certain df-columns into one column - you can do it like this:

from random import choice
import pandas as pd

# all azimuth names
azi_names = [f"Azi_{i}" for i in range(5)]  

# all distance names
dist_names = [f"Dist_{i}" for i in range(5)]

df = pd.DataFrame(columns = azi_names + dist_names)

# put some values in
for i in range(20):
    k = choice(range(5))
    df = df.append({f"Azi_{k}": i, f"Dist_{k}": i}, ignore_index=True)

print(df)

which randomly creates:

    Azi_0  Azi_1  Azi_2  Azi_3  Azi_4  Dist_0  Dist_1  Dist_2  Dist_3  Dist_4
0     NaN    NaN    NaN    0.0    NaN     NaN     NaN     NaN     0.0     NaN
1     NaN    1.0    NaN    NaN    NaN     NaN     1.0     NaN     NaN     NaN
2     2.0    NaN    NaN    NaN    NaN     2.0     NaN     NaN     NaN     NaN
3     NaN    NaN    3.0    NaN    NaN     NaN     NaN     3.0     NaN     NaN
4     NaN    4.0    NaN    NaN    NaN     NaN     4.0     NaN     NaN     NaN
5     NaN    NaN    NaN    NaN    5.0     NaN     NaN     NaN     NaN     5.0
6     6.0    NaN    NaN    NaN    NaN     6.0     NaN     NaN     NaN     NaN
7     NaN    7.0    NaN    NaN    NaN     NaN     7.0     NaN     NaN     NaN
8     NaN    8.0    NaN    NaN    NaN     NaN     8.0     NaN     NaN     NaN
9     9.0    NaN    NaN    NaN    NaN     9.0     NaN     NaN     NaN     NaN
10    NaN    NaN   10.0    NaN    NaN     NaN     NaN    10.0     NaN     NaN
11   11.0    NaN    NaN    NaN    NaN    11.0     NaN     NaN     NaN     NaN
12   12.0    NaN    NaN    NaN    NaN    12.0     NaN     NaN     NaN     NaN
13    NaN    NaN   13.0    NaN    NaN     NaN     NaN    13.0     NaN     NaN
14    NaN   14.0    NaN    NaN    NaN     NaN    14.0     NaN     NaN     NaN
15    NaN    NaN    NaN   15.0    NaN     NaN     NaN     NaN    15.0     NaN
16    NaN    NaN    NaN    NaN   16.0     NaN     NaN     NaN     NaN    16.0
17    NaN    NaN   17.0    NaN    NaN     NaN     NaN    17.0     NaN     NaN
18    NaN    NaN    NaN    NaN   18.0     NaN     NaN     NaN     NaN    18.0
19    NaN    NaN    NaN   19.0    NaN     NaN     NaN     NaN    19.0     NaN

To coalesce this and only keep filled values you use

df2 = pd.DataFrame()

# propagates values and chooses first
df2["AZI"] = df[azi_names].bfill(axis=1).iloc[:, 0]
df2["DIS"] = df[dist_names].bfill(axis=1).iloc[:, 0]

print(df2)

to get a coalesced new df:

     AZI   DIS
0    0.0   0.0
1    1.0   1.0
2    2.0   2.0
3    3.0   3.0
4    4.0   4.0
5    5.0   5.0
6    6.0   6.0
7    7.0   7.0
8    8.0   8.0
9    9.0   9.0
10  10.0  10.0
11  11.0  11.0
12  12.0  12.0
13  13.0  13.0
14  14.0  14.0
15  15.0  15.0
16  16.0  16.0
17  17.0  17.0
18  18.0  18.0
19  19.0  19.0

Attributation: inspired by Erfan's answer to Coalesce values from 2 columns into a single column in a pandas dataframe

You may need to Replacing blank values (white space) with NaN in pandas for your shown data.

@Mageo and this answer explains how you do that. You can copy your original DF, you add on the "Coalesced" columns, you delete all the other `Azi_*` and `Dist_*` columns and leave `Current_Sim_Az_obj1` and `Current_Sim_distance_r_obj1` be - then you have your "expected" output. If you do not understand what `df[azi_names].bfill(axis=1).iloc[:, 0]` does I would suggest reading the API or playing with your data. — Patrick Artner, Apr 27 '22 at 13:39
@Mageo you put data as image so don't expect that we will rewrite data from image to create exactly the same result. — furas, Apr 27 '22 at 20:15

How to convert multiple set of column to single column in pandas?

1 Answers1