logo
down
shadow

Python - Split multiple columns into multiple rows


Python - Split multiple columns into multiple rows

Content Index :

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 : facebook icon twitter icon

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
Related Posts Related QUESTIONS :
  • Problem with clicking select2 dropdownlist in selenium
  • pandas dataframe masks to write values into new column
  • How to click on item in navigation bar on top of page using selenium python?
  • Add multiple EntityRuler with spaCy (ValueError: 'entity_ruler' already exists in pipeline)
  • error when replacing missing ')' using negative look ahead regex in python
  • Is there a way to remove specific strings from indexes using a for loop?
  • select multiple tags by position in beautifulSoup
  • pytest: getting AttributeError: 'CaptureFixture' object has no attribute 'readouterror' capturing stdout
  • Shipping PyGObject/GTK+ app on Windows with MingW
  • Python script to deduplicate lines in multiple files
  • How to prevent window and widgets in a pyqt5 application from changing size when the visibility of one widget is altered
  • How to draw stacked bar plot from df.groupby('feature')['label'].value_counts()
  • Python subprocess doesn't work without sleep
  • How can I adjust 'the time' in python with module Re
  • Join original np array with resulting np array in a form of dictionary? multidimensional array? etc?
  • Forcing labels on histograms in each individual graph in a figure
  • For an infinite dataset, is the data used in each epoch the same?
  • Is there a more efficent way to extend a string?
  • How to calculate each single element of a numpy array based on conditions
  • How do I change the width of Jupyter notebook's cell's left part?
  • Measure distance between lat/lon coordinates and utm coordinates
  • Installing megam for NLTK on Windows
  • filter dataframe on each value of a samn column have a specific value of another column in Panda\Python
  • Threading with pubsub throwing AssertionError: 'callableObj is not callable' in wxPython
  • Get grouped data from 2 dataframes with condition
  • How can I import all of sklearns regressors
  • How to take all elements except the first k
  • Whats wrong with my iteration list of lists from csv
  • Tensorflow Estimator API save image summary in eval mode
  • How to Pack with PyQt - how to make QFrame/Layout adapt to content
  • How do I get certain Time Range in Python
  • python doubly linked list - insertAfter node
  • Open .h5 file in Python
  • Joining a directory name with a binary file name
  • python, sort list with two arguments in compare function
  • Is it possible to print from Python using non-ANSI colors?
  • Pandas concat historical data using date minus some number of days
  • CV2: Import Error in Python OpenCV
  • Is it possible to do this loop in a one-liner?
  • invalid literal for int() with base 10: - django
  • Why does my code print a value that I have not assigned as yet?
  • the collatz func in automate boring stuff with python
  • How to find all possible combinations of parameters and funtions
  • about backpropagation deep neural network in tensorflow
  • Sort strings in pandas
  • How do access my flask app hosted in docker?
  • Replace the sentence include some text with Python regex
  • Counting the most common element in a 2D List in Python
  • logout a user from the system using a function in python
  • mp4 metadata not found but exists
  • Django: QuerySet with ExpressionWrapper
  • Pandas string search in list of dicts
  • Decryption from RSA encrypted string from sqlite is not the same
  • need of maximum value in int
  • a list of several tuples, how to extract the same of the first two elements in the small tuple in the large tuple
  • Display image of 2D Sinewaves in 3D
  • how to prevent a for loop from overwriting a dictionary?
  • How To Fix: RuntimeError: size mismatch in pyTorch
  • Concatenating two Pandas DataFrames while maintaining index order
  • Why does this not run into an infinite loop?
  • shadow
    Privacy Policy - Terms - Contact Us © scrbit.com