Pandas - creating multiple columns similar to pd.get_dummies

Say my data looks like this:

df = pd.DataFrame({'color': ['red', 'blue', 'green', 'red', 'blue', 'blue'], 'line': ['sunday', 'sunday', 'monday', 'monday', 'monday', 'tuesday'],
               'group': ['1', '1', '2', '1', '1', '1'], 'value': ['a', 'b', 'a', 'c', 'a', 'b']})

    color   group   line    value
0   red       1     sunday   a
1   blue      1     sunday   b
2   green     2     monday   a
3   red       1     monday   c
4   blue      1     monday   a
5   blue      1    tuesday   b

Essentially, I want to get a list of strings for each color. For example, I want the color red to display each row and the value associated with it in its column. The trick is that I also want to show other lines related to colors from the same group. Corresponding values ​​will be "invalid" for them. Thus, I want my output to look like this:

    color   line_1  line_1_value    line_2  line_2_value    line_3     line_3_value
0   red     sunday       a          monday       c          tuesday    not eligible
1   blue    sunday       b          monday       a          tuesday         b
2   green   monday       c      

There are about ~ 50,000 unique “colors” that I need to make for this. I am sure that this is something relatively simple, but I do not yet have the knowledge or skills to understand this. Any help would be appreciated!

+4
2

, , , :

df = df.drop('group', axis=1)
df['index_by_color'] = df.groupby('color').cumcount()

   color     line value  index_by_color
0    red   sunday     a               0
1   blue   sunday     b               0
2  green   monday     a               0
3    red   monday     c               1
4   blue   monday     a               1
5   blue  tuesday     b               2

pivot_table, :

df.pivot_table(index='color', columns=['index_by_color'], aggfunc=lambda x:x.iloc[0])

                  line                  value
index_by_color       0       1        2     0     1     2
color
blue            sunday  monday  tuesday     b     a     b
green           monday    None     None     a  None  None
red             sunday  monday     None     a     c  None

aggfunc=lambda x:x.iloc[0] , , .

:

 res = res.sort_index(axis=1, level=1)

                  line value    line value     line value
index_by_color       0     0       1     1        2     2
color
blue            sunday     b  monday     a  tuesday     b
green           monday     a    None  None     None  None
red             sunday     a  monday     c     None  None

, , cumcount()+1, 1 0, / , res.columns =['_'.join([l0, str(l1)]) for l0,l1 in res.columns] , ..

0

dfs :

df['count'] = df.groupby('color').cumcount() + 1

pvt1 = df.pivot(columns='count', index='color', values='line').reset_index().fillna('')
pvt1.columns = ['color'] + ['line_'+str(c) for c in pvt1.columns[1:]]

pvt2 = df.pivot(columns='count', index='color', values='value').reset_index().fillna('')
pvt2.columns = ['color'] + ['line_'+str(c)+'_value' for c in pvt2.columns[1:]]

pvtdf = pd.merge(pvt1, pvt2, on=['color'])
pvtdf = pvtdf[[c for c in sorted(pvtdf.columns)]]

#    color  line_1 line_1_value  line_2 line_2_value   line_3 line_3_value
# 0   blue  sunday            b  monday            a  tuesday            b
# 1  green  monday            a                                           
# 2    red  sunday            a  monday            c                      
0

Source: https://habr.com/ru/post/1653942/


All Articles