如何将n行的行移到列？

Question

我有一个1列，比如：

[col1]
area123
account,time,day,total,users
code1,50s,monday,5,6
code2,40s,monday,5,6
area234
account,time,day,total,users
code5,20s,monday,4,9
code2,40s,monday,2,6
area26
.
.
.

如何使用新的列作为区域将其分隔为多行，使其看起来像：

enter code here

[area]     [account]    [time]    [day]   [totals]   [users]
 area123     code1        50s     monday    5          6
 area123     code2        40s     monday    5          6
 area234     code5        20s     monday    4          9
 area234     code2       40s      monday    2          6
    .          .          .          .      .          .
    .          .          .          .      .          .

注意：数据每4行重复一次这个结构，列名在第二行，第三行是用逗号分隔的值。所以每个区域都应该转换成2行。你知道吗

我在想用regex把数据按“area”之类的字符串分开。你知道吗

任何帮助或指导都会很好。你知道吗

提前谢谢

Answer 1

numpy操纵

from numpy.core.defchararray import split as csplit

c = df.col1.values

i = pd.Index(c[::4].repeat(len(c) // 4), name='area')
j = csplit(c[1], ',').tolist()
v = csplit(c.reshape(-1, 2)[1::2].ravel().astype(str), ',').tolist()

pd.DataFrame(v, i, j).reset_index()

      area account time     day total users
0  area123   code1  50s  monday     5     6
1  area123   code2  40s  monday     5     6
2  area234   code5  20s  monday     4     9
3  area234   code2  40s  monday     2     6

Answer 2

您可以先通过模来创建numpy array。你知道吗

• ^{}第一个位置的新列，区域值由^{}和^{}方法ffill创建
• 通过^{}和^{}删除带2,3的行
• 按^{}删除列，按^{}创建新列

---

a = np.arange(len(df.index)) % 4
print (a)
[0 1 2 3 0 1 2 3 0]

df.insert(0, 'area', df['col'].mask(a != 0).ffill())
df = df[np.in1d(a, [2,3])].reset_index(drop=True)
df[['account','time','day','total', 'users']] = df.pop('col').str.split(',', expand=True)
print (df)

      area account time     day total users
0  area123   code1  50s  monday     5     6
1  area123   code2  40s  monday     5     6
2  area234   code5  20s  monday     4     9
3  area234   code2  40s  monday     2     6

更一般的解决方案：

mask = df['col'].str.contains(',')
df.insert(0, 'area', df['col'].mask(mask).ffill())
df = df.iloc[0:]
df = df[~((df['col'] == df['area'])|df['col'].str.contains('account,time,day,total,users'))]
df[['account','time','day','total', 'users']] = df.pop('col').str.split(',', expand=True)
print (df)
      area account time     day total users
2  area123   code1  50s  monday     5     6
3  area123   code2  40s  monday     5     6
6  area234   code5  20s  monday     4     9
7  area234   code2  40s  monday     2     6