CODEWITHSUNDEEP
pythonpandasnumpyautomation
Harish reddy
Harish reddy

Reputation: 431

Create a number of column depending on the values of one column-Python,Pandas

I have a columns with numbers separated by comma now the values should be split into new columns.

Values
'456,567,67,96'
'67,987'

The new Dataframe should look like:

Values              Value1  Value2  Value3  Value4
'456,567,67,96'     456     567      67      96
'67,987'            67      987
'4321,96,912        4321    87       912

Also an empty column next to each column to map the numbers with the name.

Values Name 
4321   EB_Meter
987    EB_Meter987
912    DG_Meter912
567    Ups_Meter567
456    Ups_Meter456
96     DG_Meter96
67     DGB_Meter

So the final DataFrame is:

Values              Value1  Name1           Value2   Name2         Value3 Name3      Value4 Name4
'456,567,67,96'     456     Ups_Meter456    567      Ups_Meter567  67     DGB_Meter   96   DG_Meter96
'67,987'            67      DGB_Meter       987      EB_Meter987
'4321,96,912        4321    EB_Meter        96       DG_Meter96    912    DG_Meter912

Upvotes: 0

Views: 429

Answers (1)

Quang Hoang
Quang Hoang

Reputation: 150785

For the first output:

splits = df.Values.str.split(',', expand=True)
out = df.join(splits.add_prefix('Value'))

Output:

          Values Value0 Value1 Value2 Value3
0  456,567,67,96    456    567     67     96
1         67,987     67    987   None   None
2     4321,96,91   4321     96     91   None

For the later output:

melt_splits = splits.reset_index().melt('index')

melt_splits['value'] = melt_splits['value'].map(df2.set_index('Values')['Name'])

out = out.join(melt_splits.pivot('index','variable', 'value').add_prefix('Name'))

Output:

    Values           Value0    Value1    Value2    Value3  Name0         Name1         Name2      Name3
--  -------------  --------  --------  --------  --------  ------------  ------------  ---------  ----------
 0  456,567,67,96       456       567        67        96  Ups_Meter456  Ups_Meter567  DGB_Meter  DG_Meter96
 1  67,987               67       987                      DGB_Meter     EB_Meter987   nan        nan
 2  4321,96,91         4321        96        91            EB_Meter      DG_Meter96    nan        nan

Upvotes: 2

Related Questions