1

This data frame consists of a lot of NAN values and in the latter columns, the data is sparse. But I want all the into a single column.

enter image description here

Like this,

Index    Seatblock 
1        A.B.C.D
2        A.B.C.D
3        A.B.C.D
4        A.B.C.D
5        A.B.C.D
  1. Remove all NAN values. I tried dropna but it basically drops the columns off which also vanishes my sparse data.
  2. Convert them into a single column, either using list or any python built-in function but I'm quite not able to find one.

Would be helpful. Thanks!

Data is here

EDIT: This is the output after applying the suggested code. enter image description here

EDIT:

For example

Index Seatblock1 Seatblock2 Seatblock3
1       a.b.c.d    NAN         NAN
2       w.r.t.q    q.r.y.t     NAN
3       p.y.u.i    NAN         n.b.x.a

OUTPUT has to be:


Index Seatblock1  
1       a.b.c.d             
2       w.r.t.q    
3       p.y.u.i        
4       q.r.y.t
5       n.b.x.a

Like this

kirti purohit
  • 401
  • 1
  • 4
  • 18
  • Does this answer your question? [Merge multiple column values into one column in python pandas](https://stackoverflow.com/questions/33098383/merge-multiple-column-values-into-one-column-in-python-pandas) – AKS Jul 07 '21 at 02:54
  • No. I did checked. I don't want concatenation. I want the values to be stored down and down in one single column from all these columns – kirti purohit Jul 07 '21 at 02:55
  • That's exactly what is being done in the other post. The only additional part is that you need to [drop the other columns](https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.DataFrame.drop.html) to get just the `Index` and `Seatblock`. – AKS Jul 07 '21 at 02:57
  • what is the logic for converting values in all the different seatblock columns into a single column value: a.b.c.d? – Mutaz-MSFT Jul 07 '21 at 02:58
  • @Mutaz-MSFT a.b.c.d is the example for values in all columns. (resemblance) – kirti purohit Jul 07 '21 at 03:01
  • @AKS But it gives me the dataframe as it is. Doesn't merge my data into one column – kirti purohit Jul 07 '21 at 03:02
  • What do you mean that it doesn't merge? After the `apply` command did you check `df[['Key', 'Seatblock']]` to see if that worked? – AKS Jul 07 '21 at 03:04
  • @AKS Please check the output in the edited answer – kirti purohit Jul 07 '21 at 03:06
  • The length of columns is same and other values which needed to be put under first column are still sparse – kirti purohit Jul 07 '21 at 03:07
  • @kirtipurohit Please try what I have mentioned in the comment above. `df[['Key', 'Seatblock']]` – AKS Jul 07 '21 at 03:07
  • How? Put it on the LHS of the code answered below? – kirti purohit Jul 07 '21 at 03:09
  • At the end, you have written `df` instead of that use what is suggested above. – AKS Jul 07 '21 at 03:10
  • Gosh no, I don't want to concatentae values @AKS – kirti purohit Jul 07 '21 at 03:11
  • I want all the sparse data into 1 single column – kirti purohit Jul 07 '21 at 03:12
  • That's exactly what @Mutaz-MSFT was trying [to ask](https://stackoverflow.com/questions/68279426/get-all-data-from-dataframe-to-a-single-column?noredirect=1#comment120673796_68279426) earlier. Please provide an adequate example of at least one row so that it becomes clear that exactly how all those columns should be combined into a single column. – AKS Jul 07 '21 at 03:15
  • Okay I have put an edit in the answer. Please check that @AKS – kirti purohit Jul 07 '21 at 03:17
  • Yes. That's the logic I want – kirti purohit Jul 07 '21 at 03:17
  • I see values like 'L:110:QQ:9,12' in the first row, it is not clear how that translate to 'a.b.c.d', please add clarity to explain the transformation you want to achieve with some examples from values in the original data frame. – Mutaz-MSFT Jul 07 '21 at 03:18
  • That's just a quick example of what values are like in ahead columns. I don't need translation. As you said, I need the logic for getting all the values in 1 column. Please check the edit in the answer – kirti purohit Jul 07 '21 at 03:19
  • Can you guys help? @AKS – kirti purohit Jul 07 '21 at 03:29
  • @Mutaz-MSFT Can you guys help? – kirti purohit Jul 07 '21 at 03:29
  • just posted an answer.. check if that works – Mutaz-MSFT Jul 07 '21 at 03:33

1 Answers1

2

here is an example

data file:

Index,Seatblock1,Seatblock2,Seatblock3
1,a.b.c.d,,
2,w.r.t.q,q.r.y.t,
3,p.y.u.i,,n.b.x.a

load df:

df = pd.read_csv('df1.csv')

enter image description here

melt and drop na

df.melt(id_vars=['Index']).value.dropna()

output:

0    a.b.c.d
1    w.r.t.q
2    p.y.u.i
4    q.r.y.t
8    n.b.x.a
Name: value, dtype: object
Mutaz-MSFT
  • 756
  • 5
  • 20