How to split mixed value columns?

user_1537657 0
Viewed 126

I have data coming in, in weird formats and I've been trying to search the internet for similar issues, with no luck, and digging through pandas documentation but maybe I am looking at wrong places.

For example I have a pandas DataFrame, shown below and it should always have at least these elements: person, items, type, count, value. Something like this:

           0     1      2      3
0             type  count  value
1   person 1                    
2     item a     5      5      5
3   person 2                    
4     item b     9      9      9
5   person 3                    
6     item a     1      2      3
7     item b     1      2      3
8     item c     1      2      3
9     item d     1      2      3
10               4      8     12

where first column contains both person name and items that belong to that person and person should be on it's own column. Each person can have 1 or more items.

I need to find a way to clean this up by moving person name to each item row where end results would look something like that:

          0       1     2      3      4
0      name    item  type  count  value
1  person 1  item a     5      5      5
2  person 2  item b     9      9      9
3  person 3  item a     1      2      3
4  person 3  item b     1      2      3
5  person 3  item c     1      2      3
6  person 3  item d     1      2      3

I could possibly figure out how to do it by looping over each row, check if type,count,value are empty to get the person name and then attach it to each following row until next row with empty type,count,value but is that really the only solution or is there anything more flexible?

Another format would be something like that:

          0              1      2      3
0            type and item  count  value
1  person 1       6 item a     20     30
2                 7 item d      -      -
3                              20     30
4  person 2       9 item d     80     90
5  person 3      10 item b     40     50
6                11 item a     10     20
7                              50     70
8  person 4       7 item c     50     60

and would need a solution to get output like this:

          0       1           2      3      4
0              type        item  count  value
1  person 1       6      item a     20     30
2  person 1       7      item d      -      -
4  person 2       9      item d     80     90
5  person 3      10      item b     40     50
6  person 3      11      item a     10     20
8  person 4       7      item c     50     60

My mind went towards using some DataFrame schema but I don't think such a thing exists?
Is there a more flexible solution, meaning I can pass few simple parameters to pandas and it would know how to label person and item separately or really the only solution is to solve it with looping and doing if/else?

DataFrames described in my examples for your convenience:

data = pd.DataFrame([
    ['', 'type', 'count', 'value'],
    ['person 1', '', '', ''],
    ['item a', '5', '5', '5'],
    ['person 2', '', '', ''],
    ['item b', '9', '9', '9'],
    ['person 3', '', '', ''],
    ['item a', '1', '2', '3'],
    ['item b', '1', '2', '3'],
    ['item c', '1', '2', '3'],
    ['item d', '1', '2', '3'],
    ['', '4', '8', '12'],
])

data2 = pd.DataFrame([
    ['', 'type and item', 'count', 'value'],
    ['person 1', '6 item a', '20', '30'],
    ['', '7 item d', '-', '-'],
    ['', '', '20', '30'],
    ['person 2', '9 item d', '80', '90'],
    ['person 3', '10 item b', '40', '50'],
    ['', '11 item a', '10', '20'],
    ['', '', '50', '70'],
    ['person 4', '7 item c', '50', '60'],
])
pandas dataframe python
1 Answers

For data

data[4]=data[0].where(data[1].eq('')).ffill()

data=data[data[0]!=data[4]]
data=data[data[0]!='']

data
        0  1  2  3         4
2  item a  5  5  5  person 1
4  item b  9  9  9  person 2
6  item a  1  2  3  person 3
7  item b  1  2  3  person 3
8  item c  1  2  3  person 3
9  item d  1  2  3  person 3

For data 2

data2=data2.join(data2[1].str.split(' ',n=1,expand=True),rsuffix='add')
data2=data2.dropna(subset=['1add'],axis=0)
data2['0']=data2['0'].mask(data2['0'].eq('')).ffill()
data2
          0              1      2      3  0add      1add
0       NaN  type and item  count  value  type  and item
1  person 1       6 item a     20     30     6    item a
2  person 1       7 item d      -      -     7    item d
4  person 2       9 item d     80     90     9    item d
5  person 3      10 item b     40     50    10    item b
6  person 3      11 item a     10     20    11    item a
8  person 4       7 item c     50     60     7    item c
user_79645270
Related