0

I have a some csv data which I want to reformat as

enter image description here

I want to change this format with Data1-4 as column names and Value 1-4 as values besides data A and B

I have millions of rows, where I don't want to loop it. I am working with python data frame.

Please suggest the best way to do this as looping for millions loop will take huge time and I want to complete the task in the best possible way in terms of performance.

Some more sample data which I am trying to do:

enter image description here enter image description here

Improve this question
2
  • could you edit your post a little and reexplain your current format / expected format. It is difficult to see the difference as it is. Commented Sep 13, 2018 at 7:03
  • 1
    Sorry for the Format provided earlier. Updated the current and expected format. Commented Sep 13, 2018 at 7:16

1 Answer 1

Reset to default
2

If input is Series with 3 level MultiIndex use Series.unstack:

print (type(s))
<class 'pandas.core.series.Series'>
print (s.index.nlevels)
3

df = s.unstack(fill_value=0)

Or if input is 4 column DataFrame first repalce missing values in first 2 columns by forward filling and then reshape by DataFrame.set_index with Series.unstack:

print (type(df))
<class 'pandas.core.frame.DataFrame'>
print (len(df.columns))
4

df.columns = ['Col1','Col2','Col3','Col4']
cols = ['Col1','Col2']
df[cols] = df[cols].ffill()
df = df.set_index(['Col1','Col2','Col3'])['Col4'].unstack(fill_value=0)
Improve this answer
Sign up to request clarification or add additional context in comments.

9 Comments

above code gives me some idea to approach but doesn't gives the desired output. I need to transpose 3rd and 4th column of my current format and this will add a separate column for each data I have. In my above example, I am giving input of 4 columns where I want to transpose last two columns. As I am having 4 rows of data so my expected format will have 2+4 columns. This will vary if the number of data increases.
@Vaybhava - Are data confidental? If not, is possible share df.to_pickle('data.pkl') ?
Added few screen shots which may be useful. I created this as a sample data as the data I have is highly confidential.
@Vaybhava - OK, is possible send me sample not confidental sample data saved in data.pkl to my eamil or send link to dropbox or gdocs ?
@Vaybhava - Also coupon have no value?
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.