how to convert list of values of a coulmn and assign it 0 or 1

Question

Am trying to assign a one or zero if the particular item was purchased by that user. Below is the dataset used. Am getting '[' brackets when I use dummies.

DF:

user    items
A   [111,333,444]
B   [333, 444, 555]
C   [555, 111, 333]
D   [222,333, 333,333]
E   [111,333,444,555]
F   [222,555,111]

output :

    [111 222 [333 444 [555
A   1   0   1   1   0
B   0   0   1   1   1
C   1   0   1   0   1
D   0   1   1   0   0
E   1   0   1   1   1
F   1   1   0   0   1

CODE:

(df.set_index('user')['items'].str.get_dummies(','))

Please show us your code, what you have tried, and where you need help — Edwin van Mierlo, Feb 15 '18 at 08:47
Could you show the code that how are you are creating this dataframe — Arpit Solanki, Feb 15 '18 at 08:48
in samples `111, 333, 444` are spaces between? Or it is `111,333,444` ? — jezrael, Feb 15 '18 at 08:51

jezrael · Accepted Answer · 2018-02-15T08:53:43.927

1

Use set_index + get_dummies for indicators and last reshape by stack + reset_index:

df = (df.set_index('user')['items']
        .str.get_dummies(',')
        .stack()
        .reset_index(name='Y/N')
        .rename(columns={'level_1':'item'}))

print (df)
   user item  Y/N
0     A  111    1
1     A  222    0
2     A  333    1
3     A  444    1
4     A  555    0
5     B  111    0
6     B  222    0
7     B  333    1
8     B  444    1
9     B  555    1
10    C  111    1
11    C  222    0
12    C  333    1
13    C  444    0
14    C  555    1
15    D  111    0
16    D  222    1
17    D  333    1
18    D  444    0
19    D  555    0
20    E  111    1
21    E  222    0
22    E  333    1
23    E  444    1
24    E  555    1
25    F  111    1
26    F  222    1
27    F  333    0
28    F  444    0
29    F  555    1

Detail:

print (df.set_index('user')['items'].str.get_dummies(','))
      111  222  333  444  555
user                         
A       1    0    1    1    0
B       0    0    1    1    1
C       1    0    1    0    1
D       0    1    1    0    0
E       1    0    1    1    1
F       1    1    0    0    1

edited Feb 15 '18 at 08:53

answered Feb 15 '18 at 08:48

jezrael

822,522
95
1,334
1,252

You can add [`strip`](http://pandas.pydata.org/pandas-docs/stable/generated/pandas.Series.str.strip.html) like `(df.set_index('user')['items'].str.strip('[]').str.get_dummies(','))` – jezrael Feb 15 '18 at 09:10
userRatings= df[df.user== A][[1,2]] to pull out Y/N .. its trowing me an error. index out of bounds – pylearner Feb 15 '18 at 12:56
What so you need? – jezrael Feb 15 '18 at 12:56
`[1,2]` is position of columns? – jezrael Feb 15 '18 at 12:57
I think you need `df.loc[['A'], df.columns[[0,1]]]` – jezrael Feb 15 '18 at 13:02
Can you answer to this ...https://stackoverflow.com/questions/48883138/how-to-convert-rows-to-columns-with-the-frequency-of-its-values – pylearner Feb 20 '18 at 10:44

how to convert list of values of a coulmn and assign it 0 or 1

1 Answers1