Python - Split multiple columns into multiple rows

Content Index :

Python - Split multiple columns into multiple rows
Access/SQL - split multiple columns to multiple rows based on column ID
python split data frame columns into multiple rows
Split multiple fields or columns of a single row and create multiple rows using Scala
Python Pandas: Split slash separated strings in two or more columns into multiple rows
Split (Explode) String into Multiple columns and rows - Python

Python - Split multiple columns into multiple rows

Tag : python , By : vdavidovski

Date : November 28 2020, 04:01 AM

it helps some times I am trying to split multiple columns into multiple rows in few lines rather than writing a big 'def function' for it. I have 2 columns that need splitting by ;. I am trying to follow this: Split cell into multiple rows in pandas dataframe , A product is more appropriated here :

import itertools

def splitter(values):
    return list(itertools.product( *[str(v).split(';') for v in values]))

In [6]: splitter(df.iloc[2]) #example
Out[6]: 
[('g', '15', 'pinneapple'),
 ('e', '15', 'pinneapple'),
 ('a', '15', 'pinneapple')]

def expand(df):
    tuples=list()
    for i,row in df.iterrows():
        tuples.extend(splitter(row))
    return  pd.DataFrame.from_records(tuples,columns=df.columns)

In [7]: expand(df)
   var  var2        var3
0     a    1      apples
1     a    1       mango
2     b    1      apples
3     b    1       mango
4     c    1      apples
5     c    1       mango
6     d    2        kiwi
7     d    2   pineapple
8     e    2        kiwi
9     e    2   pineapple
10    f    2        kiwi
11    f    2   pineapple
12    g   15  pinneapple
13    e   15  pinneapple
14    a   15  pinneapple
15    m   12      orange

Comments

No Comments Right Now !

Boards Message :

You Must Login Or Sign Up to Add Your Comments .

Share :

Tags :

Python - Split multiple columns into multiple rows , python split data frame columns into multiple rows , Split (Explode) String into Multiple columns and rows - Python , Python Pandas: Split slash separated strings in two or more columns into multiple rows , how to freeze multiple rows and columns in excel ,

Access/SQL - split multiple columns to multiple rows based on column ID

Tag : sql , By : jim

Date : March 29 2020, 07:55 AM

Any of those help I've tried a few things but can't get this to work efficiently. Help! , I would use this table structure:

|BoardName|SensorId| ReadingTime | SensorValue | 
------------------------------------------------
|BoardA   |    1   | 1224201301  |          18 |
|BoardA   |    2   | 1224201301  |          24 |

python split data frame columns into multiple rows

Tag : python , By : alexmajy

Date : March 29 2020, 07:55 AM

it should still fix some issue This is open to bugs so use with caution:
Convert Product column to a collection of lists whose sizes are the same with the lists in other columns (say, column SKU. This will not work if the lists in SKU and Size are of different lengths)

df["Product"] = df["Product"].map(list) * df["SKU"].map(len)

Out[184]: 
                    SKU           Size       Product
0  [111, 222, 333, 444]  [XS, S, M, L]  [a, a, a, a]
1            [555, 666]         [M, L]        [b, b]

pd.DataFrame(df.sum().to_dict())
Out[185]: 
  Product  SKU Size
0       a  111   XS
1       a  222    S
2       a  333    M
3       a  444    L
4       b  555    M
5       b  666    L

cols_to_be_repeated = ["Product", "ProductType"]

na_df = df[pd.isnull(df["SKU"])].copy()

df.dropna(inplace = True)

for col in cols_to_be_repeated:
    df[col] = df[col].map(lambda x: [x]) * df["SKU"].map(len)

pd.concat([pd.DataFrame(df.sum().to_dict()), na_df])

        Product ProductType    SKU  Size
0       T-shirt         Top  111.0    XS
1       T-shirt         Top  222.0     S
2       T-shirt         Top  333.0     M
3       T-shirt         Top  444.0     L
4  Pant(Flared)     Bottoms  555.0     M
5  Pant(Flared)     Bottoms  666.0     L
2       Sweater         Top    NaN  None

Split multiple fields or columns of a single row and create multiple rows using Scala

Tag : scala , By : Julian Ivanov

Date : March 29 2020, 07:55 AM

To fix this issue You can convert DataFrame to Dataset[(String, String, String, String)] and flatMap:

import scala.util.Try

val df = Seq(
  ("A1", "B1", "C1", "D1"),
  ("A2", "B2,B3", "C2,C3", "D2,D3"),
  ("A1", "B4,B5,B6", "C4,C5,C6", "D4,D5,D6")
).toDF("x1", "x2", "x3", "x4")

// A simple sequence of expressions which allows us to flatten the results
val exprs = (0 until df.columns.size).map(i => $"value".getItem(i))

df.select($"x1", array($"x2", $"x3", $"x4")).as[(String, Seq[String])].flatMap {
  case (x1, xs) => 
    Try(xs.map(_.split(",")).transpose).map(_.map("x" +: _)).getOrElse(Seq())
}.toDF.select(exprs:_*)

// +--------+--------+--------+--------+
// |value[0]|value[1]|value[2]|value[3]|
// +--------+--------+--------+--------+
// |      A1|      B1|      C1|      D1|
// |      A2|      B2|      C2|      D2|
// |      A2|      B3|      C3|      D3|
// |      A1|      B4|      C4|      D4|
// |      A1|      B5|      C5|      D5|
// |      A1|      B6|      C6|      D6|
// +--------+--------+--------+--------+

val splitRow = udf((xs: Seq[String]) => 
   Try(xs.map(_.split(",")).transpose).toOption)

// Same as before but we exclude the first column
val exprs = (0 until df.columns.size - 1).map(i => $"xs".getItem(i))

df
  .withColumn("xs", explode(splitRow(array($"x2", $"x3", $"x4"))))
  .select($"x1" +: exprs: _*)

Python Pandas: Split slash separated strings in two or more columns into multiple rows

Tag : python , By : CodeOfficer

Date : March 29 2020, 07:55 AM

wish of those help Another solution with concat and join:

s1 = df.SUBJECT.str.split('/', expand=True).stack()
s2 = df.STUDENT.str.split('/', expand=True).stack()
print (s1)
0  0         Math
   1    Chemistry
   2      Biology
1  0      Geology
   1      Physics

print (s2)
0  0      Sam
   1    Peter
   2     Mary
1  0     John
dtype: object

df1 = pd.concat([s1,s2], axis=1, keys=('SUBJECT','STUDENT'))
        .ffill()
        .reset_index(level=1, drop=True)
print (df1)
     SUBJECT STUDENT
0       Math     Sam
0  Chemistry   Peter
0    Biology    Mary
1    Geology    John
1    Physics    John

df = df.drop(['SUBJECT','STUDENT'], axis=1)
       .join(df1)
       .reset_index(drop=True)[['SUBJECT', 'STUDENT', 'CITY','STATE']]
print (df)
     SUBJECT STUDENT         CITY STATE
0       Math     Sam  Los Angeles    CA
1  Chemistry   Peter  Los Angeles    CA
2    Biology    Mary  Los Angeles    CA
3    Geology    John       Boston    MA
4    Physics    John       Boston    MA

Split (Explode) String into Multiple columns and rows - Python

Tag : python , By : user183345

Date : March 29 2020, 07:55 AM

help you fix your problem Good afternoon, i am trying to split text in a column to a specfic format here is my table below , Given this dataframe:

   UserId                                Application
0       1       Grey Blue::Black Orange;White::Green
1       2  Yellow Purple::Orange Grey;Blue Pink::Red

df.Application.str.split(';', expand=True).stack().str.split('::', expand=True).reset_index().drop(columns=['level_0', 'level_1'])

               0             1
0      Grey Blue  Black Orange
1          White         Green
2  Yellow Purple   Orange Grey
3      Blue Pink           Red

result = df.set_index('UserId').Application.str.split(';', expand=True).stack().str.split('::', expand=True).reset_index().drop(columns=['level_1'])
result.columns = ['UserId', 'Application', 'Role']

   UserId    Application          Role
0       1      Grey Blue  Black Orange
1       1          White         Green
2       2  Yellow Purple   Orange Grey
3       2      Blue Pink           Red