-1

I am trying to scrape tables from a web page. The web page contains links to data in tables. Basically, I am writing a for loop to get the table from each link and concatenating with the other table. To do this I need to transpose each data point as a column.

df= 2004 | 2006 | 2007 | 2008 ------------------------ GrowthRate 5% | 7% |-5% | 5% Earnings 234 | 449 | -300 | 448 EPS 17.6 |11.8 | 16.8 | 500 P\E 14.08|12.04 |11.37 | 348

I want to make it as

df = GrowthRate_2004 GrowthRate_2006 GrowthRate_2007 GrowthRate_2008 Earnings_2004 Earnings_2006 Earnings_2007 Earnings_2008 EPS_2004 EPS_2006 EPS_2007 EPS_2008 P/E_2004 P/E_2006 P/E_2007 P/E_2008 5% 7% -5% 5% 234 449 -300 448 17.6 11.8 16.8 500 14.08 12.04 11.37 348

enter image description here is there any easy way to do this?

Durga Gaddam
  • 335
  • 3
  • 14
  • Are you using Python or R? Your question should *not* be using both tags. Please also provide the code from where you tried to do this on your own before you asked the question (or temporarily delete the question until you're at that point). And yes... there are easy ways to do this in both languages which you should be able to find pretty easily with a search... – Hack-R Sep 24 '17 at 16:59
  • Either of the languages is fine with me. Python is preferred. @Hack-R I have tried doing transpose as `df.T` in python, but I am not getting any idea how to transpose all the datapoints – Durga Gaddam Sep 24 '17 at 17:05
  • I know that you're new, so I'm trying to help you understand what kinds of questions we allow. You should have already written code before you ask a question here. It's not a code writing service. You should know if you're using Python or R. You are also required to research for existing questions/answer so you can find the obvious answers on how to transpose a table... – Hack-R Sep 24 '17 at 17:10
  • https://stackoverflow.com/questions/6778908/r-transposing-a-data-frame – Hack-R Sep 24 '17 at 17:11
  • https://stackoverflow.com/questions/29374385/using-unstack-in-python – Hack-R Sep 24 '17 at 17:11
  • https://stackoverflow.com/questions/25318768/stack-unstack-multi-index-pivot-table-in-python-pandas – Hack-R Sep 24 '17 at 17:11
  • @Hack-R You are being so rude... – Durga Gaddam Sep 24 '17 at 17:16
  • Actually, no I'm not. We get questions like yours all the time and there are specific things we say to the OP (you), which is what I've done above and in thousands of other cases. We're telling you the rules and pointing out that you did not due sufficient work or research before asking your question, resulting in a *very low quality* question that dilutes the value of Stack Overflow. You'll notice that I edited your question to improve its quality as much as I could. That's part of the overall theme of helping you improve the quality of your questions on Stack Overflow. – Hack-R Sep 24 '17 at 17:20

2 Answers2

1

A sketch of the answer (for python) would be:

  • read the data into a pandas.DataFrame
  • with df.unstack() you get the form you are looking for
piiipmatz
  • 400
  • 1
  • 10
1

Maybe not so elegant solution, but works:

df_ = pd.DataFrame(data = np.broadcast_to(df.columns.values.reshape(-1, 1).astype(str), df.shape), index = df.columns.values, columns = df.index.values)
df_ = pd.get_dummies(df_)
df_ = pd.DataFrame(data = df.values.flatten().reshape(1, -1), columns=df_.columns)
df_ = pd.DataFrame(data = np.broadcast_to(df.index.values.reshape(-1, 1), df.shape), columns = df.columns)

where df is the dataframe you're converting from.

Kamil Kaczmarek
  • 434
  • 3
  • 5