Rearrange a column to multiple columns

Question

I am new to Python and am having problems. I have the following data framework:

import pandas as pd
data = {'v1':('Belgium[country]', 'Antwerp[city]', 'Gent[city]', 'France[country]', 'Paris[city]', 'Marseille[city]', 'Toulouse[city]', 'Spain[country]', 'Madrid[city]')}
df = pd.DataFrame(data)
df

   v1
0  Belgium[country]
1  Antwerp[city]
2  Gent[city]
3  France[country]
4  Paris[city]
5  Marseille[city]
6  Toulouse[city]
7  Spain[country]
8  Madrid[city]

What I want to display in the following format:

   v1                v2
0  Belgium[country]  Antwerp[city]
1  Belgium[country]  Gent[city]
2  France[country]   Paris[city]
3  France[country]   Marseille[city]
4  France[country]   Toulouse[city]
5  Spain[country]    Madrid[city]

I have found a way to do this with a dictionary, but since I want to maintain order, I am looking for a way to do this using a list or so.

I tried this both on the basis of the indices and on the values ​​themselves (in particular [country] and [city]), but I could not cope with them. Any help is much appreciated!

Answer 1

This will work:

counter = df['v1'].str.contains('country').cumsum()
result = df.groupby(counter).apply(lambda g: g[1:]).reset_index(level=1, drop=True)
result = result.rename(columns={'v1': 'v2'}).reset_index(drop=False)
result['v1'] = result['v1'].replace(df.groupby(counter).first().squeeze())

The idea is to add a counter that increases for each new country. You can then group this counter to access the information you need.

, (g[1:] g). . , ( ), v1.

Answer 2

groupby:

#rename columns
df = df.rename(columns={'v1':'v2'})
#get counter
counter= df.v2.str.contains('country').cumsum()
#get mask where are changed country to city
df.insert(0, 'v1', df.loc[counter.ne(counter.shift()), 'v2'])
#forward filling NaN
df.v1 = df.v1.ffill()
#remove rows where v1 == v2
df = df[df.v1.ne(df.v2)].reset_index(drop=True)

print (df)
                 v1               v2
0  Belgium[country]    Antwerp[city]
1  Belgium[country]       Gent[city]
2   France[country]      Paris[city]
3   France[country]  Marseille[city]
4   France[country]   Toulouse[city]
5    Spain[country]     Madrid[city]

In [189]: %timeit (jez(df))
100 loops, best of 3: 2.47 ms per loop

In [191]: %timeit (IanS(df1))
100 loops, best of 3: 5.06 ms per loop

:

def jez(df):
    df = df.rename(columns={'v1':'v2'})
    counter= df.v2.str.contains('country').cumsum()
    df.insert(0, 'v1', df.loc[counter.ne(counter.shift()), 'v2'])
    df.v1 = df.v1.ffill()
    df = df[df.v1.ne(df.v2)].reset_index(drop=True)

    return (df)

def IanS(df):
    counter = df['v1'].str.contains('country').cumsum()
    result = df.groupby(counter).apply(lambda g: g[1:]).reset_index(level=1, drop=True)
    result = result.rename(columns={'v1': 'v2'}).reset_index(drop=False)
    result['v1'] = result['v1'].replace(df.groupby(counter).first().squeeze())
    return (result)