Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Pandas combining sparse columns in dataframe

I am using Python, Pandas for data analysis. I have sparsely distributed data in different columns like following

| id | col1a | col1b | col2a | col2b | col3a | col3b |
|----|-------|-------|-------|-------|-------|-------|
|  1 |   11  |   12  |  NaN  |  NaN  |  NaN  |  NaN  |
|  2 |  NaN  |  NaN  |   21  |   86  |  NaN  |  NaN  |
|  3 |   22  |   87  |  NaN  |  NaN  |  NaN  |  NaN  |
|  4 |  NaN  |  NaN  |   NaN |  NaN  |  545  |   32  |

I want to combine this sparsely distributed data in different columns to tightly packed column like following.

| id | group |  cola |  colb |
|----|-------|-------|-------|
| 1  |  g1   |   11  |   12  |
| 2  |  g2   |   21  |   86  |
| 3  |  g1   |   22  |   87  |
| 4  |  g3   |  545  |   32  |

What I have tried is doing following, but not able to do it properly

df['cola']=np.nan
df['colb']=np.nan
df['cola'].fillna(df.col1a,inplace=True)
df['colb'].fillna(df.col1b,inplace=True)
df['cola'].fillna(df.col2a,inplace=True)
df['colb'].fillna(df.col2b,inplace=True)
df['cola'].fillna(df.col3a,inplace=True)
df['colb'].fillna(df.col3b,inplace=True)

But I think there must be more concise and efficient way way of doing this. How to do this in better way?

like image 440
Prabhu Avatar asked Jun 07 '20 16:06

Prabhu


People also ask

How to combine two text columns into one in a pandas Dataframe?

You can use the following syntax to combine two text columns into one in a pandas DataFrame: df ['new_column'] = df ['column1'] + df ['column2'] If one of the columns isn’t already a string, you can convert it using the astype (str) command: df ['new_column'] = df ['column1'].astype(str) + df ['column2'] And you can use the following syntax to ...

How do I work with sparse data in pandas?

In older versions of pandas, the SparseSeries and SparseDataFrame classes (documented below) were the preferred way to work with sparse data. With the advent of extension arrays, these subclasses are no longer needed. Their purpose is better served by using a regular Series or DataFrame with sparse values instead.

How to coalesce values from multiple columns of a pandas Dataframe?

You can use the following methods to coalesce the values from multiple columns of a pandas DataFrame into one column: The following code shows how to coalesce the values in the points, assists, and rebounds columns into one column, using the first non-null value across the three columns as the coalesced value:

Can a Dataframe have a sparse column?

In a SparseDataFrame, all columns were sparse. A DataFrame can have a mixture of sparse and dense columns. As a consequence, assigning new columns to a DataFrame with sparse values will not automatically convert the input to be sparse.


1 Answers

You can use df.stack() assuming 'id' is your index else set 'id' as index. Then use pd.pivot_table.

df = df.stack().reset_index(name='val',level=1)
df['group'] = 'g'+ df['level_1'].str.extract('col(\d+)')
df['level_1'] = df['level_1'].str.replace('col(\d+)','')
df.pivot_table(index=['id','group'],columns='level_1',values='val')

level_1    cola  colb
id group
1  g1      11.0  12.0
2  g2      21.0  86.0
3  g1      22.0  87.0
4  g3     545.0  32.0
like image 95
Ch3steR Avatar answered Nov 10 '22 17:11

Ch3steR