Shift cells left from a certain column, at a certain row

user_6210219 0
Viewed 84

I have a list of dataframes as follows:

df1 = pd.DataFrame( {0: {0: 'Liu Da Jing', 1: 'Shou tai yin', 2: 'Shouyang ming', 3: 'Zu yang ming', 4: 'Zu tai yin', 5: 'Shoushao yin', 6: 'Shou tai yang', 7: 'Zu tai yang', 8: 'Zu tai yin', 9: 'Shou jue yin', 10: 'Shou shao yang'}, 1: {0: '六大经', 1: '手太阴', 2: '手阳明', 3: '足阳明', 4: '足太阴', 5: '手少阴', 6: '手太阳', 7: '足太阳', 8: '足太阴', 9: '手厥阴', 10: '手少阳'}, 2: {0: None, 1: None, 2: None, 3: None, 4: None, 5: None, 6: None, 7: None, 8: None, 9: 'Xin bao jing /', 10: None}, 3: {0: None, 1: None, 2: None, 3: None, 4: None, 5: None, 6: None, 7: None, 8: None, 9: 'luo', 10: None}, 4: {0: None, 1: None, 2: float('nan'), 3: None, 4: None, 5: float('nan'), 6: None, 7: None, 8: None, 9: '心包经/络', 10: None}} )

df2 = pd.DataFrame( {0: {0: 'Fu', 1: 'Chen', 2: 'SanBuJiu Hou'}, 1: {0: '浮', 1: '沉', 2: '三部九侯(沿'}, 2: {0: None, 1: None, 2: '经脉循行的搏'}, 3: {0: None, 1: None, 2: '动'}} )

df_list = [df1, df2]

I need to shift df1.iloc[9,3:] left towards df1.iloc[9,2] by merging df1.iloc[9,3:] with df1.iloc[9,2]; likewise, I need to shift df2.iloc[2,1:] to the left and merge the string spanning 3 columns at df2.iloc[2,1].

When the issue is at the first column, for example (this is actually df1 prior to cleaning):

df3 = pd.DataFrame( {0: {0: 'Liu Da Jing', 1: 'Shou tai yin', 2: 'Shouyang', 3: 'Zu yang ming', 4: 'Zu tai yin', 5: 'Shoushao', 6: 'Shou tai yang', 7: 'Zu tai yang', 8: 'Zu tai yin', 9: 'Shou jue yin', 10: 'Shou shao yang'}, 1: {0: '六大经', 1: '手太阴', 2: 'ming', 3: '足阳明', 4: '足太阴', 5: 'yin', 6: '手太阳', 7: '足太阳', 8: '足太阴', 9: '手厥阴', 10: '手少阳'}, 2: {0: None, 1: None, 2: '手阳明', 3: None, 4: None, 5: '手少阴', 6: None, 7: None, 8: None, 9: 'Xin bao jing /', 10: None}, 3: {0: None, 1: None, 2: None, 3: None, 4: None, 5: None, 6: None, 7: None, 8: None, 9: 'luo', 10: None}, 4: {0: None, 1: None, 2: None, 3: None, 4: None, 5: None, 6: None, 7: None, 8: None, 9: '心包经/络', 10: None}} )

I could use the following code, which recursively tests for string consistency (Chinese characters or ASCII) across a single column, does the merging at column 1, then shifts the row over one period, obliterating the original value at column 0:

import pandas as pd
import re
from tabulate import tabulate

LHan = [[0x2E80, 0x2E99],    # Han # So  [26] CJK RADICAL REPEAT, CJK RADICAL RAP
        [0x2E9B, 0x2EF3],    # Han # So  [89] CJK RADICAL CHOKE, CJK RADICAL C-SIMPLIFIED TURTLE
        [0x2F00, 0x2FD5],    # Han # So [214] KANGXI RADICAL ONE, KANGXI RADICAL FLUTE
        0x3005,              # Han # Lm       IDEOGRAPHIC ITERATION MARK
        0x3007,              # Han # Nl       IDEOGRAPHIC NUMBER ZERO
        [0x3021, 0x3029],    # Han # Nl   [9] HANGZHOU NUMERAL ONE, HANGZHOU NUMERAL NINE
        [0x3038, 0x303A],    # Han # Nl   [3] HANGZHOU NUMERAL TEN, HANGZHOU NUMERAL THIRTY
        0x303B,              # Han # Lm       VERTICAL IDEOGRAPHIC ITERATION MARK
        [0x3400, 0x4DB5],    # Han # Lo [6582] CJK UNIFIED IDEOGRAPH-3400, CJK UNIFIED IDEOGRAPH-4DB5
        [0x4E00, 0x9FC3],    # Han # Lo [20932] CJK UNIFIED IDEOGRAPH-4E00, CJK UNIFIED IDEOGRAPH-9FC3
        [0xF900, 0xFA2D],    # Han # Lo [302] CJK COMPATIBILITY IDEOGRAPH-F900, CJK COMPATIBILITY IDEOGRAPH-FA2D
        [0xFA30, 0xFA6A],    # Han # Lo  [59] CJK COMPATIBILITY IDEOGRAPH-FA30, CJK COMPATIBILITY IDEOGRAPH-FA6A
        [0xFA70, 0xFAD9],    # Han # Lo [106] CJK COMPATIBILITY IDEOGRAPH-FA70, CJK COMPATIBILITY IDEOGRAPH-FAD9
        [0x20000, 0x2A6D6],  # Han # Lo [42711] CJK UNIFIED IDEOGRAPH-20000, CJK UNIFIED IDEOGRAPH-2A6D6
        [0x2F800, 0x2FA1D]]  # Han # Lo [542] CJK COMPATIBILITY IDEOGRAPH-2F800, CJK COMPATIBILITY IDEOGRAPH-2FA1D


def build_re():
    L = []
    for i in LHan:
        if isinstance(i, list):
            f, t = i
            try:
                f = chr(f)
                t = chr(t)
                L.append('%s-%s' % (f, t))
            except:
                pass  # A narrow python build, so can't use chars > 65535 without surrogate pairs!

        else:
            try:
                L.append(chr(i))
            except:
                pass

    RE = '[%s]' % ''.join(L)
    print('RE:', RE.encode('utf-8'))
    return re.compile(RE, re.UNICODE)


def shift_cells_left(row, col, test="zh"):
    s = df_clean.iloc[row, col]
    try:
        if test == "zh":  # test if string is in Chinese
            match = rez.search(s)
        else:
            match = re.search("[a-zA-Z ]", s)

    except TypeError:
        print(f"Non-string object {s} detected at row {row} column {col} of dataframe:\n {tabulate(df_clean)}.")
        match = True

    if not (match):
        df_clean.iloc[row, col] = df_clean.iloc[row, col - 1] + " " + s
        df_clean.iloc[row, :] = df_clean.iloc[row, :].shift(-1)
        shift_cells_left(row, col, test)
    else:
        return

# Run this code:

rez = build_re()
df_clean = df3

for row in df_clean.index:
    shift_cells_left(row, 1)

But the shift_cells_left() function would fail when applied to df1 because df.shift() only works across entire rows or columns.

for row in df_clean.index:
    shift_cells_left(row, 3)

Questions:

  1. How should I overcome this problem?
  2. Is there a more efficient way to clean a list of such dataframes (such as df_list above)?

The original dataframe to be cleaned looks like this:

df = pd.DataFrame( {'Chinese\r(pinyin)': {0: 'Liu Da Jing\r六大经', 1: 'Shou tai yin\r手太阴', 2: 'Shouyang\rming\r手阳明', 3: 'Zu yang ming\r足阳明', 4: 'Zu tai yin\r足太阴', 5: 'Shoushao\ryin\r手少阴', 6: 'Shou tai yang\r手太阳', 7: 'Zu tai yang\r足太阳', 8: 'Zu tai yin\r足太阴', 9: 'Shou jue yin\r手厥阴\rXin bao jing /\rluo\r心包经/络', 10: 'Shou shao yang\r手少阳'}, 'English': {0: '6 (paired)\rchannels\r• Tai Yang\r• Yang Ming\r• Shao Yang\r• Tai Yin\r• Shao Yin\r• Jue Yin', 1: 'LU–Lung\rchannel', 2: 'LI – Large In-\rtestinechan-\rnel', 3: 'ST – Stomach\rchannel', 4: 'SP–Spleen\rchannel', 5: 'HT–Heart\rchannel', 6: 'SI – Small In-\rtestinechan-\rnel', 7: 'UB–Urinary\rBladder chan-\rnel\r•Innerpa-\rthway\r•Outerpa-\rthway', 8: 'KI–Kidney\rchannel', 9: 'PC–Pericar-\rdium channel', 10: 'TW – Triple War-\rmer channel'}, 'French': {0: '6 grands mé-\rridiens\r• Tai Yang\r• Yang Ming\r• Shao Yang\r• Tai Yin\r• Shao Yin\r• Jue Yin', 1: 'P - poumon', 2: 'GI–grosin-\rtestin', 3: 'E - estomac', 4: 'Rt - rate', 5: 'C – cœur', 6: 'IG–intestin\rgrêle', 7: 'V – vessie\r• 1ère chaîne\rde V.\r• 2ème chaîne\rde V.', 8: 'R - rein', 9: 'MC – maître\rdu cœur et de\rlasexualité\roupéricarde\rouenveloppe\rdu coeur', 10: 'TR – Triple ré-\rchauffeur'}, 'Alternative names\rand notes': {0: 'Each paired channel divi-\rdes into a “hand” chan-\rnel and a “foot” channel,\re.g. the Yang Ming paired\rchannelcomprisesShou\rYang Ming (large instes-\rtine channel) and Zu Yang\rMing (stomach channel)', 1: float('nan'), 2: float('nan'), 3: float('nan'), 4: float('nan'), 5: float('nan'), 6: float('nan'), 7: float('nan'), 8: float('nan'), 9: 'ThePericardiumasa\rfunction is known as xin\rbao luo in Chinese, while\rthe Pericardium meridian\ror channel is xin bao jing', 10: float('nan')}} )

So we are only working with the first column above.

data-cleaning dataframe python-3.x
0 Answers
Related