0

Hi I currently have a function that is able to split values in a same cell that is delimited by a new line. However the function below only accepts me to pass through one column at a time was thinking if there is any other ways that I can pass it through multiple columns or in fact the whole dataframe.

A sample would be like this 

A         B        C 
1\n2\n3  2\n\5     A

The code is below

def tidy_split(df, column, sep='|', keep=False):
indexes = list()
new_values = list()
df = df.dropna(subset=[column])
for i, presplit in enumerate(df[column].astype(str)):
    values = presplit.split(sep)
    if keep and len(values) > 1:
        indexes.append(i)
        new_values.append(presplit)
    for value in values:
        indexes.append(i)
        new_values.append(value)
new_df = df.iloc[indexes, :].copy()
new_df[column] = new_values
return new_df

It currently works when I run 

df1 = tidy_split(df, 'A', '\n')

After running the function of selecting only column A

A   B     C
1   2\n5  A
2   2\n5  A
3   2\n5  A

I was hoping to be able to pass in more than just an accepted argument and in this case splitting column 'B' as well. Previously I have attempted passing in lambda or attempted using apply but it requires a positional argument which is 'column'. Would appreciate any help given! Was thinking if a loop is possible

EDIT: Desired output as each number refer to something important Before

   A        B     C    
1\n2\n3    2\n5   A

After

A   B   C
1   2   A
2   5   A
3  n/a  A
Denova
  • 71
  • 3

1 Answers1

1

Input:

   A        B     C
0  1\n2\n3  2\n5  A

Code:

import pandas as pd

cols = df.columns.tolist()

# create list in each cell by detecting '\n'
for col in cols:
    df[col] = df[col].apply(lambda x: str(x).split("\n"))

# empty dataframe to store result
dfs = pd.DataFrame()

# loop over rows to construct small dataframes
# and then accumulate each to the resulting dataframe 
for ind, row in df.iterrows():
    a_vals = row['A']
    b_vals = row['B'] + ["n/a"] * (len(a_vals) - len(row['B']))
    c_vals = row['C'] + [row['C'][0]] * (len(a_vals) - len(row['C']))
    temp = pd.DataFrame({'A': a_vals, 'B': b_vals, 'C': c_vals})
    dfs = pd.concat([dfs, temp], axis=0, ignore_index=True)

Output:

   A    B  C
0  1    2  A
1  2    5  A
2  3  n/a  A
gyoza
  • 2,112
  • 2
  • 12
  • 18
  • Hi thank you so much for your help, i have attempt running your code but apprently my dataset consist of integers and i have encountered an error 'int' object has no attribute split – Denova Jul 03 '18 at 06:41
  • Hi! Thank you It worked according to your desired output but apparently my desired output was slightly different I have edited my post at the bottom in regards to that as I was hoping for column B values to uniquely identify with column A values – Denova Jul 03 '18 at 07:12
  • Thank again however is it possible to apply the code to more than just column 'A' and 'B' as the dataset is rather large assuming 'C' is the index row – Denova Jul 03 '18 at 09:18