1

I am fetching data from a source into a pandas data frame, it is like below

date currency price
d1 INR 31
d2 INR 32
d2 USD 21
d3 USD 41
d3 INR 51
d3 JPY 61

I want to convert it to below

date INR JPY USD
d1 31 0 0
d2 32 0 21
d3 51 61 42
Elric
  • 84
  • 1
  • 9
  • 1
    Hi Elric. Please provide us example code producing the sample data. And you can look into pandas `pivot()` function. Don't only tell us what you want but what you have tried so far! – buhtz Jun 06 '22 at 08:13

2 Answers2

3

You can use pivot to rotate your data around the currency column, then use fillna to replace NaN values with 0, and then finally reset_index and rename_axis to clean up the output:

df.pivot(index='date', columns='currency', values='price') \
.fillna(0) \
.reset_index() \
.rename_axis(None, axis=1)

Output:

  date  INR   JPY   USD
0  d1   31.0   0.0   0.0
1  d2   32.0   0.0  21.0
2  d3   51.0  61.0  41.0
Nick
  • 138,499
  • 22
  • 57
  • 95
  • Hi @Nick, thanks for your answer. I made a mistake in my questions initially, it is 'date' col and not 'data' I corrected it later. Now when I use your solution, I get below error. raise ValueError("Index contains duplicate entries, cannot reshape") ValueError: Index contains duplicate entries, cannot reshape – Elric Jun 06 '22 at 15:39
  • @Elric Since this question has been closed, I suggest you ask a new one, including some data which demonstrates the problem – Nick Jun 06 '22 at 23:52
1

Use pivot() to reshape the DataFrame, then transpose it with T.

df.pivot(index="currency", columns="data", values="price").T

Output:

currency    INR   JPY   USD
data            
d1          31.0  NaN   NaN
d2          32.0  NaN   21.0
d3          51.0  61.0  41.0
965311532
  • 506
  • 3
  • 14
  • 1
    Please improve your answer. Explain it. Explain the steps. What happens before `T` and how such sub results look like. – buhtz Jun 06 '22 at 08:16